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
.~
Update 2020: although my solution above works it replaces the default root unix_socket
authentication with a password. I've noticed this breaks tasks such as mariadb upgrade / your own maintenance scripts that would expect to be able to connect to the DB without extra passwords when running as root.
My suggestion is to add a new root login as follows:
CREATE USER `root`@`%` IDENTIFIED WITH mysql_native_password using PASSWORD('your-password-here');
GRANT ALL PRIVILEGES ON *.* TO `root`@`%` WITH GRANT OPTION;
FLUSH PRIVILEGES;
This will effectively still allow for the default behavior of root login and add external access to the DB using the password.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With