UBC Physics & Astronomy
mysql-zrm Documentation |
root@borg# cat /etc/mysql-zrm/mysql-zrm.conf backup-level=0 backup-mode=logical destination=/tank/SQLbackups retention-policy=10W compress=1 compress-plugin=/usr/bin/gzip all-databases=1 user="dbackup" password="bAck.mE.uP" host="mysql.phas.ubc.ca" mysql-binlog-path="/var/log/mysqlbin" mailto="mysqldba@phas.ubc.ca" mail-policy=always html-reports=backup-status-info, backup-retention-info, backup-performance-info, restore-info, selective-restore-info, replication-info, backup-app-performance-info html-report-directory=/var/www/html/mysql-zrm/reports/ copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
root@borg# cat /etc/mysql-zrm/Omega_Set01/mysql-zrm.conf #=================================================== # Omega_Set01 #=================================================== backup-mode=logical destination=/tank/SQLbackups user="dbackup" password="bAck.mE.uP" host="mysql.phas.ubc.ca" mysql-binlog-path="/var/log/mysqlbin" copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl all-databases=1 verbose=0Scheduled backups:
root@borg# mysql-zrm-scheduler --add -interval weekly --day-of-week 1-6 --start-time 02:00 --backup-level 1 --backup-set Omega_Set01 schedule:INFO: ZRM for MySQL Community Edition - version 2.2.0 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log DONE root@borg# mysql-zrm-scheduler --add -interval weekly --day-of-week 0 --start-time 02:00 --backup-level 0 --backup-set Omega_Set01 schedule:INFO: ZRM for MySQL Community Edition - version 2.2.0 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log DONE root@borg# mysql-zrm-scheduler --query schedule:INFO: ZRM for MySQL Community Edition - version 2.2.0 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log 0 2 * * 1-6 /usr/bin/zrm-pre-scheduler --action backup --backup-set Omega_Set01 --backup-level 1 --interval weekly 0 2 * * 0 /usr/bin/zrm-pre-scheduler --action backup --backup-set Omega_Set01 --backup-level 0 --interval weekly
Restoring a table[root@borg ~]# mysql-zrm-scheduler --query schedule:INFO: ZRM for MySQL Community Edition - version 3.0 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log 45 4 * * 1-5 /usr/bin/zrm-pre-scheduler --action backup --backup-set Omega_Set01 --backup-level 1 --interval weekly 45 4 * * 6 /usr/bin/zrm-pre-scheduler --action backup --backup-set Omega_Set01 --backup-level 0 --interval weekly 45 5 * * 1-5 /usr/bin/zrm-pre-scheduler --action backup --backup-set Delta_Set01 --backup-level 1 --interval weekly 45 5 * * 6 /usr/bin/zrm-pre-scheduler --action backup --backup-set Delta_Set01 --backup-level 0 --interval weekly [root@borg ~]# mysql-zrm-reporter --where backup-set=Omega_Set01 --show backup-status-info
REPORT TYPE : backup-status-info
backup_set backup_date backup_level backup_status backup_type comment
-----------------------------------------------------------------------------------------------------------------------------
Omega_Set01 2014-11-04 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-11-03 04:45:01 1 Backup succeeded regular ----
Omega_Set01 2014-11-01 04:45:02 0 Backup succeeded regular ----
Omega_Set01 2014-10-31 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-10-27 04:45:01 1 Backup succeeded regular ----
Omega_Set01 2014-10-25 04:45:02 0 Backup succeeded regular ----
Omega_Set01 2014-10-24 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-10-20 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-10-18 04:45:02 0 Backup succeeded regular ----
Omega_Set01 2014-10-17 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-10-15 04:45:01 1 Backup succeeded regular ----
Omega_Set01 2014-10-14 04:45:01 1 Backup succeeded regular ----
Omega_Set01 2014-10-13 04:45:01 1 Backup succeeded regular ----
Omega_Set01 2014-10-11 04:45:01 0 Backup succeeded regular ----
Omega_Set01 2014-10-10 04:45:02 1 Backup succeeded regular ----
Omega_Set01 2014-08-27 04:45:01 1 Backup succeeded regular ----
some data lines omitted for brevity
[root@borg ~]# mysql-zrm --action verify-backup --backup-set Omega_Set01 verify-backup:INFO: ZRM for MySQL Community Edition - version 3.0 Omega_Set01:verify-backup:INFO: Verification successful [root@borg ~]# mysql-zrm-reporter --where backup-set=Omega_Set01 --show restore-info
REPORT TYPE : restore-info
backup_set backup_date backup_level backup_directory backup_status comment
--------------------------------------------------------------------------------------------------------------------
Omega_Set01 2014-11-04 04:45:02 1 /tank/SQLbackups/Omega_Set01/20141104044 Backup succeeded ----
502
Omega_Set01 2014-11-03 04:45:01 1 /tank/SQLbackups/Omega_Set01/20141103044 Backup succeeded ----
501
Omega_Set01 2014-11-01 04:45:02 0 /tank/SQLbackups/Omega_Set01/20141101044 Backup succeeded ----
502
Omega_Set01 2014-10-28 04:45:02 1 /tank/SQLbackups/Omega_Set01/20141028044 Backup succeeded ----
502
Omega_Set01 2014-10-27 04:45:01 1 /tank/SQLbackups/Omega_Set01/20141027044 Backup succeeded ----
501
Omega_Set01 2014-10-25 04:45:02 0 /tank/SQLbackups/Omega_Set01/20141025044 Backup succeeded ----
502
Omega_Set01 2014-10-24 04:45:02 1 /tank/SQLbackups/Omega_Set01/20141024044 Backup succeeded ----
502
Omega_Set01 2014-10-20 04:45:02 1 /tank/SQLbackups/Omega_Set01/20141020044 Backup succeeded ----
502
Omega_Set01 2014-10-18 04:45:02 0 /tank/SQLbackups/Omega_Set01/20141018044 Backup succeeded ----
502
Omega_Set01 2014-10-13 04:45:01 1 /tank/SQLbackups/Omega_Set01/20141013044 Backup succeeded ----
501
Omega_Set01 2014-10-11 04:45:01 0 /tank/SQLbackups/Omega_Set01/20141011044 Backup succeeded ----
501
Omega_Set01 2014-10-10 04:45:02 1 /tank/SQLbackups/Omega_Set01/20141010044 Backup succeeded ----
502
some data not shown for brevity
[root@borg ~]# mysql-zrm --action verify-backup --backup-set Omega_Set01 --no-quiet --source-directory /tank/SQLbackups/Omega_Set01/20141014044501 verify-backup:INFO: ZRM for MySQL Community Edition - version 3.0 Omega_Set01:verify-backup:INFO: Verification successful [root@borg ~]# cd /tank/SQLbackups/Omega_Set01/20141014044501 [root@borg]# ll total 78069 -rw-r--r-- 1 root root 79795821 Oct 14 04:45 backup-data -rw-r--r-- 1 root root 663 Oct 14 04:45 index -rw-r--r-- 1 root root 150 Oct 14 04:45 zrm_checksum [root@borg]# mysql-zrm-restore --backup-set Omega_Set01 --source-directory /tank/SQLbackups/Omega_Set01/20141011044501 --databases "casca2013" restore:INFO: ZRM for MySQL Community Edition - version 3.0 Omega_Set01:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular' Omega_Set01:restore:INFO: Restored database(s) from logical backup: casca2013 Omega_Set01:restore:INFO: Restore done in 79 seconds. Alternatively, you can manually import the sql file after extracting it as so: [root@borg]# pwd /tank/SQLbackups/Delta_Set01/20170107054501 [root@borg]# file backup-data backup-data: gzip compressed data, from Unix, last modified: Sat Jan 7 05:45:12 2017 [root@borg]# tar ztvf backup-data drwx------ root/root 0 2017-01-07 05:45 ./ -rw-r--r-- root/root 115921850 2017-01-07 05:45 ./backup.sql |
[root@borg ~]# mysql-zrm-reporter --where backup-set=Omega_Set01 --show backup-performance-info
REPORT TYPE : backup-performance-info
backup_set backup_date backup_level backup_size backup_size_compressed backup_time backup_type compress_encrypt_time
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Omega_Set01 2014-11-04 04:45:02 1 327.64 MB 80.68 MB 00:00:20 regular 00:01:50
Omega_Set01 2014-11-03 04:45:01 1 528.97 MB 131.41 MB 00:00:32 regular 00:03:10
Omega_Set01 2014-11-01 04:45:02 0 4.00 GB 2.41 GB 00:06:27 regular 02:15:55:56
Omega_Set01 2014-10-31 04:45:02 1 272.90 MB 72.53 MB 00:00:17 regular 00:01:40
Omega_Set01 2014-10-30 04:45:02 1 320.27 MB 90.76 MB 00:00:22 regular 00:02:10
Omega_Set01 2014-10-29 04:45:01 1 375.02 MB 90.99 MB 00:00:26 regular 00:02:40
Omega_Set01 2014-10-28 04:45:02 1 377.35 MB 99.67 MB 00:00:22 regular 00:02:00
Omega_Set01 2014-10-27 04:45:01 1 550.56 MB 147.96 MB 00:00:34 regular 00:03:10
Omega_Set01 2014-10-25 04:45:02 0 4.00 GB 2.41 GB 00:06:26 regular 02:15:05:57
Omega_Set01 2014-10-24 04:45:02 1 306.23 MB 83.48 MB 00:00:29 regular 00:01:50
Omega_Set01 2014-10-23 04:45:01 1 317.41 MB 93.94 MB 00:00:21 regular 00:01:50
Omega_Set01 2014-10-22 04:45:01 1 323.67 MB 91.74 MB 00:00:20 regular 00:01:50
Omega_Set01 2014-10-21 04:45:02 1 303.38 MB 77.50 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-10-20 04:45:02 1 545.30 MB 147.06 MB 00:00:38 regular 00:04:00
Omega_Set01 2014-10-18 04:45:02 0 3.99 GB 2.40 GB 00:06:20 regular 02:15:22:32
Omega_Set01 2014-10-17 04:45:02 1 474.16 MB 161.75 MB 00:00:35 regular 00:03:40
Omega_Set01 2014-10-16 04:45:01 1 353.93 MB 102.55 MB 00:00:24 regular 00:02:30
Omega_Set01 2014-10-15 04:45:01 1 305.07 MB 88.25 MB 00:00:26 regular 00:03:00
Omega_Set01 2014-10-14 04:45:01 1 275.62 MB 76.10 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-10-13 04:45:01 1 469.51 MB 120.51 MB 00:00:29 regular 00:02:50
Omega_Set01 2014-10-11 04:45:01 0 3.97 GB 2.40 GB 00:06:17 regular 02:14:32:32
Omega_Set01 2014-10-10 04:45:02 1 272.85 MB 73.58 MB 00:00:17 regular 00:01:40
Omega_Set01 2014-10-09 04:45:02 1 259.31 MB 67.16 MB 00:00:16 regular 00:01:40
Omega_Set01 2014-10-08 04:45:01 1 357.54 MB 74.33 MB 00:00:21 regular 00:01:50
Omega_Set01 2014-10-07 04:45:01 1 254.73 MB 66.42 MB 00:00:17 regular 00:01:40
Omega_Set01 2014-10-06 04:45:02 1 642.46 MB 174.15 MB 00:00:40 regular 00:03:50
Omega_Set01 2014-10-04 04:45:02 0 3.97 GB 2.39 GB 00:06:38 regular 02:19:49:13
Omega_Set01 2014-10-03 04:45:02 1 251.66 MB 64.35 MB 00:00:16 regular 00:01:30
Omega_Set01 2014-10-02 04:45:02 1 280.79 MB 80.86 MB 00:00:18 regular 00:01:40
Omega_Set01 2014-10-01 04:45:02 1 258.35 MB 70.40 MB 00:00:16 regular 00:01:30
Omega_Set01 2014-09-30 04:45:02 1 276.69 MB 78.06 MB 00:00:20 regular 00:02:00
Omega_Set01 2014-09-29 04:45:02 1 469.00 MB 129.19 MB 00:00:30 regular 00:02:50
Omega_Set01 2014-09-27 04:45:02 0 3.96 GB 2.39 GB 00:06:56 regular 02:23:25:59
Omega_Set01 2014-09-26 04:45:01 1 292.25 MB 72.93 MB 00:00:19 regular 00:01:40
Omega_Set01 2014-09-25 04:45:02 1 280.84 MB 66.40 MB 00:00:18 regular 00:01:50
Omega_Set01 2014-09-24 04:45:02 1 325.74 MB 71.99 MB 00:00:23 regular 00:02:20
Omega_Set01 2014-09-23 04:45:01 1 267.16 MB 66.01 MB 00:00:18 regular 00:01:50
Omega_Set01 2014-09-22 04:45:01 1 537.67 MB 123.74 MB 00:00:35 regular 00:03:40
Omega_Set01 2014-09-20 04:45:02 0 3.96 GB 2.39 GB 00:05:33 regular 02:15:55:03
Omega_Set01 2014-09-19 04:45:02 1 256.16 MB 65.57 MB 00:00:17 regular 00:01:40
Omega_Set01 2014-09-18 04:45:03 1 259.51 MB 72.23 MB 00:00:16 regular 00:01:30
Omega_Set01 2014-09-17 04:45:02 1 303.27 MB 90.68 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-09-16 04:45:03 1 286.70 MB 81.41 MB 00:00:19 regular 00:01:40
Omega_Set01 2014-09-15 04:45:02 1 547.31 MB 148.68 MB 00:00:32 regular 00:03:10
Omega_Set01 2014-09-13 04:45:02 0 3.94 GB 2.38 GB 00:05:29 regular 06:28:17
Omega_Set01 2014-09-12 04:45:02 1 321.68 MB 78.43 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-09-11 04:45:02 1 260.47 MB 64.95 MB 00:00:18 regular 00:01:50
Omega_Set01 2014-09-10 04:45:02 1 314.66 MB 75.51 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-09-09 04:45:02 1 328.25 MB 82.06 MB 00:00:19 regular 00:01:50
Omega_Set01 2014-09-08 04:45:01 1 478.25 MB 120.81 MB 00:00:31 regular 00:03:00
Omega_Set01 2014-09-06 04:45:02 0 3.93 GB 2.37 GB 00:05:33 regular 02:15:38:23
Omega_Set01 2014-09-05 04:45:02 1 270.69 MB 74.09 MB 00:00:18 regular 00:01:40
Omega_Set01 2014-09-04 04:45:02 1 296.90 MB 74.90 MB 00:00:20 regular 00:02:00
Omega_Set01 2014-09-03 04:45:01 1 287.65 MB 71.84 MB 00:00:21 regular 00:02:00
Omega_Set01 2014-09-02 04:45:02 1 257.21 MB 65.91 MB 00:00:17 regular 00:01:30
Omega_Set01 2014-09-01 04:45:01 1 487.45 MB 125.98 MB 00:00:29 regular 00:02:50
Omega_Set01 2014-08-30 04:45:02 0 3.89 GB 2.34 GB 00:05:31 regular 02:12:51:41
Omega_Set01 2014-08-29 04:45:02 1 0.00 MB 0.00 MB 00:00:01 regular 00:00:00
backup_set backup_date backup_level backup_size backup_size_compressed backup_time backup_type compress_encrypt_time
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Omega_Set01 2014-08-28 04:45:02 1 263.00 MB 72.59 MB 00:00:19 regular 00:02:00
Omega_Set01 2014-08-27 04:45:01 1 301.39 MB 84.85 MB 00:00:19 regular 00:01:50
| MySQL Backups Using Zmanda (ZRM for MySQL Community Edition - version 1.2.1) |
|
The MySQL databases are backed up nightly (see crontab -l output below).
Configuration data is kept in files in /etc/mysql-zrm. Backups are kept in /home/backups/omega/var/lib/mysql-zrm Logs are kept in /var/log/mysql-zrm To run a backup immediately: mysql-zrm-scheduler --now --backup-set daily01 [daily01,daily02,,weekly,monthly] To Schedule a (daily at 1am) backup: mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-level 0 --backup-set daily Verification that scheduler has been configured: mysql-zrm-scheduler --query Backup Reports:
mysql-zrm-reporter --help (to see all options)
mysql-zrm-reporter --destination /home/backups/omega/var/lib/mysql-zrm/ --where backup-set=daily02 --show selective-restore-info
[--show <backup-method-info | backup-status-info | backup-retention-info |
backup-performance-info | restore-info | selective-restore-info |
replication-info | backup-app-performance-info
mysql-zrm-reporter --destination /home/backups/omega/var/lib/mysql-zrm/ --where backup-set=monthly01 --show backup-performance-info
mysql-zrm-reporter --destination /home/backups/omega/var/lib/mysql-zrm/ --where backup-set=monthly --show restore-info
mysql-zrm-reporter --destination /home/backups/omega/var/lib/mysql-zrm/ --where backup-set=daily --show selective-restore-info
Restoring Databases:
|