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:
You forgot to give 'john91'@'localhost' grant option. Thus, you cannot grant privileges to anyone else.
You can fix that by doing the following
Then, you should be able to run
Give it a Try !!!