Getting duplicate entries after restore

MySQL & MariaDB Percona XtraBackup
1

Hi,

We’ve recently upgraded a MySQL 5.0 master-master setup to Percona 5.6. Slaving went b0rken due to some failures on our side, but we thought we could simply fix it by using xtrabackup to create a backup from the running server and importing it to the slave. I’ve been trying to do that this entire weekend (partly because it’s a huge database with an insane amount of databases and tables), but to no avail. Can someone shed a light on what I might be doing wrong here?

First, I run the following on the current in production master:

ulimit -n 409600
innobackupex --defaults-file=/etc/mysql/debian.cnf /mnt

When that is done, I copy the resulting directory to the other server and run:

innobackupex --use-memory=4G --apply-log /srv/restore

It exits with an OK message eventually. Now I restore it to the database with:

innobackupex --move-back /srv/restore

All goes well and I can start MySQL again (after I chown the /srv/mysql directory, which is our datadir). Data is in there and the database is running fine. Now I start slaving on this database:

/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e "CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='replication', MASTER_PASSWORD='verysecret', MASTER_AUTO_POSITION=1; START SLAVE"

Slaving starts but immediately stops due to a 1062 error. After investigation, I find out that the entry it’s trying to apply was added on the master db right after I started the backup. I can fix that, but I immediately get a new error.

To me, it seems like the backup did not contain all the latest GTIDs, only the ones that were available at the start of the backup? I thought this was exactly what XtraBackup was supposed to fix? I see no alternative now to making sure no writes are done on the database during a backup. Am I doing something wrong here? Is this supposed to happen?

Running on Debian Wheezy with all the latest patches.

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
$ innobackupex --version
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
$ xtrabackup --version
xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )

Any help would be appreciated!

2

Check contents of xtrabackup_binlog_info. For replication environments using GTID follow this guide:
[url]Percona XtraBackup
Note, see step #4, you might have missed setting gtid_purged value.

3

Thanks very much! It would’ve been very helpful if this was mentioned in the man page or in one of the earlier recipes for innobackupex. At least, that’s where I expected it. Would’ve saved me a lot of time!