Resetting Your Lost mySQL Root Password

Ever forgotten your root password on a MySQL server? Or maybe you tried to change it and something went wrong. Now you're locked out of your own database.

If you find yourself in this predicament, especially if you're a bit new to MySQL, you're probably panicking a bit right now; especially if you're working on a live database. Don't panic, and don't go doing anything drastic either; all is not lost. Besides, even reinstalling MySQL isn't going to make the problem go away, so if you've done that, you're probably really panicking right now :-)

There are a few easy steps to repairing / resetting your root password. We cover them below:

1. Open a terminal as root
   
   Open your favorite terminal / terminal emulator. To gain root access, use su - or sudo su - depending on how your system is configured and which passwords you have access too.
   


2. Stop your MySQL server
   
   service mysqld stop or /etc/init.d/mysqld stop
   


3. Start MySQL in "safe mode"
   
   mysqld_safe --skip-grant-tables &
   


4. Open MySQL
   
   mysql
   


5. Change your root password
   
   update mysql.user set password = password('your new password') where user = 'root';
   


6. Exit mysql
   
   exit or ctrl + d
   


7. Kill the MySQL daemon
   
   We need to now kill the MySQL daemon that is running in "safe mode". Since you started the process in the background using &, you can easily find the PID by typing jobs -l and then force kill with kill -9.
   
   It should looks something like this:
   
   [root@ws11042 ~]# jobs -l
[2]+ 25065 Running mysqld_safe --skip-grant-tables &
[root@ws11042 ~]# kill -9 25065
[2]+ Killed mysqld_safe --skip-grant-tables
[root@ws11042 ~]#
   


8. Start up MySQL daemon
   
   /etc/init.d/mysqld start or service mysqld start
   

And you're all done.