mysql_native_passwordの認証プラグインが非推奨になったことにより、接続毎にWARNINGログが出る

2024-09-27T19:37:01.767056Z 3624 [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-09-27T19:37:01.781005Z 3625 [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-09-27T19:37:10.237038Z 3626 [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-09-27T19:37:01.767056Z 3624 [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-09-27T19:37:01.781005Z 3625 [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-09-27T19:37:10.237038Z 3626 [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 8.0.34のバージョンアップからmysql_native_passwordの認証プラグインが非推奨になったことによりWARNINGのログがでるようになったこと

非推奨なので対応したほうが良いのだが、緊急性がそこまではないと思われるので、対応は次回以降のバージョンアップか再インストール時などに後回しにして、今回は、エラーログのフィルタリングを実施

使用している認証プラグインを確認

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+--------------+-----------------------+
| user             | host         | plugin                |
+------------------+--------------+-----------------------+
| laravel          | 172.0.0.1    | mysql_native_password |
| hoge             | 192.168.**** | mysql_native_password |
| laravel          | localhost    | mysql_native_password |
| mysql.infoschema | localhost    | caching_sha2_password |
| mysql.session    | localhost    | caching_sha2_password |
| mysql.sys        | localhost    | caching_sha2_password |
| n********        | localhost    | mysql_native_password |
| w********        | localhost    | mysql_native_password |
+------------------+--------------+-----------------------+
** rows in set (0.01 sec)

フィルタリングの状態確認

mysql> SHOW VARIABLES like 'log_error_%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+

MySQLの設定ファイルにイベントで抑制するエラーコードを追加

vi /etc/my.cnf.d/mysql-server.cnf
--
[mysqld]
log_error_suppression_list = MY-013360

MySQLの再起動

systemctl restart mysqld

フィルタリングの状態確認

mysql> SHOW VARIABLES like 'log_error_%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list | MY-013360                              |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
3 rows in set (0.00 sec)

参考

ハイブリッドクラウドで動くカラーミーショップのマスターDBをMySQL 8.0にアップグレードした話 – Pepabo Tech Portal
Amazon RDS for MySQL 8.0のデフォルト認証プラグインはmysql_native_password | DevelopersIO
第155回 エラーログのフィルタリング | gihyo.jp