客户端连接 Mysql 报错

1 篇文章 0 订阅
当使用Naticat尝试连接Centos系统的Mysql时遇到Host192.168.237.1isnotallowedtoconnecttothisMariaDBserver的错误。解决方法包括登录Mysql,进入mysql数据库,查看并删除host为%的记录,然后授权允许从任何主机连接,最后刷新权限使更改生效。
摘要由CSDN通过智能技术生成

错误信息

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 = '%'; 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值