How To Reset MySQL Or MariaDB Root Password

Have you forgotten your MySQL root password? Don’t worry, it happens to all of us.
In this article, we will show you how to reset the MySQL and MariaDB root password from the command line.
[box type=”note” align=”” class=”” width=””]You must have root access on the server to reset the MySQL root password.[/box]
[box type=”info” align=”” class=”” width=””]Suggested Read: How to install MySql 8.0 on CentOS 8/RHEL 8
How to Install MariaDB on CentOS 8[/box]

How to Reset MySQL or MariaDB Root Password

Follow these steps to reset your MySQL/MariaDB root password:

1. Stop the MySQL/MariaDB service

To change the root password first, you need to stop the MySQL server. To do so type the following command:

# sudo systemctl stop mysql

2. Start the MySQL/MariaDB server without loading the grant tables

Start the MySQL server with the —skip-grant-tables option. To do this, type the following command:

# udo mysqld_safe --skip-grant-tables &

The ampersand & at the end of the command above will cause the program to run in the background, so you can continue to use the shell.
When the --skip-grant-tables option is used, anyone can to connect to the database server without a password and with all privileges granted.

3. Log in to the MySQL shell

Now you can connect to the database server as the root user:

# mysql -u root

4. Set a new root password

At the mysql> prompt, reset the password. To do this, type the following command, replacing new-password with the new root password:

mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

5. Stop and Start the database server normally

Now that the root password is set, stop the database server and start it normally:

# mysqladmin -u root -p shutdown

You will be prompted to enter the new root password.

6. Start the database server normally

For MySQL, type:

# sudo systemctl start mysql

For MariaDB, type:

# sudo systemctl start mariadb

6. Verify the password

To verify that the new root password has been applied correctly, type:

# mysql -u root -p

Conclusion

You will be prompted to enter the new root password. Enter it, and you should be logged in to your database server.
In this article we have explained how to change the MariaDB / MySQL root password – whether you know the current one or not.

As always, feel free to drop us a note if you have any questions or feedback using our comment form below. We look forward to hearing from you!