Backups

[george]

Apparently, Magnolia's problem was filesystem corruption on the
database server. I'm not sure how much regular, non-integrity-checked
backups would have helped, especially if the problem wasn't detected
immediately. More out of curiosity than concern: what's the backup
procedure for Pinboard?

[Peter VG]

Ma.gnolia's 'backup' was a filesystem level sync, over firewire, to a
different machine. What was being copied were the the straight up
mysql binary files. It seems that no historical snapshots were kept so
as the original data became more and more corrupted, so did the synced
copy. By the time the main db filesystem failed completely, the
sync'ed snapshot was unusable.

What happens at pinboard is currently quite simple - the data gets
sql-dump-ed and fired to the Amazon S3 orbital storage facilities,
nightly. The old snapshots just sit there, currently - the service is
young, the dataset manageable so just storing the snapshots is not a
significant burden.

Is this a foolproof backup strategy? Probably not. There are probably
many obvious and non-obvious ways it can go quite wrong and data can
be lost and the procedure will be improved along with the rest of the
service. It is likely reasonably resilient to the kind of failure that
ma.gnolia experienced. The major differences are, at pinboard -

- there is a sequence of historical backups
- the data format of the backup is less vulnerable to non-recoverable corruption
- backups are maintained offsite at a distributed storage service with
a reliability and availability record better than that of a mac mini
and a firewire cable.

Another unfortunate omission at ma.gnolia is that they never actually
tried to see if their backup worked until they needed it and it
didn't.
Yesterday, Maciej and I donned hazmat suits, directed an asteroid
strike the pinboard colocation facility and then had the nanobots
restore service at the failover bunker.
Ok, no, he gave me one of the S3 sql dumps and I imported it into the
mysql db running on my laptop. It worked. Data was there.

-pvg

[maciej]

From what I can understand from watching Larry Halff's video*,
Magnolia was copying over live database files over a FireWire
connection, which pretty much guarantees the backups would be useless
(since the files change constantly as mysql is running). It sounded
more like an attempt at mirroring than a backup procedure.

I have a cron job set up to do a nightly mysqldump of the production
database. For those not familiar with mysql, this means that the
entire database gets written out as a text file, containing a list of
statements that will create an identical copy from scratch when loaded
back into a mysql server. This kind of format is bulkier than a
binary backup, but much safer, because errors won't corrupt the entire
file.

The dump file gets stored locally as well as copied up to S3. The
file is datestamped, so there is basically a backup for every day the
service has been running. Every few days I test the system by
downloading the most recent snapshot to a different server and
restoring the database from scratch. The last such test was
yesterday. This verification is done by hand right now, but when
time allows I will automate it so that it just emails me "backups OK"
messages.

And then I'll still probably test it by hand, since I'm terrified of
ironic outcomes after talking so much smack about Ma.gnolia and
Bookmarks2.

----

* I am amazed at the gall of making users watch a video in order to
find out why all their data is gone, especially when the reason turns
out to be breathtaking incompetence. It's like setting someone's
house on fire and then conveying the news that you are an arsonist
through interpretive dance.