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:
- Storage level snapshots - a capability built into ZFS (Solaris), NetApp, LVM (Linux), VxFS, and VSS (Windows)
- Transaction logs generated by MySQL
- 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.