Not the answer you need?
Register and ask your own question!

Getting duplicate entries after restore

timstooptimstoop EntrantInactive User Role Beginner
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!

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Check contents of xtrabackup_binlog_info. For replication environments using GTID follow this guide:
    https://www.percona.com/doc/percona-xtrabackup/2.2/howtos/recipes_ibkx_gtid.html
    Note, see step #4, you might have missed setting gtid_purged value.
  • timstooptimstoop Entrant Inactive User Role Beginner
    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!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.