前言
MySQL 从 5.6.6 版本开始，支持密码过期功能，在 mysql.user 表中添加 password_expired 功能 [1]，这样用户可以像 Oracle 一样设置密码自动过期。 从 MySQL 5.7.4 开始，增加了新的参数 default_password_lifetime [2]，允许全局的为数据库中的用户设置密码过期策略。
如何设置密码过期
初始创建用户，我们可以看到密码默认不过期，最近一次修改密码的时间为 2022-04-07 14:30:32。
mysql> create user rudonx1@'%' identified by 'xxxxxxxx';
Query OK, 0 rows affected (0.04 sec)
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user='rudonx1';
+---------+------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+---------+------+------------------+-----------------------+-------------------+
| rudonx1 | % | N | 2022-04-07 14:30:32 | NULL |
+---------+------+------------------+-----------------------+-------------------+
1 row in set (0.03 sec)
1. 设置密码 60 天过期
mysql> ALTER USER 'rudonx1'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.03 sec)
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user='rudonx1';
+---------+------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+---------+------+------------------+-----------------------+-------------------+
| rudonx1 | % | N | 2022-04-07 14:30:32 | 60 |
+---------+------+------------------+-----------------------+-------------------+
1 row in set (0.02 sec)
```
`
### 2. 设置密码立即过期
```sql
mysql> ALTER USER 'rudonx1'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.02 sec)
```
设置账号密码过期后，会发现我们可以登录到命令行，但是在执行命令时出现密码过期的提示
```sql
(base) [root@ip-10-0-0-22 ~]# mysql -h xx.xx.xx.xx -urudonx1 -p'xxxxxx'
mysql>
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user='rudonx1';
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
在之前的版本，如 5.7.19 中，密码过期提示为：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
```
前面我们先设置了 60 天的过期时间，随后使用户自动过期，查看 MySQL user 表，如下：
```sql
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user='rudonx1';
+---------+------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+---------+------+------------------+-----------------------+-------------------+
| rudonx1 | % | Y | 2022-04-07 14:30:32 | 60 |
+---------+------+------------------+-----------------------+-------------------+
1 row in set (0.03 sec)
```
### 3. default_password_lifetime 和 手动修改 PASSWORD EXPIRE 优先级问题
当我们手动运行 `ALTER USER 'rudonx1'@'%' PASSWORD EXPIRE INTERVAL 30 DAY` 和 设置 全局参数 `default_password_lifetime = 60`，会发现手动设置的优先级更高。
```sql
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 60 |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql> ALTER USER 'rudonx1'@'%' PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user like '%rudonx1%';
+-----------+------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+-----------+------+------------------+-----------------------+-------------------+
| rudonx12 | % | N | 2022-01-09 12:54:10 | NULL |
| rudonx111 | % | N | 2022-02-09 02:42:33 | NULL |
| rudonx1 | % | N | 2022-04-07 07:38:59 | 30 |
+-----------+------+------------------+-----------------------+-------------------+
3 rows in set (0.00 sec)
```
# 如何解决密码过期的问题
当您发现用户密码过期之后，您可以使用最高权限的用户进行密码重置，命令如下：
```sql
mysql> alter user rudonx1@'%' identified by 'xxxxxx';
Query OK, 0 rows affected (0.03 sec)
```
同时，火山引擎 RDS for MySQL 提供了控制台操作的方法，您只需要在 "账号管理" 部分找到被锁定的用户，重置密码即可，对于主用户也是同样的操作。
# 参考文档
[1] [https://dev.mysql.com/doc/refman/5.6/en/expired-password-handling.html](https://dev.mysql.com/doc/refman/5.6/en/expired-password-handling.html)
[2] [https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html](https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html)
**如果您有其他问题，欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)
