Reset method of MySQL root password

Time:2021-9-23

For DBAs, it is fatal to lose the password of superuser root,

You can skip the permission table by adding the — Ship grant tables parameter.

1. Forget the root password and cannot access the database:

At this time, you need to stop the database forcibly. Check the MySQL process number first

Kill the MySQL process. The command is as follows:

After kill, you can check whether there are any processes


[[email protected] tmp]# kill -9 9840 1
[[email protected] tmp]# ps -ef |grep mysql

It is recommended that the shutdown of the database should not affect the business. For the shutdown of the service, it is best to record the password. You can use KeePass to save the password software!

Then add the skip permission table parameter and restart the database. In this way, you can enter the database even without entering a password.


[[email protected] bin]# mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
[1] 4854

You can enter MySQL directly without a password

Set a new password for the root user and refresh the permissions. After MySQL 5.7, the password field under the database uses authentication_ String field instead.


(product)[email protected]:mysql.sock [(none)]> use mysql;
Database changed
(product)[email protected]:mysql.sock [mysql]> update user set authentication_string=password('123456') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

(product)[email protected]:mysql.sock [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

After setting, restart the database. Note that there is no need to add the — skip grant tables parameter when restarting. Start the service normally and enter the new password to enter the database normally. After restarting here, you can’t enter MySQL without a password.


[[email protected] bin]# mysqld_safe --deaults-file=/etc/my.cnf &
[2] 6720
[[email protected] bin]# 2020-01-16T02:55:45.223195Z mysqld_safe Logging to '/mysql/mysql3306/logs/error.log'.
2020-01-16T02:55:45.262302Z mysqld_safe A mysqld process already exists

[2]+  Exit 1                  mysqld_safe --deaults-file=/etc/my.cnf
[[email protected] bin]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Enter the password connection here to enter normally. It is the modified password 123456


[[email protected] bin]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23-log 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.

(product)[email protected]:mysql.sock [(none)]> use mysql
Database changed
(product)[email protected]:mysql.sock [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

The above is the details of the MySQL root password reset method. For more information about MySQL root password reset, please pay attention to other related articles of developeppaer!