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.

Why?
  • 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 file
  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.

Comments:

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

Posted by Bryan Allen on April 08, 2010 at 11:11 PM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

How open innovation and technology adoption translates to business value, with stories from our developer support work at Oracle's ISV Engineering.

Subscribe

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
8
9
10
11
12
13
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Feeds