An Oracle blog about Openomics
-
sun
April 7, 2010
Helia secures MySQL with ZFS
ParisLabs is a French Web 2.0 startup, that came out of the TELECOM ParisTech Entrepreneurs incubator. ParisLabs develops and markets a SaaS platform for building networking sites, built off a Java
/ MySQL stack. It is being used, among others, by the French-speaking professional networking site Helia, kinda Facebook meets Monster. Helia stood out of the pack last year and is now reaching 300K monthly unique visitors as of March 2010 --we wish them a continued success!
Paris Labs currently hosts the Helia service on a dedicated server at
Dedibox, a leading French hoster. With a rapidly growing database, Helia needs to make frequent backups, to be able to recover from a disaster. So far, Helia had been using mysqldump to export the data into flat files. The exported files are then zipped, and sent over to a remote server. Apart from being a completely manual process, this process has a major downside: exporting a large database with mysqldump can take a long time, during which the
database is stopped, putting the entire site off-line. Also, recovering a database after a disaster from dumped SQL files is a long process, which would create even more downtime for the service.
As a member of the Sun
Startup Essentials program, ParisLabs connected with the Sun-Oracle ISV
Engineering team to come up with a solution that would create an
acceptable security level for their data backup strategy, with
minimum service downtime and minimum hardware/software additions. We ruled out, to start with, the option of setting up a slave MySQL server to act as a hot
backup, which meant a more complicated
architecture, more administration and a bigger infrastructure
cost that the start-up could not afford. Instead we explore the possibility to leverage the novel ZFS filesystem included in Solaris 10.
- All filesystem operations are copy-on-write transactions, so the on-disk state is
always valid. Every block is checksummed to prevent silent data corruption. Securing data on disk is the very first level of
data integrity. - ZFS natively supports all common RAID functionalities. By mirroring the data files, we get another level
of data security to protect them from hardware failure. - Instant
snapshot of the current state of a file system are performed in only a couple of
seconds and no initial disk space is required. - The ZFS send/receive commands allow the backup/restore of
snapshots to/from another file system (local or remote).
From the above, we concluded it was possible to design a poor man's data backup infrastructure using
ZFS as the backbone. With ParisLabs, we explored the following scenarios, all based on the ZFS snapshot capability:
- Simple snapshot of MySQL
data and binary log files - Complete backup of a MySQL data files snapshot in a single archive
file - Incremental
backup of a MySQL data files snapshot in a single archive file - Incremental backup of a MySQL data files snapshot to a “clone” file system on the local server
- Incremental
backup of a MySQL data files snapshot to a “clone” file system on
a remote server
They range from the most simple and least costly solution (#1) to the most secure and flexible solution (#5) that is combining several advantages: the backup is on a
up to a clone file system allowing us to browse and restore
individual files; the backups are done
in an incremental manner, reducing the network traffic and the
backup time.
These scenarios and their implementations have been documented into a whitepaper that we will soon be publishing on the Oracle Technical Network. In the meantime, feel free to leave a note here if you are also
interested in using ZFS to implement a backup strategy for your own application. Note that these strategies
can be applied to any kind of files, not only MySQL datafiles. As
long as your problem comes down to securing and backuping files, ZFS can
help you. And again, ZFS comes out-of-the-box with Solaris 10, OpenSolaris and the Sun OpenStorage appliances.
While still slow, mysqldump can be non-locking. See --single-transaction.