Adding node to existing cluster fails

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

I have running 3 node cluster with these specs (since a month or two):
Ubuntu16.04
percona-xtradb-cluster-57 5.7.17-29.20-3.xenia
apparmor purged

Now I try to add another node to the cluster.
On the new node I see this in “/var/lib/mysql/my-percona04.err”:


...
2017-05-16T17:31:02.219089Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 404795)
2017-05-16T17:31:02.219161Z 1 [Note] WSREP: Requesting state transfer: success, donor: 0
2017-05-16T17:31:02.219195Z 1 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(5fce7184-fbad-13e6-b2d7-befdd60d42f9:404793)
2017-05-16T17:31:03.351729Z 0 [Note] WSREP: (6d9d5572, 'tcp://0.0.0.0:4567') connection to peer 6d9d5572 with addr tcp://10.13.21.43:4567 timed out, no messages seen in PT3S
2017-05-16T17:31:03.851903Z 0 [Note] WSREP: (6d9d5572, 'tcp://0.0.0.0:4567') turning message relay requesting off
20170516 19:31:03.874 WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /var/lib/mysql//.sst from previous state transfer. Removing
20170516 19:31:03.893 WSREP_SST: [INFO] Proceeding with SST.........
20170516 19:31:03.955 WSREP_SST: [INFO] ............Waiting for SST streaming to complete!
20170516 19:31:14.020 WSREP_SST: [ERROR] ******************* FATAL ERROR **********************
20170516 19:31:14.031 WSREP_SST: [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log
20170516 19:31:14.040 WSREP_SST: [ERROR] ******************************************************
20170516 19:31:14.050 WSREP_SST: [ERROR] Cleanup after exit with status:2
2017-05-16T17:31:14.067244Z 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.13.21.43' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '11329' '' : 2 (No such file or directory)
2017-05-16T17:31:14.067277Z 0 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2017-05-16T17:31:14.067289Z 0 [ERROR] WSREP: SST script aborted with error 2 (No such file or directory)
2017-05-16T17:31:14.067324Z 0 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2017-05-16T17:31:14.067340Z 0 [ERROR] Aborting
...

On “my-percona01” I see this in “/var/lib/mysql/my-percona01.err”:


...
20170516 19:31:13.622 WSREP_SST: [INFO] Streaming the backup to joiner at 10.13.21.43 4444
2017-05-16T17:31:13.981318Z 1562 [Note] Aborted connection 1562 to db: 'unconnected' user: 'sstuser' host: 'localhost' (Got an error reading communication packets)
20170516 19:31:13.990 WSREP_SST: [ERROR] ******************* FATAL ERROR **********************
20170516 19:31:14.000 WSREP_SST: [ERROR] xtrabackup finished with error: 1. [B]Check /var/lib/mysql//innobackup.backup.log[/B]
--------------- innobackup.backup.log (START) --------------------
170516 19:31:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' as 'sstuser' (using password: YES).
170516 19:31:13 version_check Connected to MySQL server
170516 19:31:13 version_check Executing a version check against the server...
170516 19:31:13 version_check Done.
170516 19:31:13 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock
...

And on “my-percona01” in “/var/lib/mysql/innobackup.backup.log” I see this (please ignore the timestamps, I have tried this a couple of times):


170516 19:31:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' as 'sstuser' (using password: YES).
170516 19:31:13 version_check Connected to MySQL server
170516 19:31:13 version_check Executing a version check against the server...
170516 19:31:13 version_check Done.
170516 19:31:13 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.17-13-57
xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
xtrabackup: uses posix_fadvise().
[B]xtrabackup: cd to /var/lib/mysql/[/B]
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
[B]xtrabackup: innodb_data_home_dir = .[/B]
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
[B]xtrabackup: innodb_log_group_home_dir = ./[/B]
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 134217728
InnoDB: Number of pools: 1
170516 19:31:13 >> log scanned up to (3544259996)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 8 for mysql/time_zone_name, old maximum was 0
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to the directory.
xtrabackup: error: xb_load_tablespaces() failed witherror code 11

If I see this right “my-percona01” tries to create an xtrabackup for syncng it to “my-percona04”.
But there seems to an access right issue on “my-percona01” in “/var/lib/mysql/” (xtrabackup: cd to /var/lib/mysql/).

On “my-percona01”


ls -alhs /var/lib/mysql/

4,0K drwxr-x--- 13 mysql mysql 4,0K Mai 18 09:46 .
4,0K drwxr-xr-x 38 root root 4,0K Feb 24 11:56 ..
12K drwxr-x--- 2 mysql mysql 12K Apr 27 09:56 mydb01
4,0K -rw-r----- 1 mysql mysql 56 Feb 24 11:56 auto.cnf
4,0K -rw------- 1 mysql mysql 1,7K Feb 24 11:56 ca-key.pem
4,0K -rw-r--r-- 1 mysql mysql 1,1K Feb 24 11:56 ca.pem
4,0K -rw-r--r-- 1 mysql mysql 1,1K Feb 24 11:56 client-cert.pem
4,0K -rw------- 1 mysql mysql 1,7K Feb 24 11:56 client-key.pem
4,0K drwxr-x--- 2 mysql mysql 4,0K Mai 16 18:10 mydb02
129M -rw------- 1 mysql mysql 129M Mai 18 09:46 galera.cache
4,0K -rw-r----- 1 mysql mysql 270 Mär 4 16:51 GRA_2_1248.log
4,0K -rw-r----- 1 mysql mysql 264 Mär 4 16:51 GRA_2_1250.log
4,0K -rw-r----- 1 mysql mysql 117 Mär 1 12:06 GRA_2_16.log
4,0K -rw-r----- 1 mysql mysql 122 Mär 1 12:06 GRA_2_18.log
4,0K -rw-r----- 1 mysql mysql 117 Mär 1 12:06 GRA_2_20.log
4,0K -rw-r----- 1 mysql mysql 122 Mär 1 12:06 GRA_2_22.log
4,0K -rw-r----- 1 mysql mysql 96 Mär 1 13:36 GRA_2_30.log
4,0K -rw-r----- 1 mysql mysql 98 Mär 1 13:36 GRA_2_31.log
4,0K -rw-r----- 1 mysql mysql 96 Mär 1 13:36 GRA_2_32.log
4,0K -rw-r----- 1 mysql mysql 127 Mär 1 14:29 GRA_2_37.log
4,0K -rw-r----- 1 mysql mysql 274 Mär 18 11:58 GRA_2_3818.log
4,0K -rw-r----- 1 mysql mysql 268 Mär 18 11:58 GRA_2_3820.log
4,0K -rw-r----- 1 mysql mysql 182 Mär 18 12:02 GRA_2_3867.log
4,0K -rw-r----- 1 mysql mysql 170 Mär 18 12:02 GRA_2_3869.log
4,0K -rw-r----- 1 mysql mysql 160 Mär 18 12:03 GRA_2_3898.log
4,0K -rw-r----- 1 mysql mysql 126 Mär 1 14:29 GRA_2_39.log
4,0K -rw-r----- 1 mysql mysql 127 Mär 1 14:29 GRA_2_41.log
4,0K -rw-r----- 1 mysql mysql 126 Mär 1 14:29 GRA_2_43.log
4,0K -rw-r----- 1 mysql mysql 274 Mär 18 12:11 GRA_2_4810.log
4,0K -rw-r----- 1 mysql mysql 268 Mär 18 12:11 GRA_2_4812.log
4,0K -rw-r----- 1 mysql mysql 84 Mai 2 16:23 GRA_2_8896.log
4,0K -rw-r----- 1 mysql mysql 117 Mai 17 13:13 grastate.dat
4,0K -rw-r----- 1 mysql mysql 265 Mai 17 07:36 gvwstate.dat
16K -rw-r----- 1 mysql mysql 13K Mai 16 19:01 ib_buffer_pool
76M -rw-r----- 1 mysql mysql 76M Mai 18 09:46 ibdata1
128M -rw-r----- 1 mysql mysql 128M Mai 18 09:46 ib_logfile0
128M -rw-r----- 1 mysql mysql 128M Mai 18 09:46 ib_logfile1
525M -rw-r----- 1 mysql mysql 524M Mai 18 09:46 ibtmp1
4,0K -rw-r----- 1 mysql mysql 1,5K Mai 16 19:31 innobackup.backup.log
24K drwxr-x--- 2 mysql mysql 24K Mai 5 11:16 mydb03
12K drwxr-x--- 2 mysql mysql 12K Apr 26 13:21 mydb04
16K drwx------ 2 root root 16K Feb 24 09:19 lost+found
4,0K drwxr-x--- 2 mysql mysql 4,0K Feb 24 11:56 mysql
4,0K -rw-rw---- 1 root root 6 Mai 16 19:02 mysqld_safe.pid
140K -rw-r----- 1 mysql root 135K Mär 12 09:59 mysql-error.log
485M -rw-r----- 1 mysql mysql 485M Mär 12 09:59 mysql-slow.log
8,6M -rw-r----- 1 mysql root 8,6M Mai 18 09:46 my-percona01.err
4,0K -rw-r----- 1 mysql mysql 6 Mai 16 19:02 my-percona01.pid
196M -rw-r----- 1 mysql mysql 195M Mai 18 09:46 my-percona01-slow.log
102M -rw-r--r-- 1 mysql root 102M Mär 27 11:01 my-percona01-slow.log.1
1,1G -rw-r----- 1 mysql mysql 1,1G Mai 17 10:15 my-percona01-slow.log-1495008960
12K drwxr-x--- 2 mysql mysql 12K Mai 16 16:16 mydb06
4,0K drwxr-x--- 2 mysql mysql 4,0K Feb 24 11:56 performance_schema
4,0K -rw------- 1 mysql mysql 1,7K Feb 24 11:56 private_key.pem
4,0K -rw-r--r-- 1 mysql mysql 452 Feb 24 11:56 public_key.pem
4,0K -rw-r--r-- 1 mysql mysql 1,1K Feb 24 11:56 server-cert.pem
4,0K -rw------- 1 mysql mysql 1,7K Feb 24 11:56 server-key.pem
12K drwxr-x--- 2 mysql mysql 12K Feb 24 11:57 sys
4,0K drwxr-x--- 2 mysql mysql 4,0K Mär 11 18:28 mydb07
12K drwxr-x--- 2 mysql mysql 12K Mai 17 13:13 mydb08
30M -rw-r----- 1 mysql mysql 30M Mai 18 09:46 xb_doublewrite
4,0K -rw-r----- 1 mysql mysql 44 Mai 16 19:31 xtrabackup_galera_info

From my point of view the “mysql” user should have access rights.
Has someone an idea why this fails?

yogg

2

Nobody has an idea?

3

share your my.cnf file please on the DONOR node.

InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to the directory. xtrabackup: error: xb_load_tablespaces() failed witherror code 11

4

Hi

The my.cnf from my-percona01:


!includedir /etc/mysql/conf.d/

[mysqld]

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://10.13.21.43,10.13.21.44,10.13.21.45,10.13.21.46

wsrep_node_name=my-percona01
wsrep_node_address=10.13.21.43

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:MySuperSecretPassword

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

ignore-db-dir=lost+found


# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 3000M

In “/etc/mysql/conf.d/” are 2 files (mysql.cnf, mysqldump.cnf)

mysql.cnf:


[mysql]

mysqldump.cnf:


[mysqldump]
quick
quote-names
max_allowed_packet = 16M

I understand the error message.
But I don’t understand why this happens.
As you can see above mysql should have the rights to write in this directory.

5

Is there any way how to debug/fix this?

I can try a “chmod -R 777 /var/lib/mysql/” but in general this is normaly a bad idea.

6

I have now fixed it with “find /var/lib/mysql -type d -exec chmod o+rx {} ;” which gave read and execute rigts to every folder in “/var/lib/mysql”.
Not the best solution but it works without destroying security completely.