10

I have installed MariaDB on Ubuntu LTS 16.04. Then I have run

/usr/bin/mysql_secure_installation

and set a root password. Accessing the DB via mysql -u root -p works fine. But checking the status with service mysql status opens a log file with this warning:

[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.

The questions are:

  1. Is this a worry or completely normal?
  2. If this is a worry, how can I fix it?
FlorianL
  • 267
  • 2
  • 10

1 Answers1

22

It is normal, if by saying "accessing the DB via mysql -u root -p works fine" you mean that you are running it while being a system root (or under sudo). You should not be able to do it as an ordinary user.

Packages generated by Ubuntu by default have unix_socket authentication for the local root. To check, run 

SELECT user, host, plugin FROM mysql.user;

You should see unix_socket in the plugin column for root@localhost.

If you want to use the password authentication instead, run

UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';
FLUSH PRIVILEGES;
elenst
  • 3,839
  • 1
  • 15
  • 22
  • Thanks a lot for your detailed reply (an sorry for not being able to upvote your answer)! That got rid of the warning. However, I now see `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` in the status log after MariaDB is restarted after a server reboot. Plus, restarting MariaDB via `service mysql restart`does not work anymore?!? – FlorianL Apr 16 '17 at 20:04
  • Ubuntu must have changed other scripts accordingly. The old way to start and monitor the service was having a special `debian-sys-maint` user with its own `debian.conf` which contained a generated password. To my understanding, one of reasons to start using `unix_socket` was to get rid of this rather awkward legacy. If you want to keep using the scripts, my usual advice is to keep `root@localhost` as Ubuntu wants it (with `unix_socket`), and additionally create another super user which you would set up with a password authentication and use for your purposes. – elenst Apr 16 '17 at 20:10