MySql Root Password Recovery

A few of my Linux machines only get accessed remotely from a shell or web interface. Since they are usually very reliable and don't give me any problems I have a tendency to forget about them and all the different passwords that I use on them.

Now being a somewhat security conscious person I use unique and complex passwords for every account and machine that I have control over. I also do not have the best memory in the world so I have used a few of the password manager utilities. I am currently using KeePass and experimenting with LastPass to manage some of my passwords.

But as life would have it I must not have entered or forgotten to update the password for one of my MySql instances and here is how I solved the problem.

This method will take down the MySql daemon for a short time so I first made sure that no important services would be interrupted. Then I stopped the server.


[root@localhost /]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL: ... [ OK ]


Then start the server skipping the grant table.


[root@localhost /]# mysqld_safe --skip-grant-tables


If the server does not start you probably have other issues but assume that the server started up correctly. Now you need to reset the password. Login to the MySql server.


[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.1.34 - MySQL Standard Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set Password=PASSWORD('new-password') where user='root';

mysql> flush privileges;

mysql> exit


Now the password has been reset so you need to kill the mysqld process and start the server normally. To kill the server I just grepped for the process number.


[root@localhost /]# ps aux | grep mysql
mysql 8856 ....deletia...
[root@localhost /]# kill -9 8856
[root@localhost /]# /etc/rc.d/init.d/mysqld start
Starting MySQL: ... [ OK ]


Now you should be up and running with a new password and DO NOT forget to update the password manager with the new password.