MySQL rejects connections with auth_socket

Question

I recently replaced an installation of MariaDB on a CentOS 7 server with MySQL Community Edition 5.7. The previous installation was configured to allow socket peer authentication for the root user, and I attempted to replicate that same setup with the new installation.

After reinstalling MySQL and re-initializing the database directory (/var/lib/mysql), I enabled the auth_socket plugin in /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

plugin-load-add=auth_socket.so

I then connected to MySQL using the auto-generated temporary root password and set the root user to use auth_socket:

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
FLUSH PRIVILEGES;

After making this change, when I attempt to connect to the database with sudo mysql -u root, I get an "access denied" error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I'm also unable to log in with the temporary password that worked before I updated the user.

Is there some subtle configuration step that I could be missing?

Answer 1

Assuming that you restarted the server after the changes to the cnf-File you could try to add the second line in the .cnf so that the server fails if the plugin does not initialize successfully:

plugin-load-add=auth_socket.so
auth_socket=FORCE_PLUS_PERMANENT

This way you may be able to narrow the problem down. Or you could try to register the plugin at runtime from within mysql:

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

Answer 2

I cannot comment above answer, so here my "own" answer:

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

doesn't work, at least not with MariaDB (5.5) on CentOS 7. For Mariadb, it should read:

INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; 

(see documentation here https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

Afterwards, do a

update user set plugin = 'unix_socket' where user='root'; 
FLUSH PRIVILEGES;

restart mariadb, and login can be without password for user root (e.g. for crontab backups)