深夜记录一次Mysql5.7.10的密码失效问题

情景：在公司的数据库服务器上安装的mysql版本是

[root@hotdata ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.10, for Linux (x86_64) using  EditLine wrapper

安装好后，并且设置了密码，但是现在突然提示密码失效了，在mysql终端提示信息为：

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

远程访问提示信息为：

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

步骤如下，以下步骤为了快速解决问题，所以对配置做了些修改，可能与5.7强调的安全相违背，但是时间紧迫，先解决问题先，以后再深究高版本mysql的密码验证的安全性问题

去掉skip-grant-tables的注释，为了修改密码

• vi /etc/my.cnf 去掉skip-grant-tables 前面的注释

[mysqld]

skip-grant-tables  #跳过权限验证，此时任何密码和无密码都可以访问了

重启mysql

 sudo systemctl restart mysqld // 或者service mysqld restart

• -

登陆并修改密码

在Linux终端登录mysql控制台

mysql -uroot //或者可以mysql -uroot -p 然后输入过期的密码也可以进去

修改密码

//因为我的mysql为5.7.10所以使用下述方法
 update mysql.user set authentication_string=password('newPass') where user='root' ;
//update mysql.user set authentication_string=password('newPass') where user='root' and Host = 'localhost';
 //MySQL 5.7.5 或更早之前的版本
 update user set password=PASSWORD('newpass') where User='root';

 flush privileges; #刷新
 quit

将my.cnf的修改项还原，并重启mysql

结果，远程还无法登录mysql，并且还是提示密码过期，在linux终端也是一样。

好吧继续修改：

设置密码永不失效

在my.cnf上加入：

[mysqld]
default_password_lifetime=0  #设置密码永不失效

在mysql控制台设置密码强度为Low

mysql> set global validate_password_policy=0;

以上设置后，在mysql终端应该是可以访问和使用sql语句了，但是可能远程还是无法链接。这时候继续修改

设置可以允许所以主机访问

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'newPass' WITH GRANT OPTION;
mysql> flush privileges; #刷新

操作日志

mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> use mysql;
No connection. Trying to reconnect...
Connection id:    43
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> update mysql.user set authentication_string=password('newpass') where user='root' and Host = 'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'newPass' WITH GRANT OPTION;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ^C
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'newPass' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

参考链接：
http://www.voidcn.com/blog/maxsky/article/p-5778962.html

http://blog.csdn.net/ku360517703/article/details/49684809

http://www.skyf.org/reset-mysql579-root-password/

http://www.linuxidc.com/Linux/2015-12/126628.htm

http://wuqinglong.blog.51cto.com/9087037/1722598

http://blog.csdn.net/u014284252/article/details/51283876

好晚了，洗澡睡觉。Good Night!