Linux下MySQL5.7密码忘记重置操作以及修改密码方式总结！

前言

在前不久学习Linux系统时，在虚拟机中CentOS 7系统里面的MySQL服务的密码给忘记了，下面就记录下密码重置的解决方法。

忘记密码

• 首先编辑配置文件

  vim /etc/my.cnf
  

  在[mysqld]下加上 skip-grant-tables，如：

  [mysqld]
  skip-grant-tables
  

• 重启MySQL服务

  service mysqld restart
  

• 登陆MySQL修改密码

  mysql -u root
  
  update mysql.user set authentication_string=PASSWORD('123456') where User='root'; 
  
  flush privileges; 
  

• 改回my.cnf重启MySQL

修改密码

使用set password 命令

首先登陆MySQL
一般命令格式为：mysql> set password for 用户名@localhost = password(‘新密码’);

代码如下（示例）：

mysql> set password for root@localhost = password('www.imyjs.cn'); 

执行以上代码报错！信息如下：

mysql> set password for root@localhost = password('www.imyjs.cn'); 
ERROR 1133 (42000): Can't find any matching row in the user table

错误提示的字面意思：在用户表中找不到任何匹配的行

解决办法

执行以下命令：

use mysql;
select Host,User from user;

mysql> use mysql;
Database changed

mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
3 rows in set (0.00 sec)

主要原因是修改密码的条件不一致

将set password for root@localhost = password('2410685763'); 代码中的localhost修改%，与数据库Host字段值一致即可：

set password for 'root'@'%'=password('www.imyjs.cn');

注意：% 需要使用引号括住！否则会提示如下错误：

mysql> set password for root@% = password('www.imyjs.cn'); 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% = password('www.imyjs.cn')' at line 1

如果提示如下错误：

mysql> set password for 'root'@'%'=password('www.imyjs.cn');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

翻译过来就是：您的密码不满足当前策略要求

需要按照下面的修改密码规则进行操作即可！

最后刷新：flush privileges;

使用UPDATE编辑user表

update user set authentication_string=password("123456") where user="root" and host='localhost';

代码如下（示例）：

mysql> use mysql;
Database changed
mysql> update user set password=password('123456') where user='root' and host='localhost'; 
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql>  update user set authentication_string=password("123456") where user="root" and host='localhost'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

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

使用ALTER重置密码

代码如下（示例）：

mysql> ALTER USER root@'%' identified by 'www.imyjs.cn';
Query OK, 0 rows affected (0.00 sec)

修改密码规则

登录进入MySQL，输入命令mysql>select @@validate_password_policy;

该命令为了查看密码的安全等级。

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)

查看密码相关参数执行命令：show variables like 'validate_password%';

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

参数解释：

修改密码设置规则：

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)

修改后的密码相关参数：show variables like 'validate_password%';

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 0     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

微信关注

编程那点事儿

编程那点事儿