Finally, Can I do MySQL backups?
Yes!!! All configuration is complete. You can do backups now or schedule backups for a later time.
Backup now
MySQL ZRM scheduler tool can be used to perform backups immediately. The following command does a full backup for backup set dailyrun:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
Backup now option of mysql-zrm-scheduler should be used after MySQL ZRM configuration changes. The backup now option also checks the MySQL ZRM configuration before doing the backup.
If mailto parameter is configured for the backup set, following email will be sent to the administrator.
backup-set=dailyrun backup-date=20060828102505 mysql-version=4.1.12 backup-directory=/var/lib/mysql/dailyrun/20060828102505 backup-level=0 raw-databases=cdcol mysql phpmyadmin test backup-time=400 seconds. backup-size=0.29 MB last-backup=/var/lib/mysql/dailyrun/20060828101657 backup-status=Backup succeeded
Incremental backups
If backup level is set to 1 in mysql-zrm-scheduler command line or in mysql-zrm.conf configuration file, incremental backup is performed. Incremental backups are performed with respect to prior full backup or incremental backup for the backup set. Binary logging should be enabled in the MySQL server. Incremental backups uses binary logging and the binary log backups are not restricted to the list of databases or tables specified in the backup set. MySQL binary logs contain all database events executed by the MySQL server. Incremental backups of remote MySQL server is not supported.
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1
Scheduling backup runs
The mysql-zrm-scheduler tool can schedule daily, weekly, monthly backup runs for a backup set. You can also query the scheduled backup runs as well as delete a scheduled run.
If the backup run start time is not specified, start time of 2am is assumed for daily runs, 3am for weekly runs and 12:00am is assumed for monthly runs. The start time is based on date and time on the machine where MySQL ZRM is running. By default, weekly backup runs are done on Sunday and Monthly backup runs are done on the first of the month.
The mysql-zrm-scheduler tool also purges MySQL backups based on backup retention policy every day at 4am.
Examples on how to use mysql-zrm-scheduler:
- To schedule a daily incremental backup run at 1:35pm:
# mysql-zrm-scheduler --add --interval daily --start 13:35 --backup-level 1
- To schedule a weekly full backup run for backup set BackupSet1 on Tuesday and Wednesday of the week:
# mysql-zrm-scheduler --add --interval weekly --day-of-week 2-3
- To schedule a monthly backup run to start on the tenth day of each month:
#mysql-zrm-scheduler --add --interval monthly --day-of-month 10
- To display scheduled backup runs:
# mysql-zrm-scheduler --query 0 2 * * 2-3 /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1 --interval weekly 35 13 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1 --backup-level 1 --interval daily 0 0 10 * * /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1--interval monthly # comment: purging expired backup files at 4am daily 0 4 * * * /usr/bin/mysql-zrm --action purge
- To delete a daily backup run which has specific start time from schedule:
# mysql-zrm-scheduler --delete --interval daily --start 13:35
- To delete the weekly backup run from the schedule for the backup set BackupSet1:
# mysql-zrm-scheduler --delete --interval weekly --day-of-week 2-3
- To schedule a backup run for backup set dailyrun now:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
<br\>
To change backup run schedule:
- Run mysql-zrm-scheduler --query to list the current scheduled backup runs.
- Run mysql-zrm-scheduler --delete .. to remove the scheduled run.
Checking status of backup run
The mysql-zrm-reporter tool provides information on status on each backup run. The status can be
- Backup succeeded
- Backup done with errors
- Backup failed
MySQL ZRM logs can be used to figure out why the backup failed or why the errors occurred. The email notification also contains information on the status of the backup run.
Following example shows how to mysql-zrm-reporter to find status of backup runs for the backup set "backupSet3":
# mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-status \
--where backup-set=backupSet3
backup_set backup_date backup_level backup_status
------------------------------------------------------------------------------------------------
backupSet3 Tue 29 Aug 2006 02:07:39 PM PDT 0 Backup done but with
errors
backupSet3 Tue 29 Aug 2006 02:08:36 PM PDT 0 Backup succeeded
backupSet3 Tue 29 Aug 2006 02:10:29 PM PDT 0 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:18:03 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:21:07 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:23:50 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:25:00 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:26:50 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 04:31:09 PM PDT 1 Backup succeeded
backupSet3 Tue 29 Aug 2006 05:45:32 PM PDT 0 Backup succeeded
backupSet3 Tue 29 Aug 2006 05:46:35 PM PDT 0 Backup succeeded
backupSet3 Tue 29 Aug 2006 05:48:33 PM PDT 0 Backup succeeded
Email notification
The mysql-zrm tool sends email to MySQL database administrator after every backup run. The email contains information about the status of backup run, backup statistics, location of backup and backup level.
mailto parameter in mysql-zrm.conf configuration file should be configured to get email notification.
The machine where MySQL ZRM is running should be configured for sending emails. All backup report email messages from MySQL ZRM have [ZRM for MySQL Report] in the email subject for easy filtering of mails.
Example: Mail message sent after successful full backup run.
Subject: [ZRM for MySQL Report] backup-set BackupSet1 Backup set=BackupSet1 Backup date=Fri Sep 15 10:39:01 2006 Backup level=0 Raw Databases(Snapshot)=wikidb Raw Databases=mydb mysql Logical Databases=forumsdb Backup size=0.62 MB Backup time=00:00:05 Backup status=Backup succeeded
Verification of backup images
MySQL ZRM has the capability of verifying the backup images. It verifies the consistency of backup using backup checksums. It is a recommended practice to verify the newly created backup image after a backup run. Best practice is to verify the backup images for all backup sets on a regular basis.
The following command verifies the backup images of the last backup run for the backup set backup:
# mysql-zrm --action verify-backup --backup-set backup
The following command verifies the specific backup run of backup set backup:
# mysql-zrm --action verify-backup --backup-set backup --no-quiet\ --source-directory /var/lib/mysql-zrm/backup/20060829093000 INFO: Verification successful
mysql-zrm returns 0 if the backup verification is successful.
Example: Verifying all backup images. Backup set BackupSet1 backup on "12 Jul 2006" has verification failures.
# mysql-zrm --action verify-backup --no-quiet ERROR: checksum for file /var/lib/mysql-zrm/BackupSet1/20060712140704/backup.sql does not match ERROR: Errors found during verification