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.

cd /MYSQL/INSTALLATION/DIR
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";
MYSQL> FLUSH PRIVILEGES;
MYSQL> EXIT;

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

OR

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:

cd /MYSQL/INSTALLATION/DIR
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.