\\
{page-title}{excerpt}Volume Snapshots (e.g. Linux LVM) for Online Backup{excerpt}
Using disk snapshots is a very reliable and efficient method for taking Online backups of MySQL if you are comfortable doing some scripting for backups.
\\
\\
h3. How Does MySQL Backup with Volume Snapshots (e.g. LVM) Work?
If you can get a point-in-time snapshot of a live running disk volume and get the file system in a perfectly consistent state when you do it then consistent MySQL hot backup is possible.
h3. Point-in-time Volume Snapshot Tools
* [Linux Logical Volume Manager|http://tldp.org/HOWTO/LVM-HOWTO/whatislvm.html] \- LVM is a Volume Management system that has [built-in snapshot functionality|http://tldp.org/HOWTO/LVM-HOWTO/snapshotintro.html]. A big disadvantage of LVM is that your MySQL storage must be configured on a LVM Volume and you must have pre-configured dedicated storage space for snapshots. This means converting an existing MySQL installation on Linux to use LVM can be a large effort. Also some older versons of LVM do not support freezing the file system in a consistent state. This means they should Not be trusted for online MySQL backups. You may need to patch LVM with the VFS-lock patch to get consistent file system snapshots.
* [Linux Hot Copy|http://www.r1soft.com/products/linux-hot-copy/] \- Linux Hot Copy is a tool for taking point-in-time snapshots of any Linux block device that does Not require LVM. Hot Copy is typically much easier to install on an existing MySQL deployment when compared to LVM and it requires No dedicated snapshot storage.
* [Veritas VxFS|http://en.wikipedia.org/wiki/Veritas_File_System] \- VxFS is primarily used on the HP-UX operating system where it is called JFS. It is also now sold by Symantec as a product for Windows, Solaris, and Linux called [Veritas Volume Manager|http://en.wikipedia.org/wiki/Veritas_Volume_Manager]. VxFS provides Snapshot or as VXFS calls it mirroring of existing volumes for point-in-time backup purposes.
* [NetApp SnapManager|http://en.wikipedia.org/wiki/NetApp_Filer] \- If you store you raw MySQL table files on a NetApp appliance you have the functionality to do point-in-time volume snapshots on your NetApp with SnapManager.
h3. Point-in-time Snapshot Process
# Lock Tables in MySQL
# Take Volume Snapshot
# Unlock Tables in MySQL
# Copy Raw Tables from Snapshot
h3. Volume Snapshots Require All MySQL Data to be on Same Volume\!
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 volume snapshots are Not an option for your installation. 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.
h3. Summary
| {color:#cc0000}Category{color}\\ | [Online|TP:Categories of MySQL Backup Software]\\ |
| Archive Type | Disk \\ |
| MySQL Storage Engines Supported | MyISAM and InnoDB \\ |
| Supported O/S | Depends on Snapshot Tool \\ |
| Binlog Backup & Restore | {color:#00cc00}Yes{color} \\ |
| {color:#00cc00}Zero Business Interruption (Online Backups){color} | {color:#00cc00}Yes{color} |
| {color:#cc3300}Granular (Table Level) Restore{color} \\ | {color:#cc3300}No{color} |
| {color:#cc3300}Easy to Use Graphical User Interface{color} \\ | {color:#cc3300}No{color}\\ |
| Complete Server Protection (Backup All Data or just MySQL?) \\ | {color:#cc3300}No{color} |
| Backup Type \\ | [Incremental|TP:Backup Method - Full Backup]\\ |
| {color:#cc3300}Backup Window Length{color} \\ | {color:#cc3300}Long{color} (does not interrupt MySQL) \\ |
| {color:#cc3300}Server Disk I/O and Load Impact{color} \\ | {color:#cc3300}Heavy{color} \\ |
\\
{page-title}{excerpt}Volume Snapshots (e.g. Linux LVM) for Online Backup{excerpt}
Using disk snapshots is a very reliable and efficient method for taking Online backups of MySQL if you are comfortable doing some scripting for backups.
\\
\\
h3. How Does MySQL Backup with Volume Snapshots (e.g. LVM) Work?
If you can get a point-in-time snapshot of a live running disk volume and get the file system in a perfectly consistent state when you do it then consistent MySQL hot backup is possible.
h3. Point-in-time Volume Snapshot Tools
* [Linux Logical Volume Manager|http://tldp.org/HOWTO/LVM-HOWTO/whatislvm.html] \- LVM is a Volume Management system that has [built-in snapshot functionality|http://tldp.org/HOWTO/LVM-HOWTO/snapshotintro.html]. A big disadvantage of LVM is that your MySQL storage must be configured on a LVM Volume and you must have pre-configured dedicated storage space for snapshots. This means converting an existing MySQL installation on Linux to use LVM can be a large effort. Also some older versons of LVM do not support freezing the file system in a consistent state. This means they should Not be trusted for online MySQL backups. You may need to patch LVM with the VFS-lock patch to get consistent file system snapshots.
* [Linux Hot Copy|http://www.r1soft.com/products/linux-hot-copy/] \- Linux Hot Copy is a tool for taking point-in-time snapshots of any Linux block device that does Not require LVM. Hot Copy is typically much easier to install on an existing MySQL deployment when compared to LVM and it requires No dedicated snapshot storage.
* [Veritas VxFS|http://en.wikipedia.org/wiki/Veritas_File_System] \- VxFS is primarily used on the HP-UX operating system where it is called JFS. It is also now sold by Symantec as a product for Windows, Solaris, and Linux called [Veritas Volume Manager|http://en.wikipedia.org/wiki/Veritas_Volume_Manager]. VxFS provides Snapshot or as VXFS calls it mirroring of existing volumes for point-in-time backup purposes.
* [NetApp SnapManager|http://en.wikipedia.org/wiki/NetApp_Filer] \- If you store you raw MySQL table files on a NetApp appliance you have the functionality to do point-in-time volume snapshots on your NetApp with SnapManager.
h3. Point-in-time Snapshot Process
# Lock Tables in MySQL
# Take Volume Snapshot
# Unlock Tables in MySQL
# Copy Raw Tables from Snapshot
h3. Volume Snapshots Require All MySQL Data to be on Same Volume\!
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 volume snapshots are Not an option for your installation. 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.
h3. Summary
| {color:#cc0000}Category{color}\\ | [Online|TP:Categories of MySQL Backup Software]\\ |
| Archive Type | Disk \\ |
| MySQL Storage Engines Supported | MyISAM and InnoDB \\ |
| Supported O/S | Depends on Snapshot Tool \\ |
| Binlog Backup & Restore | {color:#00cc00}Yes{color} \\ |
| {color:#00cc00}Zero Business Interruption (Online Backups){color} | {color:#00cc00}Yes{color} |
| {color:#cc3300}Granular (Table Level) Restore{color} \\ | {color:#cc3300}No{color} |
| {color:#cc3300}Easy to Use Graphical User Interface{color} \\ | {color:#cc3300}No{color}\\ |
| Complete Server Protection (Backup All Data or just MySQL?) \\ | {color:#cc3300}No{color} |
| Backup Type \\ | [Incremental|TP:Backup Method - Full Backup]\\ |
| {color:#cc3300}Backup Window Length{color} \\ | {color:#cc3300}Long{color} (does not interrupt MySQL) \\ |
| {color:#cc3300}Server Disk I/O and Load Impact{color} \\ | {color:#cc3300}Heavy{color} \\ |
\\