Various Backup/Replication Fault Tolerance Strategies

[ZappoMan]

I have a sort of general MySQL question, but I'm mostly interested in an answer from someone who's currently managing a medium (>2000 blogs) to large size (>>10,000 blogs) deployment of WordPressMU.

How have you implemented your fault tolerance and backup strategy.

We're currently at about 3,000 blogs, and we've been doing the old sqldump approach to backing up our system, but frankly it's getting to be too time consuming. We need to find a better solution.

I am considering implementing a strategy like switching to LVM and then using LVM snapshots to do backups.

But I am also considering:

• implementing several master-slave approaches
• implementing a master/slave where the slave is running on something like jungledisk, persistfs - which basically use AmazonS3 as a backing store
• Using LVM for local snapshots and then copy to AWS

Part of my motivation for looking at fault tolerance is because I'm considering moving the system to EC2 so I can get more horse power at lower cost... and more flexibility. At the risk of starting a flame war over various hosts, please feel free to give your feedback on strategies for running WPMU on systems with mutable disk systems.

Thanks in advance.

[drmike]

How have you implemented your fault tolerance and backup strategy.

Fault tolerance - no.  Andrew has discussed building HyperDB into his multi database plugin a long while back but nothing to my knowledge ever came out of it.  Donncha has never stated if this is done over on wp.com although staff has mentioned that they have backup database boxes.  Not sure in what why they use them though.  I believe hyperdb does work on the standard mu install though although I've never tried it.

Backup strategy - Yes.  With our hosting, we do a drive imaging to a separate backup box on odd numbered days with accounts being backed up'ed on even days to a separate box. (Actually two of them.  For some reason, the account backups are always larger than the drive imaging.) This is something that is done with our hosting software, Direct Admin, though and is not a part of mu.  We keep backups for 2 weeks now.

Our big 21k blog mu install does do their own backups as well.  They have a script that exports out their mysql databases into a separate subdirectory while changing the name of the file to include that timestamp.  They run that every hour and, I believe, export out a copy manually to rapidshare once a day.  They copy over the blogs.dir subdirectory elsewhere on the drive as well once a day.

That's something they have written themselves and I know they won't open source it.

Does that help?

[drmike]

And it's so nice to see an intelligent question.

You may want to ask Andrew if he ever worked on the idea.

[ZappoMan]

Dr. Mike,

God forbid you answer this question "yes"... but I'll ask it anyway... have you ever had to do a restore from a completely dead disk using your backup strategy?  Have you ever done a test run? How did it work out?

Your client with 21K blogs... do you mind explaining the machine config? Is this running on a single beefy box? What kind of traffic does it get? And more importantly, how do they manage to do SQL backups (are they doing SQL Dumps?) on 21k blogs without bringing their system to a screeching halt?

[drmike]

Complete drive restore?  No.  We've restored from accounts though without issue.  That's also one of the reasons why we do both.  If an account backup failed, I could always install the drive backup onto a spare machine and lift it from that.  Either that or one of the previous backups.  Had drives fail but we've been able to get the data off in time. (Seagates.  YUCK!)

The 21k blogs client (She has a NDA in place or I would give you a link) is set up on 2 servers, the front one with the mu code and uploads, the backend one has the databases and is set to a private ip address that only the front box can see.  They're just are standard boxes.  Dual Intel 2.8 or 3.0's with HT, 4 gigs of memory and a pair of drives between 80 gigs and 250 depending on when we built them. (Not sure what's in hers exactly)

Backups are fairly easy as it does it's running Andrew's multiple database fix with 256 databases and it backs up one database at a time.  I'm sure there's been data collisions but I've never had any reported to me.

Each database only has on an average of 8-10 blogs so it's not too bad.  I think it only takes them a few minutes.

[ZappoMan]

Mike, I must be doing the math wrong, but when I try 256 time 8-10 I don't get 21K... Do you mean they have 80 to 100 blogs per DB? That would make more sense... and as you point out, still be pretty darn fast.

[drmike]

Sorry, hand slipped on keypad.  It's about 80 per db.  I was just throwing out a number.

It doesn't take that long though.  I published a while back a script that runs through all of the databases and optimizes them one at a time.  It's the same script but just exports them out one at a time to a timestamped subdirectory.

It's still fairly fast I believe.  Probably be even faster if we moved it from php into a bash script.

[Andrea_R]

I published a while back a script that runs through all of the databases and optimizes them one at a time.  It's the same script but just exports them out one at a time to a timestamped subdirectory.

AHA! My to do list:
- find drmike;s script on the hard drive wherever i put it
- read it
- edit it to do the mysqldump

Ideally I;d like a script to take a zip of the db backup (individual ones) and email it soemwhere. Good for small systems, which is what I'm aiming at irght now (not mine).

[drmike]

- find drmike;s script on the hard drive wherever i put it

I have it on our private trac somewhere.  It was removed from public sight when Matt told me that I wasn't allowed to support Wordpress anymore.  There was also a question of licensing as it was just thrown up on a website somewhere.

- edit it to do the mysqldump

Actually that was only two lines.  It's more coding to create a timestamped subdirectory to save everything to.

Ideally I;d like a script to take a zip of the db backup (individual ones) and email it soemwhere. Good for small systems, which is what I'm aiming at irght now (not mine).

We thought about this as well (Well, ftping to the backup servers) but we'd rather see interaction with the wpmu site admin and making sure the backups look right at the very least.  (Every once in a while, you get zero sized exports.)

[ZappoMan]

BTW,

We've ended up implementing something that we're pretty happy with.

We are running a mysql master/slave setup, where we stop the slave, lock it for read only, and run a TAR/GZIP on it, then unlock and restart the slave. We've got our script running a backup once an hour, so worse case scenario if we lost a drive, we'd lose  one hour.

We've also got this running on EC2, and can fire up an instance what will immediate download our latest backups and begin running in Master/Slave mode.

Granted, we're still a relatively small site (3372 blogs)... but performance of this system seems to be working well.