MariaDB root user has full privilege to access every data in the databases.
Being a human, we sometimes make a mistake or forget the root password. And that is normal, not to worry 😁.
You can still gain access to your database without losing your data. In this article, you will learn how to reset MariaDB or MySQL root password.
- You must have sudo user access on MySQL or MariaDB running Linux machine.
Identify Your Database Server Version
Before we move on to recovering the password you should know which version of MySQL or MariaDB you are running on your machine.
Depending on the version of your server you need to run the command. To check the version run the following command.
According to your version, you will see a similar output as shown below.
Note: Make sure you note your running database server version. You need this later.
Step 1: Stop Your MySQL / MariaDB Server
First, you need to stop your running server so that you can access your database manually.
Stop MySQL Server
Stop MariaDB Server
Step 2: Restart Your MariaDB / MySQL Server In Safe Mode
Start your database server without loading the grant tables. This means you are going to start the server without loading information about user privileges.
This will allow you to access your database through a root user with no password.
After entering the above command, your terminal may look similar as shown below. Press enter and you will be redirected to the command line.
Note: ’–skip-grant-tables’ run the server without grants, which allows you to enter into the server without a password and it is possible for users from other networks to connect to the server. Try to skip the network while you do this operation.
Step 3: Login MariaDB / MySQL Server as Root
Now, you will be able to login into the server as a root user.
In my case, I am using MariaDB and you can see that I am able to login in to the server as shown below.
Step 4: Change MariaDB / MySQL Root Password
First, reload the database server grant tables using the ‘FLUSH PRIVILEGES’ command.
Follow the below command to change the password of the root user if you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer.
Note: Do not forget to replace your password with NEW_PASSWORD in above command.
If the ‘ALTER USER’ command does not work for you then, try modifying the user table directly as shown below.
Remember to reload the grant tables after running the above command.
For MySQL 5.7.5 and older or MariaDB 10.1.20 and older:
If everything was done in the process, you will see the below output.
You have changed the password. Now, you can stop the manual run database server and restart it again.
Step 5: Kill mysqld and mariadb PID
Stop the database server that was run manually in step 2. To do this search the process ID or PID of MySQL and MariaDB and kill it.
You can kill the process in different ways for example: using ‘ps aux’ command to find the process ID or kill directly if you know the path of the .pid file of the process. I will suggest you use
Step 6: Restart Your MariaDB / MySQL Server Normally
Verify your password
I hope this article helped you to reset your MySQL or MariaDB root password. Make your database secure using a strong password and try to take a backup of your database daily using the ‘mysqldump’ command.