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;

繼續求流量個人博客