错误信息
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 Server
Copyright (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 -A
Database 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 = '%';