
After installing MariaDB locally you’ve also used to connect to the
unprotected root account using mysql -uroot. This will not work either, simply
use mysql without specifying a username.

If you've forgotten your root password, no problem — you can still connect
using sudo and change the password. And if you've also removed unix_socket
authentication, to restore access do as follows:

* restart MariaDB with --skip-grant-tables
* login into the unprotected server
* run FLUSH PRIVILEGES (note, before 10.4 this would’ve been the last step,
not anymore). This disables --skip-grant-tables and allows you to change the
stored authentication method
* run SET PASSWORD FOR root@localhost to change the root password.

To view inside privilege tables, the old mysql.user table still exists. You
can select from it as before, although you cannot update it anymore. It
doesn’t show alternative authentication plugins and this was one of the
reasons for switching to the mysql.global_priv table — complex authentication
rules did not fit into rigid structure of a relational table. You can select
from the new table, for example:

select concat(user, '@', host, ' => ', json_detailed(priv)) from
mysql.global_priv;

Reverting to the Previous Authentication Method for root@localhost
------------------------------------------------------------------

If you don't want the root@localhost user account created by mysql_install_db
to use unix_socket authentication by default, then there are a few ways to
revert to the previous mysql_native_password authentication method for this
user account.

Configuring mysql_install_db to Revert to the Previous Authentication Method
----------------------------------------------------------------------------

One way to revert to the previous mysql_native_password authentication method
for the root@localhost user account is to execute mysql_install_db with a
special option. If mysql_install_db is executed while
--auth-root-authentication-method=normal is specified, then it will create the
default user accounts using the default behavior of MariaDB 10.3 and before.

This means that the root@localhost user account will use mysql_native_password
authentication by default. There are some other differences as well. See
mysql_install_db: User Accounts Created by Default for more information.

For example, the option can be set on the command-line while running
mysql_install_db:

mysql_install_db --user=mysql --datadir=/var/lib/mysql
--auth-root-authentication-method=normal

The option can also be set in an option file in an option group supported by
mysql_install_db. For example:

[mysql_install_db]
auth_root_authentication_method=normal

If the option is set in an option file and if mysql_install_db is executed,
then mysql_install_db will read this option from the option file, and it will
automatically set this option.

Altering the User Account to Revert to the Previous Authentication Method
-------------------------------------------------------------------------

If you have already installed MariaDB, and if the root@localhost user account
is already using unix_socket authentication, then you can revert to the old
mysql_native_password authentication method for the user account by executing
the following:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING
PASSWORD("verysecret")

URL: https://mariadb.com/kb/en/authentication-from-mariadb-104/