Mon Jul 4 13:53:56 EEST 2011
How to restore lost (forgotten) MySQL Server root password
On a couple of occasions, I've had the issue of loosing / forgetting a MySQL password at other times I had to login to servers which contained MySQL servers with a password I don't know.
In any of this occasions I was required to restore the ROOT access to the mysql server.
Sadly recoving a mysql lost root password is not really possible in a easy way, thus in most cases the only option one have is to stop the mysql server and run it again in a special skip-grant-tables mode, which runs the server in a sort of passwordless safe mode and makes it accessible without admin password through mysql cli .
To Omit the password check the skip-grant-table option instructs the MySQL server not to check in mysql.user while any request to connect with mysql client to the server is made.
Therefore one can login to the mysql server passwordless and reset the current (lost) password to another one.
Thease can be achived in the following four easy steps:
1. Stop the MySQL server
mysql:~# /etc/init.d/mysqld stop
2. Run the mysql server with the --skip-grant-tables option
mysql:~# mysqld_safe --skip-grant-tables
&
3. Login to the mysql and change current mysql root password
mysql:~# mysq -u root
mysql> UPDATE user SET password=PASSWORD('yournewpassword')
\
WHERE user='root';
mysql> FLUSH privileges;
4. Stop and Start the Mysql server again
mysql:~# killall -9 mysqld_safe
mysql:~# /etc/init.d/mysqld start
..
Now the new mysql password will be identical to the yournewpassword . Cheers ;)