An Oracle blog about Openomics

  • sun
    April 7, 2010

Helia secures MySQL with ZFS

Guest Author

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
, 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:

  1. Simple snapshot of MySQL
    data and binary log files
  2. Complete backup of a MySQL data files snapshot in a single archive
  3. Incremental
    backup of a MySQL data files snapshot in a single archive file
  4. Incremental backup of a MySQL data files snapshot to a “clone” file system on the local server
  5. 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

distant server, so we are assured against a disk or system failure; the files are backed

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.

Join the discussion

Comments ( 1 )
  • Bryan Allen Thursday, April 8, 2010

    While still slow, mysqldump can be non-locking. See --single-transaction.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.