Reset mysql root password

If you have forgotten the root password to a mysql database, it is easy to reset as long as you have permission to kill the existing running mysql instance and restart it directly.

So the first thing is to find the running mysql.  There are multiple ways the instance could be started (e.g. launched automatically from /etc/init.d, or launched manually by someone keying in mysqld_safe –user=mysql), and it may or may not have a .pid file — and even if it had a .pid file, it may be named differently or placed in different locations.  Looking for it from the processes list is the simplest way.

ps -ef |grep -F "mysql"

Use the command above to find the PID of the running mysql, and kill it with -9

kill -9 PID

Launch mysql with the –skip-grant-tables option.  It is recommended to also use –skip-networking so while you are resetting the password, it won’t let anyone else remotely connect to it.

bin/mysqld_safe --skip-grant-tables --skip-networking

log into mysql as root, and this time, it will not ask you for password (how nice!)

mysql -u root

After you are in, reset the root password and exit as follows:

MYSQL> USE mysql;
MYSQL> UPDATE user SET Password=PASSWORD("newAndMemorablePassword") WHERE User="root";

Follow the instructions above to locate the mysql process, and kill it again.

Then finally, start mysql the normal way. If you are using the mysql that was installed using the package management tool that came with your linux distro, you may do any of the following:

cd /etc/init.d
sudo mysql start


sudo /sbin/service mysql start

OR sometimes, service is placed in a different directory

sudo /usr/sbin/service mysql start

However, if you installed mysql without using any package management tools, you probably have to start it manually:

sudo bin/mysqld_safe --user=mysql &

Note: according to mysql’s own instructions, there is supposed to be a safer and preferred way to reset the password, which is to create an init file and launch mysqld_safe by using the –init-file option, and the content of the init file is the “UPDATE…” and “FLUSH…” statements listed above. But this method didn’t seem to be working for me.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s