Reset your MariaDB / MySQL root password
This guide was tested on CentOS 7, which is using systemd and Mariadb. For those using sysvinit or upstart replace the stop/start commands with
service mariadb start and
service mariadb stop, where MySQL is installed, replace mariadb with mysql or mysqld e.g.
service mysql stop
I needed to reset the root password for MariaDB the other day and I couldn’t find any articles with easy copy and paste commands with clear explanation. When you lose the root password, you cant just recover it, so a trick is needed. This will cause downtime.
Firstly, stop the database. This will cause any website or replication to stop for now but this is essential.
systemctl stop mariadb.service
Now we start MariaDB but without the grants table, this is the table used stored user and permission data, so not loading it means no passwords and no restrictions. At the same time, we’ll ensure MariaDB has no networking because we are not loading the grants table.
mysqld_safe --skip-grant-tables --skip-networking &
Log into MariaDB
Now simply log into the the database with no password.
mysql -u root
Set a new password
The query here, is overwriting the password with a new one, just replace the password below, with a randomly generated one – see here
UPDATE `user` SET `Password` = PASSWORD('0qp1XD5ihBH85wq') WHERE `User` = 'root';
Then run the commands below to flush or reset the privileges table.
FLUSH PRIVILEGES; QUIT;
Now we’ll need to stop the database, not using systemd but with the mysqladmin command.
then start the database as normal
systemctl start mariadb.service
And that is it! Password all set and MariaDB running normally.