To provide multi-user access to a number of databases a relational database management system is been developed that runs on a server and called as MySQL. MySQL is developed under GNU General Public License and its source code is available under its defined terms. MySQL is a popular choice of database for use in web applications and it performs really well with cPanel hosting accounts to store data of any websites. And suppose if you have forgotten the MySQL root password and you are not able to access your MySQL service then you need to reset the password of MySQL root user by using the following steps which are explained in this article.

Let’s check out the steps to reset the MYSQL root password for the Plain server, cPanel server, and Plesk server.

Reset MySQL root Password for Plain Server

Steps to Reset MySQL root Password for Plain Server, On Centos 6 Plain.

Step 1: [email protected] [~]# /etc/init.d/mysqld stop Or [email protected] [~]# service mysqld stop

Step 2: Start to MySQL server without password:

[email protected] [~]# mysqld_safe –skip-grant-tables &

Step 3: [email protected] [~]# mysql

After that, you can get the mysql prompt then run the following command.

Step 4: mysql >use mysql;

Step 5: mysql > UPDATE user SET password=PASSWORD(‘NEWPASSWORD’) WHERE User=’root’;

Step 6: mysql > quit

Step 7: [email protected] [~]# /etc/init.d/mysqld stop

Step 8: [email protected] [~]# /etc/init.d/mysqld start

Now you can access your mysql service by using

Step 9: [[email protected] ~]# mysql -u root -p

On Centos 7 plain below commands

Through SystemD need to use the following command

Step 1: [email protected] [~]#  systemctl stop mysql

Step 2: [email protected] [~]# systemctl set-environment MYSQLD_OPTS=”–skip-grant-tables”

Step 3: [email protected] [~]# systemctl start mysql

Step 4: [email protected] [~]# systemctl status mysql

Step 5: [email protected] [~]# mysql -u root

Step 6: mysql > use mysql;

Step 7: mysql > UPDATE user SET password=PASSWORD(‘NEWPASSWORD’) WHERE User=’root’;

Step 8: mysql > FLUSH PRIVILEGES;

Step 9: [email protected] [~]# systemctl stop mysql

Step 10: [email protected] [~]# systemctl unset-environment MYSQLD_OPTS

Step 11: [email protected] [~]# systemctl start mysql

Now you can access your mysql service by using

Step 12: [[email protected] ~]# mysql -u root –p

Through SysVinit need to use the following command

  1. [email protected] [~]# /etc/init.d/mysqld stop
  2. [email protected] [~]# mysqld_safe –skip-grant-tables &
  3. [email protected] [~]# systemctl status mysql
  4. [email protected] [~]# mysql -u root
  5. mysql > use mysql;
  6. mysql > UPDATE user SET password=PASSWORD(‘NEWPASSWORD’) WHERE User=’root’;
  7. mysql > FLUSH PRIVILEGES;
  8. [email protected] [~]# /etc/init.d/mysql stop
  9. [email protected] [~]# /etc/init.d/mysql start

Now you can access your mysql service by using

[[email protected] ~]# mysql -u root –p

Steps for the cPanel server

Step 1: On all cPanel/WHM running server, the root MySQL credentials are stored locally in a file. This file is accessible for root user only.

You need to login to CLI of the server as a root user and view the credentials in /root/.my.cnf file

Command-

[email protected] [~]# cat /root/.my.cnf

That should output a result containing the MySQL password, which will appear similar to the following:

[email protected] [~]# cat /root/.my.cnf

[client] password= “Password”

user=root

Make a note of the current password.

Step 2: Password can also be restored the Root MySQL Password from WHM

  1. Login to WHM
  2. Go to SQL Services option
  3. Select MySQL Root Password
  4. Enter the new password and click on the Change Password Button.
  5. This process will change the MySQL root password of the server.

Step 3: If mentioned above both steps are not working for resetting the root password then, follow the steps to reset the password for the Plain server.

Steps for the Plesk server

On a Plesk server admin is the main user.

Note: This solution applies to a situation, where the password of the ‘admin’@’localhost’ user has been manually changed.

Set the MySQL admin password equal to the password specified in /etc/psa/.psa.shadow file.

Steps:

Step 1: Connect to the Plesk server via SSH.

Step 2: Download the script for automated MySQL admin restoration:

# curl -LO https://plesk.zendesk.com/hc/article_attachments/360042955514/213364309-restore-admin-user.php.tar.gz

Step 3: Unpack it:

# tar xf 213364309-restore-admin-user.php.tar.gz

Step 4: Run the script:

# plesk php 213364309-restore-admin-user.php

Step 5: Try to access Plesk again. If the automated solution did not help, apply the manual solution.

Let’s check the Manual solution:

Step 1: Connect to the Plesk server via SSH.

Step 2: With the command below, check if the directive old-passwords exist in the MySQL configuration file my.cnf (no output means it does not exist):

# grep -ir old-passwords /etc/my*

If it exists, remove it using a text editor.

Step 3: Get a hashed password of the MySQL admin user and copy it to clipboard:

Note: Make sure there is only one password specified in the file.

# grep AES /etc/psa/.psa.shadow

Here is an example of a hashed password:

$AES-128-CBC$ZmY/EEpy1+TwCNq5kalqSA==$Pd02kf4TTlpXdi/qyeo92w==

Step 4: Modify my.cnf file:

4.1. Open my.cnf file in a text editor. In this example, we are using the vi editor:

  • On CentOS/RHEL-based distributions

# vi /etc/my.cnf

  • On Debian/Ubuntu-based distributions

# vi /etc/mysql/my.cnf

4.2. Add the skip-grant-tables line under the [mysqld] section:

[mysqld]

skip-grant-tables <…>

4.3. Save the changes and close the file.

Step 5: Restart MySQL. The command depends on the MySQL version and operating system:

# service mariadb restart

# service mysql restart

# service mysqld restart

Step 6: Connect to MySQL:

# plesk db

Step 7: Switch to the mysql database:

mysql> use mysql;

Step 8: Find the version of MySQL:

mysql> SELECT VERSION();

Step 9: Reload the ACL tables cache:

mysql> FLUSH PRIVILEGES;

Note: Once the command above is executed, do not exit the current MySQL session as this command loads grant tables back.

Step 10: Restore the MySQL admin user:

  • For MySQL 5.1 up to 5.6 and MariaDB 5.5 up to 10.4

1. Drop the current admin user:

mysql> DROP USER ‘admin’@’localhost’;

2. Create a new admin user with the hashed password from step 3:

mysql> CREATE USER ‘admin’@’localhost’ IDENTIFIED BY  ‘HASHED_PASSWORD_FROM_STEP_3’;

3. Grant all permissions to the admin user:

mysql> GRANT ALL ON *.* TO ‘admin’@’localhost’ WITH GRANT OPTION;

4. Exit MySQL:

mysql> exit

  • For MySQL 5.7 up to 8.0

1. Drop the current admin user:

mysql> DROP USER ‘admin’@’localhost’;

2. Create a new admin user with the hashed password from step 3:

mysql> CREATE USER ‘admin’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘HASHED_PASSWORD_FROM_STEP_3’;

3. Grant all permissions to the admin user:

mysql> GRANT ALL ON *.* TO ‘admin’@’localhost’ WITH GRANT OPTION;

4. Exit MySQL:

mysql> exit

Step 11: Remove the skip-grant-tables line from my.cnf file (step 4).

Step 12: Restart MySQL:

# service mariadb restart

# service mysql restart

# service mysqld restart