MySQL: Time Delayed Replication
May 7, 2008 – 12:12 pmI was cruising the MySQL Forge Worklog when I came across the idea of Time Delayed Replication. I had never considered the benefits of deliberately keeping a slave server behind a master.
Kristian Koehntopp gives a good example:
Kristian Koehntopp writes:
TDS: Time delayed SQL_THREAD (Have a replication slave that is always
lagging 30 minutes behind).Currently, replication is a rolling recovery: To set up replication you
restore from a full dump with a binlog position. You then continously
download binlog and roll forward. In case of a master crash a slave is a
readily recovered instance (as opposed to a backup, which still has to be
restored).This protects against crashes, but not against oopses.
A time delayed slave (TDS) is a nice protection against oopses.
Sugar on top addendum: With the binlog being a table, any table type,
it will be very easy to delete an oopsing statement out of the
unapplied binlog queue. Currently, a simple time delayed slave will
protect you against oopses, but it will be very hard to extract the
relevant binlog portion out of the TDS replication log skipping the
oopsing statement.With a MyISAM binlog table, it is just a matter of DELETE FROM
REPLICATION.BINLOG WHERE STATEMENT_ID = 1717;
Definately a cool idea. Instead of having to restore from a backup, you just take down your master, pull out the busted query, let the server catch up, and bring the slave up as the new master. Doing a restore from our current database backups takes about 3-4 hours, and we’re not even huge. It seems like your downtime here would be limited to however long it takes your slave to catch up. Additionally, once you take down your master and remove your bogus query (truncate table perhaps?) you can allow the slave to replicate everything and catch up (no longer limited to 30 min behind, ideally). Depending on your traffic, this could limit your downtime in the case of a catastrophic loss to 20 minutes or so instead of hours, and your data loss would be far less.
4 Responses to “MySQL: Time Delayed Replication”
been there, done that
we had a slave, lagging between 1-2hrs - not only for admin oopses, but also to recover from malicious user attacks.
since our traffic was hudge - during the day, the slave needed about the sime time to recover, as the lag was - eg, a 1h lag took 1h to catch up…
so every 15 minutes a perl script did start slave, checked if the lag is less than 2hrs - if it was, it did stop the slave, if it was larger, it left the slave running, and checked again in the next 15 minutes.
By bighard on May 7, 2008
It’s useful enough, and enough people requested it, that Maatkit has a very elegant implementation of time-delayed replication. It does NOT rely on Seconds_behind_master, which is buggy and unreliable; neither does it read binary logs, which is unacceptably expensive.
By Xaprb on May 7, 2008
I had completely forgot that I suggested that back in 2001. Heh.
By Jeremy Zawodny on May 8, 2008
I didn’t even realize this went back 7 years. Wow…
By jon on May 8, 2008