How to reset your MySQL or MariaDB root password
Forgotten your MySQL or MariaDB root password?
Don’t worry, it’s not the end of the world. You can reset your MySQL or MariaDB root passwords, provided you have access to the server and a sudo-enabled account.
Step 1 – Identify your current database version
Depending on your database type, and it’s version, you will need to use different commands to recover the root password.
You can check your version using the following command
mysql --version
You should see this:
MySQL output
mysql Ver 14.X Distrib 5.7.X, for Linux (x86_64) using EditLine wrapper
or in MariaDB:
MariaDB output
mysql Ver 15.x Distrib 5.5.Y-MariaDB, for Linux (x86_64) using readline 5.Z
Note the version, this will be needed later.
Step 2 – Stop the database server
To change the root password you must first shut down the database server.
In MySQL:
$ sudo systemctl stop mysql
In MariaDB:
sudo systemctl stop mariadb
Once the database is stopped you will be able to access it manually to reset the root password.
Step 3 – Reboot the database server without permission checking
Running MySQL or MariaDB without loading information about user privileges will allow access to the database command line, with root privileges and without a password.
To do this, you need to prevent the database from loading grant tables (these store user privilege information). Note this is a security risk, so skip networking to prevent access from other clients.
To start the database without loading grant tables and without enabling networking:
sudo mysqld_safe --skip-grant-tables --skip-networking &
Remember to add the & at the end so that the process runs in the background, allowing you to continue using the terminal.
Connect to the database as the root user, a password should not be required:
mysql -u root
A database shell prompt should appear. In MySQL this will look like:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
In MariaDB this will look like:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
You now have root access!
Step 4 – Change the root password
In modern versions of MySQL you can change the root password using the command:
ALTER USER
This command will not work whilst the grant tables are not loaded, so don’t worry about it for now. You must first reload the grant tables by running the following command:
mysql> FLUSH PRIVILEGES;
Now you can change the root password.
For MySQL 5.7.6 and later versions, as well as MariaDB 10.1.20 and later versions, use the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older, as well as MariaDB 10.1.20 and older, use:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Substituting new_password with your new secure password. Consider using a random text generator to ensure your new password is secure. Write it down somewhere safe!
You should see a confirmation similar to the following:
Query OK, 0 rows affected (0.00 sec)
The password has now been changed.
Step 5 – Restarting the database server
First, stop the instance of the database server started in Step 3. This command searches for the Process ID (PID) of MySQL/MariaDB process and sends a SIGTERM, informing the database that can now be exited, without causing any havoc.
For MySQL use:
sudo kill `cat /var/run/mysqld/mysqld.pid`
For MariaDB use:
sudo kill `/var/run/mariadb/mariadb.pid'
Next, restart the service using the systemctl command.
For MySQL use:
sudo systemctl start mysql
For MariaDB use:
sudo systemctl start mariadb
Confirm the new password has been applied by running:
mysql -u root -p
This command prompts login, using the newly assigned password for the root user. Enter your new password and you should gain access.
Original Content by Mateusz Papiernik and edited by the author of this post according to the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
