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
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:
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
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.