Recover root password in MySQL
Who hasn't ever lost the root user password in mysql? Even in production systems! Today we will see what steps must be taken to restore the password for the user with the most privileges in mysql.
How does this happen?
Personally, there are two cases in which you can lose access with root to mysql. The first one has to do with simply forgetting the password, which makes you lose access. The second one is related to some change in the database such as when you change the plugin or authentication_string of mysql's user table.
Steps to recover the password
( 1 ) The first thing you need to do is to stop the mysql service. In Debian GNU/Linux-based systems it would suffice to run the following:
service apache2 stopOr,
systemctl stop apache2In MacOS you must run:
mysql.server stop( 2 ) The second step will be to start the mysql service (mysqld) in safe mode bypassing authentication verifications.
mysqld_safe --skip-grant-tables( 3 ) With this we can already enter without authentication, just by executing the next command:
mysql( 4 ) Finally, you must execute the following command to leave the root user without a password.
UPDATE mysql.user SET authentication_string=null WHERE User='root';( 5 ) At this point, you must close the session and start with root without a password.
mysql -u root( 6 ) And change the password to the desired one.
SET PASSWORD = 'auth_string';Until next time!