尘封网

让学习成为一种习惯!

Mysql5.7.9密码已过有效期的处理过程

测试环境中有使用mysql-5.7.9的版本,最近出现了一个状态:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. 根据提示要重置密码,不然无法执行命令;这个问题的造成原因,居然是密码有效期过了导致的。

root用户登陆系统后的情况:

root@(none) 09:05:21>show processlist;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

root@(none) 09:09:02>show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

root@(none) 09:09:04>select host,user,password_last_changed from mysql.user; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

无法使用任何命令查看状态信息了;

怎么知道系统默认的有效期是多久呢?使用一个普通用登陆[未过期];默认系统的密码生命周期是360天就是一年这样了;

test01@(none) 09:11:43>show variables like 'default_password_lifetime'; 
+---------------------------+-------+ 
| Variable_name             | Value | 
+---------------------------+-------+ 
| default_password_lifetime | 360   | 
+---------------------------+-------+ 
1 row in set (0.00 sec)

那么最直接的方法就是改密码啦,但是改了密码360天后一样会过期的,有办法可以变成永不过期吗?其实在5.7.11版本后都已经把默认值从360变成了0也就是永不过期了;

那么目前的解决方法:

=======================我是分隔线啦=======================

如果要认密码永不过期的话,可以使用以下方法:

1、修改密码

set passwordpassword=password('123456');

2、设定密码的有效期为0,即永不过期;

set global default_password_lifetime=0;

3、在配置文件中添加配置;[如果不做上面修改,可以直接修改配置文件,但是改完后要重启服务才生效]

[mysqld] 
default_password_lifetime=0

=======================我是分隔线啦=======================

当然上面的修改是全局的,如果你只想root用户密码不过期也可以这样配置:

先查未修改前的记录是怎么样的?

root@(none) 09:27:34>select user,host,password_lifetime,password_last_changed,password_expired from mysql.user; 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| user      | host        | password_lifetime | password_last_changed | password_expired | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| root      | localhost   |              NULL | 2017-06-12 09:24:24   | N                | 
| mysql.sys | localhost   |              NULL | 2016-06-06 11:41:56   | N                | 
| checksums | 10.0.10.110 |              NULL | 2017-05-06 10:41:38   | N                | 
| repl      | 10.0.10.61  |              NULL | 2017-05-05 11:18:11   | N                | 
| test01    | %           |              NULL | 2017-05-05 11:18:11   | N                | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
7 rows in set (0.02 sec)

修改root密码永不过期:

root@(none) 09:29:34>alter user 'root'@'localhost' password expire never; 
Query OK, 0 rows affected (0.00 sec)

修改后的效果,可以看到的是password_lifetime的值变成0了;

root@(none) 09:30:03>select user,host,password_lifetime,password_last_changed,password_expired from mysql.user; 

+-----------+-------------+-------------------+-----------------------+------------------+ 
| user      | host        | password_lifetime | password_last_changed | password_expired | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| root      | localhost   |                 0 | 2017-06-12 09:24:24   | N                | 
| mysql.sys | localhost   |              NULL | 2016-06-06 11:41:56   | N                | 
| checksums | 10.0.10.110 |              NULL | 2017-05-06 10:41:38   | N                | 
| repl      | 10.0.10.61  |              NULL | 2017-05-05 11:18:11   | N                | 
| test01    | %           |              NULL | 2017-05-05 11:18:11   | N                | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
7 rows in set (0.00 sec)

如果要恢复到默认的策略,就是360天过期:

root@(none) 09:33:29>alter user 'root'@'localhost' password expire default; 

Query OK, 0 rows affected (0.00 sec)

效果就是password_lifetime的值又变回NULL了,就是使用默认值了;

root@(none) 09:33:46>select user,host,password_lifetime,password_last_changed,password_expired from mysql.user; 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| user      | host        | password_lifetime | password_last_changed | password_expired | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| root      | localhost   |              NULL | 2017-06-12 09:24:24   | N                | 
| mysql.sys | localhost   |              NULL | 2016-06-06 11:41:56   | N                | 
| checksums | 10.0.10.110 |              NULL | 2017-05-06 10:41:38   | N                | 
| repl      | 10.0.10.61  |              NULL | 2017-05-05 11:18:11   | N                | 
| test01    | %           |              NULL | 2017-05-05 11:18:11   | N                | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
7 rows in set (0.00 sec)

为了模拟普通用户密码过期我把上面的用户test01修改一下;

root@mysql 11:43:42>update user set password_expired='Y' where user='test01'; 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 

root@mysql 11:43:48>flush privileges; 
Query OK, 0 rows affected (0.00 sec)

接下来换到test01登陆看看是否提示过期;

test01@(none) 11:44:19>show databases; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

提示要重置密码了,就是说明直接修改配置文件生效了;

接下来使用root用户来更新test01的密码看有没有改变过期状态;

root@mysql 10:36:19>update user set authentication_string=password('12345678') where user='test01';  

Query OK, 0 rows affected, 1 warning (0.00 sec)  
Rows matched: 1  Changed: 0  Warnings: 1  

root@mysql 11:47:19>flush privileges; 
Query OK, 0 rows affected (0.00 sec)

仔细看password_last_changed的值依旧没有改变,而password_expired的状态还是为Y,就是过期状态还是未改变;

root@mysql 10:39:02>select user,host,password_lifetime,password_last_changed,password_expired from mysql.user; 

+-----------+-------------+-------------------+-----------------------+------------------+ 
| user      | host        | password_lifetime | password_last_changed | password_expired | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
| root      | localhost   |              NULL | 2017-06-12 09:24:24   | N                | 
| mysql.sys | localhost   |              NULL | 2016-06-06 11:41:56   | N                | 
| test01    | %           |              NULL | 2016-01-02 12:12:12   | Y                | 
| checksums | 10.0.10.110 |              NULL | 2017-05-06 10:41:38   | N                | 
| repl      | 10.0.10.61  |              NULL | 2017-05-05 11:18:11   | N                | 
+-----------+-------------+-------------------+-----------------------+------------------+ 
8 rows in set (0.00 sec)

使用test01登陆时已经是使用新密码了;

[root@mysql ~]# mysql -utest01 -p12345678

可是依旧提示密码过期,需要修改;

test01@(none) 10:34:45>show databases; 

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

而test01的权限是怎么样的?

root@(none) 10:40:59>show grants for 'test01'@'%'; 

+-----------------------------------------------------+ 
| Grants for test01@%                                 | 
+-----------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'test01'@'%'                  | 
| GRANT ALL PRIVILEGES ON `percona`.* TO 'test01'@'%' | 
+-----------------------------------------------------+ 
2 rows in set (0.00 sec)

不管怎么样,这个普通用户一旦过期了,root用户哪怕修改了它的密码,但是无法触发到过期状态的改变;而改变过期状态的方法有两种:

1、使用普通用户登陆后,自己修改密码,也可以改回旧密码:

test01@(none) 10:40:11>set passwordpassword=password('12345678'); 
Query OK, 0 rows affected, 1 warning (0.00 sec)

2、就是使用root用户把过期的值改为N;

root@mysql 10:49:11>update user set password_expired='N' where user='test01'; 

Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 

root@(none) 10:49:51>flush privileges;    
Query OK, 0 rows affected (0.00 sec)

root用户改了状态,记录要flush权限不然用普通用户立刻登陆还是不生效的。

密码过期的参考文档:

https://bugs.mysql.com/bug.php?id=77277

提醒:本文最后更新于 675 天前,文中所描述的信息可能已发生改变,请谨慎使用。