Forgotten MySQL root password

1. Start.

2. Stop the mysqld daemon process:

root@prabhat:~# /etc/init.d/mysql stop

3. Start the mysqld daemon process with the --skip-grant-tables option.:

root@prabhat:~# /usr/bin/mysqld_safe --skip-grant-tables &

[1] 6702

Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe[6763]: started

.

4. Start the mysql client with the -u root option:

Note: - In Mysql server running with the --skip-grant-tables flag not require password

root@prabhat:~$ mysql --user=root mysql

Enter password:

5. Execute the UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';

Query OK, 2 rows affected (0.04 sec)

Rows matched: 2 Changed: 2 Warnings: 0

6. Execute the FLUSH PRIVILEGES; command:

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

mysql> exit

Bye

7. Stop the server, so that you can go back to running a secure MySQL server with password restrictions in place.

Bring the server into the foreground by typing "fg", then kill it by pressing "Ctrl+c" afterwards.

8. start the server in normal mode:

root@prabhat:~# /etc/init.d/mysql start

Starting MySQL database server: mysqld.

Checking for corrupt, not cleanly closed and upgrade needing tables..

9. Verify this by connecting with your new password:

root@prabhat:~# mysql --user=root --pass=new-password-here

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit

Bye

10. Stop