MySQL数据库root密码忘记

MySQL数据库root密码忘记，可通过–skip-grant-tables参数跳过权限表访问数据库。

忘记root密码，进不去数据库

[root@mysql1 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

查看MySQL进程号，kill掉MySQL进程，强制关闭数据库

[root@mysql1 ~]# ps -ef | grep mysql
avahi       838      1  0 14:57 ?        00:00:00 avahi-daemon: running [mysql1.local]
mysql      1884      1  0 15:11 ?        00:00:01 /mysql/base5738/bin/mysqld --defaults-file=/mysql/my3306/my.cnf --datadir=/mysqldata/my3306/data --socket=/mysql/my3306/mysql.sock --user=mysql
root       2377   1539  0 15:27 pts/0    00:00:00 grep --color=auto mysql
[root@mysql1 ~]# 
[root@mysql1 ~]# kill -9 1884
[root@mysql1 ~]# 
[root@mysql1 ~]# ps -ef | grep mysql
avahi       838      1  0 14:57 ?        00:00:00 avahi-daemon: running [mysql1.local]
root       2410   1539  0 15:28 pts/0    00:00:00 grep --color=auto mysql

添加跳过权限表参数，重启数据库

[root@mysql1 ~]# cd /mysql/base5738/bin/
[root@mysql1 bin]# ./mysqld_safe --defaults-file=/mysql/my3306/my.cnf --skip-grant-tables &
[1] 2577
[root@mysql1 bin]# 2022-08-22T07:30:50.476956Z mysqld_safe Logging to '/mysql/my3306/log/error.log'.
2022-08-22T07:30:50.509845Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/my3306/data

[root@mysql1 bin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

给root用户设置新密码，并刷新权限

mysql> use mysq;
ERROR 1049 (42000): Unknown database 'mysq'
mysql> 
mysql> 
mysql> use mysql;
Database changed
mysql> update user set authentication_string=password('rootroot') where user='root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 1

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

重启数据库，使用密码进入数据库。

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

mysql> exit
Bye
[root@mysql1 bin]# 
[root@mysql1 bin]# 
[root@mysql1 bin]# 2022-08-22T07:36:00.177170Z mysqld_safe mysqld from pid file /mysql/my3306/mysql.pid ended

[1]+  Done                    ./mysqld_safe --defaults-file=/mysql/my3306/my.cnf --skip-grant-tables
[root@mysql1 bin]# 
[root@mysql1 bin]# 
[root@mysql1 bin]# ps -ef | grep mysql
avahi       838      1  0 14:57 ?        00:00:00 avahi-daemon: running [mysql1.local]
root       4008   1539  0 15:36 pts/0    00:00:00 grep --color=auto mysql

[root@mysql1 bin]# systemctl start mysqld
[root@mysql1 bin]# 
[root@mysql1 bin]# ps -ef | grep mysql
avahi       838      1  0 14:57 ?        00:00:00 avahi-daemon: running [mysql1.local]
mysql      4054      1  5 15:36 ?        00:00:00 /mysql/base5738/bin/mysqld --defaults-file=/mysql/my3306/my.cnf --datadir=/mysqldata/my3306/data --socket=/mysql/my3306/mysql.sock --user=mysql
root       4087   1539  0 15:36 pts/0    00:00:00 grep --color=auto mysql
[root@mysql1 bin]# 
[root@mysql1 bin]# mysql -uroot -prootroot
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.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)