If you’re a Linux user who’s new to MySQL, you might find yourself in a situation where you’ve forgotten your MySQL root password. This might seem like a major hurdle, but don’t worry. With the right instructions and a little patience, you can reset your MySQL root password using command-line tools. This guide will walk you through the process step by step, making it less intimidating and more accessible for beginners.
Understanding Your Server Version
Before you can reset your MySQL root password, you need to identify the version of MySQL running on your system. Different versions of MySQL may require different commands to recover the root password.
To identify your server version, open a terminal window and enter the following command:
mysql --versionThe output will show you the version of MySQL you’re running. Keep this information handy, as you’ll need it later.
The Process of Resetting Your MySQL Root Password
Halting the MySQL Service
The first step in changing your root password is to stop the MySQL server. You can do this by entering one of the following commands:
sudo systemctl stop mysqlor
sudo systemctl stop mysqldStarting the MySQL Server without Loading the Grant Tables
Next, you’ll need to start the database server without loading the grant tables. This can be done using the following command:
sudo mysqld_safe --skip-grant-tables &The ampersand (&) at the end of the command allows the program to run in the background, freeing up your shell for further use. When the --skip-grant-tables option is used, anyone can connect to the database server without a password and with all privileges.
Logging into the MySQL Shell
At this point, you can connect to the database server as the root user by entering the following command:
mysql -u rootSetting a New Root Password
Now it’s time to set a new root password. Depending on the version of MySQL you’re running, you’ll need to use different commands.
For MySQL 5.7.6 and later, enter the following commands:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;If the ALTER USER statement does not work, you can try modifying the user table directly using the following commands:
UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';s
FLUSH PRIVILEGES;For MySQL 5.7.5, enter the following commands:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;If everything goes well, you should see the following output:
Query OK, 0 rows affected (0.00 sec)Stopping and Starting the Database Server Normally
Now that the root password is set, stop the database server by entering the following command:
mysqladmin -u root -p shutdownYou will be prompted to enter the new root password. Then, start the database server normally. For MySQL, enter one of the following commands:
sudo systemctl start mysqlor
sudo systemctl start mysqldVerifying the Password
To verify that the new root password has been applied correctly, enter the following command:
mysql -u root -pYou will be prompted to enter the new root password. If the password was set correctly, you should be logged in to your database server.
Conclusion
In conclusion, resetting a MySQL root password can be a simple process when using the command line tools available in Linux. Following the steps outlined in this guide, users can quickly recover a lost or forgotten root password, allowing them to access their MySQL databases again. However, it is important to proceed with caution and choose a strong password to ensure the security of your data. By keeping these tips in mind, users can reset their MySQL root password with confidence and ease.