If you forgot the MySQL / MariaDB root password, you could reset it easily. In this article, I will show you how to do it.
Reminder: If you are running on a live server and it has sizeable MySQL activity, maybe you consider doing it in later hours because of security issues.
Check your MySQL version
You can find your MySQL version using the following command;
Be sure to make a note of which version of MySQL or MariaDB you’re running.
Reset MySQL Root Password
Follow these steps to reset your MySQL/MariaDB root password:
Stop the MySQL/MariaDB service
To change the root password first, you need to stop the MySQL server. Your service name maybe can have a different name like mysqld, mariadb.
systemctl stop mysql
Start the MySQL/MariaDB service without loading the grant tables
Start the MySQL service without loading the grant tables:
mysqld_safe --skip-grant-tables &
When you start the MySQL service without grant tables, it will be vulnerable to attackers. Anyone can connect to the MySQL Server without a password and with all privileges granted.
Log in to the MySQL shell
Now you can connect to the MySQL server as the root user:
mysql -u root
Set a new root password
Run the following commands if you run MySQL 5.7 and later or MariaDB 10.1 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD'; FLUSH PRIVILEGES;
If ALTER USER statement doesn’t work for you, try to modify the user table directly:
UPDATE mysql.user SET authentication_string = PASSWORD('NEW_PASSWORD') WHERE User = 'root' AND Host = 'localhost'; FLUSH PRIVILEGES;
Run the following commands if you have MySQL 5.6 and earlier or MariaDB 10 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PASSWORD'); FLUSH PRIVILEGES;
Stop and Start the MySQL service
Now that the root password is set, stop the MySQL service and start it normally.
mysqladmin -u root -p shutdown
You will be prompted to enter the new root password. Start the MySQL service normally. As I said before, your service name maybe can have a different name like mysqld, mariadb.
systemctl start mysql
Make sure your new root password is strong and secure and keep it in a safe place.