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
[ads]
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
[ads]
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!