Solution to MySQL remote connection failure

I have encountered the problem that MySQL can connect locally but cannot connect remotely before, but I didn’t record it. Today, I encountered this problem again on a newly applied server in the cloud. I will record the solution process.

1. Eliminate network or firewall issues

First check whether you can ping the remote server, ping 192.168.1.211. If not, it is a network problem. Then, check if the port is blocked by the firewall, telnet 192.168.1.211 3306, if the connection fails, configure the firewall.
Configure the firewall and open port 3306

vi /etc/sysconfig/iptables
-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT (allow port 3306 to pass through the firewall)
/etc/init.d/iptables restart (restart the firewall to make the configuration take effect)

2. Check MySQL configuration

If the firewall is enabled and telnet still fails, use netstat to check the status of port 3306:

netstat -apn | grep 3306
tcp6 0 0 127.0.0.1:3306 :::* LISTEN 13524/mysqld

Note that this indicates that 3306 is bound to the local computer. Check the configuration of my.cnf, where you can configure the binding IP address.
bind-address=addr
If not configured or the IP address is set to 0.0.0.0, it means listening to all client connections.
ps: After I opened port 3306 and checked the MySQL configuration, telent still failed, but telnet on the local machine was ok. I confirmed again and again that there was no problem with the configuration. Later, I mentioned it to our ucloud account administrator and found out that the ucloud management backend also needs to open port 3306. Those who use cloud servers should pay attention to this.

3. Check user access rights

When creating a user in MySQL, a host will be specified. The default is 127.0.0.1/localhost. Then this user can only access the local machine. Other machines will be prompted that they do not have permission to access with this user account. Changing the host to % means that all machines are allowed to access.

Finally, don't forget to restart MySQL for the configuration to take effect.

# /etc/init.d/mysql restart

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.