mysql - Reset Root User Privileges

1. Find the my.ini file (my.cnf for linux). On windows, the my.ini file is typically in your install directory: c:\Program Files\MySQL\MySQL Server 5.1\my.ini
OR if your using xamp then it will be C:\xampp\mysql\bin\my.ini
Open the file with notepad (or any text editor) and add the following entry under the [mysqld] section:

skip-grant-tables

Then, save the file and restart the MySQL service.

If you have Phpmyadmin then select the mysql databese and open the user table. In that you will find the all mysql users.

Then you need to edit the root row and update all the radio buttons with Y.



If you dont have Phpmyadmin then
mysql > use mysql;
Delete the old root user like
mysql > delete from user where user='root';

Create root user again (WITH RIGHTS!)

mysql > INSERT INTO user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv) VALUES ('localhost', 'root', PASSWORD('thepass'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

OR
you can update the above instead of re creating the user account.


After that remove the skip-grant-tables from the my.ini file and restart the Mysql. thats it.