[SOLVED] Enable password and unix_socket authentication for MariaDB root user?

Issue

I’ve a root User on the MariaDB on Ubuntu 16.04.

As default the root user is authenticated by the unix_socket authentication plugin.

I can switch the authentication method to password method by setting

update mysql.user set plugin='' where user='root';

This works fine. But …

Is there a possibility to authenticate the root user by unix_socket (by root shell) or by password (when it is connected by localhost:3306)?

Solution

A reliable and straightforward way would be to create another super-user and use it when you want to connect by password.

CREATE USER [email protected] IDENTIFIED BY 'password';
GRANT ALL ON *.* TO [email protected] WITH GRANT OPTION;
-- etc

Answered By – elenst

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.