Re: Getting consistent backup of slave that has foreign keys

[Alexander Spitzer]

I have tried this with 3 different backup from the same backup slave. I was able to take over one of the prod slaves to take a backup, and the import is running now. It takes about 24 hours to import.

With the first 3 attempts, I did get different FK errors each time.

When the slave import is done, it is about 24 hours behind the master, so it is all just a ton of replaying binlogs, and not real-time transactions from the master.

This is a super busy master/slave setup with 2 masters and 6 slaves, so I can't really mess around with prod too much - which makes debugging hard :(

I guess I am confused why/how the slave can be stopped in mid foreign key inserts. I would think that the DB/slave treats FKs as transactions that are either completed or rolled back when the slave is stopped.

There are like 600 FK contains on this DB, so there are so many places where this only half-complete transaction could occur.

I should also explain that I do filesystem clones all the time, and it works fine. The problem is that we are TRYING to upgrade to 5.5, which requires a dump/restore.

I should also mention that the master is 5.1 and the slave is 5.5.

Thanks!