How to Reset MariaDB root password in Ubuntu 18.04

This tutorial explains how to reset MariaDB root password in Ubuntu server 18.04 bionic beaver.

MariaDB server on Ubuntu does not need a password for connecting as a root user because the server-side plugin named unix_socket is used to authenticate clients which are connected from the local host using the Unix socket file.

If you log in to the system root account, then you can log in to the MariaDB root account without a password:

sudo -i
mysql

Or

sudo mysql

This also means other system users cannot log in to the mysql root account:

mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Setting a Password for the MariaDB Root User

If you want to implement password authentication instead of unix_socket authentication, this what you have to do.

Login to the mysql root console from the system root account:

sudo mysql

Run the Update command to set a new password:

UPDATE mysql.user set password=password('newpassword'), plugin='mysql_native_password' where user='root';

(Here 'newpassword' is the new root password)

Flush Privileges and exit from the mysql console:

FLUSH PRIVILEGES;
quit;

Now that we have set a password for the MariaDB root account, we can now log in to the server using the new password.

mysql -u root -p

If you Forgot root Password

If the root password was assigned earlier has been forgotten, then it can be reset with the following steps.

Stop the MariaDB Server, if the server is already running:

sudo systemctl stop mariadb.service

Start MariaDB using the mysqld command with --skip-grant-tables:

sudo mysqld --skip-grant-tables &

At this point we can log in to the MariaDB root without a password, to log in simply run:

mysql

Then we can change the root password:

UPDATE mysql.user set password=password('newpassword'), plugin='mysql_native_password' where user='root';

After that, exit from the mysql console and kill the mysqld process:

sudo pkill mysqld

Restart the MariaDB Server using the systemctl command:

sudo systemctl start mariadb

Now we have reset MariaDB root password, try to log in using the new password:

mysql -u root -p

In the next tutorial we will learn how to enable remote access to the MySQL/MariaDB Server on Ubuntu 18.04.