How to reset the root password after upgrading MariaDB

If you've upgraded to MariaDB version 10.4, you may be surprised to learn that it no longer uses the root user the same way it used before. For most situations it should not be a problem, because most software packages should not use the root user anyway. Except that you may have other database management software that uses the root user to manage the MySQL databases, and they would no longer work. For example, if you use Webmin to manage your server that worked well with the old version of MySQL, you may find that it cannot login to MySQL server after the upgrade:

Webmin needs to know your MySQL administration login and password in order to manage your database. Please enter your administration username (usually root) and password below.

The problem is, the latest version of MariaDB changed the handling of the root user quite significantly. There is a way to make it work to keep other software like Webmin happy. We just need to reset the password for the root user. You may find quite a few online tutorials that suggest using the command mysqld_safe --skip-grant-tables to reset the root password, but when you try it, you may be surprised to learn that it no longer works with MariaDB 10.4 (again, because they had changed the handling of the root user.)

First things first though: what is described below worked for us, but it MAY NOT WORK for your specific server configuration. THE FOLLOWING INFORMATION IS PROVIDED "AS-IS" WITHOUT A WARRANTY OF ANY KIND. BY CHOOSING TO USE THE FOLLOWING INFORMATION, YOU ASSUME THE ENTIRE RISK OF SELECTION, APPLICATION, AND USE OF THE INFORMATION. INDEPENDENT OF THE FORGOING PROVISIONS, IN NO EVENT AND UNDER NO LEGAL THEORY, INCLUDING WITHOUT LIMITATION, TORT, CONTRACT, OR STRICT PRODUCTS LIABILITY, SHALL WINABILITY SOFTWARE CORPORATION OR ANY OF ITS SUPPLIERS BE LIABLE TO YOU OR ANY OTHER PERSON OR ENTITY FOR ANY INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES OF ANY KIND, INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF GOODWILL, WORK STOPPAGE, COMPUTER MALFUNCTION, OR ANY OTHER KIND OF COMMERCIAL DAMAGE, EVEN IF WINABILITY SOFTWARE CORPORATION HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. THIS LIMITATION SHALL NOT APPLY TO LIABILITY FOR DEATH OR PERSONAL INJURY TO THE EXTENT PROHIBITED BY APPLICABLE LAW. IN NO EVENT SHALL WINABILITY SOFTWARE CORPORATION'S LIABILITY FOR ACTUAL DAMAGES FOR ANY CAUSE WHATSOEVER, AND REGARDLESS OF THE FORM OF ACTION, EXCEED THE AMOUNT OF THE PURCHASE PRICE PAID FOR THE SOFTWARE LICENSE OR ONE UNITED STATES DOLLAR, WHICHEVER IS THE GREATER.

IF YOU DO NOT AGREE TO FULLY ACCSEPT THE RISK OF USING THIS INFORMATION, STOP HERE. If you do agree, let's continue.

To reset the root user password for MariaDB 10.4, try sudo mysql instead:

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.4.12-MariaDB MariaDB Server

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

Now enter the following command at the MariaDB command prompt (replace secret with the actual password of the root user that you want to set):

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("secret");

Now try Webmin to log in to the MySQL server again, using the same root password. Happy MySQL-ing!

Read more