recover MySQL root password with mysqld_safe

http://newexception.com/recover-mysql-root-password

In order to recover MySQL root password you need to do this five easy steps.

1. Stop MySQL server
2. Start MySQL in safe mode
3. Change MySQL root password
4. Stop MySQL safe mode
5. Start MySQL server
Stop MySQL server
To stop MySQL server under Ubuntu / Debian use one of the following commands:


#service mysql stop
If that doesn't work, you can still kill the mysqld process


#killall mysql
or


#kill -9 PID
where PID is the process id of the MySQL daemon


Start MySQL in safe mode
#mysqld_safe --skip-grant-tables
or


#mysql_safe --skip-grant-tables &
The first command will start MySQL in safe mode and you will need to keep the terminal open. The second command will put MySQL in background.


Change MySQL root password
Login to MySQL as root


#mysql -u root -p
mysql> USE mysql;
mysql> UPDATE user SET password = PASSWORD("new_password") WHERE User = 'root';
What we have done here is we told MySQL that we are going to use the MySQL database, because all settings regarding MySQL are stored in this database.


Stop MySQL safe mode
Go back to the terminal where you started MySQL in safe mode and press CTRL + C. If you used the second command, you will need to kill the process.


#killall mysqld_safe
Note: Before proceeding, be sure that MySQL is not running.
Start MySQL server
#sudo service mysql start
You are done. Now you can login to MySQL with your new password.


Recover MySQL root password with debian-sys-maint user
Another way to recover the root password is by login with the debian-sys-maint user, which will work only in Ubuntu / Debian. You can find this info in the debian.cnf file which is located under /etc/mysql.


# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
Simply login with debian-sys-maint user and enter the password which is specified in the debian.cnf file


#mysql -u debian-sys-maint -p
Thanks to Regis Caspar for pointing this out.