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


To change backup run schedule:

  1. Run mysql-zrm-scheduler --query to list the current scheduled backup runs.
  2. 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