【MySQL】MySQL重新设置Root密码

在MySQL数据库使用过程中，有时会忘记root密码，本篇将演示在Linux系统当root用户密码忘记后，如何进行密码的重置。

方法一

1 演示MySQL服务器版本

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.05 sec)

2 以操作系统用户登录到正在运行的MySQL服务器，停止MySQL服务器

[root@strong ~]# kill `cat /u01/data/mydb/strong.oracle.com.pid`

3 创建一个修改用户密码的init文本

[root@strong mydb]# cat resetpw.sql
alter user 'root'@'localhost' identified by 'root';

4 使用--init-file选项启动MySQL服务器

[root@strong ~]# mysqld --init-file=/u01/data/mydb/resetpw.sql --user=mysql &

5 删除init文件

[root@strong mydb]# rm resetpw.sql

6 使用新密码登录MySQL服务器

[root@strong ~]# mysql -uroot -proot -S /u01/data/mydb/3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>

7 停止MySQL服务并重启

[root@strong ~]# mysqladmin -uroot -proot -S /u01/data/mydb/3306.sock shutdown
[root@strong ~]# service mysqld start
Starting MySQL.. SUCCESS!

方法二

1 停止MySQL服务器

[root@strong ~]# kill `cat /u01/data/mydb/strong.oracle.com.pid`

2 重启MySQL服务器，使用选项--skip-grant-tables

[root@strong ~]# mysqld --skip-grant-tables --user=mysql &

注：使用选项--skip-grant-tables可导致任何人都可以不使用密码连接MySQL服务器，并且拥有所有权限，故为了安全起见，和选项--skip-networking一起使用。

3 连接到MySQL服务器，重新加载grant tables

[root@strong ~]# mysql -S /u01/data/mydb/3306.sock
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> flush privileges;
Query OK, 0 rows affected (0.03 sec)

4 修改root密码

mysql> alter user 'root'@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.02 sec)

5 重启MySQL服务器以验证新密码

[root@strong ~]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock shutdown
[root@strong ~]# service mysqld start
Starting MySQL.. SUCCESS!
[root@strong ~]# mysql -uroot -pmysql -S /u01/data/mydb/3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>

注：方法一主要用于Windows、Unix和Linux平台，而方法二可用于任何平台进行root密码的重置。