I'm trying to setup a galera cluster, with MariaDB 10.2, and percona-xtrabackup-2.3.10-1.el7.x86_64.
If bootstrap the donor by wsrep_sst_method=xtrabackup or xtrabackup-v2, the joiner will be unable to join in the cluster, and the error message complains about "no valid checkpoint".
However, if first bring up the cluster using wsrep_sst_method=rsync then change it to xtrabackup, then stop all nodes and bootstrap the donor again (by galera_new_cluster), the joiner is able to join in OK.
I suspect there was some data synchronized to the second node when using rsync.
- Could you please give me some pointers about why
xtrabackup doesn't work the first time?
- Is it a common practice to bootstrap first time with
rsync?
- Or, anything else.
Any hints will be highly appreciated, and just let me know if you need more information.
Thank you for your help.
More details:
When bootstrap the first time using xtrabackup, the joiner is unable to join in, and its log says:
Jan 23 04:09:14 setsv-dr.local.example.com mysqld[6924]: WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20210123 04:09:14.948)
Jan 23 04:09:14 setsv-dr.local.example.com mysqld[6924]: WSREP_SST: [ERROR] Cleanup after exit with status:2 (20210123 04:09:14.971)
, and the donor logs say:
Jan 23 04:11:31 setsv mysqld: group UUID = a53cd166-5d68-11eb-aa0f-83c1fd168f1f
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325406353152 [Note] WSREP: Flow-control interval: [16, 16]
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325646681856 [Note] WSREP: REPL Protocols: 9 (4, 2)
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325646681856 [Note] WSREP: New cluster view: global state: a53cd166-5d68-11eb-aa0f-83c1fd168f1f:0, view# 35: Primary, number of nodes: 1, my index: 0, protocol version 3
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325646681856 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325646681856 [Note] WSREP: Assign initial position for certification: 0, protocol version: 4
Jan 23 04:11:31 setsv mysqld: 2021-01-23 4:11:31 140325655074560 [Note] WSREP: Service thread queue flushed.
Jan 23 04:11:32 setsv mysqld: WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.56.71 4444 (20210123 04:11:32.454)
Jan 23 04:11:32 setsv mysqld: WSREP_SST: [INFO] Evaluating innobackupex --no-version-check $tmpopts $INNOEXTRA --galera-info --stream=$sfmt $itmpdir 2>${DATA}/innobackup.backup.log | socat -u stdio TCP:192.168.56.71:4444; RC=( ${PIPESTATUS[@]} ) (20210123 04:11:32.457)
Jan 23 04:11:32 setsv mysqld: 2021/01/23 04:11:32 socat[15384] E connect(6, AF=2 192.168.56.71:4444, 16): Connection refused
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325134952192 [Warning] Aborted connection 26 to db: 'unconnected' user: 'sst_user' host: 'localhost' (Got an error reading communication packets)
Jan 23 04:11:32 setsv mysqld: WSREP_SST: [ERROR] innobackupex finished with error: 1. Check /var/lib/mysql//innobackup.backup.log (20210123 04:11:32.467)
Jan 23 04:11:32 setsv mysqld: WSREP_SST: [ERROR] Cleanup after exit with status:22 (20210123 04:11:32.469)
Jan 23 04:11:32 setsv mysqld: WSREP_SST: [INFO] Cleaning up temporary directories (20210123 04:11:32.471)
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140324111660800 [ERROR] WSREP: Failed to read from: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.56.71:4444/xtrabackup_sst//1' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --gtid 'a53cd166-5d68-11eb-aa0f-83c1fd168f1f:0' --gtid-domain-id '0' --mysqld-args --basedir=/usr --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140324111660800 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.56.71:4444/xtrabackup_sst//1' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --gtid 'a53cd166-5d68-11eb-aa0f-83c1fd168f1f:0' --gtid-domain-id '0' --mysqld-args --basedir=/usr --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1: 22 (Invalid argument)
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140324111660800 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.56.71:4444/xtrabackup_sst//1' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --gtid 'a53cd166-5d68-11eb-aa0f-83c1fd168f1f:0' --gtid-domain-id '0' --mysqld-args --basedir=/usr --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325406353152 [Warning] WSREP: Could not find peer: b8dba97a-5d6b-11eb-8d6d-ae8d4ded2a34
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325406353152 [Warning] WSREP: 0.0 (setsv): State transfer to -1.-1 (left the group) failed: -22 (Invalid argument)
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325406353152 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 0)
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325406353152 [Note] WSREP: Member 0.0 (setsv) synced with group.
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325406353152 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325646681856 [Note] WSREP: Synchronized with group, ready for connections
Jan 23 04:11:32 setsv mysqld: 2021-01-23 4:11:32 140325646681856 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jan 23 04:11:36 setsv mysqld: 2021-01-23 4:11:36 140325414745856 [Note] WSREP: cleaning up b8dba97a (ssl://192.168.56.71:4567)
, file /var/lib/mysql//innobackup.backup.log says
210123 04:11:32 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
210123 04:11:32 Connecting to MySQL server host: localhost, user: sst_user, password: set, port: not set, socket: /var/lib/mysql/mysql.sock
Using server version 10.2.36-MariaDB
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 16384
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
question from:
https://stackoverflow.com/questions/65867812/galera-cluster-not-working-use-wsrep-sst-method-xtrabackup-v2-until-first-use 
