{page-title}{excerpt}The Three Categories of MySQL Backups{excerpt}
\\ \\
While there are a number of MySQL Backup Applications available they all use just one of several methods available for backing up MySQL. There are three types of MySQL backup methods: Legacy, Online, and Continuous Data Protection.
h3. 1. Legacy Backup Applications
*Examples: mysqldump, mysqlhotcopy, Zmanda Enterprise*
h4.
|| Legacy Backup Application Performance \\ || ||
| *Backup Method Used* \\ | {color:#ff0000}Dump / Lock and Copy / Binary Log{color}\\ |
| *Hot / Online (Zero Business Interruption)* \\ | {color:#000000}No{color} |
| *Backup Type* | {color:#000000}Full (incremental with binary log){color} |
| *Backup Window Length* \\ | {color:#000000}Hours on Larger Databases{color}\\ |
| *Granular Restore* \\ | {color:#000000}No (transactional restore with binary logs){color}\\ |
| *Server Disk I/O & Load Impact* \\ | {color:#000000}Heavy{color}\\ |
h3. 2. Online Backup Applications
*Examples: InnoDB commercial ibbackup, Linux LVM Snapshots*, *Zmanda Enterprise CDP (misleading - really means LVM Snapshots)*
h4.
|| Online Backup Application Performance \\ || ||
| *Backup Method Used* \\ | {color:#ff0000}ibbackup (binary log){color} {color:#ff0000}/ Linux LVM (Lock and Snapshot) {color} |
| *Hot / Online (Zero Business Interruption)* | Yes |
| *Backup Type* | {color:#000000}Full{color} |
| *Backup Window Length* \\ | {color:#000000}Hours on Larger Databases{color} |
| *Granular Restore* | {color:#000000}No {color} |
| *Server Disk I/O & Load Impact* | {color:#000000}Heavy{color}\\ |
h3. 3. CDP Backup Applications
*Examples: R1Soft is only CDP Backup for MySQL*
h4.
|| R1Soft CDP \\ || ||
| *Backup Method Used* \\ | Continuous Data Protection{color:#000000} {color} |
| *Hot / Online (Zero Business Interruption)* | Yes |
| *Backup Type* | {color:#000000}Virtual Full{color} \\ |
| *Backup Window Length* \\ | {color:#000000}Minutes when CDP is in Sync{color} \\ |
| *Granular Restore* | {color:#000000}Yes Table Level for InnoDB and MyISAM{color}\\ |
| *Server Disk I/O & Load Impact* | {color:#000000}Minimal = Only Time to Read Deltas{color} \\ |
h3.
\\ \\
While there are a number of MySQL Backup Applications available they all use just one of several methods available for backing up MySQL. There are three types of MySQL backup methods: Legacy, Online, and Continuous Data Protection.
h3. 1. Legacy Backup Applications
*Examples: mysqldump, mysqlhotcopy, Zmanda Enterprise*
h4.
|| Legacy Backup Application Performance \\ || ||
| *Backup Method Used* \\ | {color:#ff0000}Dump / Lock and Copy / Binary Log{color}\\ |
| *Hot / Online (Zero Business Interruption)* \\ | {color:#000000}No{color} |
| *Backup Type* | {color:#000000}Full (incremental with binary log){color} |
| *Backup Window Length* \\ | {color:#000000}Hours on Larger Databases{color}\\ |
| *Granular Restore* \\ | {color:#000000}No (transactional restore with binary logs){color}\\ |
| *Server Disk I/O & Load Impact* \\ | {color:#000000}Heavy{color}\\ |
h3. 2. Online Backup Applications
*Examples: InnoDB commercial ibbackup, Linux LVM Snapshots*, *Zmanda Enterprise CDP (misleading - really means LVM Snapshots)*
h4.
|| Online Backup Application Performance \\ || ||
| *Backup Method Used* \\ | {color:#ff0000}ibbackup (binary log){color} {color:#ff0000}/ Linux LVM (Lock and Snapshot) {color} |
| *Hot / Online (Zero Business Interruption)* | Yes |
| *Backup Type* | {color:#000000}Full{color} |
| *Backup Window Length* \\ | {color:#000000}Hours on Larger Databases{color} |
| *Granular Restore* | {color:#000000}No {color} |
| *Server Disk I/O & Load Impact* | {color:#000000}Heavy{color}\\ |
h3. 3. CDP Backup Applications
*Examples: R1Soft is only CDP Backup for MySQL*
h4.
|| R1Soft CDP \\ || ||
| *Backup Method Used* \\ | Continuous Data Protection{color:#000000} {color} |
| *Hot / Online (Zero Business Interruption)* | Yes |
| *Backup Type* | {color:#000000}Virtual Full{color} \\ |
| *Backup Window Length* \\ | {color:#000000}Minutes when CDP is in Sync{color} \\ |
| *Granular Restore* | {color:#000000}Yes Table Level for InnoDB and MyISAM{color}\\ |
| *Server Disk I/O & Load Impact* | {color:#000000}Minimal = Only Time to Read Deltas{color} \\ |
h3.