Tuesday, April 13, 2010

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


  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.

1 comment:

  1. simple as that? dammit... i wish i knew these steps a month ago, where i had to try-and-error, trying to recall the password for 2-3 hours..
    thanx for the tip, man