Password forgetting happens to even the best of us. If you have access to the server and a user account with sudo enabled, you can still acquire access and reset the root password for your MySQL or MariaDB database.
This guide will explain how to change the root password for MySQL and MariaDB versions both old and new.
You’ll need the following to regain your root MySQL/MariaDB password:
- Using a sudo user, gain access to the Linux server hosting MySQL or MariaDB.
The majority of contemporary Linux versions come pre-installed with either MariaDB, a well-liked drop-in replacement for MySQL, or both. To retrieve the root password, you’ll need to run a separate command for each database type and version.
You can verify your version by using the command:
With MySQL, you’ll get some output similar to this:
MySQL output :
mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Or output like this for MariaDB:
MariaDB output :
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
You’ll need to know which database you’re using and which version you’re using later, so make note of both. The database must then be stopped so you can access it manually.
You must first shut down the database server before changing the root password.
With MySQL, you may achieve that by using:
With regard to MariaDB,
You’ll need to manually access the database server after it has been stopped in order to reset the root password.
You can access the database command line with root rights without entering a password if you launch MySQL and MariaDB without loading information about user privileges. You’ll be able to access the database in this way without being detected.
To accomplish this, you must prevent the database from loading the grant tables, which include data on user privileges. You should avoid networking as well to prevent other customers from connecting because this poses a small security risk.
Start the database without enabling networking or loading the grant tables:
This command will run in the background so you can keep using your terminal by adding an ampersand at the end.
Now that you are logged in as the root user, which shouldn’t require a password, you can access the database.
Instead, you’ll receive a database shell prompt right away.
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)]>
You can modify the root password now that you have root access.
Modern versions of MySQL support changing the root password easily by using the ALTER USER command. The grant tables aren’t loaded right now, thus this command won’t operate.
Let’s use the FLUSH PRIVILEGES command to instruct the database server to refresh the grant tables.
The root password can now truly be changed.
Use the following command for MariaDB 10.1.20 and newer, as well as MySQL 5.7.6 and newer.
Use this for MariaDB 10.1.20 and MySQL 5.7.5 and earlier:
Make sure to substitute your new password of choice for new password.
Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE … SET to reset the root password instead.
After this, don’t forget to reload the grant tables.
In either case, you need to see evidence that the command was effectively carried out.
Query OK, 0 rows affected (0.00 sec)
You can now stop the manual instance of the database server and restart it as normal because the password has been updated.
First, terminate the database server instance that you manually launched in Step 3. This command looks up the PID, or process ID, of the MySQL or MariaDB process and sends SIGTERM, instructing it to gracefully terminate after carrying out cleanup procedures.
Use: for MySQL
Use this for MariaDB
Use systemctl to restart the service after that.
Use: for MySQL
Use this for MariaDB:
Now, you can verify that the new password has been appropriately applied by executing:
The freshly assigned password should now be requested by the command. If you type it in, you should successfully access the database prompt.
The MySQL or MariaDB server’s administrative access has been restored. Ensure that the new root password you select is robust and secure, and store it safely.