How to Restore Full Privileges to a MySQL Root User After Accidental Revocation?

Restoring Full Privileges to MySQL's Root User

Enhancing the security of your database is crucial, but mishaps can occur during privilege modifications. In such cases, accidentally revoking essential privileges from the root user, like the ability to alter tables, can hamper database management. Fortunately, there's a straightforward solution to restore the root user's comprehensive privileges.

Troubleshooting Failed Restore Attempts

When attempting to restore privileges using the conventional method (e.g., updating Grant_priv and Super_priv), an empty result set or an access denied error may occur. To address these issues, follow the steps below:

Restart MySQL with --skip-grant-tables Option

1. Shut down mysqld.
2. Restart it with the --skip-grant-tables option: mysqld_safe --skip-grant-tables &
3. This action temporarily disables the privilege tables, allowing you to access MySQL without any authentication.

Modify Grant Privileges Directly

1. Connect to the MySQL server using mysql without any additional options.

2. Execute the following commands:

   • UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
   • FLUSH PRIVILEGES;

Re-enable Privilege Tables

1. Exit the client and restart mysqld normally (without --skip-grant-tables).
2. Verify that privileges have been successfully restored by executing GRANT ALL ON . TO 'root'@'localhost';

By following these steps, you can regain full administrative privileges for the root user and continue managing your MySQL database with ease.

The above is the detailed content of How to Restore Full Privileges to a MySQL Root User After Accidental Revocation?. For more information, please follow other related articles on the PHP Chinese website!