Home / server / Questions / 49571
Accepted
Alex Balashov
Asked: 2009-08-04 17:29:47 +0800 CST

MySQL differential dump? Other strategy for restore?

  • 772

Is it possible to generate a differential dump with mysqldump - between two databases, or, ideally, between a database and a dumped version of that database?

Here's the issue I've got - I have an active/passive HA mirror of MySQL with the actual DB data (the physical MyISAM files, indexes, etc.) residing on a shared DRBD mirror. Last week, the primary node failed and the DRBD master shifted from the primary to the secondary node, and service takeover happened like it was supposed to.

Of course, a bunch of changes have been written to the secondary copy's version of the DRBD mirror, so when the primary comes back up, it takes over the DRBD volume but both sides consider their halves "out of sync" (i.e. StandAlone).

So, now I have a situation where there are two divergent sets of transactions that have taken place on the database:

  • That which happened in the time the primary node was down and the data was being written on the secondary;

  • That which happened since the primary node came back up and took the services over again; they were never synchronised!

DRBD gives me the ability to revert to either "half" of the mirror (in its current partitioned state) as the "master" revision, but as can be seen, either way causes me to lose data.

Oh, yeah: There was no replication and there were no local transaction logs, so there are no binlogs to replay. Oops. facepalm

There are nightly backups, of course, so I can revert the DB to just about any ~2 AM state from the past year.

I suppose what I'm looking to do is revert to the version of the database that's on the secondary "half" now (i.e. changes that happened while primary was down) and then try to somehow apply the changes in state from that point forward to the present state of the database on the primary's "half" cumulatively.

The problem is, I have no idea how one would go about that without replaying transaction logs.

Insights appreciated, and thanks in advance!

linux mysql diff drbd

2 Answers

  1. Best Answer

    Ouch. Sucky situation to be in. I hope you've turned on binlogging now...

    Two tools that might be of help:

    • MySQLdiff - could probably work.
    • Maatkit - Look at this one first. I think it actually has some tools designed to help resolve split brain issues.
    • 2

  2. You shouldn't need to replay the data on the failed server. This situation is called split brain, and you will need to tell DRBD what you want it to do.

    http://www.drbd.org/users-guide/s-resolve-split-brain.html

    Edit: Didn't realize you said the primary came back online... not really sure. The binary log should contain a record of all the transactions executed by the server.

    Edit (Take 2): I need to make a note to read the whole question before posting... :( sorry

    • 0