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 runs as well as delete a scheduled run. If 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.
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 weekly full backup run for backup set BackupSet1
# mysql-zrm-scheduler --add --interval weekly
- To schedule a daily incremental backup run at 1:35pm:
# mysql-zrm-scheduler --add --interval daily --start 13:35 --backup-level 1
- To display scheduled backup runs:
# mysql-zrm-scheduler --query 0 2 * * 0 /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1 35 13 * * * /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1 --backup-level 1 0 4 * * * /usr/bin/mysql-zrm --action purge --destination /var/lib/mysql-zrm # purging expired backup files at 4am daily
- To delete a weekly backup run from the schedule for the backup set BackupSet1:
# mysql-zrm-scheduler --delete --interval weekly
- To delete a weekly backup run which has specific start time from schedule:
# mysql-zrm-scheduler --delete --interval weekly --start 08:20
- 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
Backup failed. What do I do?
MySQL ZRM logs are available under /var/log/mysql-zrm directory on the machine where MySQL ZRM is running. All backup and recovery operations, status of these operations are logged. These logs can be used for debugging in case of failures as well as auditing backup/recovery database operations.
mysql-zrm-scheduler and mysql-zrm-reporter also have log files in the same directory.
Example: mysql-zrm log file entries from a restore of incremental backup:
Wed Aug 30 02:39:48 2006: INFO: mysql-zrm started
All the MySQL backup runs starts with "mysql-zrm started" message.
Wed Aug 30 02:39:48 2006: INFO: action being performed is restore Wed Aug 30 02:39:48 2006: INFO: backup set being used is backup
Above lines show the list of command line parameters to mysql-zrm
Wed Aug 30 02:39:48 2006: INFO: Reading options from file /etc/mysql-zrm/backup/mysql-zrm.conf Wed Aug 30 02:39:48 2006: INFO: Mail address: root is ok Wed Aug 30 02:39:48 2006: INFO: Input Parameters Used { Wed Aug 30 02:39:48 2006: INFO: verbose=1 Wed Aug 30 02:39:48 2006: INFO: backup-level=1 Wed Aug 30 02:39:48 2006: INFO: mailto=root Wed Aug 30 02:39:48 2006: INFO: destination=/mysql-zrm Wed Aug 30 02:39:48 2006: INFO: databases=wikidb Wed Aug 30 02:39:48 2006: INFO: source-directory=/mysql-zrm/backup/20060830020843 Wed Aug 30 02:39:48 2006: INFO: host=localhost Wed Aug 30 02:39:48 2006: INFO: database=wikidb Wed Aug 30 02:39:48 2006: INFO: backup-mode=raw Wed Aug 30 02:39:48 2006: INFO: password=****** Wed Aug 30 02:39:48 2006: INFO: user=root Wed Aug 30 02:39:48 2006: INFO: stop-position=5003 Wed Aug 30 02:39:48 2006: INFO: }
The above lines show the list of parameters used for restore operation
Wed Aug 30 02:39:48 2006: INFO: Getting the data directory Wed Aug 30 02:39:48 2006: INFO: mysqladmin --user=root --password=***** --host=localhost variables 2> /tmp/mysql-zrm-20060830023948.out Wed Aug 30 02:39:48 2006: INFO: datadir is /var/lib/mysql/ Wed Aug 30 02:39:48 2006: INFO: mysql_version is 4.1.21-standard-log Wed Aug 30 02:39:48 2006: INFO: Checking if this is a replication slave using command Wed Aug 30 02:39:48 2006: INFO: echo "show slave status"|mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: This is not a replication slave or we do not have appropriate access rights. replication data if any has not been backed up. Wed Aug 30 02:39:48 2006: INFO: Ignoring the --replication option
The above lines show validation of backup parameters
Wed Aug 30 02:39:48 2006: INFO: Restoring incremental Wed Aug 30 02:39:48 2006: INFO: mysqlbinlog --user=root --password=***** --host=localhost --stop-position=5003 --database=wikidb "/mysql-zrm/backup/20060830020843"/vmsql4-bin.[0-9]* | mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: Incremental restore done
Incremental restoration is complete
Wed Aug 30 02:39:48 2006: INFO: for database wikidb Wed Aug 30 02:39:48 2006: INFO: Wed Aug 30 02:39:48 2006: INFO: Shutting down MySQL Wed Aug 30 02:39:53 2006: INFO: Restore done in 5 seconds.
MySQL server is shutdown after database restoration.