Forgetting passwords happens to the best of us. If you forget or lose the root password to your MySQL or MariaDB database, you can still gain access and reset the password if you have access to the server and a user account with sudo privileges.
_Note: On fresh Ubuntu 18.04 installations, the default MySQL or MariaDB configuration usually allows you to access the database (with full administrative privileges) without providing a password as long as you make the connection from the system's root account. In this scenario it may not be necessary to reset the password. _
Step 1 — Identifying the Database Version and Stopping the Server
Check version
mysql --version
Stop service
sudo systemctl stop mariadb
Step 2 — Restarting the Database Server Without Permission Checks
sudo mysqld_safe --skip-grant-tables --skip-networking &
Connect to the database as the root user:
sudo mysql -u root
Step 3 — Changing the Root Password
The database server is now running in a limited mode; the grant tables are not loaded and there's no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you've gained access to the server.
Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.
FLUSH PRIVILEGES;
Changing the MariaDB Password
UPDATE mysql.user SET password = PASSWORD('new_password') WHERE user = 'root';
MariaDB allows using custom authentication mechanisms, so execute the following two statements to make sure MariaDB will use its default authentication mechanism for the new password you assigned to the root account:
UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
UPDATE mysql.user SET plugin = '' WHERE user = 'root';
Step 4 - Restart database
Call background jobs at step 3 to foreground by command fg %1 and kill it
jobs
[1]+ Running sudo mysqld_safe --skip-grant-tables --skip-networking &
fg %1
Restart service
sudo systemctl restart mariadb
