What should I do if the remote connection to mysql fails?

Solution to the failure of remote connection to mysql: 1. Configure the firewall and open port 3306; 2. Edit the my.cnf configuration file and bind the IP address; 3. Modify user access rights to allow access from all machines; 4. Restart mysql.

1. Troubleshoot network or firewall problems

(Recommended tutorial: mysql tutorial)

First check if you can ping the remote server, ping 192.168.1.211. If not, there is a network problem. Then, check whether 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（允许3306端口通过防火墙）
/etc/init.d/iptables restart（重启防火墙使配置生效）

2. Check the MySQL configuration

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

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

Pay attention to the place, this shows that 3306 is bound to the local. Check the configuration of my.cnf, where you can configure the binding IP address.

bind-address=addr

If not configured or the IP is configured as 0.0.0.0, it means monitoring all client connections.

ps: I opened port 3306 and checked the MySQL configuration. Telent still failed, but telnet on the local machine was ok. I repeatedly confirmed 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 permissions

MySQL will specify a host when creating a user. The default is 127.0.0.1/localhost. Then this user can only access the local machine. Others If the machine is accessed using this user account, it will prompt that there is no permission. If the host is changed to %, it means that all machines are allowed to access.

4. Restart mysql to make the configuration take effect.

Related recommendations: php training

The above is the detailed content of What should I do if the remote connection to mysql fails?. For more information, please follow other related articles on the PHP Chinese website!