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!