设置mysql密码不过期报错_MySQL用户密码过期登陆报错ERROR 1820 (HY000): You must reset your password using ALTER USER stat...

今天接到主从复制失败告警，查看MySQL，发现MySQL能够登陆但是执行命令报错，

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

因为是用户密码过期了

解决办法：

登陆MySQL命令行，执行

set password=password("password");

flush privileges;

#设置用户密码永不过期

ALTER USER'root'@'localhost' PASSWORD EXPIRE NEVER;

查看用户密码默认过期时间设置，默认360天

mysql> show global variables like "default_password_lifetime";+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| default_password_lifetime | 360 |

+---------------------------+-------+

1 row in set (0.00sec)设置用户密码永不过期(临时生效)

mysql> SET GLOBAL default_password_lifetime = 0;

Query OK,0 rows affected (0.00sec)

mysql> show global variables like "default_password_lifetime";+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| default_password_lifetime | 0 |

+---------------------------+-------+

1 row in set (0.00 sec)

如果要设置密码永不过期，设置my.cnf

vi /etc/my.cnf

添加

#设置用户密码永不过期

default_password_lifetime=0

这样重启后也是没问题的

查看用户密码设置

mysql> selectuser,host,password_expired,password_last_changed,password_lifetime from mysql.user;+----------------+-----------+------------------+-----------------------+-------------------+

| user | host | password_expired | password_last_changed | password_lifetime |

+----------------+-----------+------------------+-----------------------+-------------------+

| root | localhost | N | 2018-01-05 16:54:02 | 0 |

| mysql.sys | localhost | N | 2017-01-07 16:49:55 | NULL |

| root | % | N | 2017-01-12 17:21:08 | NULL |

| zabbix_monitor | localhost | N | 2017-05-08 09:45:40 | NULL |

+----------------+-----------+------------------+-----------------------+-------------------+

6 rows in set (0.00 sec)

创建一个有效期30天的用户

create user tt@'%' identified by '123456' password expire interval 30day;

mysql> selectuser,host,password_expired,password_last_changed,password_lifetime from mysql.user;+-----------+------------+------------------+-----------------------+-------------------+

| user | host | password_expired | password_last_changed | password_lifetime |

+-----------+------------+------------------+-----------------------+-------------------+

| root | localhost | N | 2017-12-28 15:59:39 | NULL |

| mysql.sys | localhost | N | 2017-12-28 11:42:12 | NULL |

| mysync | 10.73.86.% | N | 2017-12-28 16:11:49 | NULL |

| tt | % | N | 2018-01-08 12:59:08 | 30 |

+-----------+------------+------------------+-----------------------+-------------------+

参考

mysql-5.7 密码过期详解 http://www.mamicode.com/info-detail-2042849.html

Mysql 5.7 账户过期设置 - CSDN博客 http://blog.csdn.net/jc_benben/article/details/77934469