MariaDB Access denied for user ‘root’@’localhost’

Recently i posted on Installing MariaDB on AWS EC2 instance and securing the installation.

After securing the installation, the root user login is failing with error

Access denied for user ‘root’@’localhost’ (using password: YES)

The problem is the root user plugin will be set to unix_socket for authentication rest of the users will have mysql_native_password

In MariaDB 10.4.3 and later, the unix_socket authentication plugin is installed by default, and it is used by the ‘root’@’localhost’ user account by default.

This plugin used the UID of the installing user, so the other users of the system cannot login

Which means if you install the MariaDB-server from ubuntu user of EC2 instance using root command, you can log in only through running command with Sudo

Workaround

There are quick workaround / solution available for this

Uninstall the plugin

You can uninstall the plugin dynamically by executing UNINSTALL SONAME or UNINSTALL PLUGIN. For example:

UNINSTALL SONAME 'auth_socket';

Set the password authentication for the user

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('asvignesh');

After that you can login to root user from any account

ubuntu@ip-172-31-38-177:~$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

Originally published at asvignesh.