It happens quite often, that You need to do something on database, which was installed long long time ago. And nobody remembers the root password.
Kinda good practice is to save mysql password in root user directory with a “.” in front of it (hidden), for example .my.cnf
Nobody can read root users directory, except root, and anyway, root user can change mysql password at any time, so it’s not an security issue.
If all users are able to see root users directory, that is a problem :)
Easy steps how to recover mysql root password.
# /etc/init.d/mysql stop # mysqld_safe --skip-grant-tables & # mysql -u root mysql> USE mysql; mysql> UPDATE user SET password=PASSWORD("new_root_password") WHERE user='root'; mysql> FLUSH PRIVILEGES; mysql> QUIT # killall -9 mysqld_safe # /etc/init.d/mysql start # mysql -uroot -p
That’s it :)
If You need, below are the same steps in details:
“#” means root shell
1. Stopping mysql service
# /etc/init.d/mysql stop Stopping MySQL database server: mysqld.
2. Starting mysql without permissions and passwords
# mysqld_safe --skip-grant-tables &  12985 111101 21:52:06 mysqld_safe Logging to syslog. 111101 21:52:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3. Loging in to mysql as root user without password
# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-3 (Debian) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
5. Choosing database where all grants are
mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
6. Changing root password
mysql> UPDATE user SET password=PASSWORD("new_root_password") WHERE user='root'; Query OK, 0 rows affected (0.03 sec) Rows matched: 3 Changed: 0 Warnings: 0
7. Apply changes
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
8. Quit mysql shell
mysql> QUIT Bye
9. Stop mysql service running without permissions
# killall -9 mysqld_safe + Killed mysqld_safe
10. Start normal mysql process
# /etc/init.d/mysql start Starting MySQL database server: mysqld ..
11. Here You go, try Your new password :)
# mysql -uroot -p Enter password: