MySQL – Recover Root Password

Oops, I ran a MySQL root password reset script which was supposed to generate a random password and the process died halfway through. What now? We have to figure out a way to reset it.

These commands may differ on your distro of Linux that you are running. In these commands I’m assuming a Debian based system, more specifically – CentOS.

Before you get started, you need to login to your shell either as root or a user with root privileges, eg. sudo . For the latter, just do sudo su and be on your way.

First thing, stop the MySQL service from running.

service mysql stop

Great, now we can start MySQL in safe mode and basically turn off authentication. This is a security risk but we have to quickly push through the commands to reset our root password, then we’ll secure it again.

mysqld_safe --skip-grant-tables &

The --skip-grant-tables disables the authentication while the ampersand & just silences the script from running in the shell so that you can continue with the rest of your commands.

Now launch the MySQL service in the command line.

mysql

Then set a new password to your liking.

UPDATE mysql.user SET Password=password('superpassword') WHERE User='root';

We’re almost done. Just flush privileges and exit the MySQL command line interface.

FLUSH PRIVILEGES;
exit;

Kill the safe mode MySQL process that is running

mysqladmin -u root -p shutdown

At this point you’ll be prompted the enter the new root password you just set. Happy days.

Now we can start MySQL service back up again – fingers crossed 🙂

service mysql start

That’s it you’re back up and running.