Plugin mysql_native_password reported: ‘‘mysql_native_password‘ is deprecated and will be removed i

﹏ヽ暗。殇╰゛Y 2024-03-24 19:44 25阅读 0赞

Plugin mysql\_native\_password reported: ''mysql\_native\_password' is deprecated and will be removed in a future release. Please use caching\_sha2\_password instead'

![c472e8b9b6044d85b20659912d5d9206.png][]

show variables like 'default\_authentication%';

![070e49d86fca47f3828643b20967717b.png][]

select host,user,plugin,authentication\_string from mysql.user;

![f9a6c1b7faaa469baf6a232392e0e7d9.png][]

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不停写入报错信息  
  
2021-07-11T13:17:25.067300Z 2385 \[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'  
........  
...............  
............................  
2021-07-11T13:17:31.197610Z 2417 \[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'  
  
  
1.首先的排查思路是要查清楚什么原因导致的大量报错,疯狂的写入日志，从报错看有点像bug，但最后打消了这个念头此版本是MySQL-8.0.25最新的GA，不应该有这么低级的错误  
  
然后梳理下面排查思路，从字面上看是sha256\_password以后不被支持了，所以不断的报错，是什么原因出发这个报错呢，很可能是老的程序客户端使用的加密方式与MySQL 8.0.25的加密方式不兼容导致的  
那么我们就从连接方向来排查，首先要找到哪些客户端和程序连接到MySQL，导致的报错  
  
首先查询本地下加密方式，所有用户使用的都是caching\_sha2\_password，也是MySQL 8.0建议的加密方式  
  
mysql> show variables like '%auth%';  
\+-------------------------------+-----------------------+  
| Variable\_name | Value |  
\+-------------------------------+-----------------------+  
| default\_authentication\_plugin | caching\_sha2\_password |  
\+-------------------------------+-----------------------+  
  
mysql> select user,host,plugin from mysql.user;  
\+------------------+--------------+-----------------------+  
| user | host | plugin |  
\+------------------+--------------+-----------------------+  
| repl | % | caching\_sha2\_password |  
| root | 127.0.0.1 | caching\_sha2\_password |  
| NC | 192.168.200.%| caching\_sha2\_password |  
| mysql.infoschema | localhost | caching\_sha2\_password |  
| mysql.session | localhost | caching\_sha2\_password |  
| mysql.sys | localhost | caching\_sha2\_password |  
| root | localhost | caching\_sha2\_password |  
\+------------------+--------------+-----------------------+  
7 rows in set (0.00 sec)  
2.可以从查询connection\_control\_failed\_login\_attempts表来确定哪些客户端在连接MySQL和连接报错  
mysql> select \* from information\_schema.connection\_control\_failed\_login\_attempts;  
ERROR 1109 (42S02): Unknown table 'CONNECTION\_CONTROL\_FAILED\_LOGIN\_ATTEMPTS' in information\_schema  
  
默认情况下connection\_control\_failed\_login\_attempts没有被启用，我们需要安装connection\_control.so插件来获取查询支持  
  
mysql> INSTALL PLUGIN CONNECTION\_CONTROL SONAME 'connection\_control.so';  
Query OK, 0 rows affected (0.00 sec)  
  
  
mysql> INSTALL PLUGIN CONNECTION\_CONTROL\_FAILED\_LOGIN\_ATTEMPTS SONAME 'connection\_control.so';  
Query OK, 0 rows affected (0.00 sec)  
安装好后查看默认配置  
mysql> show variables like 'connection\_control%';  
\+-------------------------------------------------+------------+  
| Variable\_name | Value |  
\+-------------------------------------------------+------------+  
| connection\_control\_failed\_connections\_threshold | 3 |  
| connection\_control\_max\_connection\_delay | 2147483647 |  
| connection\_control\_min\_connection\_delay | 1000 |  
\+-------------------------------------------------+------------+  
  
01.connection\_control\_failed\_connections\_threshold ：连续失败最大次数3次,0表示不开启  
02.connection\_control\_max\_connection\_delay :超过最大失败次数之后阻塞登录最大时间(毫秒)  
03.connection\_control\_min\_connection\_delay :超过最大失败次数之后阻塞登录最小时间(毫秒)  
  
  
3.通过查询我们可以看到两个网段的程序一个是mysqlrouter一个是nc的程序不断尝试连接MySQL，很可能是他们的连接加密方式问题导致的  
  
mysql> select \* from information\_schema.connection\_control\_failed\_login\_attempts;  
\+-----------------------------------------------+-----------------+  
| USERHOST | FAILED\_ATTEMPTS |  
\+-----------------------------------------------+-----------------+  
| 'mysql\_router5\_da1ufs1lvt0b'@'172.16.200.153' | 22 |  
| 'NCAPP'@'192.168.200.153' | 1154|  
\+-----------------------------------------------+-----------------+  
2 rows in set (0.00 sec)  
  
  
4.经过排查和确认，NC程序的加密方式与MySQL 8.0.25 caching\_sha2\_password 不兼容导致的，可以通过使用 mysql\_native\_password 创建用户尝试避开不兼容的问题

[c472e8b9b6044d85b20659912d5d9206.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/e6916b97ef8441a78f490781d9f2a08a.png
[070e49d86fca47f3828643b20967717b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/3e5bd3ad9b7944198480a9206a7d6087.png
[f9a6c1b7faaa469baf6a232392e0e7d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/18762a9355804cf598c342c2a17a49b6.png