Lost your MySQL root password? Here is how to reset it via SSH.

[MannDude]

I recently needed the MySQL root password on a server of mine to complete a task but had misplaced it and was unable to locate it. Thinking that I had seriously goofed up I was worried. Luckily, resetting the password was surprisingly simple.

All you will need is root access to your VM and about two minutes of time.

First things first, you'll need to shutdown MySQL temporarily:

service mysql stopOnce the service has shutdown, you can proceed to restart it with the following:

mysqld_safe --skip-grant-tables &Now you should be able to login as the MySQL root user without being prompted for a MySQL password. Give it a whirl:

mysql -u rootYou should see the familiar MySQL prompt now.

To reset the password you can follow the commands below:

mysql> use mysql;
mysql> update user set password=PASSWORD("MyNewMySQLPassword") where User='root';
mysql> flush privileges;
mysql> quit(Obviously replacing "MyNewMySQLPassword" with a proper password)

Restart MySQL:

service mysql restartCheck that your new MySQL password works, it should:

mysql -u root -p
That's it.

 

[samuellb]

Thanks, this is very useful! Just a heads up, the second command seems to give all users full access:

--skip-grant-tables Start without grant tables. This gives all users FULL
ACCESS to all tables.
So you better stop anything that can access the DB before you do this (e.g. web server running PHP scripts or even phpmyadmin)

I also found another (longer) way of doing this with an "--init-file" option, which is apparently only available on Linux and Windows. I haven't tested it but it could be an option on systems with multiple user accounts:
https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html