mysql 5.7 withc gtid innobackupex options for master backup and new slave

MySQL & MariaDB Percona XtraBackup
1

  • Used innobackupex with this options to get a backup from the master:
    innobackupex /var/backup/xtra --slave-info --safe-slave-backup

  • The master and the (future) slave running mysql 5.7 with GTID:

mysql -sre “show global variables”| grep gtid_mode

gtid_mode ON

  • After moving the backup to the Slave and restoring it with:
    innobackupex --apply-log /var/backups/xtra/2017-05-26_22-49-20/
    innobackupex --copy-back /var/backups/xtra/2017-05-26_22-49-20/

  • But Replicacion is getting errors:
    Last_SQL_Error: Could not execute Write_rows event on table XXXXX; Duplicate entry ‘XXX’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.002255, end_log_pos 1384983

My doubt is if the options --slave-info or --safe-slave-backup in a Master with GTID can cause this problems.

Thanks.

2

If you want to use Xtrabackup to provision a new slave one should not use --slave-info or --safe-slave-backup. The only time you use these two variables when taking a backup is if 1) you take a backup from a slave and 2) you intend to use that backup to provision another slave replicating from the same master where the backup was taken.

3

Thanks.
So, those options (–slave-info and --safe-slave-backup) just when doing the backup from a slave.

At the end the real problem was GTID_EXECUTED global variable.

When doing: mysql> show global variables like ‘GTID_EXECUTED’;
The variable had an old value.

To get the new GTID Slave Running i had to do:
mysql> reset master;
mysql> SET GLOBAL gtid_purged=“XXXXXXXXXXXXX”;
mysql> CHANGE MASTER TO MASTER_HOST=“XXX”, MASTER_USER=“XXX”, MASTER_PASSWORD=“XXX”, MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;

It was a GTID problem/miss understod more than Innobackupex Options.

4

are you using master_info_repository=TABLE perhaps? care to share your my.cnf from the master?