Home / server / Questions / 708641
Accepted
Tim Stoop
Asked: 2015-07-27 13:45:28 +0800 CST

XtraBackup restore gives duplicates on slaving?

  • 772

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: )

mysql-replication

1 Answers

  1. Best Answer

    You have to tell the slave what was the last GTID executed on the master when backup was taken.

    NewSlave > SET GLOBAL gtid_purged="XXXX";
NewSlave > CHANGE MASTER TO
           MASTER_HOST="10.x.x.x",
           MASTER_USER="replication",
           MASTER_PASSWORD="verysecret",
           MASTER_AUTO_POSITION = 1;
NewSlave > START SLAVE;
NewSlave > SHOW SLAVE STATUS \G;

    Source: https://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_gtid.html

    • 1