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 atDedibox, 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 thedatabase 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 SunStartup Essentials program, ParisLabs connected with the Sun-Oracle ISVEngineering team to come up with a solution that would create anacceptable security level for their data backup strategy, withminimum 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 hotbackup, which meant a more complicatedarchitecture, more administration and a bigger infrastructurecost that the start-up could not afford. Instead we explore the possibility to leverage the novel ZFS filesystem included in Solaris 10.


Why?
  • All filesystem operations are copy-on-write transactions, so the on-disk state isalways 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 usingZFS as the backbone. With ParisLabs, we explored the following scenarios, all based on the ZFS snapshot capability:
  1. Simple snapshot of MySQLdata and binary log files
  2. Complete backup of a MySQL data files snapshot in a single archivefile
  3. Incrementalbackup 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. Incrementalbackup of a MySQL data files snapshot to a ?clone? file system ona 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 alsointerested in using ZFS to implement a backup strategy for your own application. Note that these strategiescan be applied to any kind of files, not only MySQL datafiles. Aslong as your problem comes down to securing and backuping files, ZFS canhelp you. And again, ZFS comes out-of-the-box with Solaris 10, OpenSolaris and the Sun OpenStorage appliances.



Read More about [Helia secures MySQL with ZFS...