问题：[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: '‘mysql_native_password’ is deprecated
mysql8.0.36两节点mgr+centos7.9黄金，日志每3秒提示告警，日志一直增大，信息如下：
[root@ycla data]# tail -f ycla.err
2024-03-06T09:47:59.016653+08:00 23718 [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-06T09:48:01.412458+08:00 23719 [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-06T09:48:04.445279+08:00 23720 [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-06T09:48:07.478052+08:00 23721 [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-06T09:48:10.509576+08:00 23722 [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-06T09:48:13.541385+08:00 23723 [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-06T09:48:16.575060+08:00 23724 [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-06T09:48:19.608302+08:00 23725 [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-06T09:48:22.642264+08:00 23726 [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-06T09:48:25.676105+08:00 23727 [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_native_password 改成caching_sha2_password
检查主备插件情况以及/etc/my.cnf
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | mgruser | mysql_native_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
处理办法：
1.在线全局修改
主库：
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | mgruser | mysql_native_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
ERROR 4058 (HY000): 1 factor authentication method does not match against authentication policy. Please refer @@authentication_policy system variable.
mysql> SET GLOBAL authentication_policy = 'caching_sha2_password';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER USER 'mgruser'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
flush privileges;Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | mgruser | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
备库：
mysql> SET GLOBAL authentication_policy = 'caching_sha2_password';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> ALTER USER 'mgruser'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.修改my.cnf参数
vi /etc/my.cnf
[root@ycla data]# cat /etc/my.cnf
[client]
port=3307
socket=/db/mysql/mysql-8.0.36/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
port=3307
user=mysql
socket=/db/mysql/mysql-8.0.36/mysql.sock
basedir=/db/mysql/mysql-8.0.36
datadir=/db/mysql/mysql-8.0.36/data
lower-case-table-names=1
#authentication_policy=mysql_native_password
authentication_policy=caching_sha2_password
#skip-grant-tables
#innodb_buffer_pool_size=105109258240
innodb_buffer_pool_size=190G
innodb_redo_log_capacity=1024M
slow_query_log=ON
slow_query_log_file=/db/mysql/mysql-8.0.36/data/ycla-slow.log
long_query_time=0.5
log_timestamps=SYSTEM
log_queries_not_using_indexes=off
max_connections=5000
wait_timeout = 600
interactive_timeout = 600
character_set_server=utf8mb4
innodb_flush_log_at_trx_commit=2
skip-log-bin
collation-server = utf8mb4_0900_ai_ci
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci'
skip-character-set-client-handshake = true
skip-name-resolve
sql_generate_invisible_primary_key=ON
large-pages
server-id=52
log_bin=binlog-bin
log_slave_updates=ON
binlog_format=ROW
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid_mode=ON
enforce_gtid_consistency=true
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.1.52:33071"
loose-group_replication_group_seeds="192.168.1.51:33071,192.168.1.52:33071"
loose-group_replication_bootstrap_group=OFF
report_host=192.168.1.52
report_port=3307
loose-group_replication_recovery_get_public_key=ON
admin_address=192.168.1.52
admin_port=33072
create_admin_listener_thread=1
log_bin_trust_function_creators=1
[root@ycla data]#
3.重启主备库：
主库：
[root@yclb ~]# service mysql stop
Shutting down MySQL...................... SUCCESS!
主库自动切换到备库，此时备库变为主库，主库未加入集群，需手工加入START GROUP_REPLICATION;
[root@yclb ~]# service mysql start
Starting MySQL.......................... SUCCESS!
[root@yclb ~]# mysql -uroot -pxxxxxx -Djyc
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 8.0.36 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 host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | mgruser | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.01 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.17 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 773123a4-daad-11ee-930b-3009f923fbf1 | 10.1.1.51 | 3307 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | b2b77622-daad-11ee-a732-3009f925119f | 10.1.1.52 | 3307 | ONLINE | PRIMARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
备库：
观察主库加入后，集群恢复正常，此时重启原备库，同样需要手动重新加入集群操作
[root@ycla data]# mysql -uroot -pxxxxxx -Djyc
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 23835
Server version: 8.0.36 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 * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | b2b77622-daad-11ee-a732-3009f925119f | 10.1.1.52 | 3307 | ONLINE | PRIMARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 773123a4-daad-11ee-930b-3009f923fbf1 | 10.1.1.51 | 3307 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | b2b77622-daad-11ee-a732-3009f925119f | 10.1.1.52 | 3307 | ONLINE | PRIMARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@ycla data]# service stop mysql
The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.
[root@ycla data]# service mysql stop
Shutting down MySQL............................. SUCCESS!
[root@ycla data]# service mysql start
Starting MySQL.......................... SUCCESS!
[root@ycla data]# mysql -uroot -pxxxxxx -Djyc
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 8.0.36 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 * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (9.11 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 773123a4-daad-11ee-930b-3009f923fbf1 | 10.1.1.51 | 3307 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | b2b77622-daad-11ee-a732-3009f925119f | 10.1.1.52 | 3307 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | mgruser | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
