Monday, 15 August 2011

How to recover MySQL root password

We have a MySQL server in an Ubuntu Linux machine and the root user’s password for that server was lost. We need to recover the root user’s password and it was done as follows:
1) Stop the running MySQL server
#service mysql stop

2) Start MySQL server with --skip-grant-tables
--skip-grant-tables causes MySQL server to start without using the privilege system, so anyone with access to the server will have unrestricted access to all databases .
#/usr/sbin/mysqld --skip-grant-tables &

3) Connect to the MySQL server using root account
#mysql -u root

4) Once we have mysql prompt we will set a new password for the root user.
mysql> use mysql;
mysql> update user set password=PASSWORD(”new_password”) where user=’root’;
mysql> flush privileges;
mysql> quit;

5) Stop MySQL server
#/etc/init.d/mysql stop

6) Start MySQL server in normal mode
#/etc/init.d/mysql start

Now we can connect to MySQL using root user account with the new password we set.

জয় আই অসম,
প্রণব শর্মা