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.