How to Recover MySQL Root Password: A Comprehensive Guide with Steps and Tips

Recovering the MySQL root password is a critical task, especially if you forget it or it's compromised. This guide provides a detailed step-by-step approach to resetting the MySQL root password using both safe mode and the MySQL configuration file method. Follow these instructions carefully to regain access to your MySQL server securely.

Method 1: Using Safe Mode

Safe mode is a useful method to reset the MySQL root password when standard methods fail. Follow the detailed steps below to perform this process:

Step 1: Stop the MySQL Service

The first step involves stopping the MySQL service on your server.

Linux:

sudo systemctl stop mysql

Windows:

net stop mysql

Step 2: Start MySQL in Safe Mode

Next, start MySQL in safe mode, which allows you to bypass the authentication process and manipulate the MySQL system tables.

Linux:

sudo mysqld_safe --skip-grant-tables

Windows:

Navigate to the MySQL bin directory. Run the following command in the Command Prompt:

mysqld --skip-grant-tables

Step 3: Log into MySQL

Open a new terminal and log into MySQL using the root user:

Run the following command:

mysql -u root

Step 4: Reset the Password

Once inside MySQL, execute the following SQL commands to reset the root password:

Flush privileges to reset the system: Change the root password to a new one:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Step 5: Exit MySQL

Exit the MySQL shell by running:

Exit the MySQL shell:

EXIT;

Step 6: Stop Safe Mode

On Linux, stop the MySQL service using:

sudo killall mysqld

On Windows, close the command window or stop the process from Task Manager.

Step 7: Restart MySQL Service

To enable authentication again, restart the MySQL service:

Linux:

sudo systemctl start mysql

Windows:

net start mysql

Step 8: Test the New Password

Log into MySQL with the new root password to ensure the change was successful:

mysql -u root -p

Method 2: Using MySQL Configuration File

If you prefer not to use safe mode, you can set the skip-grant-tables option in your MySQL configuration file.

Step 1: Edit the MySQL Configuration File

Open the MySQL configuration file ( or C:ProgramDataMySQLMySQL Server ) and add the following line under the [mysqld] section:

skip-grant-tables

Step 2: Restart MySQL

Restart the MySQL service to apply the configuration change. Follow the steps from Method 1 to reset the password and exit.

Step 3: Remove the skip-grant-tables Option

After resetting the password, remove the skip-grant-tables option from the configuration file:

For Linux:

sudo nano 

For Windows:

C:ProgramDataMySQLMySQL Server 

Restart the MySQL service again.

Important Notes

Security: Always ensure that any temporary configurations that allow unrestricted access are removed after resetting the password.
Backup: It is highly recommended to backup your databases before making any major changes to the server configuration.
After following these steps, you should be able to log in to MySQL using the new root password.

Note: The command to start MySQL with skip-grant-tables is:

service mysql start --skip-grant-tables

Now, you can log in to MySQL using:

mysql -u root

Once inside MySQL, change the root password using the following command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Restart MySQL and log in with the new password to verify the change.