Comprehensive Technical Guide for MySQL Root Password Reset and Change in Ubuntu Server

Overview of MySQL Root Password Management

In Ubuntu server environments, MySQL database root account password management forms the foundation of system security. When resetting or changing root passwords becomes necessary, administrators must follow specific security procedures to ensure database service continuity and data integrity. This article provides comprehensive technical guidance based on community-verified best practices and official documentation.

Preparatory Steps Before Password Reset

Before executing any password change operations, the MySQL service must be stopped first. This is a critical step to ensure data consistency and avoid concurrent access conflicts. In Ubuntu systems, the following command can be used to stop the service:

sudo /etc/init.d/mysql stop

In certain system configurations, it may be necessary to manually create the MySQL runtime directory and set correct permissions:

sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld

Secure Reset Method Using skip-grant-tables

Starting the MySQL service with the --skip-grant-tables parameter bypasses privilege verification, which is the core mechanism for password reset. This mode allows password-free login but also requires additional security measures:

sudo mysqld --skip-grant-tables &

In this mode, you can connect directly to the MySQL server for password reset operations:

mysql -u root mysql

Password Update Syntax for Different MySQL Versions

MySQL 8.0 and later versions introduced new authentication mechanisms and password storage methods. For these versions, using the ALTER USER statement is recommended:

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

For MySQL 5.7 and earlier versions, the password update syntax differs. Note the use of the authentication_string field:

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

Authentication Plugin Compatibility Handling

In certain MySQL configurations, it may be necessary to explicitly set authentication plugins to ensure backward compatibility:

UPDATE user SET plugin="mysql_native_password" WHERE User='root';
FLUSH PRIVILEGES;

This step is crucial for ensuring normal connections with various client tools, including phpMyAdmin.

Service Recovery and Verification

After completing the password reset, the temporary privilege-free MySQL process needs to be terminated and the service restarted normally:

sudo killall -9 mysqld
sudo service mysql start

After restarting, test the connection using the new password to ensure the configuration has taken effect correctly.

phpMyAdmin Integration Considerations

When phpMyAdmin is deployed in the system, its configuration file needs to be updated accordingly after password changes. phpMyAdmin does not automatically detect MySQL password changes; administrators need to manually modify the password settings in the config.inc.php file:

$cfg['Servers'][$i]['password'] = 'YOURNEWPASSWORD';

Security Best Practices

Although the --skip-grant-tables method is effective in emergency situations, it carries security risks. In production environments, it is recommended to prioritize regular change methods using known passwords:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!';
FLUSH PRIVILEGES;

Regular password rotation, strong password policies, and restricting remote access for root accounts are all important security measures.

Troubleshooting and Common Issues

Common problems that may be encountered during password reset processes include privilege table corruption, plugin mismatches, or service startup failures. By checking MySQL error logs and system logs, these issues can be quickly identified and resolved. Ensuring all steps are executed in sequence and verifying results after each critical operation are effective methods for preventing failures.