解决MySql报错：1130 - Host 'xxx' is not allowed to connect to this MySQL server的方法

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

目录

• 发现问题
• 解决办法
• 补充

 

正文

解决MySql报错：1130 - Host 'xxx' is not allowed to connect to this MySQL server的方法

回到顶部

发现问题

使用Navicat连接MySql数据库时，未能成功，提示信息如下图：

这个错误提示已经很明确了，"不允许主机'desktop-teat9ob'连接到此mysql服务器"，知道问题所在就好解决了。

回到顶部

解决办法

mysql> select  Host,User,password_expired,password_last_changed,password_lifetime,Password_reuse_history,Password_reuse_time,Password_require_current from mysql.user;
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
| Host      | User             | password_expired | password_last_changed | password_lifetime | Password_reuse_history | Password_reuse_time | Password_require_current |
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
| %         | root             | N                | 2020-09-06 22:00:05   |              NULL |                   NULL |                NULL | NULL                     |
| 127.0.0.1 | root             | N                | 2020-09-06 22:00:05   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.infoschema | N                | 2020-09-06 21:56:24   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.session    | N                | 2020-09-06 21:56:24   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.sys        | N                | 2020-09-06 21:56:24   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | root             | N                | 2020-09-06 22:00:05   |              NULL |                   NULL |                NULL | NULL                     |
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
6 rows in set (0.12 sec)

INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`, `Create_role_priv`, `Drop_role_priv`, `Password_reuse_history`, `Password_reuse_time`, `Password_require_current`, `User_attributes`)
VALUES('127.0.0.1','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0','mysql_native_password','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9','N','2020-09-06 22:00:05',NULL,'N','Y','Y',NULL,NULL,NULL,NULL);
INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`, `Create_role_priv`, `Drop_role_priv`, `Password_reuse_history`, `Password_reuse_time`, `Password_require_current`, `User_attributes`)
VALUES('%','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0','mysql_native_password','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9','N','2020-09-06 22:00:05',NULL,'N','Y','Y',NULL,NULL,NULL,NULL);

FLUSH PRIVILEGES;

---

问题解决。

回到顶部

补充

如果你想允许用户root从ip为192.168.0.8的主机连接到MySql服务器，并使用123456作为密码，可以执行下面的SQL语句：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.8' IDENTIFIED BY '123456' WITH GRANT OPTION;
#执行下面的语句，使上面的改动生效
FLUSH PRIVILEGES;

如果你想允许用户root从ip为192.168.0.8的主机连接到MySql服务器的testdb数据库，并使用123456作为密码

GRANT ALL PRIVILEGES ON testdb.* TO 'root'@'192.168.0.8' IDENTIFIED BY '123456' WITH GRANT OPTION;
#执行下面的语句，使上面的改动生效
FLUSH PRIVILEGES;