MySQL用户远程登录问题

今天来水一篇，差点被这个远程登录搞死了，所以记录下这个问题。

先使用status命令查看一下MySQL版本，这里可以可以看到我用的是是8.0.12版本。

mysql> status
--------------
mysql  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          14
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.12 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 25 min 14 sec

Threads: 3  Questions: 201  Slow queries: 0  Opens: 333  Flush tables: 2  Open tables: 309  Queries per second avg: 0.132
--------------

如果什么都不设置，那么用PHP连接时会报错：

Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

Warning: mysqli::__construct(): (HY000/2054): The server requested authentication method unknown to the client

caching_sha2_password报错是因为加密规则的原因。

可以在mysql数据库下查看相关信息。

mysql> use mysql;
mysql> select host, user, authentication_string, plugin from user; 

解决方法

首先修改远程用户加密规则

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.03 sec)

然后更新密码

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

刷新权限

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

搞定！

授权所有权限远程登录

GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
8.0版本的授权命令

刷新权限

mysql> flush privileges;

继续求流量个人博客