mysql forgot root password

MySQL is a commonly used relational database management system that is often used to build web applications and manage data. In MySQL, the root user is the user with the highest privileges and is usually used to manage the entire database. But in some cases, we may forget the root user’s password. This article will introduce some common ways to reset the MySQL root user password.

1. Use mysqld_safe to start MySQL

If you forget the root user password, the first method you should try is to use mysqld_safe to start the MySQL service. This method starts MySQL without requiring a password and allows you to change the root user's password. The steps are as follows:

First, stop the MySQL service.

$ sudo systemctl stop mysql

Then, use mysqld_safe to start the MySQL service and specify --skip-grant-tables.

$ sudo mysqld_safe --skip-grant-tables &

--skip-grant-tables parameter will cause MySQL to ignore the access control list, which means anyone can connect to MySQL server and have root permissions. Because of this, you should ensure that only trusted users have access to the server.

Next, connect to the MySQL server in another terminal window.

$ mysql -u root

At the MySQL prompt, run the following command to change the root user's password.

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

In this command, replace new_password with your new password.

Finally, exit MySQL and restart the MySQL service.

mysql> FLUSH PRIVILEGES;
mysql> quit;
$ sudo systemctl start mysql

Now you can log in as the root user with the new password!

2. Use the mysql_secure_installation script

If you cannot use mysqld_safe to start MySQL, you can also try using the mysql_secure_installation script to reset the root user's password. This script can quickly make some settings for MySQL, including resetting the root user's password, deleting anonymous users, disabling remote login, etc.

First, run the following command to install the mysql_secure_installation script.

$ sudo apt-get update
$ sudo apt-get install mysql-server

After the installation is complete, you can use the following command to run the mysql_secure_installation script.

$ sudo mysql_secure_installation

During the running process, you need to answer several questions, including setting the root user password, deleting anonymous users, etc. When setting a password, even if you have forgotten the root user's original password, you can leave it blank and choose to set a new password. Once everything is set up, you will be able to log in as root using your new password.

3. Use skip-grant-tables parameter to start MySQL

If the above two methods cannot be used, the last attempt is to use skip-grant-tables parameter to start MySQL. While this method can start MySQL, it leaves the database in a vulnerable state and should not be used in a production environment.

First, find the MySQL configuration file my.cnf or my.ini. The file should contain the following lines:

[mysqld]
skip-grant-tables

If the file does not exist, you can create a new file and add the above two lines to file and save it as my.cnf or my.ini.

Then, use the following command to restart MySQL.

$ sudo systemctl restart mysql

After restarting, you will be able to log in to MySQL as the root user without entering a password. At the MySQL prompt, enter the following command to change the root user's password.

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

Replace new_password with your new password, then exit MySQL and restart the MySQL service .

mysql> FLUSH PRIVILEGES;
mysql> quit;
$ sudo systemctl restart mysql

Now you can log in as the root user with the new password!

Summary

Forgetting the MySQL root user password is a common problem, but there are many ways to solve it. In most cases, password reset is easy using the mysqld_safe or mysql_secure_installation scripts. If these methods don't work, starting MySQL with the skip-grant-tables parameter may be a last resort, but is not recommended in a production environment.

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