Mariadb – Galera cluster node fails with error. Brand new cluster + master / node. Access denied or can’t connect

galeramariadb

I am having issue.

Previous cluster fatally crashed, so I've created a new one with pretty much same settings (changed cluster name only).

Here is a galera.cnf for a node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="cluster"
wsrep_cluster_address="gcomm://y.y.y.139, x.x.x.25, 10.5.2.1"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="x.x.x.25"
wsrep_node_name="xxx002"

Galera.cnf for master differs only in wsrep_node_address (y.y.y.139) and wsrep_node_name (yyy001).

I have add permissions for debian-sys-maint but as soon I connect to master node, I will get:

Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: 1.0 (xxx002): State transfer from 0.0 (yyy001) complete.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Member 1.0 (xxx002) synced with group.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: Synchronized with group, ready for connections
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 11 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 12 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 13 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Of course I won't be able to login to mysql as I will get:

ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

I have no idea what is happening. It worked fine (I had to reinstall all servers, but I am using same template).

I cannot create cluster for 4 days now 🙁

I have added those as soon I've installed MariaDB on the node:

GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost IDENTIFIED BY  'your password' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'PASS';
CREATE USER 'admin'@'10.%' IDENTIFIED BY 'PASSS';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

The master data was restored from .sql (backup from a previous cluster).

Not sure what it could cause it ?

Best Answer

It doesn't sound like the problem is related to Galera. Maybe the debian-sys-maint@localhost user had already been created with the unix_socket as the defined authentication plugin before you did the GRANT for it? And the error message is pretty clear on what is the issue with that: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded.

So, you can either recreate the debian-sys-maint@localhost user with the default authentication plugin - mysql_native_password (option 1), or you can install the unix_socket plugin (option 2):

Option 1

DROP USER debian-sys-maint@localhost; 
CREATE USER debian-sys-maint@localhost IDENTIFIED BY 'your password'; `
GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost WITH GRANT OPTION;

Option 2

INSTALL PLUGIN unix_socket SONAME 'auth_socket';

The advantage of option 2 is that you don't need a password for the user, assuming debian-sys-maint is the name of your OS user. MariaDB will use that OS username as the MariaDB username.

Related Question