How to recover lost MySQL 5.6 password

MySQL is a popular relational database management system that is widely used in websites, applications, and other data-driven systems. However, sometimes we forget the administrator password of the MySQL database, resulting in the inability to log in to the database for management. At this time, we need to recover the password of the MySQL database.

In MySQL 5.6, the password hash encryption algorithm uses sha256 by default, and some manual operations are required during the recovery process. This article will explain how to recover a lost password for MySQL 5.6.

Step 1: Stop the MySQL service

Before restoring the password, you need to stop the MySQL service first. On Linux or macOS you can use the following command:

$ sudo service mysql stop

On Windows you can use the following command:

> net stop mysql

Step 2: Create a springboard file

During the password recovery process , we need to create a springboard file to modify the permission system of the MySQL database so that we can create a new administrator account. A springboard file can be created using the following command:

$ echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';" > /var/lib/mysql/mysql-init && sudo chmod 700 /var/lib/mysql/mysql-init && sudo chown mysql:mysql /var/lib/mysql/mysql-init

The above command will set the new password to newpassword, which you can replace with your own password.

Step 3: Restart the MySQL service

After the springboard file is created, you need to restart the MySQL service. You can use the following command on Linux or macOS:

$ sudo service mysql start

You can use the following command on Windows:

> net start mysql

Step 4: Run the MySQL service and use the springboard file to change the password

After MySQL restarts, you can use the following command to run the MySQL service and use the springboard file to change the password:

$ sudo mysqld_safe --init-file=/var/lib/mysql/mysql-init &

Note that the springboard file path here should be consistent with the one set when creating the springboard file above.

After running, you will re-enter the MySQL console. In the console, you can use the following command to view existing administrator accounts:

mysql> SELECT User FROM mysql.user WHERE Super_priv='Y';

After viewing the account list, you can use the following command to modify the administrator account password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Here newpassword refers to the new password you need to set.

Step 5: Complete password recovery

After changing the password, you can use the following command to check whether the modified password is effective:

mysql> SELECT User, Host, authentication_string FROM mysql.user;

The above is the method for recovering the password in MySQL 5.6 . Of course, in order to avoid password loss again, it is recommended that you regularly back up the MySQL database or use a password management tool to manage database passwords.

The above is the detailed content of How to recover lost MySQL 5.6 password. For more information, please follow other related articles on the PHP Chinese website!