linux mysql forgot root password

Linux MySQL Forgot Root Password

When using a Linux system and installing a MySQL database, we usually create a root user as the administrator. But sometimes, we may forget the password of the root user or encounter a situation like being unable to log in as the root user. Such a situation could result in the inability to access, maintain and back up our databases, which could impact our business. Therefore, this article will introduce some solutions to help us reset MySQL root password.

Method 1: Use the mysqladmin command line tool

The mysqladmin command line tool is a commonly used tool in MySQL. It can be used to perform some MySQL management operations. We can use this to reset the root user’s password.

1. First, we need to stop the MySQL service and close the MySQL database:

sudo systemctl stop mysql

2. Then, we need to use sudo permissions to run the MySQL service management command and skip Authorization authentication to avoid being unable to log in:

sudo mysqld_safe --skip-grant-tables &

3. Next, we need to enter the MySQL command line:

sudo mysql -u root

4. Now, we have entered the MySQL command OK, enter the following command (note: the new password needs to be set according to the actual situation):

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

5. In order to make the password just set take effect, we need to refresh the permissions:

FLUSH PRIVILEGES;

6. Finally, we exit the MySQL command line, stop the MySQL service, and restart the service:

exit
sudo systemctl stop mysql
sudo systemctl start mysql

At this point, we have successfully reset the MySQL root password. You can log back into MySQL using the new password.

Method 2: Use the mysql_secure_installation script

The mysql_secure_installation script is an installation tool provided by MySQL. It performs MySQL security settings interactively, including removing anonymous users, closing external root logins, and deleting Test database etc. Additionally, it can be used to reset the root user’s password.

1. We need to run the script with sudo permissions:

sudo mysql_secure_installation

2. When executing the script, we will encounter several problems. First, it will give us the option to remove anonymous users. If you want to keep the user anonymous, select No. Otherwise select Yes.
3. Next, it will ask us to set a new root user password. Enter new password and confirm.
4. Then, the script will let us choose whether to prohibit the root user from logging in remotely. If you want to allow remote login, select No. Otherwise select Yes.
5. Finally, the script will ask us if we want to delete the test database. If you want to continue using the test database, select No. Otherwise, select Yes to delete the test database.

Now we have successfully reset the MySQL root password using the mysql_secure_installation script and made some security settings. You can log back into MySQL using the new password.

Method 3: Recover the password from the database file

If the above two methods do not work, we can try to recover the password from the database file. However, this method has certain risks, which may cause data loss or damage the database structure. Therefore, we should proceed with caution and ensure that we have backed up the database.

1. Stop the MySQL service and back up the database file:

sudo systemctl stop mysql
sudo cp -a /var/lib/mysql /var/lib/mysql.bak

2. Edit the MySQL configuration file and add the following line:

skip-grant-tables

sudo nano /etc/mysql/my.cnf

Save and exit.

3. Now, we need to start the MySQL service as a user with sudo permissions:

sudo mysqld_safe &

4. Then, we can use the following command to enter the MySQL command line:

sudo mysql -u root

5. Then, we need to check and repair the MySQL database table:

mysql> use mysql;
mysql> repair table user;
mysql> exit

6. At this point, we have successfully entered the MySQL command line. You can use the following command to reset the root user password (note: the new password needs to be set according to the actual situation):

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

7. Finally, we need to stop the MySQL service and restore the configuration we modified before File:

sudo nano /etc/mysql/my.cnf

Remove the skip-grant-tables line, save and exit.

sudo systemctl stop mysql
sudo systemctl start mysql

At this point we have successfully recovered the MySQL root password from the database file. You can log back into MySQL using the new password.

Summary

When using a Linux MySQL database, if you encounter the situation of forgetting the root password or being unable to log in, you can use the above three methods to reset the password. However, these methods have certain risks. Please make sure you have backed up the database and operate with caution. It is also recommended to set a strong password and change the password regularly to enhance database security.

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