As we all settle into 2012, I remember the great saying “Stay hungry, stay foolish.”
The famous“Stay hungry, stay foolish” reference is from the last Whole Earth Catalog and also made popular via the Steve Jobs 2005 Commencement Address at Stanford, for those that do not know. Computers today would be a very different place without the numerous people that did stay hungry and foolish as it all came together. How does that apply to the DBA of today?
Hopefully we are all still hungry but not foolish with our data. MySQL is running most of the databases that power the web these days and we cannot afford to be foolish with that data. When it comes to data integrity and security we should “wag more bark less.” By that I mean, be excited about the options you have, anticipate issues before they arise, “wag more , be hungry.” Do not be stuck in a “bark or foolish” situation of lost data and tables, downtime, or lost backups.
How many of you have had to recover a critical table that someone, you thought never would, decided to truncate. Granted accidents happens and it is hard to anticipate all of the possible situations you might find yourself in. Which is why you need a stable backup solution. Now, how long did it take you to recover that truncated data? How long it took for you to be notified? How far behind was your replicated slave database? Could you stop replication and dump the table for a recovery? This is why I, and many others, are excited about the Delayed Replication options with MySQL 5.6.
I was at Oracle Open World 2011 and I sat in on a few of the replication sessions. During one I heard someone mention, “Why would you want to delay replication?” Live Rolling backups I immediately thought and this person soon realized the benefits. It depends on the database size and activity and disks are becoming cheaper and cheaper for you to handle the logs. Would you rather pull your database off a tape, load it, dump the table then fix what was lost? I touched on this some before in a Database Chain blog post. With the Delayed Replication option you can now address some of your backups concerns with a live replicated database chain. Consider this, Server A is your slave, Server B gets time delayed set to a Day ( CHANGE MASTER TO MASTER_DELAY=86400 ), Server C gets 6 Days (So a week back from master , CHANGE MASTER TO MASTER_DELAY=518400 ), Server D gets set to 24 Days back (~month from master , CHANGE MASTER TO MASTER_DELAY=2073600 ). Now you have full access to historical data if needed. Live rolling backups !
Don't be foolish with data. Of course, you can still dump to tape the pending logs and database backups for a serious disaster. But the option to have live historical data native to MySQL replication is fantastic.
Lastly, To quote from Eric Raymond's the The Cathedral and the Bazaar, “The next best thing to having good ideas is recognizing good ideas from your users.” Delayed Replication is a community driven idea and was entered as a bugs in 2006 and 2007 http://bugs.mysql.com/bug.php?id=21639 , http://bugs.mysql.com/bug.php?id=22072, http://bugs.mysql.com/bug.php?id=28760 then entered into the worklogs in 2010 WL#344: Time-delayed Replication.
More data on this topic:
http://www.clusterdb.com/mysql-replication/delayed-replication-in-mysql-5-6-development-release/
http://datacharmer.blogspot.com/2011/01/first-look-at-delayed-replication-in.html
See ya soon at SCALE ! ( https://www.socallinuxexpo.org/scale10x/schedule )
