Resetting MySql Root Password

Have you ever been in a position where you have lost or been presented with the loss of the mysql root password. Oh yes, much fun and this happened when someone in authority needed mysql access.

We I tried the –skip grant tables, did not work so time for a different approach, enter the realm of the sever bodge (a bodge I would use in future unless other sites would be affected)

1. OK, log into the sever with SSH
2. find the mysql files, on fedora they are in /var/lib/mysql/mysql
3. Change the name of the three files user.frm, user.MYD and user.MYI, don’t delete them, call them user1.frm etc.
4. Log into another server with which has mysql and and you know the password and download the three user files. Transfer them to the original server.
5. Change the owner and group to mysql

chown mysql:mysql mysql

6. Change the chmod to the same as the other files in that directory, probably, 660
7. No you are nearly ready to go, you need to restart mysql so that the new password if activated as you need to flush the old privileges

Service mysqld restart

8. Right, if you have live sites on there this is the fly by the seat of the your pants bit as any of the usernames and passwords wont be present. So now open up phpmyadmin and use the root UN/PS. Bada Bing, your in. Got to the mysql db and look, you should see the new table called “user1”. Go into that and do a dump. Now take that dump and put it back into and sql window. Make sure you remove the root sql insert as you wont be able to overwrite this and quite frankly you don’t want to be able to either. If you don’t want to go through this last stage I suppose you could add the missing users before you do 5.

Hope that helps.