Deploying the MySQL Environment locally (192.168.1.152), authorizing the remote client 192.168.1.% to connect to the native MySQL, the iptables firewall has also opened 3306 ports.
As follows:
Mysql>select Host,user,password 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 |
+---------------+---------------+------------------------------------------------------------+
Problem:
On the client (such as 192.168.1.20) remote connection above the 192.168.1.152 machine MySQL, the connection failed!
[Email protected] ~]# mysql-udb_ro_hqsb-h 192.168.1.152-pmhxzkhl0802xqsjdb
ERROR 1130 (HY000): Host ' 192.168.1.20 ' isn't allowed to connect to this MySQL server
Solve:
is due to 192.168.1.152 MySQL "host for Localhost,user and password empty" this statement caused, delete this can 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)
This allows the authorized connected client to connect successfully!
[Email protected] ~]# 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) and/or, Oracle, its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
Mysql>
MySQL client licensing after connection failure issue