Highly Scalable MySQL Backups using Snapshots (ZFS or NetApp)

We have been focusing on providing the best possible backup solution for following scenario: 100 GB+ of data stored in MySQL database, Transaction intensive workload (i.e. rapid rate of change of data), with a business requirement to be able to perform point-in-time restoration of the MySQL database. Oh, the solution also needs to take into account that the database can grow to 1TB or more very quickly.

For such a scenario, we believe that the best possible solution today is a combination of:

  1. Storage level snapshots - a capability built into ZFS (Solaris), NetApp, LVM (Linux), VxFS, and VSS (Windows)
  2. Transaction logs generated by MySQL
  3. Point-and-click restore capability provided by Zmanda Recovery Manager for MySQL

Two reports came out today which go into nitty-gritty of above. First is a joint report written by NetApp and Zmanda engineers, titled “MySQL Backup and Restore Using Zmanda Recovery Manager and NetApp Snapshot Technology“. This report describes how NetApp Snapshot and Zmanda Recovery Manager can be used to back up and restore a MySQL database for NetApp storage systems. Specifically, this report covers the following topics:

  • Infrastructure required to deploy Zmanda Recovery Manager for MySQL with a NetApp storage system
  • Backing up a MySQL database using Zmanda Recovery Manager using NetApp Snapshot plug-in
  • Restoring a MySQL database using Zmanda Recovery Manager

Second one is a how-to blog written by Paddy on O’Reilly Databases: MySQL backups using ZFS snapshot. A key observation is sub-second time spent holding the read lock on the database while the snapshot was being taken.

While performing point-in-time recovery of their MySQL databases, DBAs don’t have to search for specific snapshots and manually combine them with database transaction logs. Zmanda Recovery Manager takes care of that behind the covers. DBAs simply key-in (or point-and-click) the timestamp to which they want to recover to.

One Response to “Highly Scalable MySQL Backups using Snapshots (ZFS or NetApp)”

  1. [...] As I had indicated before, we have been working with the engineering team at NetApp to create highly scalable backup and archiving solutions for MySQL. Today we formally announced this joint work. Our MySQL backup product, Zmanda Recovery Manager (ZRM) for MySQL with NetApp SnapShot Manager, meets two crucial design goals for scaling MySQL backups: least impact on the live production applications and quick point-in-time recovery. Database snapshots created by ZRM can also act as source for NetApp SnapMirror software, creating a highly scalable disaster recovery solution. [...]