rootforgot password mysql

When performing MySQL database management, we often need to use the root account to operate. However, due to various reasons, we sometimes forget the password of the root account, which will directly affect our data management work. So, what should we do when we forget the MySQL root password?

In the MySQL database, there are two common situations when forgetting the root password:

1. Forgot the root password, but have the permissions of the root account

2. Forgot the root password , do not have root permissions

For the first case, you only need to reset the password of the root account. In the second case, you need to obtain root permissions before resetting the password. Below I will introduce the solutions in these two cases respectively.

1. Forgot the root password, but have the permissions of the root account

In this case, we can use the SET PASSWORD statement to reset the password of the root account.

First, log in to the MySQL server as root:

mysql -u root -p

After logging in, enter the following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

Where, newpassword is the new password you want to set.

If you want the root account to be able to connect to the server from any host, you can use the following command:

SET PASSWORD FOR 'root'@'%' = PASSWORD('newpassword');

Then you can re-login to the MySQL server using the new password:

mysql -u root -p newpassword

2. Forgot the root password and do not have root permissions

In this case, although we cannot directly reset the root password, we can obtain root permissions by modifying the MySQL configuration file. Below I will introduce the specific steps:

1. Stop the MySQL service

sudo service mysql stop

2. Use sudo permissions to edit the MySQL configuration file my.cnf

sudo vim /etc/mysql/my.cnf

Add the following content under the [mysqld] section:

skip-grant-tables
skip-networking

Then save and exit.

3. Use sudo permissions to start the MySQL service

sudo service mysql start

4. Log in to the MySQL server as root

mysql -u root

Because we added the skip-grant-tables option, in this case, no password is required when logging in. After logging in, you can use the following command to reset the root password:

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

where newpassword is what you want Set a new password.

Note: The following command is required in MySQL 5.7.6 and above:

UPDATE mysql.user SET authentication_string=PASSWORD('newpassword') WHERE User='root' AND Host= 'localhost';

After the update is completed, you need to re-enable the authorization table, re-edit the my.cnf file and delete the skip-grant-tables and skip-networking options.

5. Restart the MySQL service

sudo service mysql restart

Now you can log in to MySQL using the new password.

Summary:

Don’t worry when you forget the MySQL root password. We can use the above two methods to solve the problem. For the first case, only a simple password reset operation is required; for the second case, we need to modify the MySQL configuration file and restart the server. In either case, it's important to stay calm and follow the steps closely.

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