'MySQL: Time Delayed Replication'
I 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.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.