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

How to use JavaScript strategy pattern to validate forms

Table of contents Overview Form validation withou...

Example of pre-rendering method for Vue single page application

Table of contents Preface vue-cli 2.0 version vue...

MySQL index pushdown details

Table of contents 1. Leftmost prefix principle 2....

mysql 8.0.15 winx64 decompression version graphic installation tutorial

Every time after installing the system, I have to...

Web project development VUE mixing and inheritance principle

Table of contents Mixin Mixin Note (duplicate nam...

IIS 7.5 uses URL Rewrite module to achieve web page redirection

We all know that Apache can easily set rewrites f...

Uniapp's experience in developing small programs

1. Create a new UI project First of all, our UI i...

How to build mysql master-slave server on centos7 (graphic tutorial)

This article mainly introduces how to build a MyS...

CSS realizes process navigation effect (three methods)

CSS realizes the process navigation effect. The s...

MySQL database transaction example tutorial

Table of contents 1. What is a transaction? 2. Th...

Summary of three rules for React state management

Table of contents Preface No.1 A focus No.2 Extra...