How to reset the MySQL root password
This article describes how to reset the MySQL root password. You may need to do this, for example, if you have forgotten the password or If you’re getting the below error then it seems the root password is not working or something bad happens to the MySQL root user :
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
To reset the root password for MySQL, follow these steps:
Step 1: Log in to your account using SSH
You must run the commands in the following steps as the root user. Therefore, you can either log in directly as the root user (which is not recommended for security reasons), or use the su or sudo commands to run the commands as the root user.
Step 2: Stop the MySQL server using the command
systemctl stop mariadb.service
Step 3: Restart the MySQL server with the —skip-grant-tables option. To do this, type the following command:
mysqld_safe –skip-grant-tables &
- Make sure you type the ampersand (&) at the end of the command. This runs the command in the background and allows you to type the commands in the following steps.
- Running MySQL with the —skip-grant-tables option enabled is highly insecure, and should only be done for a brief period while you reset the password. The steps below show you how to stop the mysqld_safe server instance safely and start the MySQL server securely after you have reset the root password.
- After running the above command press “Enter” Key > to get the shell again
Step 4: Log into MySQL using the following command:
mysql
Step 5: At the mysql> prompt, type the following commands:
FLUSH PRIVILEGES;
ALTER USER ‘root’@’localhost’ IDENTIFIED VIA mysql_native_password USING PASSWORD(‘new_password);
FLUSH PRIVILEGES;
EXIT;
Step 6: Stop the MySQL server using the following command.
killall -u mysql
Step 7: Now Stop and restart MySQL
systemctl stop mariadb.service
systemctl restart mariadb.service