MySQL-5.6 – How to Recover a Deleted Root Account

mysql-5.6users

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'@'127.0.0.1' 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'@'127.0.0.1';
  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?

Best Answer

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'@'127.0.0.1';

Give it a Try !!!