“Stay hungry, stay foolish” ...... but have a backup :)

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 )

MySQL Relayed Replication Solution

Once I had a situation where MySQL cluster was not an option.  I still wanted replication and redundancy. 

What I built was a relayed replication solution. 

I had a MYSQL DB running the INNODB engine that contained over a TB of data. 

MySQL master server ( SERVER_A ) that replicated to the slave ( SERVER_B ).

SERVER_B was also a slave that then replicated to a second slave (SERVER_C).

 I would have preferred to add a server_d but budget did not allow. 

What did this do for us?  Well this structure allowed us few different options.

First we had redundancy hardware and with replication. If SERVER_A crashed we could update the masterDB reference, which was just an internal dns reference, to SERVER_B and the site was up and running with little downtime. We could then do some research on SERVER_A to find what the problem was. Once this box (server_a) was ready for rotation again we would make it a slave to SERVER_C since SERVER_B was the new master.  

This round robin of servers also allowed us to add database updates in a manor that updated the slaves first and near zero downtime for an update to the master. We just rotated the order of the databases. 

Another benefits of this hardware setup was we where able to build out smart database handles. So based on your query and related settings we could decide which database would handle the select. All writes went to the master of course.  This allowed the big queries to stay out of the way of other selects and resulted in few if any deadlocks or slow responses. 

So for example: 

Then after a database rotation:

If you are running a Master with two to three slaves off it I would ask why? Once you loose the master all of your slaves are also down. With relayed replication you can keep replication in case of a master crash. The worst case scenario is when the slave in the middle crashes. The best solution for this would be a forth box so you can skip down further and still have replication up.

With optimized queries you can keep the seconds behind master at 0 and diversify your select load to enable a robust footprint that has built in replication redundancy.

The Data Charmer also talks about  Relayed replication