MySQL. Recovering a Password with Physical Access to the Server

I’ve had to restore access to MySQL several times now, and today I decided to write this down. Basically, it’s all explained on the page http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html, but I still wanted to keep a note of it here.

And yes, this will actually change the password rather than recover it. But you will regain access!

First option

Let’s start mysqld without enforcing privileges and change the password for the root user.

Останавливаем сервер MySQL и пробуем запустить mysqld без применения привелегий:
$ sudo /etc/init.d/mysql stop
$ mysqld --skip-grant-tables --skip-networking

После чего обновляем пароль для пользователя root:
$ mysql -uroot
mysql> UPDATE `mysql`.`user` SET `password`=PASSWORD('YOUR_NEW_PASSWORD') WHERE `user`="root";
mysql> FLUSH PRIVILEGES;

Запускаем MySQL-сервер:
$ sudo /etc/init.d/mysql start

Second option

If the first option doesn’t let me access mysql, what helps me is loading an init script that changes the password for the root user.

Stop the MySQL server and create the file “~/init.sql”:

$ sudo /etc/init.d/mysql stop
$ nano ~/init.sql

With the following contents:

UPDATE `mysql`.`user` SET `password`=PASSWORD('YOUR_NEW_PASSWORD') WHERE `user`='root';
FLUSH PRIVILEGES;

Then start mysqld_safe, interrupt it, and start the MySQL server:

$ mysqld_safe --init-file=~/init.sql
$ sudo /etc/init.d/mysql start

These two options always work. Good luck!