8

It looks like I can not login to MySQL (actually MariaDB) as root user anymore in Ubuntu 15.04 (I upgraded from 14.04 via 14.10)

I already tried to reset the password.

What is working, is sudo mysql - but I want to login as root from an other user using mysql -uroot -p. When I create a new user with full rights and password, it is working.

Was there something changed?

Alex
  • 1,062
  • ehm ... "but I want to login as root" To me(!) there never has been a reason for this. You can set the system up for any other user than root and do the same from that user if you want to ;-) – Rinzwind Sep 09 '15 at 08:28
  • @Rinzwind Plenty of reason to log in as root in a database. It's the only way to manage users, databases, etc. There's no internal sudo mechanism. – Oli Sep 09 '15 at 08:35
  • @oli we use another user for that. ie. access with "root" is only possible when using "sudo su" to reset mysql but that one does not require any authentication. – Rinzwind Sep 09 '15 at 08:44

1 Answers1

6

So you've gone from version 5.5.44-1ubuntu0.14.04.1 to 10.0.20-0ubuntu0.15.04.1. Sounds scarier than it is, that's just what they called 5.6 for some reason.

It seems that newer versions of MariaDB have added a plugin to the user table to force authentication through a fixed path. In this case, the root database user is forced through the unix_socket plugin. This also seems to be known as auth_socket in some circles.

Anyway this plugin restricts things so only the system root user can log in as the database root, with no password. It's a security choice they've made.

You can revert this by blanking the plugin field for the root user:

shell$ sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;

A specified password should work after this. I'm not sure how advisable this is though.

Oli
  • 299,936
  • After this, the login via sudo mysql -u root is not possible anymore. That means, the /etc/mysql/debian.conf should also be adapted. – Alex Sep 10 '15 at 09:37
  • 4
    Did not work for me. After this I could not even login with 'sudo mysql -u root' anymore. – MrSmith42 May 23 '16 at 14:45