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:

[code lang=bash]
sudo service mysql stop
sudo mysqld_safe –skip-grant-tables &
mysql -u root mysql
[/code]

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:

[code lang=bash]
sudo mkdir /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld
[/code]

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 #79027:

[code lang=sql]
UPDATE mysql.user SET authentication_string=PASSWORD('mypassword') WHERE User='root';
UPDATE mysql.user SET plugin="mysql_native_password" WHERE User='root';
FLUSH PRIVILEGES;
[/code]

Once finished, kill all MySQL processes and start the service again:

[code lang=bash]
ps aux | grep mysql
sudo kill -9 [pid]
sudo service mysql start
[/code]

Done, you have reset the root password! Make sure to keep it safe this time around!

See something wrong in this tutorial? Please don’t hesitate to message me through the comments or the contact page.

• Tutorial