Introduction
The best people will also forget their passwords. If you forget or lose the root password for the MySQL or MariaDB database, as long as you can access the server and have onesudo-enabled User Account, you can still get access and reset your password.
This tutorial will explain how to reset the root password for older and newer versions of MySQL and MariaDB.
Prerequisites
To restore the root MySQL/MariaDB password, you need:
- Access a Linux server running MySQL or MariaDB and have a sudo user.
Step 1 ——Determine the database version
Most modern Linux distributions come with MySQL or MariaDB, a popular MySQL-compatible alternative. Depending on the database used and its version, you need to use different commands to restore the root password.
You can check your version with the following command:
mysql --version
For MySQL, you will see the following output:
[secondary_label MySQL output] mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
For MariaDB, you will see the following output:
[secondary_label MariaDB output] mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Note the database and version you are running because you will use them later. Next, you need to stop the database so that you can access it manually.
Step 2 - Stop the database server
To change the root password, you must first shut down the database server.
For MySQL, you can use the following command:
sudo systemctl stop mysql
For MariaDB, you can use the following command:
sudo systemctl stop mariadb
After the database server is stopped, you will access it manually to reset the root password.
Step 3 - Restart the database server without permission checking
If you run MySQL and MariaDB without loading information about user permissions, it will allow you to access the database command line with root permissions without providing a password. This will allow you to access the database without knowing your password.
To do this, you need to prevent the database from loading grant tables that store user permission information, and you should also skip the network to prevent other clients from connecting.
Don't load grant tables when starting the database or enable network:
sudo mysqld_safe --skip-grant-tables --skip-networking &
The "&" at the end of this command will cause the process to run in the background so that you can continue using the terminal.
You can now connect to the database as root, which should not require a password.
mysql -u root
You will immediately see a database shell prompt.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Now that you have root access, you can change the root password.
Step 4 ——Change the root password
For newer versions of MySQL, an easy way to change the root password is to useALTER USEROrder. However, this command does not work now because grant tables is not loading.
Let's tell the database server to issueFLUSH PRIVILEGESCommand reload grant tables.
FLUSH PRIVILEGES;
Now we can actually change the root password.
ForMySQL 5.7.6 and newer versionsas well asMariaDB 10.1.20 and later, use the following command.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
ForMySQL 5.7.5 and olderas well asMariaDB 10.1.20 and older,use:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Please make sure tonew_passwordReplace with the new password you selected.
In either case, you should see a confirmation that the command has been successfully executed.
Query OK, 0 rows affected (0.00 sec)
The password has been changed, and now you can stop the manual instance of the database server and restart it as before.
Step 5 ——Restart the database server normally
First, stop the database server instance you manually started in step 3. This command searches for the PID (process ID) of the MySQL or MariaDB process and sendsSIGTERMto inform it to exit smoothly after performing a cleanup operation. You can learn more in this Linux Process Management Tutorial.
For MySQL, use:
sudo kill `cat /var/run/mysqld/`
For MariaDB, use:
sudo kill `/var/run/mariadb/`
Then, usesystemctlRestart the service.
For MySQL, use:
sudo systemctl start mysql
For MariaDB, use:
sudo systemctl start mariadb
You can now confirm that the new password is applied correctly by running the following command:
mysql -u root -p
The command should now prompt for the newly assigned password. After entering, you should get access to the database prompts as expected.
in conclusion
You have now restored administrative access to your MySQL or MariaDB server. Make sure that the new root password you choose is powerful and secure and save it in a safe place.
This is the article about resetting your MySQL or MariaDB root password. For more information about resetting root passwords for MySQL or MariaDB, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!