I am trying to setup MariaDB (10.0.29) on Ubuntu (16.04.02). After I installed it and started the process (sudo service mysql start), I cannot login as root even though I originally set the password to blank.
Ie mysql -u root will deny me access. I logged in through sudo mysql and checked the user table, ie. select user, password, authentication_string from mysql.user and as expected:
+---------+----------+-----------------------+
| User | password | authentication_string |
+---------+----------+-----------------------+
| root | | |
+---------+----------+-----------------------+
I also created a new user, ie. create user 'test'@'localhost' identified by ''; and when I try to do mysql -u test (empty password), it works as expected and logs me in.
The user table looks like this:
+---------+----------+-----------------------+
| User | password | authentication_string |
+---------+----------+-----------------------+
| root | | |
| test | | |
+---------+----------+-----------------------+
So, can anyone tell me why I cannot login as root with empty password but I can login as test?
Unlike native MariaDB packages (those provided by MariaDB itself), packages generated by Ubuntu by default have unix_socket authentication for the local root. To check, run
SELECT user, host, plugin FROM mysql.user;
If you see unix_socket in the plugin column, that's the reason.
To return to the usual password authentication, run
UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';
FLUSH PRIVILEGES;
(choose the WHERE clause which fits your purposes, the one above is just an example)
The issue you're having is due to changes in the authentication system of MariaDB 10.4:
As a result of the above changes, the open-for-everyone all-powerful root account is finally gone. (...) because the root account is securely created automatically. They are created as: CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'
If you really want to access your DB as root, you should login via cli mariadb -p and 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 the other solution bellow: they won't work because MariaDB won't also allow you to update the plugin column: ERROR 1348 (HY000): Column 'plugin' is not updatable.
I struggled with this for some time. My Ubuntu comes with MariaDB (10.0.31) by default. After reinstalling a few times and changing the plugins to various suggestions - I still could not login properly to mysql.
In the end I installed the latest MariaDB (10.2.12) from the repo :
https://downloads.mariadb.org/mariadb/repositories/
I was able to login properly immediately.
