How to Reset the MySQL Root Password Using `DROP USER`

Prerequisites:

• You need root (superuser) access to your VPS.
• You must be able to access your server via SSH.

Step 1: Stop the MySQL Service

Before proceeding, you need to stop the MySQL service to prevent any conflicts while resetting the password.

Run this command to stop MySQL:

sudo systemctl stop mysql

Step 2: Bypass Authentication by Editing the MySQL Configuration

To reset the root password, you need to bypass the MySQL authentication mechanism by allowing access without a password. To do this, we will temporarily disable the password authentication by editing the MySQL configuration file.

1. Open the MySQL configuration file (mysqld.cnf or my.cnf):

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1. Add the following line under the [mysqld] section to skip the grant tables:

   skip-grant-tables

This setting allows you to access MySQL without requiring a password, but only for this temporary process.

1. Save the file and exit (Ctrl + X, then press Y to confirm).

Step 3: Start MySQL in Safe Mode

Now, start MySQL again, which will now run without enforcing password restrictions.

sudo systemctl start mysql

Step 4: Log in to MySQL Without a Password

Now that MySQL is running in "safe mode" without requiring passwords, you can log in without being prompted for the root password.

mysql -u root

Step 5: Drop and Recreate the Root User

Once logged in to the MySQL shell, follow these steps to drop and recreate the root user:

1. Drop the existing root user:

   DROP USER 'root'@'localhost';

This removes the existing root user, including any potential issues related to password or privileges.

1. Recreate the root user with a new password:

Now, you can create a new root user and assign a new password to it.

   CREATE USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Replace 'your_new_password' with the actual password you want to set for the root user.

1. Grant all privileges to the new root user:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

This grants the new root user full access to all databases and tables, as well as the ability to grant permissions to other users.

1. Flush privileges to apply the changes:

   FLUSH PRIVILEGES;

This ensures that all changes you've made to users and permissions take effect immediately.

Step 6: Exit MySQL and Remove `skip-grant-tables`

After resetting the password and recreating the root user, you need to revert the changes made to the MySQL configuration.

1. Exit the MySQL shell:

   exit;

1. Remove or comment out the skip-grant-tables line:

Open the MySQL configuration file again:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Remove the line:

   skip-grant-tables

Or comment it out by adding a # at the beginning:

   #skip-grant-tables

1. Save the file and exit (Ctrl + X, then press Y to confirm).

Step 7: Restart MySQL Service

Now, restart the MySQL service so that it starts with the new configuration (with password authentication enabled again).

sudo systemctl restart mysql

Step 8: Log in with the New Root Password

Now that MySQL has restarted and is enforcing password authentication, you can log in to MySQL using the new root password you set earlier.

mysql -u root -p

You will be prompted to enter the password. Enter the new password you set in Step 5.

Troubleshooting

• If you can't start MySQL after removing skip-grant-tables, double-check the configuration file (mysqld.cnf) for typos or errors.
• Make sure MySQL is running by checking its status:

  sudo systemctl status mysql

Conclusion

This tutorial provided a step-by-step guide on resetting your MySQL root password using the DROP USER method. By dropping the existing root user and creating a new one with full privileges, you can resolve common issues related to root access and password management. Remember to always remove the skip-grant-tables option after completing the password reset to keep your MySQL server secure.