Cannot get GTID replication to work from one XtraDB cluster to another

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I have an on-prem XtraDB Cluster running 5.7. That cluster replicates to an EC2 instance in AWS. I am setting up a recovery cluster to be identical to the on-prem cluster, but I cannot get it to work.

Here is how it works:
On-Prem Cluster → Replicate to AWS EC2 → Replicate to new AWS Cluster

Here are my steps:

  1. On the replica: nc -l 9999 | xbstream -x -C /var/lib/mysql/ to open up netcat to stream the backup
  2. On the source: xtrabackup --backup --parallel=4 --compress --compress-threads=4 --stream=xbstream --target-dir=/var/lib/mysql/ | nc 172.31.3.84 9999
  3. On the replica: xtrabackup --decompress --remove-original --parallel=4 --target-dir=/var/lib/mysql/
  4. On the replica: xtrabackup --prepare --use-memory=8G --target-dir=/var/lib/mysql/ --apply-log-only --parallel=4
  5. On the replica: chown -R mysql:mysql /var/lib/mysql/
  6. On the replica: '/etc/init.d/mysql bootstrap-pxc
  7. On the replica: CHANGE MASTER TO MASTER_HOST='172.31.42.17', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION=1;
  8. On the replica: START SLAVE;

Every single time I have done this (6 now), I get some error for the Last_SQL_Error. This time it’s this one:
SHOW SLAVE STATUS\G

Last_SQL_Error: Could not execute Write_rows event on table database_name.table_name; Duplicate entry '7082-293643-9269675' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001047, end_log_pos 3314

Surely I have to be doing something wrong or this would not fail every time, right? What am I doing wrong?

2

Why are you using the flag --apply-log-only? This flag is only to be used for incremental backups, which you are not doing. Repeat steps 1-4 but remove the incorrect flag.

Because you are getting a duplicate PK error, this means that you did not configure replication to start at the correct position. Confirm GTID_EXECUTED / GTID_PURGED on the replica matches the contents of xtrabackup_binlog_pos file.

3

That may be where I’m lost. I know where to get the GTID, but how do I set that value on the replica?

4
5

Thank you SO much Matthew. I did Step 4 and now I’m successfully replicating.