Detailed explanation of the solution to the problem of connection failure after mysql client authorization

The following editor will bring you an article that perfectly solves the problem of mysqlconnection failure after client authorization. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

Deploy the mysql environment locally (192.168.1.152), authorize the remote client 192.168.1.% to connect to the local mysql, and open port 3306 in the iptables firewall.

is as follows:

mysql> 
select host,user,passw
ord
 from mysql.user;
+--------------+-----------------+---------------------------------------------------------+
| host | user | password |
+--------------+-----------------+----------------------------------------------------------+
| localhost | root | |
| fdm1 | root | |
| 127.0.0.1 | root | |
| localhost | | |
| fdm1 | | |
| 192.168.1.% | db_hqsb | *DFC9DC16B13651A95ECEC3A26E07D244431B55C9 |
| 192.168.1.% | db_ro_hqsb | *2C0B0DD50595BB40879110437BEEF026D019DFB7 |
| 192.168.1.% | db_jkhwuser | *2C0B0DD50595BB40879110437BEEF026D019DFB7 |
| 192.168.1.25| slave | *EE52B8EACB3CCD13624273AD6B5CDA52B9B53EB7 |
| 192.168.1.% | tech_db_user | *6053E57C7B61043DC2C6B4E3291D5F61CCC23F5C |
| 192.168.1.% | game_db_user| *05EA4D71C9A1273ECF3E24E6323F7175AE45C366 | 
| localhost | zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+---------------+------------------------------------------------------------+

Question:

Remotely connect to the mysql of the above 192.168.1.152 machine on the client computer (such as 192.168.1.20), but the connection fails!

[root@huanqiu ~]# mysql -udb_ro_hqsb -h 192.168.1.152 -pmhxzkhl0802xqsjdb
ERROR 1130 (HY000): Host '192.168.1.20' is not allowed to connect to this MySQL server

Solution:

is due to the fact that "host is localhost, user" in mysql of 192.168.1.152 and password is empty" is caused by this statement, Deleting will solve the problem!

mysql> 
delete
 from mysql.user where host="localhost" and user="";
Query OK, 1 row affected (0.00 sec)
mysql> 
flush
 privileges;
Query OK, 0 rows affected (0.00 sec)

In this way, the client authorized to connect can successfully connect!

[root@huanqiu ~]# mysql -uxqsj_db_ro_user -h 192.168.1.152 -pmhxzkhl0802xqsjdb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The above is the detailed content of Detailed explanation of the solution to the problem of connection failure after mysql client authorization. For more information, please follow other related articles on the PHP Chinese website!