mysql reset root password

For the database, if you forget the password of the root user, it will be very fatal, but we can add the --ship-grant-tables parameter to skip the permission table.

The specific method is as follows:

Forgot the root password and cannot enter the database:

At this time, you need to force the database to stop, check MySQL first Process number

Kill the MySQL process, the command is as follows:

After killing, you can check whether there are still processes

[root@tse2 tmp]# kill -9 9840 1
[root@tse2 tmp]# ps -ef |grep mysql

It is recommended that when the operation of stopping the database does not affect the business, it is best to record the password and use keepass to save the password software!

Free learning video tutorial sharing: mysql video tutorial

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

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

Enter mysql directly without a password to enter

Set a new password for the root user and refresh the permissions , after MySQL5.7, the password field under the library is replaced by the authentication_string field.

(product)root@localhost:mysql.sock [(none)]> use mysql;
Database changed
(product)root@localhost: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)root@localhost:mysql.sock [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

After the settings are completed, 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, I will test that mysql cannot be entered without a password.

[root@tse2 bin]# mysqld_safe --deaults-file=/etc/my.cnf &
[2] 6720
[root@tse2 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
[root@tse2 bin]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Enter the password here to connect and you can enter normally. It is the modified password 123456

[root@tse2 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)root@localhost:mysql.sock [(none)]> use mysql
Database changed
(product)root@localhost:mysql.sock [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
rows in set (0.01 sec)

Recommended related article tutorials: mysql tutorial

The above is the detailed content of mysql reset root password. For more information, please follow other related articles on the PHP Chinese website!