I accidentally revoked all privileges from root user, now unable to login to my mysql server
Hello,
[SORRY FOR THE LONG MESSAGE, I TRIED A LOT OF STAFF SO I WANTED TO GIVE YOU A CONTEXT]
Please help me, I have done a silly mistake; I accidentally revoked all privileges from root user from the same server.
- I first granted all privileges to root from the same server [because I was getting access denied error through a browser on some application I am working on, and I thought this could solve it].
I ran this command:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'my-server-ip-address' IDENTIFIED BY 'server-password';
=> This didn't solve anything, it rather affected another application hosting on the same server. So, without thinking too much, I wanted to undo what I had done, by revoking all the privileges.
I ran this command:
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'my-server-ip-address' IDENTIFIED BY 'server-password';
- As a result, I now cannot log in to MySQL server even from the server itself (I revoked privileges from exactly that server ip-address), I am getting access denied error.
=> Fortunately, I am able to access the MySQL database through a client (Navicat).
Through Navicat I have done the following:
- Checked 'information-schema' database, 'user_privileges' table, I tried to add a record with root privileges, BUT I get access denied (can't save anything to that database)
- Checked 'MySQL' database, 'user' table: to give privileges ==> in this table I saw a record with root user & my-server-ip-address as host, and changed all privileges from 'N' to 'Y'. [meaning for all privileges it had 'N']
-I restarted MySQL server
=> After changing all these, I still get access denied error when trying to login to Mysql server.
Please help me, I have done all I could do!