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

mysql privileges
Steven picture Steven · Nov 10, 2009 · Viewed 125.4k times · Source

I am using MySQL. My root user doesn't have all privileges. How can I get all privileges back to the root user? How to do it step by step?

Answer

Joe picture Joe · Feb 9, 2013

This worked for me on Ubuntu:

Stop MySQL server:

/etc/init.d/mysql stop

Start MySQL from the commandline:

/usr/sbin/mysqld

In another terminal enter mysql and issue:

grant all privileges on *.* to 'root'@'%' with grant option;

You may also want to add

grant all privileges on *.* to 'root'@'localhost' with grant option;

and optionally use a password as well.

flush privileges;

and then exit your MySQL prompt and then kill the mysqld server running in the foreground. Restart with

/etc/init.d/mysql start

Related questions

How to start MySQL with --skip-grant-tables?

I locked my root user out from our database. I need to get all privileges back to the root user. I have my password and I can log in to MySQL. But the root user has no all privileges.

Read More
I can not find my.cnf on my windows computer

My computer is Windows XP. I need to find my.cnf to get all privileges back to the root user. I accidentally removed some privileges of the root user. I still have the password and there is no problem for …

Read More
How can I restore the MySQL root user’s full privileges?

I accidentally removed some of the privileges from my MySQL root user, including the ability to alter tables. Is there some way I can restore this user to its original state (with all privileges)? UPDATE mysql.user SET Grant_priv = …

Read More