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

MySQL 5.7.11 zip installation and configuration method graphic tutorial

1. Download the MySQL 5.7.11 zip installation pac...

Detailed explanation of how to implement secondary cache with MySQL and Redis

Redis Introduction Redis is completely open sourc...

Implementation ideas for docker registry image synchronization

Intro Previously, our docker images were stored i...

Linux file and user management practice

1. Display the files or directories in the /etc d...

JavaScript adds event listeners to event delegation in batches. Detailed process

1. What is event delegation? Event delegation: Ut...

Solution to blank page after Vue packaging

1. Solution to the problem that the page is blank...

How to transfer files between Windows and Linux

File transfer between Windows and Linux (1) Use W...

Solution to the impact of empty paths on page performance

A few days ago, I saw a post shared by Yu Bo on G...

MySQL 8.0.14 installation and configuration method graphic tutorial

This article records the installation and configu...

Solve the problem of secure_file_priv null

Add secure_file_priv = ' '; then run cmd ...

First experience of creating text with javascript Three.js

Table of contents Effect Start creating text Firs...

Detailed steps to install Docker 1.8 on CentOS 7

Docker supports running on the following CentOS v...

About front-end JavaScript ES6 details

Table of contents 1. Introduction 1.1 Babel Trans...