How to reset a forgotten MySql root password

After using ServerPilot to manage a server and running some backup scripts, I accidentally deleted the /root/.my.cnf file containing the mysql root password. This meant I had no way of connecting as the mysql root user.

It seems ServerPilot uses it's own mysql admin user account to manage the databases, so there is no harm in changing the root mysql password.

This is how to do it when you don't know the root password:

• login as root on your server or sudo su to become root

• stop the mysql service
  service mysql stop

• run the mysql server in safe mode without grant tables
  mysqld_safe --skip-grant-tables &

• hit enter to continue

• You may get errors about missing /var/run/mysqld. if you do, simply create the folder

  • mkdir -p /var/run/mysqld
  • chown mysql:mysql /var/run/mysqld

• Start a mysql shell as root. You will not need a password
  mysql -u root

• use the mysql database
  use mysql;

• update the root password
  update user set password=PASSWORD("the-new-root-password") where user='root';

• If you get an error saying the password column doesn't exist, change password= to authentication_string= e.g.

  • update user set authentication_string=PASSWORD("the-new-root-password") where user='root';

• flush the privileges so they are current and saved
  flush privileges;

• exit the mysql shell
  quit

Now you can restart the mysql service service mysql start

If you need to re-create a lost /root/.my.cnf file for the root user, it should contain the following:

[client]
user = root
password = the-new-root-password