Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync.

Basically 'mysqldump' was running almost whole night and then the restore process took a couple of hours so when I've started the slave it was ~63874 seconds behind master.

The slave server is read only (read_only) and there was no any writes during re-sync process so I don't understand why there are duplicated keys.

Binary log format is set to MIXED on master.

Command used to backup DB:

mysqldump --opt --single-transaction -Q --master-data=2 db | bzip2 -cz > db.sql.bz2

The slave is replicating only one database from master (db -> db_backup) with the following options:

replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup
share|improve this question

2 Answers 2

up vote 3 down vote accepted
+50

ASPECT #1 : Replication

I don't think that

replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup

belong together.

Other people have wondered about this as well

The problem stems from the order replication rules are processed. According to the MySQL Documentation on Replication Rules:

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.

Even the MySQL Documentation on replicate-rewrite-db says:

The database name translation is done before the --replicate-* rules are tested.

The replicate-wild-do-table is enforced after the rewrite. It would not be surprising if this ordering somehow imposed an INSERT into a table that has data already.

You are probably asking how did the data get there ?

ASPECT #2 : mysqldump

Doing mysqldump --single-transaction would seem to be the greatest way to point-in-time dumps of data. Unfortunately, mysqldump --single-transaction has an Achilles' Heel : ALTER TABLE. If a table is subject to any ALTER TABLE commands, such as a DROP TABLE and CREATE TABLE, that can break the integrity of the transaction the mysqldump was trying to do the dump in. Truncating a table (which is DDL in the MySQL Universe) and dropping and adding indexes can also be as disruptive.

You can find more information on that from MySQL Performance Blog's Best kept MySQLDump Secret. I actually addressed this point in a past question describing 12 commands that can break the integrity of a mysqldump's transaction : MySQL backup InnoDB

CAVEAT

EPILOGUE

One or both of the aspects may have contributed to letting a row slip in during the mysqldump that should not have existed due to either the rewrite rules or the isolation of the mysqldump being overridden.

SUGGESTIONS

I would do a mysqlbinlog dump of all the relay logs since the start of the mysqldump to see all INSERTs that the Slave will process and see if those rows already exist on the Slave. If they do, you could probably do two things:

1 : Skip all the Duplicate Key errors

Simply add this to my.cnf on the Slave

[mysqld]
slave-skip-errors=1062
skip-slave-start

and restart mysql. Then, run START SLAVE;

all the duplicate-key errors will get bypassed. When Seconds_Behind_Master gets to 0, remove those lines and restart mysql.

2 : Download percona tools

The tools you need are

Use these to find the differences in the Slave, and then correct them

share|improve this answer

Check for insert query in code which inserts directly into the slave. We can only read from slave. Write queries should be directed to master.

share|improve this answer
    
Look back at the question. The Slave is read-only and there are no writes during resync. –  RolandoMySQLDBA Jul 4 '13 at 11:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.