最新消息:20190529 VPS服务器已从腾讯云香港换为Vultr新加坡,主题仍用朋友推荐的大前端D8

【已解决】Sequel Pro连接mysql数据库出错:MySQL said: Host is not allowed to connect to this MySQL server

database crifan 2746浏览 0评论

Mac中用Sequel Pro去连接一个Mysql的数据库

点击连接,结果失败:

Connection failed!
Unable to connect to host 115.29.173.126, or the request timed out.
Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).
MySQL said: Host ‘58.208.67.177’ is not allowed to connect to this MySQL server

如图:

感觉像是:

服务器端,加了防火墙之类的,不让连接。

MySQL said: Host is not allowed to connect to this MySQL server

Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server – Stack Overflow

ubuntu – ERROR 1130 (HY000): Host ” is not allowed to connect to this MySQL server – Stack Overflow

database connection – Error: Host xxx is not allowed to connect to this MySQL server – Stack Overflow

How to Allow MySQL Client to Connect to Remote MySQL server

Configuring database connection results in Error: Host ‘xxxxxxx’ is not allowed to connect to this MySQL server – Atlassian Documentation

(RunningFast) ➜  RunningFast mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 604
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> create user ‘runningfast’@’115.29.173.126’ identified by ‘Jiandao123’; 
Query OK, 0 rows affected (0.01 sec)
mysql> create database runningfast_dev character set utf8 collate utf8_unicode_ci;
Query OK, 1 row affected (0.01 sec)
mysql> grant select,insert,update,delete,create,drop,alter,index on runningfast_dev.* to ‘runningfast’@’115.29.173.126’ identified by ‘Jiandao123’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for runningfast@115.29.173.126;
+—————————————————————————————————————————+
| Grants for runningfast@115.29.173.126                                                                                     |
+—————————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘runningfast’@’115.29.173.126’ IDENTIFIED BY PASSWORD ‘*E6276652A4CE8A03BDA11FF7B3B0C88D617E8472’   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `runningfast_dev`.* TO ‘runningfast’@’115.29.173.126’ |
+—————————————————————————————————————————+
2 rows in set (0.00 sec)
mysql> quit
Bye

但是结果:

【已解决】新建mysql用户无法访问数据库:ERROR 1045 (28000): Access denied for user @’localhost’ (using password: YES)

但是远程去连接,还是无法连接:

去看权限,是OK的:

mysql> show grants for runningfast@115.29.173.126;
+—————————————————————————————————————————+
| Grants for runningfast@115.29.173.126                                                                                     |
+—————————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘runningfast’@’115.29.173.126’ IDENTIFIED BY PASSWORD ‘*E6276652A4CE8A03BDA11FF7B3B0C88D617E8472’   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `runningfast_dev`.* TO ‘runningfast’@’115.29.173.126’ |
+—————————————————————————————————————————+
2 rows in set (0.00 sec)
(RunningFast) ➜  RunningFast mysql -u runningfast -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 622
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> grant all privileges on *.* to ‘runningfast’@’58.208.67.177’ identified by ‘Jiandao123’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后就可以连接了:

【总结】

对于远程连接服务器端的mysql但是出错:

MySQL said: Host ‘58.208.67.177’ is not allowed to connect to this MySQL server

指的是:

客户端,此处的IP:58.208.67.177

不允许连接到远程服务器端的mysql

解决办法:

把58.208.67.177赋给对应的访问权限:

mysql -u 你的myql用户 -p
grant all privileges on *.* to ‘你的用户’@’58.208.67.177′ identified by ‘你的密码’;
flush privileges;

即可。

-》

但是有个问题:

此处的IP地址,是过段时间就变化的

-》当此处的IP发生变化后,就要重写运行上述命令更新一下,然后才能继续用Sequel Pro去连接远程mysql。。。

-》暂时没有想到,一劳永逸,即使本地IP变化,也可以继续连接mysql的办法。。。

看到:

Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server – Stack Overflow

中,带百分号的

难道是任意host的IP都可以?

去试试:

(RunningFast) ➜  RunningFast mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 643
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> create user ‘runningfast’@’%’ identified by ‘Jiandao123’;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to ‘runningfast’@’%’ with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

然后,就等着,下次本地的IP地址变化后,看看还能不能继续访问

-》如果还可以继续访问,则说明上述配置是OK的,是可以允许任意的IP用该用户访问服务器端的mysql的。

【已解决】Mac中如何确定当前电信网络的IP地址

【后记】

后来IP地址变了:

IP查询|IP地址查询-世界网络 www.linkwan.com

“查询输入

114.216.27.185

查询定位

江苏省苏州市”

不过Sequal Pro还是可以继续访问服务器端的MySQL数据库的:

-》

说明之前的:

grant all privileges on *.* to ‘runningfast’@’%’ with grant option;
flush privileges;

是有效果,的确可以设置,来自任何IP的该用户,都可以访问此数据库的。

转载请注明:在路上 » 【已解决】Sequel Pro连接mysql数据库出错:MySQL said: Host is not allowed to connect to this MySQL server

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
67 queries in 0.088 seconds, using 18.81MB memory