MySQL8.0设置远程访问权限，Navicat连接mysql

jiawang · 发表于 2020-9-4 18:27:12

本帖最后由 jiawang 于 2020-9-5 20:34 编辑

centos7.8安装了mysql8.11过后远程登录数据库报错

27475f52165b39ed2.png (53 KB, 下载次数: 394)

2020-9-4 18:26 上传

解决办法：
首先登陆到mysql命令行：
mysql -u root -p

进入之后选择mysql库，用户信息都存在这个库的user表中

use mysql;

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+-------------------------------------------+-----------------------+
| host    | user          | authentication_string                   | plugin             |
+-----------+------------------+-------------------------------------------+-----------------------+
| localhost | mysql.infoschema | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys       | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | root          | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | mysql_native_password |
+-----------+------------------+-------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

查看user表的root用户Host字段是localhost，说明root用户只能本地登录，现在把他改成远程登录

GRANT ALL ON *.* TO 'root'@'%';
GRANT ALL ON 表示所有权限，% 表示通配所有 host，可以访问远程。

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

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

修改mysql加密规则
ALTER USER 'root'@'%' IDENTIFIED BY 'mysql' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql';

刷新权限
mysql> FLUSH PRIVILEGES;

帐号		自动登录	找回密码
密码			注册

[命令及语法] MySQL8.0设置远程访问权限，Navicat连接mysql