MySQL : Cannot connect to database server

MySQLwindows-vista

I downloaded MySQL last week and I've been having a ton of problems getting things to work. I ended up reinstalling it and setting the port to 3307 because for some reason 3306 was greyed out. When I installed it I got an error that the server wasn't configured, but I was able to get a connection working just fine.

Now (a few days later) I'm trying to restart that connection and I keep getting this error:

Cannot Connect to Database Server – Your connection attempt failed for user 'root' from your host to server at 127.0.0.1:3306: Can't connect to MySQL server on '127.0.0.1' (10061).

I've tried adding exceptions to my firewall to ports 3306 and 3307 and running a new instance configuration wizard (which crashed when it tried to start a service). I'm really new to MySQL and could definitely use help! Thanks!

Best Answer

I've had that happen (on XP and 7), and the easiest way was to use WAMP or XAMPP. If those aren't options (all of these have worked for me at some point):

See if you can't just set the port for the connection request. You might just need mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password'); (not sure you're using PHP, but you can set the port on most language DB interfaces)
restart the MySQL service
use netstat -n to see if there are anything on those ports. It might be that MySQL is running on 3307 try that.
tasklist and see if MySQL is open twice. If it is, kill both instances and then restart the server.
use taskkill to kill MySQL. Open MySQL. repeat above step.

I always end this type of post in a similar manner -- The spectral wolf fears only fire. The Google maps team can no longer help you, but if you master the wolf, he will guide you. Godspeed.

Related Solutions

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

SSH Tunnel – Cannot SSH Tunnel with PuTTY (Windows Vista) to Connect to MySQL on a Linux Box

It sounds like you've got it "backwards". The destination is not your loopback, it's the remote machine on the other network. That, or you're using Remote instead of Local for the tunnel.

Try flipping the IP addresses around, example:

putty.exe -L 3307:mysql.yourdomain.com:3306

Where mysql.yourdomain.com is the name of the host that has the MySQL service you're trying to contact. When putty pops up the connection dialog, put in the address, connect, and log in. Then connect to localhost:3307 using the Administrator tool.

Best Answer

Related Solutions

MySQL – Export and Import Privileges to a New Server

SSH Tunnel – Cannot SSH Tunnel with PuTTY (Windows Vista) to Connect to MySQL on a Linux Box

Related Topic