i have a program called heidiSQL on my computer that enables me to edit data inside my database with querys. recently tried to create a new user through this program while signed in as the root account. i used the following code

  create user 'john91'@'localhost' identified by 'google';
  grant all privileges on *.* to 'john91'@'localhost;
  flush privileges;

i then closed the HeidiSQL program and opened it again to sign in with the new user 'john91', it worked fine, but what struck me was that i couldn't sign in to my root account anymore.

i then open the command line and sign in with the user 'john91' (which works) and checked all my user in the server by using this code:

  select host,user from mysql.user;

i found that 'john91' was the only user in the system. i was able to create a new root account by using this code and while signed in with 'john91' user:

  create user 'root'@'' identified as 'google';

this worked fine but i was unable to grant this root account any privileges, so when i used:

  grant all privileges on *.* to 'root'@'';
  ERROR 1045 (28000): access denied for user 'john91'@'localhost' (using                    
  password: YES)

so is this the right way to recreate my root account, if yes, what am i doing wrong, if no, how should i do it?

Update, i need to create 'root'@'localhost' MySQL is not letting me do that through the user 'john91', how can it be done?


You were supposed to do this:

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

You forgot to give 'john91'@'localhost' grant option. Thus, you cannot grant privileges to anyone else.

You can fix that by doing the following

update mysql.user set grant_priv='Y' where user='john91' and host='localhost';
flush privileges;

Then, you should be able to run

grant all privileges on *.* to 'root'@'';

Give it a Try !!!

  • thanks for the answer, i tried as you said, i entered the codes through 'john91' user, they went through. but the last code still gave me the same error. also, why can't i create a 'root'@'localhost' account? but i was able to create a 'root'@'' account
    – mohamed
    Oct 27 '15 at 16:56
  • ok i logged out of 'john91' and logged in again, and the code worked. thanks for the answer, but i need to create 'root'@'localhost' account do you know how?
    – mohamed
    Oct 27 '15 at 17:11
  • every time i try to create 'root'@'localhost' i get ERROR 1396 (HY000) : Operation create user failed for 'root'@'localhost';
    – mohamed
    Oct 27 '15 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.