How to recover the MySQL root password

If you have lost the password for the MySQL root user, you can reset it by following the steps detailed below.

To reset the password, you will need to access the server over SSH as the root user.

Once you are logged in to your server, you will need to:

1. Stop the MySQL service. You can do this with one of the following commands:

   service mysqld stop

   or:

   /etc/init.d/mysqld stop 

2. Start the MySQL service in open mode with the following command:

   mysqld_safe --skip-grant-tables --skip-networking&

   This command, with the –skip-grant-tables option, will start MySQL without using the privilege system, so you will have unlimited access to all databases.The –skip-networking option will stop listening for external TCP/IP connections, preventing external problems during the change.

3. Once MySQL has started, you must log in as the “root” user. You can do this simply with:

   mysql -u root 

4. Once inside MySQL, you will need to access the MySQL database, which is where you will need to change the password:

   mysql> use MySQL; 

5. Within the MySQL database, you can run the following query to change the password:

   mysql> UPDATE USER SET password=PASSWORD('contraseña') WHERE user='root';

    Note

   Remember that you will need to replace contraseña with the new password you want to set. The function PASSWORD() was removed in MySQL 8.0 and the password column was replaced by authentication_string as of MySQL 5.7. The correct command for MySQL 5.7+ is: 

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'nueva_contraseña';

6. And when it finishes, you can exit the MySQL server with:

   mysql> exit

7. Finally, you will need to restart the MySQL service so that it starts in normal mode:

   service mysqld restart

   or:

   /etc/init.d/mysqld restart

Once you have completed the steps above, you will have changed the password for the MySQL “root” user. The command for changing the password can be used for any users you want; however, you must start MySQL without the privilege system to change the “root” user's password if you have lost your password.