Skip to main content

How to manually reset the MySQL root password with an init file

James Wright
  • Updated

Introduction

This article walks you through manually resetting the MySQL root password if the password in the /root/.my.cnf becomes desynced. When possible, we recommend using the WHM interface to make these changes:

How to change the root MySQL user password

Please note the steps below should only be used if the WHM interface can not be used to reset the password. This is best performed by a qualified system administrator, and cPanel support staff are not able to perform these steps for you.

 

Procedure

  1. Log in to WHM, select Service Manager under the 'Service Configuration' section, and disable monitoring for the MySQL service.
  2. Connect to the server via SSH as the root user.
  3. Stop the MySQL service:
    /scripts/restartsrv_mysql --stop
  4. Create a text file in /var/lib/mysql called mysql-init:
    touch /var/lib/mysql/mysql-init
  5. Open the file in a text editor and add the following text to the file, where MyNewPass represents the password you would like to use:
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
  6. Update the password in /root/.my.cnf to the password used in this statement:
    [root@server]# grep -i pass /root/.my.cnf
password="MyNewPass"
  7. Change the ownership of the file to ensure there are no issues reading the file:
    chown -v mysql. /var/lib/mysql/mysql-init
  8. Start MySQL manually using this command:
    mysqld -u mysql --init-file=/var/lib/mysql/mysql-init &
  9. Now that the password has been updated shut down MySQL safely using this command:
    mysqladmin -u root shutdown
  10. Remove the init file:
    /bin/rm -vi /var/lib/mysql/mysql-init
  11. Restart MySQL normally:
    /scripts/restartsrv_mysql
  12. In the WHM interface, re-enable MySQL service monitoring in Service Manager >> Service Configuration.

When using MySQL 5.7:

It may be necessary to use the following statement in place of the ALTER statement shown above:

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

 

Additional Resources

More information on these steps can be found in the official MySQL documentation here:

MySQL 5.7

MySQL 8.0

The following article can show you how to edit text files using command line tools:

How to edit a file on command-line

Related articles