Regular consistent backups of the 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 for databases, number of MySQL servers, MySQL configuration, level of security required.
ZRM for MySQL provides 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 the 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 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, backup size was obtained from 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 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 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.
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.
backup-level=0 backup-mode=logical
To reduce the size of backup, we can use backup compression using gzip program (default compression method). To do backup compression, following parameter was added to mysql-zrm.conf.
compress=1
To improve security of the backup images, the backup image is encrypted using 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. 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"
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 the configuration 3 were removed. To do "raw" backups with backup compression, add the following to mysql-zrm.conf to the mysql-zrm configuration file from the configuration 2:
backup-mode=raw lvm-snapshot=100M
The following table shows the comparison of backup measurements for various configurations:
Configuration | Backup size | Backup size(compressed) | Backup time | Verification time | Restoration time |
---|---|---|---|---|---|
1 (default) | 2.99GB | - | 1260s | 90s | 3551s |
2 (compression) | 2.99GB | 681.15MB | 1345s | 285s | 3723s |
3 (compression + encryption) | 2.99GB | 682.90MB | 1296s | 328s | 3713s |
4 (compression + raw backups using lvm snapshot) | 4.84GB | 1.71GB | 1442s | 236s | 276s |
Eventhough, 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.
backup_set | backup_date | backup_level | backup_size | backup_size_compressed | backup_time | compress_encrypt_time |
---|---|---|---|---|---|---|
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.
backup_set | backup_date | backup_level | backup_size | backup_time | read_locks_time | flush_logs_time |
---|---|---|---|---|---|---|
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.