好想忘记转生史莱姆再看一遍，朱莱太可爱啦
在我的公众号的发消息界面点击 “加我滴群” 即可获取我的群的二维码。
前言
最近在测试虚拟机配置 xtrabackup 备份的用户的时候发现一个问题，我的 MySQL 是 8.0 版本，默认插件为 caching_sha2_password
，xtrabackup 执行version_check
的时候报错 failed: Authentication plugin 'caching_sha2_password' cannot be loaded: usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
这个问题虽然不会导致备份失败，但是看着很难受，想给它弄掉，要么关闭版本检查（你懂的，我太懒了，备份懒得多写一个配置），然后我就去改备份用户的身份验证插件改为 mysql_native_password
，这个时候就发现一个之前没注意的问题，只写身份验证插件不加密码，导致用户密码过期，主要还是因为懒哈哈，等会详细说明。
case 发生过程
创建测试用户
root@localhost [(none)] 21:04:57 > create user testau identified by '123456';
Query OK, 0 rows affected (0.19 sec)
root@localhost [(none)] 21:41:55 > show create user testau;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for testau@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `testau`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$%2l\\b)^u8TPA7:#C280t1zdU0c5WfaswQGACArQ12P9BaEMRt3A1tvex8Bf1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意一个点：PASSWORD EXPIRE DEFAULT
使用 default_password_lifetime 配置的全局过期策略，我这里是不过期。
root@localhost [(none)] 21:43:24 > select @@global.default_password_lifetime;
+------------------------------------+
| @@global.default_password_lifetime |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
*This variable defines the global automatic password expiration policy. The default
default_password_lifetime
value is 0, which disables automatic password expiration. If the value ofdefault_password_lifetime
is a positive integerN
, it indicates the permitted password lifetime; passwords must be changed everyN
days.The global password expiration policy can be overridden as desired for individual accounts using the password expiration option of the
”CREATE USER
andALTER USER
statements. See Section 8.2.15, “Password Management”.
然后这个时候呢，xtrabackup 报错：
241128 21:07:50 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'bkuser' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock','bkuser',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
但是备份正常：
2024-11-28T21:08:00.803423+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
我就想把用户的身份验证插件给改了，但是我偷懒，用的这个命令
alter user testau identified with mysql_native_password;
然后我再去备份，然后就报错了，直接显示连接不上了。
2024-11-28T21:54:11.279093+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql_8_3306 --tmpdir=/data/mysql_8_3306/tmp --innodb_buffer_pool_size=64M --log_bin=/data/mysql_8_3306/mysql-bin --server-id=40 --innodb_data_file_path=ibdata1:100M;ibdata2:50M:autoextend
2024-11-28T21:54:11.279739+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --socket=/tmp/mysql.sock --user=testau --password=* --backup=1 --target-dir=/backup/xtrabackup1128/full --parallel=2 --throttle=0 --xtrabackup-plugin-dir=/app/percona-xtrabackup-8.0.32-25-Linux-x86_64.glibc2.17/lib/plugin --keyring-file-data=/log/component_keyring_file
xtrabackup version 8.0.32-25 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 14f007fb)
241128 21:54:11 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'testau' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock','testau',...) failed: Access denied for user 'testau'@'localhost' (using password: YES) at - line 1535.
2024-11-28T21:54:11.494256+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: testau, password: set, port: not set, socket: /tmp/mysql.sock
2024-11-28T21:54:11.500947+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Access denied for user 'testau'@'localhost' (using password: YES)
然后我去就拿这个用户连接 mysql
[root@localhost ~]# mysql -utestau -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testau'@'localhost' (using password: YES)
登不上了，但是不指定密码，Enter 两下进来了
[root@localhost ~]# mysql -utestau -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 135
Server version: 8.0.32
Copyright (c) 2000, 2023, 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.
testau@localhost [(none)] 21:56:03 >
然后我 use 一下 db
testau@localhost [(none)] 21:56:03 > use test;
No connection. Trying to reconnect...
Connection id: 136
Current database: *** NONE ***
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
直接断开连接了哈哈。这要是生产不就毁了吗哈哈。提桶跑路了。
为啥会这样呢？拿 root 看下账号的创建语句：
root@localhost [(none)] 22:00:08 > show create user testau;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for testau@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `testau`@`%` IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
跟上面比，明显少个个 AS '$A$005$%2l\\b)^u8TPA7:#C280t1zdU0c5WfaswQGACArQ12P9BaEMRt3A1tvex8Bf1'
而且 PASSWORD EXPIRE DEFAULT
→ PASSWORD EXPIRE
为啥？我就去看了官方文档的解释：
*
IDENTIFIED WITH
auth_pluginSets the account authentication plugin to
auth_plugin
, clears the credentials to the empty string (the credentials are associated with the old authentication plugin, not the new one), and stores the result in the account row in themysql.user
system table.” In addition, the password is marked expired. The user must choose a new one when next connecting.
有一个关键点：将密码清空为空字符串，且密码被标记为已过期。用户在下次连接时必须选择一个新密码。
真相大白，生产可不能偷懒。
总结
identified with
修改用户的身份验证插件，不管修改的值如何（只要有这个身份验证插件），都会清空密码，并将密码标记为过期。
往期回顾
主从切换后导致 Event 停滞 and 主从故障 - Event 导致主从停滞的案例 INSERT 加什么锁 - 聊聊 INSERT 的加锁情况 MySQL 临时表空间 - 临时表空间（Temporary Tablespaces 及相关的变量 MySQL/PG 数据脱敏 - data_masking 插件和 postgresql_anonymizer 扩展初体验 MySQL/PG 对事务 DDL 的支持程度 - 从隐式提交聊到DDL事务 ONLINE DDL 收尾篇|如何有效实现 ONLINE - 关于 ONLINE DDL 的最佳实践 MySQL/PG 索引对于排序规则的依赖性 - 排序规则与索引的相关性问题，涉及到索引是否会失效 如何正确杀会话， KILL processlist_id/KILL QUERY？ - 你知道如何正确杀掉活跃会话吗？
感谢阅读！关注我获取更多精彩内容。
