How to reset MySQL root password

How to reset MySQL root password

For a DBA, losing the password of the super user root is fatal.

You can skip the grant tables by adding the --ship-grant-tables parameter.

1. Forgot the root password and cannot enter the database:

At this time, you need to force the database to stop. First check the MySQL process number

Kill the MySQL process with the following command:

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 to stop the service when the operation does not affect the business. It is best to record the password well. You can use keepass to save the password software!

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 

Directly enter mysql without a password to enter

Set a new password for the root user and refresh permissions. After MySQL 5.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 complete, restart the database. Note that you do not need to add the --skip-grant-tables parameter when restarting. Start the service normally and enter the new password to access the database normally. After restarting here, I tested that I could not enter mysql 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|
+--------------------+
4 rows in set (0.01 sec)

The above is the detailed content of how to reset the MySQL root password. For more information about resetting the MySQL root password, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Problem of retrieving root password in MYSQL 5.7 under Linux (tested and available)
  • Quick solution for forgetting MySQL8 password
  • Solution to forgetting mysql database password

<<:  Usage of HTML H title tag

>>:  JavaScript implementation of the back to top button example

Recommend

Detailed explanation of using scp command to copy files remotely in Linux

Preface scp is the abbreviation of secure copy. s...

Vue improves page response speed through lazy loading

Table of contents Overview What is lazy loading? ...

How to switch directories efficiently in Linux

When it comes to switching directories under Linu...

How to connect to a remote docker server with a certificate

Table of contents 1. Use scripts to encrypt TLS f...

Use js to write a simple snake game

This article shares the specific code of a simple...

Detailed explanation of how Tomcat implements asynchronous Servlet

Preface Through my previous Tomcat series of arti...

Implementation of iview permission management

Table of contents iview-admin2.0 built-in permiss...

Detailed explanation of JavaScript Promise and Async/Await

Table of contents Overview Four examples Example ...

In html table, set different colors and widths for each cell

It is recommended that you do not set the width, h...

How to use provide to implement state management in Vue3

Table of contents Preface How to implement Vuex f...

Detailed usage of Vue more filter widget

This article example shares the implementation me...

Application of anchor points in HTML

Set Anchor Point <a name="top"><...