root lost all privileges

Portal Home > Knowledgebase > Articles Database > root lost all privileges

Posted by Vortex153, 10-27-2010, 02:11 AM
I leave on vacation and my site is running fine. I get back and there seems to be some sql errors. After some searching, the root account (which I was currently using since I'm still in early development) had a changed password. No idea how, either way I managed to change the root password to something else. So I now have access to the mysql, however the 'root' user now has no privileges at all except Select_priv. root is currently the only user and I can't seem to GRANT it anything. Any suggestions?
Posted by sysadm2, 10-27-2010, 02:20 AM
If you have MySQL root user's password, then you can change the privileges for the user as well..rite ?
Posted by sysadm2, 10-27-2010, 02:47 AM
Yeah..now since you know the root password, re-assign the privileges and also take measures so that the same won't be happening again..
Posted by drspliff, 10-27-2010, 04:26 AM
Did you just absolutely fail to comprehend his predicament? He can't do that because the 'root' mysql user doesn't have the privileges to do it. Anyway, by restarting MySQL with --skip-grant-tables you can then login and alter the `mysql.users` table or add new grants to your mysql root user, then restart mysql again and all should be fix'd.
Posted by sysadm2, 10-27-2010, 06:40 AM
ohh..ok..then yeah.. --skip-grant-tables will do. I mean just you would need to follow the exact steps to reset MySQL root password.
Posted by SI_Support, 10-27-2010, 08:14 AM
drspliff is right. In this situation you have no other way. You should "hack" (restart with --skip-grant tables) your mysql server and change the grants and password.
Posted by Vortex153, 10-27-2010, 01:43 PM
Cheers, used the --skip-grant-tables and all is well. Curious as to how the password got changed in the first place, and the privleges reduced. :/
Posted by drspliff, 10-27-2010, 01:47 PM
Which applications do you have which access MySQL as the root user on a regular basis?
Posted by Vortex153, 10-27-2010, 03:04 PM
It's just a forum website I'm developing. When it's actually used, it will have a unique mysql user.
Posted by akasharya, 10-28-2010, 12:29 AM
Same thing happened to me once but have not found how the root password was changed?
Posted by sysadm2, 10-28-2010, 12:29 AM
Do you have mysql logs defined ? You may get some clue from the log traces.
Posted by Vortex153, 10-28-2010, 01:29 AM
I honestly don't know how to check the logs (if they're defined). I'm using the site I'm making as a learning tool in my spare time, so I don't have everything down.
Posted by sysadm2, 10-28-2010, 01:49 AM
From console execute the command mysqladmin variables It will list all the default set variables for MySQL service. The defined logfiles if any will be listed. Once getting the logfile, you will have to check the file for traces. If the file is not too large in size, you can use the vi command to open and check, else use tail -100 /path/to/logfile (this will show the last 100 lines frm the file ) you can change 100 to number you wish to see.
Posted by care4server, 11-14-2010, 03:53 AM
try the last steps in teh following url.. helped me in restoring root privileges. http://www.jimhermann.com/ensim/index10811.htm