HowTo recover MySQL root password

By default, MySQL Server will be installed with root superuser without any password. You can connect to MySQL server as root without requiring password or by keying in blank password. However, if you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.

Step 1 is to stop MySQL service with

# /etc/init.d/mysql stop

or

# service mysqld stop

Step 2 is to start MySQL w/o root password

# mysqld_safe --skip-grant-tables &

Step 3 is to connect with mysql client

# mysql -u root

Step 4 is to finally change old root password with

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

Step 5 is to stop mysql server and restart it without –skip-grant-tables parameter

# /etc/init.d/mysql stop

Step 6 is to start mysql server and test it with

# /etc/init.d/mysql start
# mysql -u root -p

Note:

After you install your new server, it is very good idea to improve the security of your MySQL installation with

# mysql_secure_installation

With mysql_secure_installation wizard you can set a password for root account, you can remove root accounts that are accessible from outside the local host, you can remove anonymous-user accounts and you can remove the test database, which by default can be accessed by anonymous users. The script will prompt you to determine which actions to perform.