How to reset your mysql root password

Sometimes after long time not touching the backend we tend to forget what was the password for mysql root account and we will need to change it to be able to access our databases to clean them or add new database.
First thing you need to do is to stop your mysql daemon. This depending on your operating system can be done dunning service or systemctl commands. For more clear understanding will assume that we are on a debian 7.x environment, so we will stop the mysql daemon using service commands.

#service mysql stop

When we made sure that the mysql daemon is stopped we need to start the mysql but skipping the grant tables where mysql stores the passwords:

#mysqld_safe --skip-grant-tables

You should see the mysql start successfully. One a side note this command will run in the forground therefore any new command will have to be executed in a new shell.
Now you should be able to connect to your mysql database without root password

#mysql -u root

Once connected to the database you can update the password running the following commands in the mysql shell:

mysql> update user set Password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
mysql> exit

Now it’s time to kill your current mysql daemon and start the mysql normally.

#killall -9 mysqld_safe
#service mysql start

You can actually try these commands on our platform in few minutes utilizing our PCS (Private Cloud Solution) which allows you to have VPSie(s) on a private network – NAT – Port forward – traffic control for inbound and outbound – multiple gateway IPs which you could use for the load-balancing and failover.

Related Articles