重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛
标题:
MySQL8.0设置远程访问权限,Navicat连接mysql
[打印本页]
作者:
jiawang
时间:
2020-9-4 18:27
标题:
MySQL8.0设置远程访问权限,Navicat连接mysql
本帖最后由 jiawang 于 2020-9-5 20:34 编辑
centos7.8安装了mysql8.11过后远程登录数据库报错
登录/注册后可看大图
27475f52165b39ed2.png
(53 KB, 下载次数: 417)
下载附件
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;
欢迎光临 重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)
Powered by Discuz! X3.2