Mysql root password forgotten

MySQL is a popular open source database management system that is widely used in enterprise and personal application development. However, due to various reasons, sometimes it happens that you forget your MySQL root password. When this happens, you don’t have to worry as there are several ways to reset the MySQL root password.

First, you can try to log in using the MySQL default root user and an empty password. This may be because you have not changed or forgotten to change the password. Run the following command in command line mode. If you can log in directly, you can change the MySQL root password by modifying the password:

mysql -u root -p

If you cannot log in with the default root user and empty password, you can consider Use the following method to reset your MySQL root password:

Method 1: Use safe mode to reset the MySQL root password

Step 1: Shut down the MySQL service.

In MySQL server, you can use the following command to shut down the MySQL service:

sudo systemctl stop mysql

Step 2: Start MySQL server safe mode.

In Linux systems, you need to use root privileges to start the MySQL server safe mode. You can run the following command:

sudo mysqld_safe --skip-grant-tables &

Step 3: Log in to the MySQL server, no password is required.

After starting the MySQL server in safe mode, you can log in to the MySQL server without providing a password. Log in by entering the following command:

mysql -u root

Step 4: Change the root user password using the following command:

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

You need to replace "new_password" with the new password you want to set. This will change the password of the MySQL root user.

Step 5: Refresh permissions and exit MySQL.

After updating the password, you need to refresh the MySQL user permissions and exit MySQL. Enter the following command to complete this operation:

FLUSH PRIVILEGES;
EXIT;

Step 6: Restart the MySQL server.

After completing the above steps, you need to turn off safe mode and restart the MySQL server. Do this by entering the following command:

sudo systemctl stop mysql
sudo systemctl start mysql

Now you have successfully reset the MySQL root password and restarted the MySQL server.

Method 2: Reset the MySQL root password using MySQL's init-file option

Step 1: Create a file named resetpass.sql and open it.

Enter the following command in the file:

UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

Replace "new_password" with the new password you want to set.

Step 2: Stop the MySQL service.

You can stop the MySQL service by running the following command:

sudo systemctl stop mysql

Step 3: Start the MySQL service using the --init-file option.

At the command line, pass the resetpass.sql file to the MySQL server to initialize the new password as follows:

sudo mysqld --init-file=/path/to/resetpass.sql &

To specify the correct path, change /path/to/resetpass. sql replaced with the actual path to the resetpass.sql file.

Step 4: Check the MySQL log file for abnormalities.

In the previous step, you can now restart the MySQL service. After starting MySQL, you can confirm whether there are any abnormal logs by viewing the MySQL log file:

sudo tail -f /var/log/mysql/error.log

Step 5: Delete the resetpass.sql file.

After starting the MySQL server, you can delete the resetpass.sql file to prevent others from accessing and changing the root password.

Method 3: Use dpkg-reconfigure to reset the MySQL root password

Step 1: Stop the MySQL server.

At the command line, stop the MySQL server by entering the following command:

sudo systemctl stop mysql

Step 2: Start the dpkg-reconfigure tool.

At the command line, enter the following command to start the dpkg-reconfigure tool:

sudo dpkg-reconfigure mysql-server-5.7

Step 3: Follow the wizard prompts and reset the MySQL root password.

In the wizard of the dpkg-reconfigure tool, you need to set a new password for the MySQL root user. Follow the tool prompts to complete setting your new password.

Step 4: Restart the MySQL server.

Now that you have successfully reset the MySQL root password, please restart the MySQL server by entering the following command:

sudo systemctl start mysql

Conclusion

In this article, we have covered three different Method to reset MySQL root password. While each method is different, they can all be used to avoid the problems of forgetting your MySQL root password. No matter which method you choose to reset your password, you need to pay attention to security and ensure that only people with access to the MySQL server can reset the password. By taking necessary precautions and following the correct steps, you can easily reset your MySQL root password.

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