Monday, October 28, 2013

Reset mysql root Password

I wanted to reset the mysql root password of one of my temporary hosted mysql database server. Since it was not used for a long period of time, I couldn't remember the the root password correctly.

As the first step, I loged into the server as the root user. I was using a Ubuntu 12.04 server.

First log in to the system as a normal user and then 'sudo' to get root access.

As the second step it was need to stop the mysql daemon using the following command.
$/etc/init.d/mysql stop
$service mysql stop


Please note that any of the above command is working fine.

As the third step, I started up the mysql daemon and skipped the grant tables which store the passwords using the following command.
$mysqld_safe --skip-grant-tables

As the next step I connected to mysql without a password.
$mysql --user=root mysql

As the final step I entered the following command to set a password to the root user.
$UPDATE user SET Password=PASSWORD('new-password') WHERE user='root'; FLUSH PRIVILEGES;

The below listed format of the command also worked as expected
$UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root'; FLUSH PRIVILEGES;

Hope this will work fine to you also....:)