How to reset a forgotten MySql root password

Including how to recover a lost .my.cnf file installed by ServerPilot

Posted by Ian on 21st Feb 2018

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

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:

user = root
password = the-new-root-password