How To Reset Your MySQL or MariaDB Root Password
Posted on September 18th, 2020
This tutorial will help you reset the root password of MySQL and MariaDB.
Step 1 — Identifying the Database Version
In order to recover root password you’ll have to use different commands depending on the database versions.
Run the following command to check your version mysql --version
And the Output will be for MySQL: mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
And output for MariaDB will be: mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Let’s make the note of the database and the version you are on, which is required later. Now let’s stop the database so we can access it manually.
Step 2 — Stopping the Database Server
In order to change the root password lets’s shut down the database server.
For MySQL: sudo systemctl stop mysql
For Maria DB: sudo systemctl stop mariadb
Now the database server has stopped and we will access it manually to rerset the root password.
Step 3 — Restarting the Database Server Without Permission Checking
We need to make sure to run MySQL and MariaDB without loading information about user privileges, so it will allow us to access database command line with root privilages, which will grant access to the database. You must stop the database from loading the grant tables.
Lets’s start the database without loading the grant tables.
sudo mysqld_safe --skip-grant-tables --skip-networking &
The command will make this process run in the background so you can continue to use the terminal.
Now lets connect to the database as the root user, which will not ask for the password.
mysql -u root
It will prompt a database shell:
MySQL prompt
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
MariaDB prompt
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
This has given you the root access and can change the root password.
Step 4 — Changing the Root Password
Let’s use FLUSH PRIVILEGES command so the database server to reload the grant tables.
mysql> FLUSH PRIVILEGES;
Now we have the privillage to change the root password.
For MySQL 5.7.6 and newer MariaDB 10.1.20 we can 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 we can use the dollowing command;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Please make a note to replace new_password with your desired new password.
You can also use UPDATE ... SET t o reset the root password instead of ALTER USER command doesn’t work.
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Once the command is exicuted succesfully you will see: Output Query OK, 0 rows affected (0.00 sec)
Now the password has been changed and you can now stop the manual instance and restart the database server.
Step 5 — Restart the Database Server Normally
We must look into the Step3 where we have started manually which has to be stopped. Where PID of MySQL or MariaDB process sends a SIGTERM to exit smoothly.
MySQL: sudo kill `cat /var/run/mysqld/mysqld.pid`
MariaDB: sudo kill `/var/run/mariadb/mariadb.pid`
Let’s restart systemctl:
MySQL sudo systemctl start mysql
MariaDB sudo systemctl start mariadb
Let's confirm if the new password has been applied correctly= by running: mysql -u root -p
This command will show the newly assigned password, now you can access the database.
This will provide you the administrative access to the MySQL or MariaDB.
