From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Regular consistent backups of databases with minimal impact on the application using the database is the goal of all database adminstrators. The backup process should also minimise the use of CPU, memory and network resources on the MySQL server. Optimal backup method and backup parameters must be choosen to keep the backup window as small as possible. The backup parameters depends on various factors - number of databases, number of MySQL servers, MySQL configuration, level of security required.
ZRM for MySQL provides an extensive list of parameters to tune the backup process for the user environment. It also tracks lots of backup parameters that are available to the administrator to tune the backup process.
This section shows an example of doing full backup of a local InnoDB database of size 6GB using various backup parameters. We are considering four measurements - backup time, restoration time, verification time and the backup size for different combination of parameters. Backup and verification of backups are regular activities performed by a database administrator. Backup verification does not impact the MySQL database or the application using it. When there is a failure, it is important to restore the database as soon as possible. The size of backup helps in determining the amount of backup storage required.
Backup was done using mysql-zrm-scheduler. The backup time and backup size were obtained from the backup-performance-info report. A read-only application was using the database when the backups were being done.
# mysql-zrm-scheduler --now --backup-set dailyrun1 # mysql-zrm-reporter --show backup-performance-info --where backup-set=dailyrun1
The verification time was obtained by verifying backup images using the mysql-zrm command verify-backup action.
# time mysql-zrm --action verify-backup --backup-set dailyrun1
Restoration time is the time taken to restore the complete database. Time taken to restore can be obtained from the output of the mysql-zrm command and is also logged to the mysql-zrm log (/var/log/mysql-zrm/mysql-zrm.log) file.
# mysql-zrm --action restore --backup-set dailyrun1 \ --source-directory /var/lib/mysql-zrm/dailyrun1/20061115154319 .... INFO: Restore done in 276 seconds. MySQL server has been shutdown. Please restart after verification.
Computing checksums asynchronously can result in considerable reduction in the backup time for large databases. Checksum computation for multi-terabyte databases can take couple of hours.
Configuration 1 (default parameters; logical backup)
The database is backed up locally using logical backup method with no backup compression or encryption (default configuration for InnoDB database). MySQL server parameters are not shown.
Configuration 2 (backup compression)
To reduce the size of backup, we can use backup compression using the gzip program (default compression method). To do backup compression, the following parameter was added to mysql-zrm.conf.
Configuration 3 (backup compression and encryption)
To improve security of the backup images, the backup image is encrypted using the gpg program - Gnu Privacy Guard. The default encryption plugin supports data encryption using gpg. It may not be necessary to encrypt the database backup if the database contents are encrypted using MySQL. The following additions should be made to mysql-zrm.conf to do backup encryption:
encrypt=1 encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt-plugin.pl" decrypt-plugin="-d"
Configuration 4 (raw backups using lvm snapshot)
Since the databases are stored in LVM2 logical volumes, it is possible to do backups in "raw" mode using lvm snapshots. In this configuration, encryption parameters added in configuration 3 were removed. To do "raw" backups with backup compression, add the following to mysql-zrm.conf from configuration 2:
The following table shows the comparison of backup measurements for various configurations:
|Configuration||Backup size||Backup size(compressed)||Backup time||Verification time||Restoration time|
|3 (compression + encryption)||2.99GB||682.90MB||1296s||328s||3713s|
|4 (compression + raw backups using lvm snapshot)||4.84GB||1.71GB||1442s||236s||276s|
Even though the backups using LVM snapshots (Configuration 4) took lot of time, the time taken by the actual backup was not high. Most time was spent in doing the backup compression. This information can be obtained from the backup-performance-info backup report. The backup compression took 1221 seconds (this time is included in the backup time). The actual backup took 221 seconds.
|dailyrun1||Wed 15 Nov 2006 03:43:19 PM PST||0||4.84 GB||1.71 GB||00:24:02||00:20:21|
The backup application performance report for LVM snapshot raw backup shows that the application impact was minimal. Read locks are not obtained during "raw" InnoDB storage engine backups using LVM snapshots.
|dailyrun1||Wed 15 Nov 2006 03:43:19 PM PST||0||4.84 GB||00:24:02||00:00:00||00:00:00|
The same set of backup parameters will not yield optimal backup performance results for all MySQL configurations. It will be useful to tune the backup parameters before putting ZRM for MySQL to production use and watch these parameters over time. The application characteristics can also change over time.