MySQL cannot connect to a remote server

Question

We have a MySQL server on one of the remote virtual machines (Windows Server 2008). Until yesterday, we were able to connect to the MySQL server using the tools installed on our local machine.

Yesterday, a restart of the machine on which the virtual machine was installed. After that, we cannot connect to MYSQL. Although I can ping and remotely connect this particular virtual machine. I can even execute queries inside the tools installed on the virtual machine.

I'm not too good at networking or security-related stuff. Please help me solve this problem.

Mistake:

Error connecting to the user "root" from your host to the server in ABC: 3306: cannot connect to the MySQL server on "ABC" (10060)

Answer 1

In fact, this may be the magnitude of possible reasons, I hope this is the beginning:

Check core network

From the MySQL virtual machine, open a command prompt and enter IPCONFIG /ALL . This will show you all the IP addresses associated with the various network adapters.

Make sure that the IP address you are connected to is specified, the virtual machine may have a new IP address from DHCP, and not a static IP address after restarting it.

Hostname vs IP

You should check the resolution of the host name with your quoted error, this assumes that you are connecting to the host name and not to the IP address of the server. Verify that your computer can resolve the host name using the correct IP address - it can also be useful to change the host name for the actual server IP address in the connection string.

MySQL configuration file

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

If you find the configuration file, open it in Notepad (or a similar text editor), find the [mysqld] section and make sure port= port you are trying to connect to and bind-address= IP address you are trying to to connect.

Server ports

From the virtual MySQL server, open a command prompt and enter netstat –ano , this will show you a list of processes and IP ports that they are listening on. The MySQL server must be listed here and must be listened on the same port and IP as the configuration file.

Windows Firewall

You should have a firewall rule allowing MySQL, the following will add it to the default port 3306

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

Determine if this is a specific machine.

You can configure the Workbench MySQL application to another workstation and try to connect to determine if this is a global problem or simply related to your specific workstation.

Answer 2

The mysql administrator of your database must allow remote connection to the mysql server. change this in my.cnf:

 bind-address = 127.0.0.1 # this shoul be your mysql server ip 

and comment on this:

 # skip-networking 

Answer 3

Most likely, your configuration is configured for the IP address that has been changed. By default, mysql will not allow you to connect from remote hosts unless you explicitly give permission for a specific user in a particular scheme or group of schemes, for example, if you did something like this:

 GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION; 

It is possible that you actually did to install the grant on your own IP address, that is, the address of your local computer, and if your local computer (and not the remote server) changed its IP address, then mysql will not allow you to connect, if you do not have the IP address "1.2.3.4", which obviously you no longer have if you have a dynamic IP address (shared with DSL / cable connections)

So, connect via SSH or Telnet or whatever you use on your Windows server and go to mysql as root and do the following:

 SELECT * from information_schema.user_privileges; 

This will show you grants for all users and how they are allowed to connect. If you do not see the local IP address indicated there, or a wildcard (which will allow you to connect from any remote computer to the server), you should configure it as follows:

 GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION; 

If USERNAME is your user. See that after that there is a wildcard / dot / wildcard, which means that you want this user to be able to connect to any schema (database for mysql) from any user from any network. But I would recommend that you provide only a grant for the user for a specific scheme to which you need to connect.

Then after that, if you really have the right information and still cannot connect, than use portscanner, for example nmap or something like that, to perform a port scan and see if there is mysql:

• Open and listen to the external network.
• Launch on the port that you really want to connect through

If 1 is true, then check 2 because there may be a wrong port configuration. But if either of these two points works, then it definitely doesn’t sound like a network configuration, but a user preference or something else.

Answer 4

Have you tried to check if someone added security to the specified port, i.e. 3306.

Answer 5

 GRANT ALL ON *.* to user@ '%' IDENTIFIED BY 'password'; 

this command should do the trick for all users. @Gustavo Rubio has already given the correct explanation.

To ensure that the ports are open, run cmd in the virtual machine and enter.

 netstat -a TCP 127.0.0.1:3360 Hostname:3360 LISTENING 

In my.cnf is Mysql-install-path \ MySQL \ MySQL Server xxx make sure you back up the original before changing

Cannot connect to [local] MySQL server

Testing MySQL Server Installation on Microsoft Windows

MySQL Workbench: Manage MySQL on Windows Servers in Windows Mode

Answer 6

For the first time, you need to test and make sure your connection to mysql is not blocked by a firewall.

To disable the firewall on each host in your cluster, follow these steps on each host.

1. Save the existing iptables ruleset.

 iptables-save > /root/firewall.rules 

2. Disable iptables.

For RHEL, CentOS, Oracle, and Debian:

 chkconfig iptables off 

and

 /etc/init.d/iptables stop 

For SLES:

 chkconfig SuSEfirewall2_setup off 

and

 rcSuSEfirewall2 stop 

For Ubuntu:

 service ufw stop 

https://www.cloudera.com/documentation/enterprise/5-7-x/topics/install_cdh_disable_iptables.html