mysql server log flooded with mysql_native_password deprecated warnings [MY-013360]

Question

I have multiple MySQL InnoDB Clusters running that are being used by mysqlrouter instances. A recent disk space warning showed that the mysql error log is getting flooded with the same error again and again:

2023-10-24T13:43:30.771948Z 49185 [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'
2023-10-24T13:43:30.786352Z 49186 [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'

It seems the mysqlrouter instances are using an incorrect authentication plugin, even though the server default is configured as caching_sha2_password and the mysqlrouters are the latest version and bootstrapped normally.

show global variables like 'default_auth%'; 
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

But the mysqlrouters are clearly using the old plugin:

select host,user,plugin from mysql.user;
+-----------------------+---------------------------------+-----------------------+
| host                  | user                            | plugin                |
+-----------------------+---------------------------------+-----------------------+
| %                     | clusteradmin                    | caching_sha2_password |
| %                     | mysql_innodb_cluster_1506993484 | caching_sha2_password |
| %                     | mysql_innodb_cluster_15617768   | caching_sha2_password |
| %                     | mysql_innodb_cluster_1701831455 | caching_sha2_password |
| %                     | mysql_innodb_cluster_2276414794 | caching_sha2_password |
| %                     | mysql_innodb_cluster_3880077975 | caching_sha2_password |
| %                     | mysql_innodb_cluster_86200734   | caching_sha2_password |
| %                     | mysql_innodb_cs_59d2e54c        | caching_sha2_password |
| %                     | mysql_innodb_cs_656fe31f        | caching_sha2_password |
| %                     | mysql_router11_mqhk6m29wfw8     | mysql_native_password |
| %                     | mysql_router12_kfpd8reiyr1c     | mysql_native_password |
| %                     | mysql_router1_22dtj6g8hdu5      | mysql_native_password |
| %                     | mysql_router2_fha5xpstts6x      | mysql_native_password |
| %                     | mysql_router4_8l8v4j34dfkd      | mysql_native_password |
| %                     | mysql_router4_9bde20xjoj96      | mysql_native_password |
| %                     | mysql_router4_h1stlfc3ydmi      | mysql_native_password |

Since the error log is 10G at the moment, and the mysql_native_password will disappear in the future, how can I make sure the mysqlrouter apps use caching_sha2_passwords? I checked all the documentation, but there seems no way to configure it in the config file.

Since a similar question was on stackoverflow already (sha256_password deprecated warnings filling logs), unfortunately that solution didn't work for me, as there are no bad users spamming connection requests

I would expect that when you install mysqlrouter via the official repo, it would use the default authentication plugin from the cluster you connect them to?

Answer 1

Met the same problem.

I followed the official document to generate a mysql router user. This should be done after you generate the cluster and before you set up the router.

mysqlsh> testCluster.setupRouterAccount('myRouter1')

MySQL user create by this way will use the default plugin, so if your default choice is caching_sha2_passwords, it will be that.

Then using this user as the --account value when you run mysqlrouter --bootstrap

mysqlrouter --bootstrap icadmin@ic-1:3306 --account=mysqlrouter

If you manually modify the mysqlrouter.conf, it should be in the [metadata_cache:bootstrap] section.

[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=mysqlrouter1
metadata_cluster=testCluster