Forgot MySQL Root Password? Complete Reset Guide (Updated)

2014-01-07 · Ryan · Post Comment

Complete Steps to Reset MySQL Root Password

If you have forgotten the MySQL root password, you can reset it by starting the MySQL server in safe mode, bypassing the grant tables. This operation requires root or sudo privileges on the server. Perform this during a maintenance window and temporarily block application access to the database.

Step 1: Stop the MySQL Service

First, stop the running MySQL service.

sudo systemctl stop mysql
# For systems using SysV init:
# sudo service mysql stop

Step 2: Start MySQL in Safe Mode

Start the MySQL service, skipping privilege verification. This is typically done by modifying startup parameters or the configuration file.

Method A: Start via Command Line (Recommended)

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

Note: The --skip-networking parameter prevents remote connections, enhancing security.

Method B: Modify the Configuration File (For Older Versions)

Edit the MySQL configuration file (usually /etc/mysql/my.cnf or /etc/my.cnf).

sudo vi /etc/mysql/my.cnf

Add the following line under the [mysqld] section:

[mysqld]
skip-grant-tables

Save and exit the editor, then start the MySQL service:

sudo systemctl start mysql

Step 3: Connect to MySQL and Reset the Password

You can now connect to the MySQL server without a password.

mysql -u root

Once connected, execute the appropriate SQL command to update the password based on your MySQL version.

For MySQL 5.7.6 and later:

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

For MySQL 5.7.5 and earlier:

UPDATE mysql.user SET authentication_string = PASSWORD('YourNewPassword') WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Note: Replace YourNewPassword with a strong password.

Step 4: Restore MySQL to Normal Operation

After resetting the password, you must stop the MySQL server running in safe mode and restore the original configuration.

Exit the MySQL client:
```
quit
```

Stop the MySQL service:

sudo systemctl stop mysql
# Or use pkill:
# sudo pkill mysqld

If you used Method B (modified the config file), edit the configuration file and remove or comment out the skip-grant-tables line you added.
```
sudo vi /etc/mysql/my.cnf
# Comment out the line by adding a # at the beginning:
# skip-grant-tables
```
Start the MySQL service in normal mode:
```
sudo systemctl start mysql
```

Step 5: Verify the New Password

mysql -u root -p

You will be prompted for the new password. Enter it to log in successfully.

Troubleshooting and Security Recommendations

Cannot Find Configuration File: Use find / -name "my.cnf" 2>/dev/null or mysql --help | grep "Default options" to locate it.
Permission Issues: Ensure you use sudo for all commands requiring elevated privileges.
Security Warning: Complete this process as quickly as possible. In skip-grant-tables mode, the database is open to any local user. Restore normal operation immediately after finishing.
Next Steps: After resetting the password, check and update the configuration of all applications that used the old password.

Following these steps should allow you to successfully reset the MySQL root password. If you encounter issues, check the MySQL error log (usually at /var/log/mysql/error.log) for details.