How To Retrieve or Reset the Root MySQL Password

If the root MySQL password is required and can’t be located, it can be retrieved or reset.

Pre-Flight Check

Step #1: Recovering the Root MySQL Password on a cPanel server

On servers running cPanel, the root MySQL credentials are stored locally in a file that only the root user can access. After you’ve connected to the server as root via SSH, you can view the credentials by running the following command:

cat /root/.my.cnf

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

[root@host ~]# cat /root/.my.cnf
[client]
password=AVeryStrongPassword
user=root

Make a note of the current password.

Step #2: Resetting the Root MySQL Password

If you need to reset the root MySQL password and have access to WebHost Manager on a cPanel server, you can do so by selecting MySQL Root Password from WHM’s menu. If you don’t have access to WHM, you still can change the password via CLI after stopping and restarting MySQL in single-user mode.

Caution: While the MySQL server is running in single-user mode, any user can access any database without a password. You will want to work quickly, so please read through these instructions to ensure you fully understand the process before proceeding. You also may wish to temporarily configure your firewall to block external traffic to the MySQL port (3306).
  1. Stop MySQL:

    service mysql stop

  2. Start MySQL in single-user mode, bypassing password authorization, by pasting in the command below (the ampersand is required):

    mysqld_safe --skip-grant-tables &

  3. Now type “mysql” to connect:

    mysql

  4. At the mysql prompt, change the root password using the following three commands:

    UPDATE mysql.user SET password=password("YourNewStrongPassword") WHERE user='root';
    FLUSH PRIVILEGES;
    exit;

  5. Now stop MySQL and start it in normal mode:

    service mysql stop
    service mysql start

  6. Verify that you are able to connect to MySQL by entering “mysql”.
  7. Finally, update the MySQL root password in /etc/.my.cnf to match the new value. You can use vi, vim or nano:

    vim /root/.my.cnf

    In vim, you can press “a” or “i” to enter text insertion mode; pressing the escape key (Esc) on your keyboard returns you to command mode. For a refresher on editing files with vim, see New User Tutorial: Overview of the Vim Text Editor.

 

Be Sociable, Share!

    Published by

    Here's 75 % off, Launch a New VPS Today. Find out why 30,000 customers have chosen our Best-in-Class Performance & 24x7 Heroic Support