MySQL root problems (using mysqlworkbench accidently revoked root's access)

Question

please help!...

I am facing a huge problem..i had access to mysql database through MySQL workbench but one of my coworkers accidentally went on to users and privileges and revoked something for something...he thought he was deleting another user.

Now I can't access my database on mysql workbench and all my privileges are gone....i am able to login to mysql on bash via mysql -h 127.0.0.1 -u root -p

but i can't seem to add or get my privileges back......i am logging through root on terminal...also my website is down because of this. test 1:

 mysql> show grants for 'root'; ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%' mysql> SHOW GRANTS FOR 'root'@'localhost'; +--------------------------------------------------------------+ | Grants for root@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON . TO 'root'@'localhost' | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.00 sec) **test 2: mysql> select host, user from mysql.user; ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user' ERROR 1142 (42000): SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFEREN CES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER command denied to user 'root' @'localhost' for table '*'** 

also I have no privileges, it seems.

 mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 

also there doesn't seem to be any grant here

Please help, I have no access to my database and my website is down..

Answer 1

Like mysql manual reset permissions(adjust URL to server version). Instead of ALTER USER / SET PASSWORD you'd need CREATE USER root@localhost IDENTIFIED BY "password"; GRANT ALL ON *.* to root@localhost WITH GRANT OPTION in the text file as your root user has been removed. Also recommend creating another user for your trusty co-workers to use instead of root.