Recover MySQL root password

Do not worry if you lost your MySQL root password. If you have access to the server where it is running, and assuming it is a Linux or Mac or BSD server, you can easily recover the MySQL root password.

Here are the steps to recover the root password. You need to run this commands as root or using sudo

Stop the server.

/etc/rc.d/mysqld stop

Start the server with --skip-grant-tables option

mysqld_safe --skip-grant-tables &

Connect to the MySQL server as root, you will not be asked for a password

mysql -u root

Set the new password

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit

Restart the server

/etc/rc.d/mysqld restart

I've done that on Arch Linux, but it should work on any Nix server.

Here are all steps with outputs in a terminal window

[root@li367-250 http]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@li367-250 http]# /etc/rc.d/mysqld stop
:: Stopping MySQL Server                                                                                                                                                                           [DONE] 
[root@li367-250 http]# mysqld_safe --skip-grant-tables &
[1] 25560
[root@li367-250 http]# /usr/bin/mysqld_safe: line 534: hostname: command not found
120610 10:08:25 mysqld_safe Logging to '/var/lib/mysql/.err'.
/usr/bin/mysqld_safe: line 611: hostname: command not found
120610 10:08:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@li367-250 http]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("YOUR-NEW-PASS-GOES-HERE") where User='root';
Query OK, 3 rows affected (0.25 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql> quit
Bye
[root@li367-250 http]# /etc/rc.d/mysqld stop
:: Stopping MySQL Server                                                                                                                                                                           [BUSY] 120610 10:10:16 mysqld_safe mysqld from pid file /var/lib/mysql/.pid ended
                                                                                                                                                                                                   [DONE] 
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@li367-250 http]# /etc/rc.d/mysqld start
:: Starting MySQL Server                                                                                                                                                                           [DONE] 
[root@li367-250 http]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye