mysql密码有效期是多久 | 您所在的位置:网站首页 › wps登录别人的账号有效期是多久 › mysql密码有效期是多久 |
测试环境中有使用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 setpassword_expired='Y'whereuser='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 setauthentication_string=password('12345678') whereuser='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; #root用户改了状态,记录要flush权限不然用普通用户立刻登陆还是不生效的。 Query OK, 0 rows affected (0.00 sec) 密码过期的参考文档: https://bugs.mysql.com/bug.php?id=77277 |
今日新闻 |
推荐新闻 |
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 |