How to reset MySQL root password

In this article I will show you how to reset MySQL root password in two different ways. Also both methods happen to work with the Oracle-free MariaDB fork.

Why?

There’s two main reasons one would need to reset the root password:

• you forgot that password
• you don’t know that password

There could be a lot of reasons to not knowing the password: you never knew the password and the old DBA has gone; someone changed for fun or malicious reasons; it’s 3am, the system is crashed and the dba is on vacation on another country, etc.

The fact is that one morning you need to to stuff in the database and get to this situation:

I’m assuming that you have superuser (i.e. su or sudo to root) access to the Operating System. My Operating System in this tutorial is Debian Linux.

Both methods require you to stop mysql service before. Do it with (don’t forget to su/sudo as necessary):

# service mysql stop

Method 1:

Create a file with the SQL command to set a new root password that we will use in the mysql start. You can use any text editor or just type:

# echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';" > passreset.sql

If you choose the text editor, create a file with just the bolded part. Of course, choose a real password, easy to remember and hard to discover.

And start it manually with the user mysql ( -u mysql) and with the file we created before (–init-file:passreset.sql). The full command line would be:

# mysqld -u mysql --init-file=/var/lib/mysql/passreset.sql

Wait until the message: mysqld: ready for connections. Then open another console to test your new root password:

Method 2:

In the past I’ve seen this how to reset mysql root password several times in the internet. In the first try this morning didn’t work for me, but I found a workaround.

We will use this switch of mysqld command:

--skip-grant-tables    Start without grant tables. This gives all users FULL
                               ACCESS to all tables.

This left me full access to all tables. The procedure is to connect to mysql and run the ALTER USER command we’ve seen in the previous method, but I got an error about mysqld running with skip-grant-tables.

The workaround is to run mysqld with the switch skip-grant-tables and using the mysql user. The full command line is:

# mysqld -u mysql --skip-grant-tables

And, in another console, flush privileges before the update, then flush privileges again:

# mysql -u root
MariaDB (none) > flush privileges;
MariaDB (none) > ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
MariaDB (none) > flush privileges;
MariaDB (none) > ^DBye

Finish

Now you can stop mysql and start it normally. In my case, ctrl-c didn’t work to stop it, I have to kill the manually started process before:

# killall mysqld
# service mysql start

And this is it, with one of those methods you regain the control on your MySQL or MariaDB.