How to reset MariaDB's root user password

Posted on: Sunday, Dec 17, 2017

It's really annoying to forget what your MariaDB's root account password is. However, with just a little commandline effort, it's an easy challenge to resolve. I learned most of these instructions with this Digial Ocean article. It's a great article but it instructions for ending the MariaDB mysqld process didn't work for me on Ubuntu 17.04.

Stop MariaDB
$ sudo systemctl stop mariadb
Restart the database without permission checking

This command doesn't seem to finish. After about 30 seconds or press enter to get back to a command line and go to the next step.

$ sudo mysqld_safe --skip-grant-tables --skip-networking &
Connect to database with root
$ mysql -u root
Reload grant tables with FLUSH PRIVILEGES
Reset root password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Exit MySQL with

Find and kill the mysqld process

The command below lists the mysqld processes.

ps aux | grep mysqld

On my system, the mysqld process to kill is the one associated with user mysql and /usr/sbin/mysqld as shown below.

mysql  4267  0.0  0.5 1775204 68340 pts/0   SNl  11:43   0:01 
    /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql 
    --plugin-dir=/usr/lib/mysql/plugin --user=mysql 
    --skip-grant-tables --skip-networking --skip-log-error 
    --socket=/var/run/mysqld/mysqld.sock --port=3306

Kill the /usr/sbin/mysqld process with

sudo kill -SIGTERM <pid-of-mysqld-process>
Restart MariaDB
sudo systemctl start mariadb

You should now be able to log back into MariaDb with:

    mysql -u root -p

You'll be prompted for the password; enter the one you just assigned to the root account.

Add your comment
You email is never shared with anyone else.

© Copyright 2017 by Roger Pence. All rights reserved.