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)]>