Master to master replication out of sync after crash

[Brandon Laing]

Hello,

As mentioned in the subject, we have a master to master setup running. These systems are about 1,000 miles apart and serve as the primary MySQL server in each location. Total DB size is about 400GB.

One of the servers went down hard last night. Still working on the why part, but the end result was that some data is now missing from that system. Replication is back up and running, and we're trying to figure out the best way to get the missing data back without breaking anything.

The master at each location has multiple slaves that hang off of them.

Some options we've been considering:
- Using mysqlbinlog to get a full list of all updates that took place during the time that's missing. That works, but the issue there is that running that in now may update records that have already been updated since that time.

- Use mysqldump from one of the slaves on the good master and move important databases/tables over. Some of these are huge. Seems like it could work, but would potentially be messy.

The hard part is that these databases are consistently very busy and always changing. So we need to figure out the best way to get the data loaded on the master missing data, have that data replicate to it's slaves, but not to the other master. Short of rebuiling the master entirely off of a backup from the good system and then resetting the whole replication process, I'm not sure what more we can do.

Any other thoughts? These systems are running CentOS 6 with MySQL 5.1.61.