How to Backup
When planning how to backup, there are two aspects to look at. One is the method to be used for backup and the other is the backup level that can be used to tune how much data gets backed up each time.
Backups occupy hard disk space. While they are being run, the application performance suffers. Network bandwidth can also be cramped. It is important that the method used and level of Backups are optimised to ensure easy recovery of data along with least degradation of application performance. Full backups take the most time and cause the maximum disruption to users. Incremental backups occupy much less space and has minimum impact on users. Similarly the backup method used also has major effect on the impact of backups on the database users.
ZRM for MySQL provides two methods to backup the database, namely logical and raw. The mode to be used can be specified using the parameter backup-mode. Please refer to Backup Set Parameters for more details.
Logical backups of a database contain set of SQL statements that can be used to recreate the databases. All storage engines can be backed up using the logical backup method. When this is specified, all of the databases are backed up using mysqldump command. To specify that the backup mode to be used must be logical, add the following parameter to the mysql-zrm.conf.
You can also use mydumper - multi-thread logical backup command. You can select mydumper by specifying
- This backup method works for all MySQL storage engines (except MySQL cluster NDB storage engine)
- The backup image can be restored to any platform architecture or another database. For example: Backups of MySQL database running on Redhat Enterprise server powerpc platform can be restored to Ubuntu server running on x86 platform.
- The logical backup requires ZRM for MySQL to obtain a read lock on the database(s) or tables being backed up or run as a single transaction. As a result, the backup will have an impact on the applications using the MySQL database.
- Both backup and restoration of the database can be slow compared to other backup methods.
- Backup size can be larger than the actual database(s) or tables being backed up.
Raw backups contain binary copies of the data being backed up. When the backup mode is specified as raw, ZRM for MySQL will choose the optimal backup method to use for the backup set. To specify that the backup mode to be used must be raw, add the following line in the mysql-zrm.conf confoiguration file.
If snapshot-plugin is specified, it will use snapshots to backup the database, if the database and all its related data are on volumes that can be snapshot.
If snapshot-plugin is not specified or if there is a backup failure,
- If all of the tables in the database use non transactional storage engines, then mysqlhotcopy command is used to backup the database.
- If any of the tables in the database uses transactional storage engines then mysqldump is used to backup the database.
Under all backup methods, suitable precautions are taken to obtain a consistent copy of the database.
- Backup window will be smaller than logical backups.
- Size of the backup is same as the size of data being backed up.
- Raw backup method scales better with database size compared to logical backups.
- Under some circumstances, the read locks have to be obtained on the database. If snapshots are being used to get consistent copy, the application interruption is minimal.
- Backup set can be restored only to same version of MySQL server on the same platform architecture.
ZRM for MySQL allows you to schedule a mix of full and incremental backups as is suitable for your users and your environment. It makes it convenient for you to schedule by using almost identical commands for both type of backups. Note that the only difference between the command for full backup and incremental backup is the backup-level specified. Specify 0 for full backup and 1 for incremental backup.
Full backups cover all data present in the databases specified.
The following command will fire an immediate full backup of backup set dailyrun :
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
Incremental backups cover changed data since the last backup.
Incremental backup are done when backup level is set to 1 on the mysql-zrm-scheduler command line or in the mysql-zrm.conf configuration file. Additionally, Binary logging should be enabled in the MySQL server. MySQL binary logs contain all database events executed by the MySQL server.
The following command will fire an immediate incremental backup of backup set dailyrun :
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1
The default location where ZRM for MySQL will look for binary logs is /var/lib/mysql. If a different location is used for storing the binary logs the the mysql-binlog-path needs to be specified in the configuration file. For example
Quick and Regular Backups
ZRM can treat a snapshot as a backup. This requires MySQL server data and logs to reside on a storage volumes that can be snapshotted such as LVM or Solaris ZFS. The backup is referred to as "quick". Normal backups to ZRM server is referred to as "regular".
"Quick" ZRM backups can be done frequently and are always full backups. The backup snapshots can be mounted using mysql-zrm-manage-backup command. "Quick" snapshots are inexpensive and do not consume network bandwidth (no transfer of images to server). But if the machine running MySQL server fails, the quick backups are also lost. It is important to convert "Quick" backups to "Regular" backups. The mysql-zrm-manage-backup command can be used to convert "quick" backups to "regular" backups.
Use mysql-zrm-reporter command to find out where the "quick" backup information is stored.
$ /usr/bin/mysql-zrm-reporter --where backup-set=zfstest --show restore-info
REPORT TYPE : restore-info backup_set backup_date backup_level backup_directory backup_status comment ----------------------------------------------------------------------------------------------------------------------------------------------------- zfstest Sun Nov 16 03:08:01 2008 0 /var/lib/mysql-zrm/zfstest/20081116030801 Backup succeeded ----
Convert the quick backup to regular backup using mysql-zrm-manage-backup command.
$ /usr/bin/mysql-zrm-manage-backup --convert-to-regular --backup-set zfstest --source-directory /var/lib/mysql-zrm/zfstest/20081116025111