Introduction
If you forget your root MySQL or MariaDB password, don’t worry! It can be reset easily with this tutorial.
Prerequisites To recover your MySQL or MariaDB password, make sure you are logged into your server with a sudo user.
Step 1 – Stop the MySQL/MariaDB Service
Before you can reset the root password, you must first stop the MySQL/MariaDB service.
You can do that for MySQL with:
sudo systemctl stop mysql
And for MariaDB with:
sudo systemctl stop mariadb
After that, you will access it manually to reset the root password.
Step 2 – Restart MySQL/MariaDB
Now that the server is stopped, run the commands below to start it back up with the --skip-grant-tables option. This bypasses the security measures (permission checking) put in place and allows for the root password to be reset.
sudo mysqld_safe --skip-grant-tables &
Step 3 – Log into the MySQL/MariaDB Shell
Now you can connect to the database server as the root user, without being asked for a password:
mysql -u root
Step 4 – Set new Root Password
To reload the grant table run the command below:
FLUSH PRIVILEGES;
Now, you can change your MySQL/MariaDB password.
Run the following commands if you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Run the following commands if you have MySQL 5.7.5 and older or MariaDB 10.1.20 and older:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Make sure to replace new_password with your new password of choice.
You shoud see confirmation that the command has been successfully executed.
Query OK, 0 rows affected (0.00 sec)
Step 5 – Restart the Database Server
Stop the database server using the following command:
mysqladmin -u root -p shutdown
You will be prompted to enter the new root password.
Start the database server normally:
For MySQL, use:
sudo systemctl start mysql
For MariaDB, use:
sudo systemctl start mariadb
Now you can confirm that the new password has been applied correctly by running:
mysql -u root -p
You will be prompted to enter the new root password. Enter it, and you should be logged into your database server.
Conclusion
Now, you know how to reset your MySQL/MariaDB root password. Make sure your new root password is strong and secure and keep it in a safe place.
Reprint: https://community.hetzner.com/tutorials/how-to-reset-mysql-or-mariadb-root-password
Leave a Reply