Navicat连接数据库配置 1130报错 1251报错

1130报错，默认情况下root用户只允许本机访问,即使用localhost访问。

查看mysql的配置
可以看到最后一行，只允许localhost访问root用户

mysql> use mysql;	
Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

修改配置允许其他用户连接数据库

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

刷新

mysql> flush   privileges;
Query OK, 0 rows affected (0.13 sec)

再次查看配置

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

此时用Navicat连接，这次报错为1251。

原因是MySql8.0之后的版本的密码加密方式改变了，用最新版Navicat可解决该问题，或者更改MySql加密方式。

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.09 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

此时连接成功