Percona XtraDB Fails on Replication on Oracle Linux 7

Hello all,

I’ve been banging my head around this. I have a Percona setup on Oracle Linux 7 (OEL 7) with the following packages below.

Percona-XtraDB-Cluster-shared-57-5.7.25-31.35.1.el7.x86_64
Percona-XtraDB-Cluster-shared-compat-57-5.7.25-31.35.1.el7.x86_64
Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64
Percona-XtraDB-Cluster-client-57-5.7.25-31.35.1.el7.x86_64
Percona-XtraDB-Cluster-57-5.7.25-31.35.1.el7.x86_64
percona-xtrabackup-24-2.4.13-1.el7.x86_64

On the first (and only node at this time), I setup as following:

mysql -uroot -p -e “CREATE USER ‘sstuser’@‘localhost’ IDENTIFIED BY ‘REDACTED’;”
mysql -uroot -p -e “GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO sstuser@localhost; FLUSH PRIVILEGES;”
INSTALL PLUGIN keyring_udf SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME ‘keyring_udf.so’; CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME ‘keyring_udf.so’;

CREATE TABLESPACE foo ADD DATAFILE ‘foo.ibd’ ENCRYPTION=‘Y’;
CREATE USER ‘redacted’@‘%’ IDENTIFIED BY ‘REDACTED’;
GRANT ALL PRIVILEGES ON redacted.* TO ‘redacted’@‘%’;
FLUSH PRIVILEGES;
FLUSH HOSTS;

On node 1: To start replication, I ran:

systemctl start mysql@bootstrap.service

/etc/my.cnf:

#!includedir /etc/my.cnf.d/
#!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
require_secure_transport=ON
pxc_encrypt_cluster_traffic=ON
ssl-ca=REDACTED
ssl-cert=REDACTED
ssl-key=REDACTED

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=pxc-prod
wsrep_cluster_address=gcomm://IP1,IP2,IP3

wsrep_node_name=pxc-prod-node001
wsrep_node_address=IP1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:REDACTED

pxc_strict_mode=ENFORCING

On node 2: I ran a simple:

systemctl start mysql

/etc/my.cnf:

!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
require_secure_transport=ON
pxc_encrypt_cluster_traffic=ON

ssl-ca=REDACTED
ssl-cert=REDACTED
ssl-key=REDACTED

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=pxc-stage-prod
wsrep_cluster_address=gcomm://IP1,IP2,IP3

wsrep_node_name=pxc-prod-node002
wsrep_node_address=IP2

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:REDACTED

The process on this node fails:

systemctl start mysql

Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.

If I check the DONOR logs (first node), I see this:

/var/lib/mysql # cat innobackup.backup.log

-0-root@mr36p01ww-stagedb001:/var/lib/mysql # cat innobackup.backup.log
xtrabackup: recognized server arguments: --defaults_group=mysqld --parallel=4 --transition-key=*
xtrabackup: recognized client arguments: --defaults_group=mysqld --parallel=4 --transition-key=* --user=sstuser --password=* --socket=/var/lib/mysql/mysql.sock --lock-ddl=1 --backup=1 --galera-info=1 --binlog-info=ON --stream=xbstream --target-dir=/tmp/pxc_sst_hhfd/donor_xb_B6di
190325 12:30:26 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock’ as ‘sstuser’ (using password: YES).
Failed to connect to MySQL server: DBI connect(‘;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock’,‘sstuser’,…) failed: Access denied for user ‘sstuser’@‘localhost’ (using password: YES) at - line 1314.
190325 12:30:26 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock
Failed to connect to MySQL server: Access denied for user ‘sstuser’@‘localhost’ (using password: YES).

Not sure

After each attempt, I clean the /var/lib/mysq/* dir so it’s a clean start…

I am at my wit’s end. I have previously configured Percona in the lab, and didn’t have many problems. Here I am trying to do it in prod with no ports blocked/firewall turned off (by default):

Node 1:

iptables -L

Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Node2:

iptables -L

Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

If you need any more logs, please let me know.

And HALP!

Thanks,

d4z3

d4z3.

Error
190325 12:30:26 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock
Failed to connect to MySQL server: Access denied for user ‘sstuser’@‘localhost’ (using password: YES).

States that XtraBackup can’t connect to the node with given sstuser and password.

Make sure that in my.cnf use have a user configured that can connect to the instance.

===
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=“root:”

vadimtk,

I do have a sst user created, on the donor and in the configuration. I did have a hash “blahblah#blah” (e.g.) in the sst user password, so I removed that with a different non-comment symbol and it seems to be working now.

Thanks for the input.