MySQL 默认身份验证插件从 sha256_password 更换到 caching_sha2_password

查看日志，发现有大量警告写入

root@hetzner-fsn1-001:~# tail -f /usr/local/mysql/var/hetzner-fsn1-001.err
2025-03-31T02:16:18.973249Z 58388 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:17:04.872715Z 58389 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:17:28.111458Z 58390 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:17:44.072550Z 58391 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:17:44.083118Z 58392 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:18:59.119749Z 58393 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2025-03-31T02:18:59.195938Z 58394 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

排查了一下，发现当初在 phpMyAdmin 上新增使用者账号时使用的 SHA256 密码认证，而在 MySQL 从MySQL 8.0 开始，caching_sha2_password 已成为默认的身份验证插件，替代了之前的 sha256_password和 mysql_native_password

解决办法

查看当前使用 sha256_password 的用户

SELECT user, host, plugin 
FROM mysql.user 
WHERE plugin = 'sha256_password';

更新对应用户其认证插件

ALTER USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'user_password';

实际操作

root@hetzner-fsn1-001:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58398
Server version: 8.4.3 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT user, host, plugin
    -> FROM mysql.user
    -> WHERE plugin = 'sha256_password';
+---------+-----------+-----------------+
| user    | host      | plugin          |
+---------+-----------+-----------------+
| typecho | localhost | sha256_password |
+---------+-----------+-----------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'typecho'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your-password';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT user, host, plugin
    -> FROM mysql.user
    -> WHERE plugin = 'sha256_password';
Empty set (0.00 sec)

mysql> SELECT user, host, plugin
    -> FROM mysql.user
    -> WHERE plugin = 'caching_sha2_password';
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
| typecho          | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

参考资料

MySQL :: MySQL 8.4 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication