How to reset MySQL root password for plain server, cPanel server and Plesk server

To provide multi-user access to a number of databases a relational database management system is been developed that runs on a server called 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 website. 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 the 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: root@server [~]# /etc/init.d/mysqld stop Or root@server [~]# service mysqld stop

Step 2: Start to MySQL server without a password:

root@server [~]# mysqld_safe –skip-grant-tables &

Step 3: root@server [~]# mysql

After that, you can get the MySQL prompt and 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: root@server [~]# /etc/init.d/mysqld stop

Step 8: root@server [~]# /etc/init.d/mysqld start

Now you can access your mysql service by using

Step 9: [root@server ~]# mysql -u root -p

On Centos 7 plain below commands

Through SystemD need to use the following command

Step 1: root@server [~]#  systemctl stop mysql

Step 2: root@server [~]# systemctl set-environment MYSQLD_OPTS=”–skip-grant-tables”

Step 3: root@server [~]# systemctl start mysql

Step 4: root@server [~]# systemctl status mysql

Step 5: root@server [~]# 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: root@server [~]# systemctl stop mysql

Step 10: root@server [~]# systemctl unset-environment MYSQLD_OPTS

Step 11: root@server [~]# systemctl start mysql

Now you can access your mysql service by using

Step 12: [root@server ~]# mysql -u root –p

Through SysVinit need to use the following command

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

Now you can access your mysql service by using

[root@server ~]# 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-

root@server [~]# cat /root/.my.cnf

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

root@server [~]# cat /root/.my.cnf

[client] password= “Password”

user=root

Make a note of the current password.

Step 2: The password can also be restored to 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 the 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