Host 'xxx' is not allowed to connect to this MySQL server.
今天在伺服器安裝了mysql,準備用mysqlguitools遠端登入的時候出錯,提示:Host ‘xxx’ is not allowed to connect to this MySQL server。網上找了一些資料,是mysql未開啟mysql遠端訪問許可權導致。
記錄解決方案供以後參考。:
1.登入到mysql: mysql -uroot -ppwd
2.檢視user表:
mysql> use mysql
Database changed
mysql> select host,user,password from user;
±-----±-----±------------------------------------------+
| host | user | password |
±-----±-----±------------------------------------------+
| localhost | root | *826960FA9CC8A87953B3156951F3634A80BF9853 |
±-----±-----±------------------------------------------+
1 row in set (0.00 sec)
表中host、user欄位標識了可以訪問資料庫的主機和使用者。例如上面的資料就表示只能本地主機通過root使用者訪問。原來如此,難怪遠端連線死活連不上。
為了讓資料庫支援遠端主機訪問,有兩種方法可以開啟遠端訪問功能。
第一種(改表法):
修改host欄位的值,將localhost修改成需要遠端連線資料庫的ip地址。或者直接修改成%。修改成%表示,所有主機都可以通過root使用者訪問資料庫。為了方便,我直接修改成%。命令:mysql> update user set host = ‘%’ where user = ‘root’;
再次檢視user表
±-----±-----±------------------------------------------+
| host | user | password |
±-----±-----±------------------------------------------+
| % | root | *826960FA9CC8A87953B3156951F3634A80BF9853 |
±-----±-----±------------------------------------------+
1 row in set (0.00 sec)
修改成功,輸入命令mysql> FLUSH PRIVILEGES; 回車使剛才的修改生效,再次遠端連線資料庫成功。
第二種(授權法):
例如,你想root使用mypassword從任何主機連線到mysql伺服器的話。
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;
如果你想允許使用者myuser從ip為192.168.1.3的主機連線到mysql伺服器,並使用mypassword作為密碼
GRANT ALL PRIVILEGES ON . TO ‘root’@‘192.168.1.3’ IDENTIFIED BY
‘mypassword’ WITH GRANT OPTION;
輸入命令mysql> FLUSH PRIVILEGES; 回車使剛才的修改生效,再次遠端連線資料庫成功。bingo.
別忘記最後的FLUSH PRIVILEGES; 重新整理先前的修改。
本文轉載:https://www.cnblogs.com/vathena/p/4012399.html
相關文章
- “Host ‘xxxx‘ is not allowed to connect to this MySQL server“MySqlServer
- Host 'xxx' is not allowed to connect to this MySQL server 解決方法MySqlServer
- Host 'localhost' is not allowed to connect to this MySQL serverlocalhostMySqlServer
- mysql遠端連線 Host * is not allowed to connect to this MySQL serverMySqlServer
- MySQL-1130-host ... is not allowed to connect to this MySql serverMySqlServer
- 解決MySql報錯:1130 - Host ‘xxx‘ is not allowed to connect to this MySQL server的方法MySqlServer
- Host is not allowed to connect to this MySQL server 錯誤的處理方法MySqlServer
- SQL Error (1130): Host IP is not allowed to connect to this MySQL serverErrorMySqlServer
- Navicat連線centos裡mysql報錯Host is not allowed to connect to this MySQL serverCentOSMySqlServer
- 解決mysql Navicat 出錯:1130-host . is not allowed to connect to this MySql server,MySqlServer
- ERROR 1130: Host ***.***.***.*** is not allowed to connect to this MySQL serverERROR 1062 (23000):ErrorMySqlServer
- navicat無法連線遠端的mysql--Host ‘xx.xx.xx.xx‘ is not allowed to connect to this MySQL server“MySqlServer
- 虛擬機器NAT模式下連線mysql報錯:Host '192.168.30.1' is not allowed to connect to this MySQL server虛擬機模式MySqlServer
- 報錯:1130-host ... is not allowed to connect to this MySql server 開放mysql遠端連線 不使用localhostMySqlServerlocalhost
- 上手MySQL之解決問題:not allowed to connect to this MySQL serverMySqlServer
- 連線mysql時提示is not allowed to connect不允許連線MySql
- 關於對連線資料庫時出現1130-host “**” is not allowed to connect to this MySql/mariadb server 的錯誤解決方法資料庫MySqlServer
- could not connect to redis Instance at xxx.xxx.xxx.xxx:6379Redis
- ssh: connect to host localhost port 22: Connection refusedlocalhost
- 【linux】Can't connect to MySQL server on 'XXX' (13)問題解決方式LinuxMySqlServer
- Stop SCM Server.Server
- SQL0668N Operation not allowed for reason code "7" on table XXXSQL
- 坑:ssh: connect to host github.com port 22: Connection refusedGithub
- Linux - Ping: Unknown Host www.xxx.comLinux
- mysql Incorrect key file for table ‘/xxxxxxxx/xxxx.MYI'; try to repair itMySqlAI
- MySQL-undefinedfunctionmysql_connect()MySqlUndefinedFunction
- mysql connect speed is slowMySql
- 解決 connect to host github.com port 22 operation timed outGithub
- 【故障處理】ORA-12545: Connect failed because target hostAI
- ORA-12545: Connect failed because target host or object does not existAIObject
- 【Mysql】MySql Host is blocked because of many connection errors;MySqlBloCError
- unable to access ‘https://gitee.com/XXX/XXX.git/‘: Failed to connect to 127.0.0.1 portHTTPGiteeAI127.0.0.1
- Unknown host ‘XXXX: nodename nor servname provided, or not known‘. You may need to adjust the proxyIDE
- 異常解決——GitLab : ssh: connect to host port 22: Connection refusedGitlab
- MySQL Study之–Mysql啟動失敗“mysql.host”薦MySql
- Vue init webpack xxxx project 報錯處理(connect ETIMEDOUT 192.30.253.112)VueWebProject
- (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=download))JDBC
- 【MySQL】Host is blocked because of many connection errorsMySqlBloCError