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
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.