How to reset the MySQL root password

This is a usual situation where you have added a password to the root user during the MySQL installation, but later on, you forgot it and can’t login. Don’t worry, we have been to the same situation and will help you to change the password in a few steps.

1. The first thing to do is to stop the MySQL server:

1
2
3
sudo /etc/init.d/mysql stop

1. Start the server with additional flags:

1
2
3
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

The --skip-grant-tables flag will start the server without the grant tables in MySQL system schema. That means no privileges and unrestricted access to all databases, so it is best to also disable remote connections by using the --skip_networking.

1. Use the MySQL client to connect back to the server:

1
2
3
 mysql -u root

1. Now, let’s reload the grant tables:

1
2
3
FLUSH PRIVILEGES;

1. Update the password for root user:

1
2
3
4
5
update user set authentication_string=password('new_pass') where user='root';

update user set plugin="mysql_native_password" where User='root'; 

The mysql_native_password is the default authentication plugin used. After executing the above query just restart the server and you can loggin with new password.

Change the root password in MariaDB 10.x

In the latest MariaDB versions the above steps are simplified. You can skip steps 1 and 2 from above. Start from the 3 step and use sudo when login to MySQL client. The 4 and 5 steps are the same. To verify if the new password works:

1
2
3
mysql -u root -p