mysql数据库root密码遗忘后，修改root密码

目录

方式一：

方式二：

2.1 也可以像我这样，普通用户登录进去后

2.2 执行如下命令，将已知的user1的加密密文更新到root中

2.3 查询数据库

2.4 用root用户登录

2.5 登录正常，但这会root登录进去后，无法执行具体的sql，只要有操作，会报如下的错：

2.6 需要重新ALTRE下用户信息

2.7 退不退出没关系，在重新执行sql命令时，已显示正常

---

时间久了，root密码已遗忘，只要普通用户的密码记得。

不幸中的幸运是，创建普通用户时，赋予普通用户的权限足够大。不然就得更改数据库配置，配置文件末尾添加skip-grant-tables属性修改了。那比较麻烦，网络上也有很多博客介绍

方式一：

1. 停数据库服务
2. 修改mysql.cnf文件，添加属性skip-grant-tables
3. 启动数据库，root免密登陆
4. 此时，alter user 'root'@'localhost' IDENTIFIED BY 'xxxxx' ;，更新root用户密码
5. 停mysql服务
6. 修改mysql.cnf文件，去掉属性skip-grant-tables
7. 启动mysql服务。

root密码修改完成。

方式二：

2.1 也可以像我这样，普通用户登录进去后

SELECT Host, User,authentication_string FROM mysql.user ;
+-----------+---------------+-------------------------------------------+
| Host      | User          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *1B3C2E06495E4EA09B0212594330B69A3FF6330B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | user1         | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| %         | user2         | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
+-----------+---------------+-------------------------------------------+

2.2 执行如下命令，将已知的user1的加密密文更新到root中

# UPDATE mysql.user SET authentication_string='*BE707F4785F1FD02DCF2BF85AED55095BE461D94' WHERE User='root';
# FLUSH PRIVILEGES;

2.3 查询数据库

mysql> SELECT Host, User,authentication_string FROM mysql.user ;
+-----------+---------------+-------------------------------------------+
| Host      | User          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | user1         | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| %         | user2         | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
+-----------+---------------+-------------------------------------------+

2.4 用root用户登录

2.5 登录正常，但这会root登录进去后，无法执行具体的sql，只要有操作，会报如下的错：

mysql> SELECT Host, User,authentication_string FROM mysql.user ;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SELECT *  FROM mysql.user ;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

2.6 需要重新ALTRE下用户信息

mysql> shwo databases;
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 'shwo databases' at line 1
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' IDENTIFIED BY '嘻嘻嘻嘻嘻嘻嘻嘻' ;
Query OK, 0 rows affected (0.03 sec)

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2.7 退不退出没关系，在重新执行sql命令时，已显示正常

# mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 156907
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| finance            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)