3

So I use a master-master replication of a mysql database and load balance traffic between the two.

This results in server A with a binlog of all it's transactions and Server B with a binlog of only it's transactions. Each server also has a replication log of the other server's binlog.

The problem is that if we ever have a disaster (ie a bad sql statment that deletes too much) and need to restore last nights back-up, we'd like to restore the db using binary log up until the point of the bad sql statement. But we have two binary logs that each contain all the statements run.

How can I combine the two files into one while making sure the entries in each remain in chronological order?

After running mysqlbinlog on each file I can get a readable sql file. Each file will have timestamps in it, but I need to figure out a way to read in each file and run through them both deciding when the next statement goes in the combined file. Any suggestions on how to do this?

Joel
  • 31
  • 1
  • This is a scripting exercise. I would normally think of using Perl to read the lines, parse the timestamps into dates, merge the two lists and sort by date, then print them. – Andrew Schulman May 15 '15 at 14:00
  • Thanks that is what I thought too. I found a mysql option though that should solve the issue in an easier way: log-slave-updates – Joel May 18 '15 at 17:29

0 Answers0