Skip to main content

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

James Wright
  • Updated

Introduction

Note: These steps should only be used if the WHM interface can not be used to reset the password. Also, these steps are best performed by a qualified system administrator, and cPanel support staff are not able to perform these steps for you.

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

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. 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.

Note 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;

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

MySQL 5.7

MySQL 8.0

Related articles