Gonzalo Rivero
I am Gonzalo, I live in Salta, a city located in the NW of Argentina near the border with Bolivia and Chile (in South America). I play the guitar and a little harmonica, I love blues music and heavy metal but any kind of music is fine. I also like to bike; I used to mountain bike a lot, but now I have a condition on my back near the neck and because of that I have to choose plain terrains to ride and not very long distances. I hope my days of going up hill or down hill aren't over yet.

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:

can't login
can’t login

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:

As you see, I’m doing this actually on MariaDB. The important thing is that I could login with the new 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.
MySQL start faster with a lot less output

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
Not shown in the screenshot: the other console where I’ve restarted MySQL with –skip-grant-tables -u mysql

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

x