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 &
[1] 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
[2]+  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: