MySQL remote connection failure Solution

MySQL remote connection failure Solution

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 ECS instance on the cloud 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 3306
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

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

How to forget the MySQL root Password in Windows

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: