How to get all privileges back to the root user in MySQL?

Question

I have logged in to MySQL with the --skip-grant-tables option. But I don't know how to get all privileges back to the root user.

Error

SQL query:

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

MySQL said: Documentation

1290 - The MySQL server is running with the --skip-grant-tables option so

it cannot execute this statement

Error

SQL query: Edit

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL said: Documentation

1290 - The MySQL server is running with the --skip-grant-tables option so

it cannot execute this statement

Answer 1

To run a GRANT query, you just don't run mysql with skip-grant-tables -- what's complicated about that...?

Answer 2

When you run mysql using --skip-grant-tables mysql won't check any permissions. So basically you can do anything.

To get back the root privileges you would need to run a query in mysql DB like this

select * from user where user = 'root'

just to check if the root user is still there if ok:

UPDATE user SET Grant_priv = 1, Super_priv = 1 WHERE user = 'root'

after you can restart mysql without the --skip-grant-tables and the root user should be able to do some grant so your query should work