Five steps to recover MySQL root password

MySQL is one of the most commonly used database. Most of the small and medium websites uses MySQL as the back end. It is the most popular open source database. The full privilege user account for MySQL is "root" user. This user account was configured at the time of installation. So some people facing issues to login to MySQL database because of forgetting root password. You can recover a MySQL database server password with the following five easy steps:

The commands explained here is for linux machines. First login to your system as the 'root' user.
Step 1: Stop the MySQL server process.
Open command prompt and type following command to stop MySQL server process.

# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld.

Step 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables.
By using this option you can login to MySQL without password.

command used for login is

# mysqld_safe --skip-grant-tables &

Output:
[1] 2961
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Step 3: Connect to the MySQL server as the root user.
Use MySQL client to connect. Now you can login to MySQL without password.

#mysql -u root

Output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

Step 4: Set a new root password.
You can set new password to root user now. Use following commands to set new password.

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

Step 5: Restart MySQL server
First quit mysql and then restart the MySQL server by using following command

# /etc/init.d/mysql restart

Posted in: MySQL