Wednesday, July 3, 2013

Resetting MySQL root Password

If you forget MySQL Root Password, you can reset it.
You need to have root access on the system OS (CentOS, Ubuntu etc..)

First Stop the current MySQL process.

service mysqld stop

OR

/etc/init.d/mysql stop

After stopping the current MySQL process, Start MySQL in safe mode with --skip-grant-tables option

mysqld_safe --skip-grant-tables &

After running MySQL in safe mode, you can run mysql update statement to change the root password.

Connect to MySQL server with root user

mysql -u root

Select/Connect to mysql database

use mysql;

Update root password

update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

Flush MySQL privileges

flush privileges;

Exit MySQL

quit

You are all done now.

Stop MySQL safe mode instance

/etc/init.d/mysql stop

OR

service mysqld stop

Now It's time to start MySQL in normal mode.

service mysqld start

OR

/etc/init.d/mysql start