mysql password forgotten

MySQL is a commonly used relational database management system that can be used for data storage, data processing, and data analysis. In the process of using MySQL, we may encounter the problem of forgetting the password. If this problem is not dealt with in time, it will cause unnecessary impact on our work and data. In this article, I will introduce readers to how to deal with the problem if you forget your MySQL password.

First of all, 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 this encryption process is irreversible, we must take some specific measures to recover the MySQL password.

Next, I will introduce to readers some commonly used methods to recover MySQL passwords:

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 root User password

3. Enter the MySQL command line

4. After entering the mysql command line, use the following command to change 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 a new password

When using MySQL, the root user has the highest authority, so if you know the password of the root user, then changing the password of MySQL is easy It has become 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

MySQL stores data files in the /var/lib/mysql directory by default. 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('new_password') WHERE User='root';
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.

Conclusion:

When we use MySQL as the database, forgetting the password is a common problem. This article introduces two feasible solutions to solve the problem of forgotten password in MySQL. If you are a user with no MySQL experience, do not modify the MySQL configuration file at will. If you are not very familiar with the methods introduced above, please consult a professional to help solve related problems.

The above is the detailed content of mysql password forgotten. For more information, please follow other related articles on the PHP Chinese website!