Reset mysql root password

This post is about how to reset your mysql root password if you cannot recall it and you have lost it forever.
First thing first you need to make sure that your mysql server is turned off. This can be done by the following commands.
Debian or older Ubuntu:

#service mysql stop

Centos 6 or lower:

#service mysqld stop

Centos 7, Fedora20, latest version of Ubuntu:

#systemctl stop mariadb.service

After we have stopped the mysql database server we will start the server manually with skipping the grant table. This will not load the tables where the passwords are stored.

#mysqld_safe --skip-grant-tables

You should see the mysql start in the foreground. If you see any error messages then you will have to analyze more in dept fact which is not covered in this post. You will need to open a new session of console to be able to connect to the database without password:

#mysql -u root
Once connected you can update your root password with a new one:

mysql> update user set Password=PASSWORD('newpassword') where user='root';
mysql> flush privileges;
mysql> quit;

Now it's time to kill the existing mysqld process and restat the server normally.

#killall -9 mysqld_safe

Starting the mysql server on Debian or older Ubuntu OS:

#service mysql start

Starting the mysql server on Centos 6 or older:

#service mysqld start

Centos 7, Fedora20, latest version of Ubuntu:

#systemctl start mariadb.service

Keresztes Péter-Zoltán

Read more posts by this author.

Subscribe to IT Ghost

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!