Backup Mysql by [ZRM] for MySQL

2019-05-10 11:17:23
install zrm backup
1
2
yum install epel-release -y
yum install MySQL-zrm.noarch -y
create mysql user account privileges
1
grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to 'backupuser@'%' identified by 'secret password';
config zrm backup
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
cd /etc/mysql-zrm/
mkdir dailyrun
cp mysql-zrm.conf dailyrun
cd dailyrun
cat mysql-zrm.conf

backup-mode=logical
destination=/var/lib/mysql-zrm  //backup directory, make sure disk large enough
retention-policy=180D
compress=1
compress-plugin=/usr/bin/gzip
databases=wikidb forums
user="backupuser"
password="password"
host="192.168.10.1"
port=3306
default-character-set=utf8
mysql-binpath="/usr/local/mysql/bin"
mailto="user@company.com.cn"   // make user postfix service start 
mail-policy=only-on-error
Perform a Backup
1
2
3
4
5
6
7
8
9
#backup now Verification of Backup Images
mysql-zrm --action verify-backup --backup-set dailyrun
mysql-zrm-scheduler --now --backup-set dailyrun     

#add backup task to schedule, for example, backup database every day 1am
mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-level 0 --backup-set dailyrun

#query backup schedule info
mysql-zrm-scheduler --query
Backup Reports
1
2
3
4
mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info
mysql-zrm-reporter --where backup-set=dailyrun --show backup-performance-info
# Verification of Backup Images
mysql-zrm --action verify-backup --backup-set dailyrun
Perform a full restoration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#example for restore database
[root@localhost dailyrun]# mysql-zrm-reporter --show restore-info --where backup-set=dailyrun

REPORT TYPE : restore-info 

          backup_set  backup_date                  backup_level  backup_directory                          backup_status         comment
-----------------------------------------------------------------------------------------------------------------------------------------------------
            dailyrun  Fri 10 May 2019 09:48:58                0  /var/lib/mysql-zrm/dailyrun/201905100948  Backup succeeded      ----
                      AM CST                                     58

[root@localhost dailyrun]# mysql-zrm --action restore --backup-set dailyrun --source-directory /var/lib/mysql-zrm/dailyrun/20190510094858/
restore:INFO: ZRM for MySQL Community Edition - version 3.0
dailyrun:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
dailyrun:restore:WARNING: Binary logging is off.
Warning: Using a password on the command line interface can be insecure.
dailyrun:restore:INFO: Restored database(s) from logical backup:  wikidb forums
dailyrun:restore:INFO: Restore done in 3 seconds.

ref
mysql-zrm
[ZRM] for MySQL
MySQL-ZRM and BackupPC – CentOS 7