MariaDB - cannot log in as root

Question

I am trying to configure MariaDB (10.0.29) on Ubuntu (04.16.02). After I installed it and started the process ( sudo service mysql start), I can not log in as root, although I initially set the password to empty.

Those. mysql -u rootwill deprive me of access. I logged in through sudo mysqland checked the user table, i.e. select user, password, authentication_string from mysql.userand as expected:

+---------+----------+-----------------------+ 
| User    | password | authentication_string |
+---------+----------+-----------------------+
| root    |          |                       |
+---------+----------+-----------------------+

I also created a new user, i.e. create user 'test'@'localhost' identified by '';, and when I try to do mysql -u test(blank password), it works as expected and logs me in.

The user table looks like this:

+---------+----------+-----------------------+
| User    | password | authentication_string |
+---------+----------+-----------------------+
| root    |          |                       |
| test    |          |                       |
+---------+----------+-----------------------+

So, can someone tell me why I cannot log in as rootwith an empty password, but can log in as test?

Answer 1

MariaDB ( MariaDB), , Ubuntu , unix_socket . ,

SELECT user, host, plugin FROM mysql.user;

unix_socket plugin, .

,

UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';
FLUSH PRIVILEGES;

( WHERE, , )

Answer 2

. Ubuntu MariaDB (10.0.31). - mysql.

MariaDB (10.2.12) ​​: https://downloads.mariadb.org/mariadb/repositories/

.

Answer 3

MariaDB 10.4:

. (...) root . : CREATE USER root @localhost IDENTIFIED VIA unix_socket mysql_native_password ""

If you really want to access your database with rightsroot , log in using cli mariadb -pand run:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("your-password-here");

Source: https://mariadb.com/kb/en/library/authentication-from-mariadb-104/#altering-the-user-account-to-revert-to-the-previous-authentication-method

About another decision below : they will not work, because the MariaDB also allows you to update the column plugin: ERROR 1348 (HY000): Column 'plugin' is not updatable.