You probably tried to login with your root password and got this error.
Access denied for user 'root'@'localhost'
Recover MySQL root password with mysqld_safe
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
#kill -9 PID
where PID is the process id of the MySQL daemon
Start MySQL in safe mode
#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.
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.
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.