Solution to mysql remote connection failure

We know that MySQL does not support remote access by default. It is the default MySQL user root does not support remote access by default. Because it is localhost, remote access is not allowed, however, if you have opened an account remotely or not, the solution is summarized below.

We know that MySQL does not support remote access by default. It is the default MySQL user root does not support remote access by default. Because it is localhost, remote access is not allowed, however, if you have opened an account remotely or not, the solution is summarized below.

The following message is displayed when you remotely CONNECT to the mysql database: "MYSQL connect ERROR-1130: Host '2017. 43. **. ** 'is not allowed to CONNECT to this MySQL server"

The original mysql account was created to restrict the remote login of the connected account. In other words, except for the current mysql Installation server, other ip addresses (hosts) are not allowed to access, even though your username and password are correct.

In fact, MySQL does not support remote access by default. It is the default MySQL user root does not support remote access by default, because it is localhost...

To put it bluntly, that is, permission issues. The root user's Host is localhost by default...

The following solution is to re-create a user and support remote access without modifying the root permission. We recommend that you do this by calling...

After knowing the cause of the problem, we will solve the problem below.

First, you need to log on to the mysql installation host, and then enter mysql:

The Code is as follows:
Mysql/usr/local/mysql/bin mysql-u root-p

Enter

Root is the highest authorized user name of mysql. You will be prompted to enter the password. Enter the correct password and press enter to enter mysql.

Run the following command:

The Code is as follows:
Grant all on database name. * to 'database account name' @ '%' identified by 'Password' with grant option;

Enter

The Code is as follows:
Flush privileges;

Enter
Note: The preceding single quotes cannot be saved. The database name. * indicates all tables under the database to be opened. If all databases connected to the database must be open, use *. * instead.
'Database account name' @ '%' indicates the account to be opened. The percent sign indicates that access is allowed on any host.
If the preceding two steps show "Query OK, 0 rows affected (0.00 sec)", the command has been successfully executed, and now you can remotely connect to your mysql database.

Important Note: (my experience ,(~ O ~)~ ZZ)

(1). If you want to grant all operation Permissions

Grant all on... to user name ......

(2) If you want to grant permissions to all tables in all databases

Grant... on *. * to user name ......

(3) If you want to grant all tables of a Database

Grant... on database name. '*' to username... ([*] must be enclosed in single quotes)

(4) If you want to assign a table to a database

Grant... on database name. Table name to user name ......

(5). If you want to allow remote access from any client using the user name

Grant... on... to username @ % ...... (Change IP address to [%])

(6) You must restart the MySQL service after the modification.

The user will be saved to the user table on the MYsql server.

You can also do this:

1. Change the table. It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to mysql on the computer of localhost, and change the "host" entry in the "user" table in the "mysql" database to "%" from "localhost"

The Code is as follows:
Mysql-u root-pvmwaremysql> use mysql; mysql> update user set host = '%' where user = 'root'; mysql> select host, user from user;

2. Authorization method. For example, if you want myuser to use mypassword to connect to the mysql server from any host.

The Code is as follows:
Grant all privileges on *. * TO 'myuser' @ '%' identified by 'mypassword' with grant option;

If you want to allow myuser to connect to the mysql server from a host whose ip address is 192.168.1.3, and use mypassword as the password

The Code is as follows:
Grant all privileges on *. * TO 'myuser' @ '192. 168.1.3 'identified BY 'mypassword' with grant option;

If the remote connection is still unavailable, refer

1. Check whether the Mysql port is correct. Use netstat-ntlp to check the port usage. Generally, the port is 3306. A port is used to connect to MySQl using tools. For example, My AdminMy Query BrowserMySQl Front.

2. Check whether the user permissions are correct.
For example, in user Tester, there are two records in the user table: localhost and % (for security purpose, % can be changed to the IP address you need to connect externally ).

3. Check whether skip-networking has been injected in/etc/my. cnf.
ERROR: ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.51.112 '(192)

6
-Check whether iptables is stopped. If no, the connection fails.
Service iptables stop is temporarily disabled.
ERROR: ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.51.112 '(192) 51Testing