CentOS: solution to mysql remote connection failure

CentOS: solution to mysql remote connection failure

Note: I will use the tool I introduced for editing. For details, refer to the tool to be installed in my CENTOS post.

1) Stop the MySQL service first
/Etc/init. d/mysqld stop

2) view the mysql configuration file

[Plain] view plaincopy

1. Nano/etc/my. cnf

Pay special attention to the following two items: bind_address and skip_networking. bind_address cannot be 127.0.0.1; otherwise, it can only be connected locally and skip_networking cannot appear, otherwise, only the unix socket is accepted and the tcp socket service is not available. We recommend that you comment out bind_address and skip_networking directly.

3) restart the mysql Service

[Plain] view plaincopy

1. Servicemysqld <spanstyle = "font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"> restart </span>

4) authorize the user to allow remote access by the specified user. The simplest way is to set the host of the user in the user table in the mysql database to %, that is, allow remote access from any ip address.

[Plain] view plaincopy

1. Mysql-uroot-ppassword // enter the mysql Console
2. Mysql> usemysql;
3. Mysql> updateusersethost = '%' whereuser = 'root'; // you can skip this error when executing this command.
4. Mysql> flushprivileges;
5. Mysql> selecthost, userfromuser; // check whether '%' is inserted into the database
6. Mysql> quit

5) in general, remote access is allowed at this time. However, some systems still need to check firewall settings and ip access policies, in case that the system cannot remotely access mysql due to network access restrictions
For CentOS systems, it is best to check iptables settings. The procedure is as follows:
A) suspend the iptables service.
Service iptables stop

B) view iptables configuration
Nano/etc/sysconfig/iptables

C) You may see the following content:
: Output accept [1009120: 257185920]
-A input-p tcp-m tcp -- dport 3306-j ACCEPT
#-A input-s 118.144.89.18-p tcp-m tcp -- dport 3306-j ACCEPT
#-A input-s 123.127.177.239-p tcp-m tcp -- dport 3306-j ACCEPT
We recommend that you open port 3306 directly, instead of specifying an ip address to access port 3306.
D) restart the iptables service.
Service iptables start

Article