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

Question

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?

Take a look at http://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04 — Charney Kaye, Jan 11 '17 at 20:33

score 24 · Answer 1 · answered Feb 09 '13 at 03:55

24

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

answered Feb 09 '13 at 03:55

Joe

2

The only solution that worked for me after 3 hours of googling and trying almost everything !! – vinita Jun 25 '19 at 08:27
`ERROR 1410 (42000): You are not allowed to create a user with GRANT` – mercury Jan 10 '23 at 07:27

score 10 · Answer 2 · answered May 30 '13 at 09:37

10

If you facing grant permission access denied problem, you can try mysql_upgrade to fix the problem:

/usr/bin/mysql_upgrade -u root -p

Login as root:

mysql -u root -p

Run this commands:

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

answered May 30 '13 at 09:37

Kin Shu

`ERROR 1410 (42000): You are not allowed to create a user with GRANT` / Centos8, Mysql8 – mercury Jan 10 '23 at 07:29

score 2 · Answer 3 · answered Nov 10 '09 at 15:34

2

Log in as root, then run the following MySQL commands:

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

answered Nov 10 '09 at 15:34

Kaleb Brasee

Error SQL query: GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost'; MySQL said: Documentation #1045 - Access denied for user 'root'@'localhost' (using password: YES) – Steven Nov 10 '09 at 15:40
You were logged in to mysql as root when you ran this, correct? – Kaleb Brasee Nov 10 '09 at 15:47
I wonder if you need to set the password as well, and maybe the ability to grant: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'some_password' GRANT OPTION; – Kaleb Brasee Nov 10 '09 at 15:51
Yes, I was logged in to mysql as root when I ran it. I still have my password. When I run,GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'my_password' GRANT OPTION; it says I have an error in my SQL syntax. – Steven Nov 10 '09 at 16:11
It said i had an error when i did this via MYSQL Workbench but it did the command successfully if you look it may show the error being with a ( on line 1, which is not in the sql query anyway – Theresa Forster Dec 22 '11 at 11:59
Instead of 'GRANT OPTION' USE 'WITH GRANT OPTION'. For example : GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'my_password' **WITH GRANT OPTION** – Pierre-luc S. Jun 07 '16 at 15:52
`ERROR 1410 (42000): You are not allowed to create a user with GRANT` / Centos8, Mysql8 – mercury Jan 10 '23 at 07:29

score 0 · Answer 4 · answered Oct 28 '20 at 04:55

If you facing grant permission access denied problem, you can try mysql to fix the problem:

grant all privileges on . to root@'localhost' identified by 'Your password';

grant all privileges on . to root@'IP ADDRESS' identified by 'Your password?';

your can try this on any mysql user, its working.

Use below command to login mysql with iP address.

mysql -h 10.0.0.23 -u root -p

4 Answers4