How do I manually reset the MySQL root password with an init file?

Question

How do I manually reset the MySQL root password with an init file?

Answer

This article walks you through manually resetting the MySQL root password if the password in the /root/.my.cnf becomes desynced. 

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.

How to change the root MySQL user password

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:

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

Warning: For security purposes, please do not utilize MyNewPass as the new password on your server. This is an example only.

Additional Resources

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

MySQL 5.7

MySQL 8.0