Enable password and unix_socket authentication for MariaDB root user?

mysql authentication passwords mariadb unix-socket
powerpete picture powerpete · Jan 25, 2017 · Viewed 23.9k times · Source

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

Answer

elenst picture elenst · Jan 25, 2017

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

CREATE USER admin@localhost IDENTIFIED BY 'password';
GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
-- etc

Related questions

MySQL root access from all hosts

I've installed MySQL server on a remote Ubuntu machine. The root user is defined in the mysql.user table this way: mysql> SELECT host, user, password FROM user WHERE user = 'root'; +------------------+------+-------------------------------------------+ | host | user | password | +------------------+------+…

Read More
Cannot connect to MySQL 4.1+ using old authentication

I'm trying to connect to a mySQL database at http://bluesql.net, but when I try to connect, it gives this error: Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication I've looked into this, and it has to …

Read More
MySQL Check if username and password matches in Database

I have a form which has a textbox with the name attribute username and another one with the name attribute password. I also have a database with columns called user and pass. When my users signed up it added the …

Read More