How to Reset the root Password of MySQL | Reset MySQL Password if you have lost the root Password
Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. Follow the steps to recover the password
Step 1: Stop MySQL instance.
[[email protected]~]# service mysqld stop Stopping MySQL: [ OK ]
Step 2: Start MySQL in Safe mode with the mysqld_safe command and mention not to read the grant tables with all the MySQL database passwords.
[[email protected]~]# mysqld_safe --skip-grant-tables --skip-networking &  6116 [[email protected]~]# Starting mysqld daemon with databases from /var/lib/mysql
Step 3: Now MySQL is now running without password protection. You now have to use the mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.
[[email protected]~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.16 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Step 4: You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to 123456.
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 mysql> UPDATE user SET Password=PASSWORD("123456") WHERE User="root"; Query OK, 1 row affected (0.01 sec) Rows matched: 2 Changed: 1 Warnings: 0 mysql>
Step 5: Exit MySQL and restart the mysqld server daemon.
mysql> exit Bye [[email protected]~]# service mysqld restart STOPPING server from pid file /var/run/mysqld/mysqld.pid 051224 17:24:56 mysqld ended Stopping MySQL: [ OK ] Starting MySQL: [ OK ] + Done mysqld_safe --skip-grant-tables --skip-networking
The MySQL root user will now be able to manage MySQL using this new password.
Step 6: Use the following command to login to MySQL. The following method of login is not recommended, it just for example.
[[email protected]~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>