How Do I Restore Full Privileges to a MySQL Root User After Accidentally Restricting Them?

Problem:

Oops! I just restricted the privileges of the MySQL root user, barring it from altering tables. How do I restore this user to its former glory, with absolute privileges reinstated?

Solution:

Let's take this step by step:

• Double-Check:
  Start by verifying that you indeed removed the required privileges. Run the following command:

  SELECT * FROM mysql.user WHERE User = 'root';

  Copy after login

  Check the Grant_priv and Super_priv columns.

• Simple Fix:
  Attempt to grant all privileges using this command:

  GRANT ALL ON *.* TO 'root'@'localhost';

  Copy after login

• Fallback Option:
  If the above doesn't restore the privileges, restart MySQL with the --skip-grant-tables option. Then, connect to the server with:

  mysql

  Copy after login

  Issue these commands:

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

  Copy after login
• Verify the Reset:
  Run the privilege verification query again to ensure the root user's powers have been restored.
• Additional Troubleshooting:
  If the steps mentioned above fail, you may need to check the MySQL logs or seek further assistance from the MySQL documentation or community forums.

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