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?