MySQL用户密码过期登陆报错ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

 今天接到主从复制失败告警,查看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.00 sec) 设置用户密码永不过期(临时生效) mysql> SET GLOBAL default_password_lifetime = 0; Query OK, 0 rows affected (0.00 sec) 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> select user,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 30 day;

mysql> select user,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

posted @   paul_hch  阅读(2611)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示