How Can I Restore Full Privileges to My MySQL Root User?

Restoring Full Privileges to the MySQL Root User

Losing privileges for the MySQL root user can be a significant inconvenience, particularly if you need to alter tables. Fortunately, there are ways to regain full privileges for the root user.

One attempted solution involves using the UPDATE statement to grant privileges directly to the root user. However, this may fail due to insufficient permissions.

To bypass this limitation, follow these steps:

1. Stop and restart mysqld with the --skip-grant-tables option: This allows you to connect to the database without authentication.
2. Connect to the mysqld server without specifying credentials: Simply run mysql without the -p option and username.

3. Update privileges in the mysql client: Execute the following commands in the mysql client:

   • UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
   • FLUSH PRIVILEGES;
4. Restart mysqld normally: Once the privileges have been restored, restart mysqld without the --skip-grant-tables option.

You should now have full privileges for the root user. Running the GRANT ALL ON *.* TO 'root'@'localhost'; command should succeed without errors.

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