How Can I Recover Full Privileges for My MySQL Root User?

Regaining Full Privileges for MySQL Root User

When handling MySQL privileges, accidental alterations can lead to restricted access, hindering essential operations. If you've inadvertently stripped some privileges from the MySQL root user, restoring its full capabilities becomes crucial.

Solution

To reinstate the root user with all its privileges, follow these steps:

1. Stop and Restart MySQL with --skip-grant-tables:

sudo service mysql stop
sudo service mysql start --skip-grant-tables

2. Connect to MySQL without Password:

mysql

3. Update User Privileges:

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

4. Grant Full Privileges:

Restart MySQL normally:

sudo service mysql restart

Then, execute this command again to grant unrestricted access to the root user:

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

After these steps, the root user will regain all its privileges, allowing you to perform necessary alterations and manage MySQL effectively.

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