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)]>
Also published on Medium.
