Comprehensive Guide to Recovering and Resetting MySQL Root Password on Ubuntu Systems

Technical Background of MySQL Root Password Recovery

After installing MySQL database on Ubuntu operating systems, users may encounter situations where the root account cannot log in normally. This typically occurs when the password is unclear after initial installation, forgotten, or due to system configuration anomalies. As a widely used relational database management system, MySQL requires strict authentication processes as part of its security mechanisms, making password loss directly impact database management and maintenance.

Core Password Reset Methodology

The standard solution involves temporarily bypassing MySQL's privilege verification system. This can be achieved by starting the MySQL service in safe mode using the --skip-grant-tables parameter. This parameter causes the MySQL server to start without loading privilege tables, allowing users to connect to the database without a password.

The operational procedure is as follows: first stop the running MySQL service, then start the service in privileged mode:

# service mysql stop
# mysqld_safe --skip-grant-tables &

Next, connect to the database using the MySQL client as the root user without providing a password:

$ mysql -u root

Database Privilege Modification Steps

After successful connection, the user authentication information in the MySQL system database needs to be updated. First switch to the mysql database, which stores all user privileges and authentication data:

mysql> use mysql;

Then execute the appropriate update operation based on the MySQL version. For MySQL 5.7 and later versions, password information is stored in the authentication_string field:

mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';

For versions prior to MySQL 5.7, the password field is named password:

mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';

After updating, privileges must be flushed to make changes take effect immediately:

mysql> flush privileges;
mysql> quit

Service Restart and Verification

After modifying the password, the MySQL service needs to be restarted normally to restore full privilege verification functionality:

# service mysql stop
# service mysql start

Finally, verify successful login using the newly set password:

$ mysql -u root -p

The system will prompt for the password, and entering the previously set "YOUR-NEW-ROOT-PASSWORD" will complete authentication.

Auxiliary Recovery Methods

In addition to the standard method, password recovery can sometimes be attempted by checking MySQL command history. The MySQL client records executed commands in the .mysql_history file in the user's home directory. For regular users, the path is /home/$USER/.mysql_history; for root users, it's /root/.mysql_history. Examining these files may reveal previously executed password modification or setup commands.

However, this approach has limitations: history may be cleared, overwritten, or incomplete, and it offers lower security. Therefore, the --skip-grant-tables method is more reliable and universal.

Security Considerations

When performing password reset operations, the following security precautions must be observed:

1. During the operation, the MySQL service operates without privilege verification, so ensure the system network environment is secure to prevent unauthorized access.
2. After completing password reset, immediately restore normal service mode to minimize security risk exposure.
3. Newly set passwords should comply with security policy requirements, possessing sufficient complexity and length.
4. For production environments, perform operations during maintenance windows and ensure data backups are available.

Version Compatibility Considerations

MySQL has improved its password storage mechanism across different versions. Version 5.7 introduced the authentication_string field to replace the original password field, supporting more flexible authentication plugin mechanisms. Users should confirm the MySQL version before operation and select the correct field name for updates; otherwise, the operation will be ineffective.

The MySQL version can be queried using:

$ mysql --version

Or after connecting to the database:

mysql> SELECT VERSION();

Conclusion

MySQL root password loss is a common issue in database management. Starting the service with the --skip-grant-tables parameter and modifying system privilege tables constitutes the standard solution. This method applies to most MySQL installation scenarios but requires attention to version differences and operational security. Meanwhile, history files can serve as supplementary recovery tools but should not be the primary reliance. Mastering these technical details helps database administrators efficiently handle authentication issues, ensuring database system availability and security.