xtrabackup备份提示密码过期，查询结果password_expired是N？（MySQL密码过期策略）
xtrabackup备份提示密码过期，查询结果password_expired是N？
# xtrabackup --backup \ > --host=localhost --user=root --password='xx' \ > --port=3306 --parallel=4 --compress \ > --compress-threads=4 --slave-info \ > --target-dir=/apps/backups/0330 2026-03-29T23:03:17.526506+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/apps/works/data/mysql --open_files_limit=65535 --log_bin=mysql-bin --server-id=3 --innodb_file_per_table=1 --innodb_data_home_dir=/apps/works/data/mysql --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/apps/works/data/mysql --innodb_buffer_pool_size=21G --innodb_log_file_size=5M --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=2 2026-03-29T23:03:17.526649+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --backup=1 --host=localhost --user=root --password=* --port=3306 --parallel=4 --compress --compress-threads=4 --slave-info=1 --target-dir=/apps/backups/0330 xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49) 260329 23:03:17 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1548. 2026-03-29T23:03:17.602031+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock 2026-03-29T23:03:17.602958+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Your password has expired. To log in you must change it using a client that supports expired passwords.但是查询的结果密码过期是N
mysql> select user,host,password_expired,password_lifetime,password_last_changed from user; +------------------+-----------+------------------+-------------------+-----------------------+ | user | host | password_expired | password_lifetime | password_last_changed | +------------------+-----------+------------------+-------------------+-----------------------+ | root | localhost | N | NULL | 2025-12-24 15:47:28 | +------------------+-----------+------------------+-------------------+-----------------------+求解
原因分析 1. 自动密码过期策略（最常见） password_expired 字段只记录手动执行 ALTER USER ... PASSWORD EXPIRE 的过期状态。而 MySQL 还有自动密码过期机制，基于密码的使用时长来判断。 判断逻辑如下： 先检查 password_expired 是否被手动标记为 'Y' 若为 'N'，则继续检查：当前日期 - password_last_changed 是否超过了允许的有效期 如果超过，服务器会在连接时判定密码已过期，但不会将 password_expired 字段更新为 'Y'，只有手动 密码过期的时候，这个字段才会显示'Y‘mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE; Query OK, 0 rows affected (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> user, -> host, -> password_expired, -> password_last_changed, -> password_lifetime, -> TIMESTAMPDIFF(DAY, password_last_changed, NOW()) AS days_since_change -> FROM mysql.user -> WHERE user='test'; +------+-----------+------------------+-----------------------+-------------------+-------------------+ | user | host | password_expired | password_last_changed | password_lifetime | days_since_change | +------+-----------+------------------+-----------------------+-------------------+-------------------+ | test | localhost | Y | 2026-03-29 12:15:42 | 180 | 0 | +------+-----------+------------------+-----------------------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql>排查命令
-- 查看关键字段 SELECT user, host, password_expired, password_last_changed, password_lifetime, TIMESTAMPDIFF(DAY, password_last_changed, NOW()) AS days_since_change FROM mysql.user WHERE user='root';查询结果：
mysql> -- 查看关键字段 mysql> SELECT -> user, -> host, -> password_expired, -> password_last_changed, -> password_lifetime, -> TIMESTAMPDIFF(DAY, password_last_changed, NOW()) AS days_since_change -> FROM mysql.user -> WHERE user='root'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 145218 Current database: mysql +------+-----------+------------------+-----------------------+-------------------+-------------------+ | user | host | password_expired | password_last_changed | password_lifetime | days_since_change | +------+-----------+------------------+-----------------------+-------------------+-------------------+ | root | localhost | N | 2025-12-24 15:47:28 | NULL | 95 | +------+-----------+------------------+-----------------------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql> show variables like '%lifetime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 90 | +---------------------------+-------+ 1 row in set (0.00 sec)设置密码永不过期
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> user, -> host, -> password_expired, -> password_last_changed, -> password_lifetime, -> TIMESTAMPDIFF(DAY, password_last_changed, NOW()) AS days_since_change -> FROM mysql.user -> WHERE user='root'; +------+-----------+------------------+-----------------------+-------------------+-------------------+ | user | host | password_expired | password_last_changed | password_lifetime | days_since_change | +------+-----------+------------------+-----------------------+-------------------+-------------------+ | root | % | N | 2026-03-03 01:19:56 | NULL | 26 | | root | localhost | N | 2026-03-29 12:06:59 | 0 | 0 | +------+-----------+------------------+-----------------------+-------------------+-------------------+ 2 rows in set (0.00 sec)单独设置用户密码过期时间，不使用全局参数限制
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> user, -> host, -> password_expired, -> password_last_changed, -> password_lifetime, -> TIMESTAMPDIFF(DAY, password_last_changed, NOW()) AS days_since_change -> FROM mysql.user -> WHERE user='root'; +------+-----------+------------------+-----------------------+-------------------+-------------------+ | user | host | password_expired | password_last_changed | password_lifetime | days_since_change | +------+-----------+------------------+-----------------------+-------------------+-------------------+ | root | % | N | 2026-03-03 01:19:56 | NULL | 26 | | root | localhost | N | 2026-03-29 12:06:59 | 180 | 0 | +------+-----------+------------------+-----------------------+-------------------+-------------------+ 2 rows in set (0.00 sec) mysql> mysql> show variables like '%lifetime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 30 | +---------------------------+-------+ 1 row in set (0.01 sec) mysql>关键判断逻辑：
若 password_lifetime 有值（如 30），表示该账户有独立有效期
若 password_lifetime 为 NULL，则使用全局参数 default_password_lifetime
若 password_lifetime 为 0，表示密码永不过期
当 days_since_change >= 有效期 时，密码自动过期
解决方法：
方案1：重置密码（立即生效）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xdbhxZVUYQ$4Fs%6Nug3Tn'; ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'xdbhxZVUYQ$4Fs%6Nug3Tn'; FLUSH PRIVILEGES;这会同时更新 password_last_changed 为当前时间，过期计时器重新开始
方案2：设置密码永不过期
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'127.0.0.1' PASSWORD EXPIRE NEVER;方案3：调整全局策略（对所有用户生效）
SET PERSIST default_password_lifetime = 0;