错误1
1130 - Host XXX is not allowed to connect to this MySQL server。
我查了一下,1130问题是由于Mysql配置了不支持远程连接引起的,因此我们需要开启允许远程连接
执行命令:
- 登录MySQL:mysql -u root -p
- 进入mysql库改配置:use mysql;
- 查看当前主机的配置信息:select host from user where user=‘root’;
可以看到当前主机的配置是localhost ,因此是不知吃远程连接的
- 修改配置,改成通配符%就可以远程连接了:update user set host =’%’ where user =‘root’;
- 5.配置立即生效:flush privileges;
下面为命令输入过程:
[root@izuf62e1aik19tck77y9qwz ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 315
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> 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
mysql> select host from user where user='root';
+-----------+
| host |
+-----------+
| localhost |
+-----------+
1 row in set (0.00 sec)
mysql> update user set host ='%' where user ='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host from user where user='root';
+------+
| host |
+------+
| % |
+------+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
错误2:
1045 -Access denied for user ‘root’@‘xxx.xxx.xxx.xxx’(using password:yes)
- 这个问题其实我到现在还没有完全明白
网上的解决办法:
参考了一下网上的很多方法:有些建议改为空密码,有些跳过登录验证。
但是我的小服务器上有点别的小项目在,要用到数据库,所以不是很想改密码啥的(牵一发而动全身的感觉),于是我重启了一下MySQL,一开始还是报1045错误
后来我在Navicat的连接中加了SSH,如下所示:
最终成功连接
疑问
我的问题其实还没有完全解决,因为我在之后又验证了一遍(重新建一个连接,但没有用SSH),还是成功了,说明不用SSH其实也可以,那么猜测起效果的是重启Mysql。