How to Recover MySQL Root Access After Accidentally Revoking Privileges?

How to Recover Root Privileges in MySQL Using --skip-grant-tables

In case you accidentally locked yourself out of your MySQL database by revoking root privileges, here's a step-by-step guide to regain access using the --skip-grant-tables option.

Prerequisites:

• You have the MySQL root password.
• You can still log in to MySQL.

Steps:

1. Stop the MySQL Service:

   Open the Administrator Tools and go to Services. Locate the MySQL service and stop it.

2. Edit the Configuration File:

   Locate the MySQL configuration file (my.ini or my.cnf) on your system. The default locations are:

   • Windows: C:Program FilesMySQLMySQL Server 5.5my.ini
   • MySQL >= 5.6: C:ProgramDataMySQLMySQL Server 5.6my.ini

3. Add the --skip-grant-tables Option:

   In the [mysqld] section of the configuration file, add the following line:

   skip-grant-tables

4. Restart the MySQL Service:

   Return to the Services window and start the MySQL service again.

5. Log In Without a Password:

   Open a command prompt and log in to MySQL. You should now be able to access the database without entering a password.

6. Reset the Root Password (Optional):

   If desired, you can reset the root user's password by running the following command:

   ALTER USER 'root'@'localhost' IDENTIFIED BY '<new_password>';

7. Remove the --skip-grant-tables Option:

   Once you have regained access, be sure to remove the --skip-grant-tables option from the configuration file and restart the MySQL service.

The above is the detailed content of How to Recover MySQL Root Access After Accidentally Revoking Privileges?. For more information, please follow other related articles on the PHP Chinese website!