mysql 升级8.0.35之后一直打印mysql_native_password密码插件驱动过时的警告，覆盖errorlog的问题

mysql 升级8.0.35之后一直打印mysql_native_password密码插件驱动过时的警告，覆盖errorlog的问题

目录

• mysql 升级8.0.35之后一直打印mysql_native_password密码插件驱动过时的警告，覆盖errorlog的问题
  • 现象
    • 查看mysql 默认的密码插件驱动类型
    • 查看用户使用的密码插件类型
    • 解决方案：

现象

mysql 升级8.0.35之后一直打印mysql_native_password密码插件驱动过时的警告，覆盖errorlog的问题

 use caching_sha2_password instead'
2024-03-16T05:54:00.941087Z 1870 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-03-16T05:54:05.942288Z 1871 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-03-16T05:54:07.105550Z 1872 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-03-16T05:54:10.943586Z 1873 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-03-16T05:54:15.942375Z 1874 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-03-16T05:54:20.942246Z 1875 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

查看mysql 默认的密码插件驱动类型

root@mysqldb 13:58:  [(none)]> show variables like 'default_authentication%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

查看用户使用的密码插件类型

root@mysqldb 13:58:  [(none)]>  select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | oper_dc          | mysql_native_password | *ss                               |
| %         | read_dc          | mysql_native_password | *xxx                              |
| %         | repl             | mysql_native_password | *ss222xxxxxxxx                    |
| %         | zbx_monitor      | mysql_native_password | *xxx                              |
| localhost | backup           | mysql_native_password | *xxx                             

解决方案：

1.参考初步分析中的方案，将应用的连接配置修改为正确的用户信息；
2.可以在mysql数据库中通过参数将该告警过滤，避免该告警信息输入到错误日志文件。相关配置如下：

show variables like 'log_error_suppression_list';
set global log_error_suppression_list='MY-013360';
show variables like 'log_error_suppression_list';

注意，使用该方案也会导致某个存在且使用SHA256_PASSWORD认证插件产生的告警。可以作为临时方案；

3.修改mysql代码，避免在使用不存在用户登录数据库时，选择 SHA256_PASSWORD认证插件。目前针对该方案已提交Bug #109635。
或者

sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead
mysql server errorlog忽然爆出大量的sha256_password' is deprecated and will be removed in a future release.错误，导致error不停写入报错信息

4.经过排查和确认，NC程序的加密方式与MySQL 8.0.25 caching_sha2_password 不兼容导致的，可以通过使用 mysql_native_password 创建用户尝试避开不兼容的问题