MySQL数据库忘记密码

1、MySQL数据库忘记密码

MySQL数据库忘记密码有多种处理方式、首选的处理方法就是通过配置 skip-grant-tables 来跳过权限认证、然后进入数据库更新密码即可。次选方案就是进行数据库初始化、但是数据库初始化之后数据和MySQL的配置信息会全部丢失（该方法谨慎使用）。

MySQL数据库密码更新有两种版本、这是因为在MySQL5.7.5之前和MySQL5.7.6以后的更新命令有所不同、如下：

# MySQL5.7.5以前的命令
update mysql.user set password=password("z0ukun@2018") where user="root" and host="localhost";

# MySQL5.7.6以后的命令
update mysql.user set authentication_string=password('z0ukun@2019') where user='root';

现在我们一起来看看详细过程如下。

1.1、MySQL5.7.5以前

# 跳过权限认证
[root@mysql-02 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
skip-grant-tables
# 重启MySQL数据库
[root@mysql-02 ~]# systemctl restart mysqld
[root@mysql-02 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> update mysql.user set password=password("z0ukun@2018") where user="root" and host="localhost";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> \q
Bye
[root@mysql-02 ~]# 
[root@mysql-02 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@mysql-02 ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-02 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> \q
Bye
[root@mysql-02 ~]# 

1.2、MySQL5.7.6以后

# 跳过权限认证
[root@mysql-01 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
skip-grant-tables
[root@mysql-01 ~]# systemctl restart mysqld
[root@mysql-01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         | *0F165DBC5138762F91E03623B61D95A2E6B0EBD1 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.01 sec)

# 更新密码
mysql> update mysql.user set authentication_string=password('z0ukun@2019') where user='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[root@mysql-01 ~]#

[root@mysql-01 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>