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.
sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
sudo mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
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.
sudo mysql_secure_installation
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.
sudo systemctl stop mysql sudo cp -a /var/lib/mysql /var/lib/mysql.bak
skip-grant-tables
sudo nano /etc/mysql/my.cnf
Save and exit.
sudo mysqld_safe &
sudo mysql -u root
mysql> use mysql; mysql> repair table user; mysql> exit
mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root'; mysql> FLUSH PRIVILEGES; mysql> exit
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!