MySQL can be connected locally, and the remote connection fails.

MySQL can be connected locally, and the remote connection fails.

I have encountered the problem that MySQL can be connected locally but cannot be connected remotely. I have not recorded the problem. I encountered this problem on the new server applied for on ucloud today. I will record the solution process.

1. Eliminate network or firewall Problems

Check whether you can ping the remote server and ping 192.168.1.211. 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 to enable 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 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, telnet still fails. Use netstat to check the port status of port 3306:
Netstat-apn | grep 3360
Tcp6 0 0 127.0.0.1: 3306: * LISTEN 13524/mysqld

Pay attention to the red area, which means that 3306 is bound to the local device. Check the configuration of my. cnf. You can bind an IP address here.
Bind-address = addr
If this parameter is not set or the IP address is 0.0.0.0, all client connections are monitored.

Ps: After I enable port 3306 and check the MySQL configuration, telent still fails, but the local telnet is OK, and I have repeatedly confirmed that the configuration is correct. Later I mentioned it to our ucloud account administrator to know that port 3306 needs to be enabled in the ucloud management background, so I should pay attention to this with ECS.

3. Check User Access Permissions

When a MySQL user is created, a host is specified. The default value is 127.0.0.1/localhost. Therefore, this user can only access the machine. If other machines use this user account to access the database, the system prompts that the user has no permission and the host is changed to %, allow access from all machines.
# Mysql-u root-p
Mysql> use mysql;
Database changed
Mysql> update user set host = '%' where user = 'root ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Mysql> select host, user from user;
+ ---- + --- +
| Host | user |
+ ---- + --- +
| 127.0.0.1 | root |
| % | Night |
+ ---- + --- +
2 rows in set (0.00 sec)

Finally, do not forget to restart mysql to make the configuration take effect.
#/Etc/init. d/mysql restart