客户端连接 Mysql 报错

错误信息

Mysql安装在Centos系统中，使用Naticat连接Mysql时报错，信息如下：

Host '192.168.237.1' is not allowed to connect to this MariaDB server 

解决方案

1、进入Mysql

mysql -u root -p

输入密码

[root@localhost src]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \\g.
Your MariaDB connection id is 360
Server version: 10.2.43-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.MariaDB [(none)]> 

2、进入mysql数据库

use mysql;

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed

3、查看用户的host

select host, user,password from user;

MariaDB [mysql]> select host, user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *D06A34E97174580E449FBB7AAF0481C1744FCB |
| 127.0.0.1 | root | *D06A34E97174580E449FBB7AAF0481C1744FCB |
| ::1       | root | *D06A34E97174580E449FBB7AAF0481C1744FCB |
| %         | root | *2470C0C06DEE42F618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

此时发现host为%对应的密码与其它3个不一样，%表示为允许从任何主机连接到Mysql数据库。

4、删除host为%的记录

delete from user where host = '%';

MariaDB [mysql]> delete from user where host = '%';
Query OK, 1 row affected (0.00 sec)

5、授权允许从任何主机连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx' WITH GRANT OPTION;

• 注： xxx为root用户的密码，若改其它用户，则root改为其它用户即可

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx' WITH GRANT OPTION; 
Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

6、刷新授权，使其生效

FLUSH PRIVILEGES;

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

感觉以上第5步与第6步可以合并为一步，改为更新host为%的密码，密码为其它3个的密码，不过没有尝试，下次再遇到可以尝试一下

update user set password = 'xxxx' where host = '%';