How to fix mysql password if I forget it

First, we need to understand how MySQL passwords are stored. MySQL stores passwords as encrypted hash strings using the SHA1() encryption function and a random string called Salt. Since the encryption process of MySQL passwords is irreversible, we need to implement some specific measures to be able to restore it.

Method 1: Use the root user to change the password

If you have root permissions, you can use the following steps to change the password:

1. Log in to the MySQL server as root

mysql -u root -p

2. Enter the root user password

3. Enter the MySQL command line

4. Enter mysql After entering the command line, use the following command to modify the root user password:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Please replace "new_password" with the new password you want to set, and make sure to add a semicolon at the end of the MySQL command line;

5. Exit MySQL and log in again with the new password

In When using MySQL, the root user has the highest authority, so if you know the root user's password, changing the MySQL password becomes very simple.

Method 2: Reset MySQL password

If you cannot change the password through the root user, or do not know the root password at all, then resetting the MySQL password may be your best choice. The following are two commonly used methods to reset MySQL password:

Method 1: Use skip-grant-tables to reset MySQL password

This method will skip the MySQL user authentication process, And allows you to log into MySQL without providing a password. The following are the specific steps:

1. Stop the MySQL service

sudo systemctl stop mysql

2. Execute the following command to skip MySQL user authentication Process:

sudo mysqld_safe --skip-grant-tables &

3. Log in to the MySQL server as root

mysql -u root

4. After entering the MySQL command line, use the following command to change the password:

mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';

Please replace "new_password" with the new password you want to set, and make sure to add a semicolon at the end of the MySQL command line;

5. Refresh the mysql.user table

mysql> FLUSH PRIVILEGES;

6. Exit MySQL and restart MySQL

sudo systemctl start mysql

7. Log back into MySQL using the new password

Method 2: Use the reset script to reset the MySQL password

This method requires you to run the script as root , and need to know the location of the MySQL data files. The following are the specific steps:

1. Stop the MySQL service

sudo systemctl stop mysql

2. Find the MySQL data file

By default, MySQL stores its data files in the /var/lib/mysql directory. The exact location of the data files can be found by executing the following command:

sudo find / -name "*.frm"

3. Create a reset script

Please use the following command to save the following code to the resetmysql.sh file:

#!/bin/bash
/usr/sbin/mysqld --skip-grant-tables --skip-networking  &
sleep 5s

mysql -u root <<EOF
UPDATE mysql.user SET Password=PASSWORD(&#39;new_password&#39;) WHERE User=&#39;root&#39;;
FLUSH PRIVILEGES;
EOF

killall mysqld
sleep 5s
/usr/sbin/mysqld --skip-networking &

Please replace "new_password" with the new password you want to set.

4. Run the script to reset the MySQL password

Use the following command to run the reset script with root privileges:

sudo bash resetmysql.sh

5. Start MySQL

sudo systemctl start mysql

6. Log in to MySQL again with a new password

At this point, readers should have I know the following two methods to solve the problem of forgotten MySQL password: use the root user to change the password, and use the reset MySQL password script to reset the password.

The above is the detailed content of How to fix mysql password if I forget it. For more information, please follow other related articles on the PHP Chinese website!