Bottom line: It may not be necessary to reset the root password for your mariadb.

With a recent compromise of my wordpress server on a Raspberry Pi, I thought it best to change database user passwords as a precaution. Resetting my own password was simple:

$ mariadb -u myself -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6111
Server version: 10.1.44-MariaDB-0+deb9u1 Raspbian 9.11

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

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

MariaDB [(none)]> set password = password('MyNewPassword');
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye
$

But how about the root password? When I tried something similar,

MariaDB [(none)]> set password for 'root'@'localhost' = password('NewRootPassword');
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------+
| Note | 1699 | SET PASSWORD has no significance for users authenticating via plugins |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>

The above warning is telling me that the user root does not use the password table to log in. It uses a unix (linux) socket, that is, the same authorization that allows the root user to validate. But on my default Raspian Linux installation, I use sudo and never log in as root. I don’t know the root password, or even if there is one. It is therefore unnecessary for me to set the root password on mariadb. But then, how will I log in with all privileges if I need to do something on mariadb? No problem, just use sudo:

$ sudo mariadb -u root
[sudo] password for myself:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6132
Server version: 10.1.44-MariaDB-0+deb9u1 Raspbian 9.11
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
$

If your mariadb or mysql installation is different, the above may not apply to you. The only reason I wrote this article was that things had changed from my recollections of how mysql worked years ago.