How to reset MySQL root password

January 25, 2023 / Plesk

MySQL is a popular relational database management system (RDBMS) used widely for web applications. If you have forgotten the MySQL root password and are unable to access your database, follow the guide below to reset it for different server environments: Plain server, cPanel server, and Plesk server.

Reset MySQL Root Password on a Plain Server:

  1. For CentOS 6 (SysVinit)
    1. Stop the MySQL service:
      /etc/init.d/mysqld stop
    2. Start MySQL without a password:
      mysqld_safe --skip-grant-tables &
    3. Log in to MySQL:
      mysql
    4. Switch to the MySQL database:
      USE mysql;
    5. Update the root password:
      UPDATE user SET password=PASSWORD('NEWPASSWORD') WHERE User='root';
    6. Stop MySQL:
      /etc/init.d/mysqld stop
    7. Restart MySQL:
      /etc/init.d/mysqld start
    8. Access MySQL with the new password:
      mysql -u root -p
  2. For CentOS 7+ (SystemD)
    1. Stop the MySQL service:
      systemctl stop mysql
    2. Set environment for skipping grant tables:
      systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
    3. Start MySQL:
      systemctl start mysql
    4. Log in to MySQL:
      mysql -u root
    5. Update the root password:
      USE mysql;
      
      UPDATE user SET password=PASSWORD('NEWPASSWORD') WHERE User='root';
      
      FLUSH PRIVILEGES;
    6. Unset the environment and restart MySQL:
      systemctl unset-environment MYSQLD_OPTS
      
      systemctl restart mysql
    7. Access MySQL with the new password:
      mysql -u root -p

Reset MySQL Root Password on a cPanel Server:

  1. Locate the current password:
    1. Log in as the root user and view the .my.cnf file:
      cat /root/.my.cnf
    2. The file contains the MySQL root password.
  2.  Change the password via WHM:
    1. Log in to WHM.
    2. Navigate to SQL Services > MySQL Root Password.
    3. Enter the new password and confirm the change.
  3.  If the above methods don’t work, reset the password using the steps for a Plain server.

Reset MySQL Root Password on a Plesk Server:

  1. Automated Method:
    1. Download the restoration script:
      curl -LO https://plesk.zendesk.com/hc/article_attachments/360042955514/213364309-restore-admin-user.php.tar.gz
    2. Extract and run the script:
      tar xf 213364309-restore-admin-user.php.tar.gz
      
      plesk php 213364309-restore-admin-user.php
    3. Attempt to log in to Plesk.
  2. Manual Method:
    1. Check for old-passwords directive:
      grep -ir old-passwords /etc/my*
      1. Remove the directive if it exists.
    2. Add skip-grant-tables to my.cnf:
      vi /etc/my.cnf
      
      Add:
      
      [mysqld]
      
      skip-grant-tables
    3. Restart MySQL:
      service mysql restart
    4. Log in to MySQL:
      plesk db
    5. Restore the admin user:
      DROP USER 'admin'@'localhost';
      
      CREATE USER 'admin'@'localhost' IDENTIFIED BY 'NEWPASSWORD';
      
      GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
      
      FLUSH PRIVILEGES;
    6. Remove skip-grant-tables: Remove the directive from my.cnf and restart MySQL:
      service mysql restart

By following the above-outlined methods, you can regain access to your MySQL database.

After resetting MySQL, ensure your server has enough space. Learn How to Check Server Disk Space Usage

Spread the love