How to reset root MariaDB password on Ubuntu 18.04 Bionic Beaver Linux

Objective

The objective is to reset lost root MariaDB password on Ubuntu 18.04 Bionic Beaver Linux.

Please note that if you have just installed your MariaDB server and are unable to login as root user with:

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

you do not need to reset your password. Instead, to login as root amend the above command to:

$ sudo mysql

Operating System and Software Versions

  • Operating System: – Ubuntu 18.04 Bionic Beaver
  • Software: – mysql Ver 15.1 Distrib 10.1.25-MariaDB or higher

Requirements

Privileged access to your Ubuntu System as root or via sudo command is required.

Difficulty

EASY

Conventions

  • # – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
  • $ – requires given linux commands to be executed as a regular non-privileged user

Instructions

Let’s start by stopping the currently running MariaDB database:

$ sudo service mariadb stop

Once ready manually start MariaDB server with the following linux command and command line options :

$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 3216


Confirm that the MariaDB process is running as expected:

$ jobs
[1]+  Running        sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

At this stage we are able to access MariaDB database without password:

$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Using the current MariaDB session first flush privileges:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Next, reset root password. The following linux command will reset MySQL root password to linuxconfig.org:

mysql> update mysql.user set password=password('linuxconfig.org') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Quit MariaDB session:

mysql> quit                                                                                                                                                                                    
Bye

Gracefully terminate current mysqld process:

$ sudo pkill mysqld                                                                                                                                                        
linuxconfig@ubuntu:~$ jobs                                                                                                                                                                     
[1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

Lastly, start MariaDB database:

$ sudo service mariadb start

If all went well you should now be able to login to your MariaDB database with a root password:

$ sudo mysql -u root --password=linuxconfig.org
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>