You don’t need this tutorial if you have access to the root user or another one with SUPER and GRANT privileges.

The following instructions works for MySQL 5.7. You will need to stop the MySQL server and start it with mysqld_safe with the option skip-grant-tables:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root mysql

If you get an error on start, chances are there is no folder created for the mysqld_safe executable to run, on my tests I was able to solve by doing:

sudo mkdir /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld

And then trying to start the mysqld_safe process again.

After this, the MySQL console will pop up, and you need to set up a new password for root. The second line is necessary due to a MySQL bug # …

Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the …