· Databases Linux password resets

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.

Stop MariaDB

Firstly, stop the database. This will cause any website or replication to stop for now but this is essential.

systemctl stop mariadb.service

Start MariaDB

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;

Shutdown MariaDB

Now we’ll need to stop the database, not using systemd but with the mysqladmin command.

mysqladmin shutdown

then start the database as normal

systemctl start mariadb.service

And that is it! Password all set and MariaDB running normally.

  • LinkedIn
  • Tumblr
  • Reddit

Aaron Mehar

Berkshire, UK