R1Soft Continuous Data Protection® for MySQL

{page-title}{excerpt}How to Leverage R1Soft's Continuous Data Protection® for MySQL{excerpt}

*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.

!mysql-cdp-diagram.jpg!

h3. 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..
# Continuous Data Protection Server (CDP Server) connects to CDP Agent on target MySQL server to request backup operation (synchronization).
   
# Agent Repeats the Following Steps _Three Times_:
## execute *FLUSH TABLES*
*     *
## sync disk containing MySQL data directory (uses proprietary Linux IOCTL call on R1Soft CDP device driver to sync only MySQL data directory disk)
 
# 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)
*  *
# Create a Disk/Volume Snapshot using the proprietary R1Soft Linux CDP Device Driver.  This typically takes less than 1 second.
   
# *UNLOCK TABLES*    After this point MySQL queries can proceed again. 
   
# Read Block Level Deltas from the Snapshot and Send them over the network to the CDP Server Disk Safe Repository
   
# Remove the Snapshot

h3. 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. 

h3. 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:
* [TP:Computing Deltas - near-Continuous (CDP)]
* [TP:Block Based Backup Technology]
* [TP:Backup Method - Virtual Full Backup]

h3.


h3. Summary

| {color:#00cc00}Category{color}\\ | [MySQL Continuous Data Protection|TP:Categories of MySQL Backup Software]\\ |
| Archive Type | Disk \\ |
| MySQL Storage Engines Supported | {color:#00cc00}MyISAM and InnoDB{color} \\ |
| Supported O/S | {color:#cc3300}Linux Only (Windows coming with CDP 3.0){color}\\ |
| Binlog Backup & Restore | {color:#cc3300}No{color} |
| {color:#00cc00}Zero Business Interruption (Online Backups){color} | {color:#00cc00}Yes{color} |
| {color:#00cc00}Granular (Table Level) Restore{color} \\ | {color:#00cc00}Yes{color} |
| {color:#00cc00}Easy to Use Graphical User Interface{color} \\ | {color:#00cc00}Yes{color} |
| Complete Server Protection (Backup All Data or just MySQL?) \\ | {color:#00cc00}Yes{color} |
| Backup Type \\ | [Virtual Full|TP:Backup Method - Virtual Full Backup]\\ |
| {color:#00cc00}Backup Window Length{color} \\ | {color:#00cc00}Minimal{color} (does not interrupt MySQL) \\ |
| {color:#00cc00}Server Disk I/O and Load Impact{color} \\ | {color:#00cc00}Minimal{color} |
\\

h3. 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()|http://linux.die.net/man/2/sync] or the equivalent command line utility [sync.|http://linux.die.net/man/8/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 tghe 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. 
\\

h3. 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.

!mysql-vs-cdp-graph.jpg!

h3.


h3. 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 locate don different devices.

R1Soft is adding the ability to synchronize multiple volumes in one snapshot to their [CDP 3.0 technology|CDP Product Road Map].  Look for this in a version of CDP 3.0

h3.