R1Soft Continuous Data Protection® for MySQL

What is Continuous Data Protection® for MySQL?

 
Online Backup - Zero Business Interruption Backups for Even the Busiest MySQL Servers
Snapshots of InnoDB and MyISAM tables are created in less than a second for online backups with Zero interruption to users. 
 
near-Continuous Delta Computation using Specialized Device Drivers
R1Soft's proprietary Linux device driver tracks low level block deltas as they happen to minimize backup windows down to only reading deltas.
Virtual Full Backups and Recovery Point Archiving
The R1Soft CDP Server uses proprietary Disk Safe technology to archive hourly, daily, weekly, or monthly disk-based MySQL backups.  Only block level deltas are stored for every recovery point.  Each recovery point appears as a full backup.  This is called Virtual Full Backup.

Table Level Granular Restore for InnoDB and MyISAM
Granular table level restore for both InnoDB and MyISAM is supported with a web-based graphical user interface.

How Does Continuous Data Protection® for MySQL Work on Linux?

These steps are the same for both MyISAM and InnoDB.  If a database contains only InnoDB tables then the FLUSH TABLES and READ LOCK queries are only needed to synchronize the MySQL binary log if it is enabled..

1. Continuous Data Protection Server (CDP Server) connects to CDP Agent on target MySQL server to request backup operation (synchronization).
      
2. Agent Repeats the Following Steps Three Times:
   1. execute FLUSH TABLES
           
   2. sync disk containing MySQL data directory (uses proprietary Linux IOCTL call on R1Soft CDP device driver to sync only MySQL data directory disk)
       
3. Execute FLUSH TABLES WITH READ LOCK  After the previous step this typically takes less than 1 second
   (Note: unusually long running queries on your MySQL server can cause this step to take longer)
     
4. Create a Disk/Volume Snapshot using the proprietary R1Soft Linux CDP Device Driver.  This typically takes less than 1 second.
      
5. UNLOCK TABLES    After this point MySQL queries can proceed again. 
      
6. Read Block Level Deltas from the Snapshot and Send them over the network to the CDP Server Disk Safe Repository
      
7. Remove the Snapshot

How Long is MySQL Locked?

MySQL is only locked for steps 3) and 4).  These steps combined typically take less than 1 second even on write intensive MySQL servers. 

How Does CDP Help MySQL Backups?

Step 6) is where the Continuous Data Protection comes in.  Using a proprietary low level Linux device driver the R1Soft Linux Agent knows in advance what block level changes where made to the MySQL data files for MyISAM and InnoDB.  The backup window only consists of reading changed blocks from disk.  Delta computation meaning determining what files or blocks have changed is done real-time as the system is running.
 
For more information on Continuous Data Protection® see:

• Computing Deltas - near-Continuous (CDP)
• Block Based Backup Technology
• Backup Method - Virtual Full Backup

Summary

Fast MySQL Lock and Flush Times on Linux MySQL Servers with Large multi-Gigabyte Memory Caches

The steps taken in 2) of doing a FLUSH TABLES and disk sync over and over again several times greatly reduces the time needed to complete a final LOCK and FLUSH of mysql. 

MySQL servers with large memory typically have a large number of MyISAM writes cached in memory.  If MySQL is configured to do so these servers may also have very large MySQL caches containing dirty MySQL data pages.  Executing a FLUSH TABLES WITH READ LOCK query on these can take several minutes to complete!  This is because MySQL needs to write many GB of changes cached in memory to disk.  MySQL flushes it's cache then the data must be written again from file system cache to disk.  This can take a very long time on large MySQL servers easily causing queries to timeout and making applications unavailable until the LOCK and FLUSH is complete.

To overcome this R1Soft developed a new block device kernel IOCTL call available in its CDP Linux device driver to sync a particular disk.   The Linux Agent is Not calling the typical libc sync() or the equivalent command line utility sync.Doing so will SYNC ALL DISKS and can cause severe performance problems on large MySQL Servers.  Instead the R1Soft Linux Agent uses low level proprietary method to sync only the disk containing the MySQL data directory.

The Linux Agent will execute a FLUSH TABLES query (with No Lock) and then call the special sync IOCTL on the disk containing the MySQL data directory.  This will be repeated several times.  This is a critical step in performing a Online backup of MySQL using disk snapshots and can only be done correctly using the R1Soft CDP device driver. 

Continuous Data Protection Reduces System Load During Bakcups

One of the most important benefits of CDP is that only Deltas are read during the backup operation.  Other backup methods must either dump all of the mysql data e.g. mysqlhotcopy or mysqldump.  Or instead they dump the data then use a program like rsync to read all of the data and determine the changes using checksums.  Compiting deltas is a major drain on precious disk I/O resources and what causes backups to take minutes or hours for MySQL.

Below is a MRTG Graph Provided to R1Soft by a Customer Comparing their MySQL Dump and Tar backup v.s. R1Soft CDP.  The graph shows Linux load average with daily bars.

How Does R1Soft Work When MySQL Files are Located on Different Devices?

Most MySQL installations (also the default) are configured so that their MySQL data directory, MyISAM tables, and InnoDB table spaces and logs are all located on the same volume.  It is possible to locate tables on different devices.  Particularly this is seen with InnoDB where the table space is on one device and the journal Innobase log on another device.  Or even the InnoDB table space can be split across multiple devices.  If this is the case then the R1Soft CDP server (as of 2.0) fail to add the new MySQL instance to your backup policy.  The challenge is that the entire database must be cleanly part of the same snapshot.  This is not possible if different files of database storage are located on different devices.

R1Soft is adding the ability to synchronize multiple volumes in one snapshot to their CDP 3.0 technology.  Look for this in a version of CDP 3.0