MySQL unable to connect with remote server

mysql networking port windows-server-2008-r2 firewall
TBA picture TBA · Feb 7, 2014 · Viewed 81.4k times · Source

We have a MySQL server in one of the remote Virtual Machine (Windows Server 2008). Till yesterday we were able to connect to the MySQL server, with the help of workbench installed in our local machine.

Yesterday there was a restart to the machine which has the Virtual Machine installed. After that we are unable to connect to MYSQL. Though I can ping and remote connect this particular VM. I can even execute the queries inside the workbench installed in the VM.

I am not too good at networking or security related stuffs. Please help me to solve this issue.

Error :

Your connection attempt failed for user 'root' from your host to server at ABC:3306: Can't connect to MySQL server on 'ABC' (10060)

Answer

Steve picture Steve · Feb 13, 2014

Really this could be a magnitude of possible reasons, hopefully this is a start:

Check basic network

From the MySQL virtual machine open up a command prompt and type IPCONFIG /ALL. This will show you all the IP addresses bound to different network adapters.

Check that the IP address you're connected to is listed there, the virtual machine might have got a new IP from DHCP rather than having a static IP after its restart.

Hostname vs IP

You should check the hostname resolution, from your quoted error it would suggest you are you are connecting to a hostname rather than a server IP. Check your machine can resolve to the hostname using the correct IP address - it could also be worth changing the hostname for the actual IP of the server in the connection string.

MySQL config file

You've said you're running MySQL on Windows, it was customary to rename the my.cnf to my.ini. The configuration file for older versions of MySQL previous to 4.1.5 was usually stored in either c:\my.ini or c:\windows\my.ini. For versions after this the default location is the installation directory usually %PROGRAMDATA%\MySQL\MySQL Server xxx.

When you have located the configuration file please open it on Notepad (or similar text editor), locate the [mysqld] section and make sure that port= the port you're trying to connect to and bind-address= the IP address you're trying to connect to.

Server ports

From the MySQL virtual server open a command prompt and type netstat –ano, this will show you a list of processes and what IP's / ports they are listening on. The MySQL server should be listed here and should be listening on the same port and IP as the config file defines.

Windows firewall

You should have a firewall rule to allow MySQL, the following will add one on the default port of 3306

netsh advfirewall firewall add rule name="MySQL Server" action=allow protocol=TCP dir=in localport=3306

Identify if this is machine specific

You could setup the MySQL Workbench application on another workstation and try to connect to identify if this is a global problem or just one related to your specific workstation.

Related questions

MySQL: SELECT from another server

I'm afraid that I already know the answer to my question, but I'll ask it anyway: When there are two MySQL DB servers, can I access data that is stored on the other server? In other words: Can I somehow …

Read More
MySQL access denied 1045 error

I'm getting a very strange error, I've created a user 'testuser' with the following credentials: CREATE USER 'testuser'@'%' IDENTIFIED BY '123456'; GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%'; FLUSH PRIVILEGES; I have also modified my /etc/…

Read More
tutorials about creating a social network using PHP & MySQL?

Where can I find tutorials about creating a social network (like db structure, sql query samples eg.) using PHP & MySQL ? I do not need a ready script, I just want to learn how to do it..

Read More