rogerpence.com

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

mysql> FLUSH PRIVILEGES;    

Reset root password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Exit MySQL with

exit;

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 
    --pid-file=/var/run/mysqld/mysqld.pid 
    --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.