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'@'%';

刷新权限

mysql> flush privileges;

继续求流量