linux zrm数据库备份,利用ZRM(lvm+binlog方式)进行数据库备份及还原

最新推荐文章于 2022-07-17 16:00:58 发布
最新推荐文章于 2022-07-17 16:00:58 发布
阅读量284 收藏
点赞数
文章标签： linux zrm数据库备份

安装ZRM：

[root@master ~]# yum -y install perl-DBI perl-DBD-MySQL perl-XML-Parser

[root@master ~]# yum localinstall MySQL-zrm-3.0-1.noarch.rpm

[root@master ~]# ls /etc/mysql-zrm/

mysql-zrm.conf mysql-zrm-release mysql-zrm-reporter.conf RSS.header

[root@master mysql-zrm]# ln -s /usr/local/mysql/bin/* /usr/bin/

创建备份用户并授权

mysql> grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to 'backup-user'@'localhost' identified by 'redhat';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

创建备份目录

[root@master ~]# mkdir /backup/zrm -pv

[root@master mysql-zrm]# mkdir full

修改配置文件：

[root@master full]# egrep -v '^#|^$' mysql-zrm.conf

backup-level=0

backup-mode=raw

lvm-snapshot=100M

snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl"

backup-type=regular

destination=/backup/zrm

retention-policy=1W

compress=1

compress-plugin=/usr/bin/gzip

all-databases=1

user="backup-user"

password="redhat"

host="localhost"

socket=/tmp/mysql.sock

mysql-binlog-path="/mydata/data"

mailto="mysqldba@sanyu.com"

手工执行备份：

[root@master mysql-zrm]# mysql-zrm-scheduler --now --backup-set full

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

backup:INFO: ZRM for MySQL Community Edition - version 3.0

full:backup:INFO: START OF BACKUP

full:backup:INFO: PHASE START: Initialization

full:backup:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

full:backup:INFO: backup-set=full

full:backup:INFO: backup-date=20130827000610

full:backup:INFO: mysql-server-os=Linux/Unix

full:backup:INFO: backup-type=regular

full:backup:INFO: host=localhost

full:backup:INFO: backup-date-epoch=1377533170

full:backup:INFO: retention-policy=1W

full:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 3.0

full:backup:INFO: mysql-version=5.5.28-log

full:backup:INFO: backup-directory=/backup/zrm/full/20130827000610

full:backup:INFO: backup-level=0

full:backup:INFO: backup-mode=raw

full:backup:INFO: PHASE END: Initialization

full:backup:INFO: PHASE START: Running pre backup plugin

full:backup:INFO: PHASE END: Running pre backup plugin

full:backup:INFO: PHASE START: Flushing logs

full:backup:INFO: PHASE END: Flushing logs

full:backup:INFO: PHASE START: Creating snapshot based backup

full:backup:INFO: File Position Binlog_Do_DB Binlog_Ignore_DB

mysql-bin.000019 107

full:backup:INFO: innodb-data=/mydata/data/ibdata1;

full:backup:INFO: innodb-logs=/mydata/data/./ib_logfile*

full:backup:INFO: raw-databases-snapshot=test shop mysql performance_schema bbs

full:backup:INFO: PHASE END: Creating snapshot based backup

full:backup:INFO: PHASE START: Calculating backup size & checksums

full:backup:INFO: next-binlog=mysql-bin.000019

full:backup:INFO: backup-size=35.41 MB

full:backup:INFO: PHASE END: Calculating backup size & checksums

full:backup:INFO: PHASE START: Compression/Encryption

full:backup:INFO: compress=/usr/bin/gzip

full:backup:INFO: backup-size-compressed=1.60 MB

full:backup:INFO: PHASE END: Compression/Encryption

full:backup:INFO: read-locks-time=00:00:02

full:backup:INFO: flush-logs-time=00:00:01

full:backup:INFO: compress-encrypt-time=00:00:20

full:backup:INFO: backup-time=00:00:15

full:backup:INFO: backup-status=Backup succeeded

full:backup:INFO: Backup succeeded

full:backup:INFO: PHASE START: Running post backup plugin

full:backup:INFO: PHASE END: Running post backup plugin

full:backup:INFO: PHASE START: Mailing backup report

full:backup:INFO: PHASE END: Mailing backup report

full:backup:INFO: PHASE START: Cleanup

full:backup:INFO: PHASE END: Cleanup

full:backup:INFO: END OF BACKUP

/usr/bin/mysql-zrm started successfully

备份过程使用了逻辑卷快照功能

root@master ~]# df -Th

Filesystem Type Size Used Avail Use% Mounted on

/dev/mapper/mydata-mydata

ext4 5.0G 182M 4.5G 4% /mydata

/dev/mapper/mydata-zrmQGzbaZ6jWo

ext4 5.0G 182M 4.5G 4% /backup/zrm/full/20130827000610/ZRM_MOUNTS/zrmQGzbaZ6jWo

[root@master mysql-zrm]# ll -h full/

total 4.0K

-rw-r--r-- 1 root root 32 Aug 27 00:06 last_backup

指向备份集所在的目录

[root@master mysql-zrm]# cat full/last_backup

/backup/zrm/full/20130827000610

[root@master mysql-zrm]# ll -h /backup/zrm/full/20130827000610/

total 1.7M

#启用了压缩功能的缘故

-rw-r--r-- 1 root root 1.6M Aug 27 00:06 backup-data

-rw-r--r-- 1 root root 680 Aug 27 00:06 index

-rw-r--r-- 1 root root 126 Aug 27 00:06 zrm_checksum

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bbs |

| mysql |

| performance_schema |

| shop |

| test |

+--------------------+

6 rows in set (0.00 sec)

插入一条记录

mysql> insert into shop.ecs_users(user_name,last_time) values('week0',now());

Query OK, 1 row affected, 7 warnings (0.00 sec)

mysql> select user_id,user_name,last_time from shop.ecs_users;

+---------+-----------+---------------------+

| user_id | user_name | last_time |

+---------+-----------+---------------------+

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 6 | week0 | 2013-08-27 00:15:21 |

+---------+-----------+---------------------+

5 rows in set (0.00 sec)

mysql> drop database shop;

Query OK, 88 rows affected (0.13 sec)

还原(备份后发生的数据改变需要用二进制日志恢复)：

[root@master mysql-zrm]# mysql-zrm-reporter --show restore-info --where backup-set=full

REPORT TYPE : restore-info

backup_set backup_date backup_level backup_directory backup_status comment

-----------------------------------------------------------------------------------------------------------------------------------------------------

full Tue 27 Aug 2013 12:06:10 0 /backup/zrm/full/20130827000610 Backup succeeded ----

AM CST

校验备份集

[root@master mysql-zrm]# mysql-zrm --action verify-backup --backup-set full

verify-backup:INFO: ZRM for MySQL Community Edition - version 3.0

full:verify-backup:INFO: Verification successful

还原

root@master mysql-zrm]# mysql-zrm --action restore --backup-set full --source-directory /backup/zrm/full/20130827000610

restore:INFO: ZRM for MySQL Community Edition - version 3.0

dailyrun:restore:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

dailyrun:restore:INFO: Restored innodb log '/mydata/data/ib_logfile0'

dailyrun:restore:INFO: Restored innodb log '/mydata/data/ib_logfile1'

dailyrun:restore:INFO: Restored innodb data file '/mydata/data/ibdata1'

dailyrun:restore:INFO: Restored database from raw backup: test

dailyrun:restore:INFO: Restored database from raw backup: shop

dailyrun:restore:INFO: Restored database from raw backup: mysql

dailyrun:restore:INFO: Restored database from raw backup: performance_schema

dailyrun:restore:INFO: Restored database from raw backup: bbs

dailyrun:restore:INFO: Restore done in 31 seconds.

MySQL server has been shutdown. Please restart after verification.

[root@master ~]# service mysqld start

Starting MySQL... SUCCESS!

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bbs |

| mysql |

| performance_schema |

| shop |

| test |

+--------------------+

6 rows in set (0.00 sec)

mysql> use shop

Database changed

mysql> select user_id,user_name,last_time from shop.ecs_users;

+---------+-----------+---------------------+

| user_id | user_name | last_time |

+---------+-----------+---------------------+

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

+---------+-----------+---------------------+

4 rows in set (0.00 sec)

记录中不含week0

增量备份

插入一条记录

mysql> insert into shop.ecs_users(user_name,last_time) values('week0',now());

Query OK, 1 row affected, 7 warnings (0.03 sec)

mysql> select user_id,user_name,last_time from shop.ecs_users;

+---------+-----------+---------------------+

| user_id | user_name | last_time |

+---------+-----------+---------------------+

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 6 | week0 | 2013-08-27 01:11:39 |

+---------+-----------+---------------------+

5 rows in set (0.00 sec)

全备

[root@master mysql-zrm]# mkdir mybak-`date +%F`

[root@master mysql-zrm]# cp mysql-zrm.conf mybak-`date +%F`

[root@master mysql-zrm]# mysql-zrm-scheduler --now --backup-set mybak-`date +%F`

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

backup:INFO: ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:backup:INFO: START OF BACKUP

mybak-2013-08-27:backup:INFO: PHASE START: Initialization

mybak-2013-08-27:backup:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

mybak-2013-08-27:backup:INFO: backup-set=mybak-2013-08-27

mybak-2013-08-27:backup:INFO: backup-date=20130827012922

mybak-2013-08-27:backup:INFO: mysql-server-os=Linux/Unix

mybak-2013-08-27:backup:INFO: backup-type=regular

mybak-2013-08-27:backup:INFO: host=localhost

mybak-2013-08-27:backup:INFO: backup-date-epoch=1377538162

mybak-2013-08-27:backup:INFO: retention-policy=1W

mybak-2013-08-27:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:backup:INFO: mysql-version=5.5.28-log

mybak-2013-08-27:backup:INFO: backup-directory=/backup/zrm/mybak-2013-08-27/20130827012922

mybak-2013-08-27:backup:INFO: backup-level=0

mybak-2013-08-27:backup:INFO: backup-mode=raw

mybak-2013-08-27:backup:INFO: PHASE END: Initialization

mybak-2013-08-27:backup:INFO: PHASE START: Running pre backup plugin

mybak-2013-08-27:backup:INFO: PHASE END: Running pre backup plugin

mybak-2013-08-27:backup:INFO: PHASE START: Flushing logs

mybak-2013-08-27:backup:INFO: PHASE END: Flushing logs

mybak-2013-08-27:backup:INFO: PHASE START: Creating snapshot based backup

mybak-2013-08-27:backup:INFO: File Position Binlog_Do_DB Binlog_Ignore_DB

mysql-bin.000026 107

mybak-2013-08-27:backup:INFO: innodb-data=/mydata/data/ibdata1;

mybak-2013-08-27:backup:INFO: innodb-logs=/mydata/data/./ib_logfile*

mybak-2013-08-27:backup:INFO: raw-databases-snapshot=test shop mysql performance_schema bbs

mybak-2013-08-27:backup:INFO: PHASE END: Creating snapshot based backup

mybak-2013-08-27:backup:INFO: PHASE START: Calculating backup size & checksums

mybak-2013-08-27:backup:INFO: next-binlog=mysql-bin.000026

mybak-2013-08-27:backup:INFO: backup-size=35.41 MB

mybak-2013-08-27:backup:INFO: PHASE END: Calculating backup size & checksums

mybak-2013-08-27:backup:INFO: PHASE START: Compression/Encryption

mybak-2013-08-27:backup:INFO: compress=/usr/bin/gzip

mybak-2013-08-27:backup:INFO: backup-size-compressed=1.60 MB

mybak-2013-08-27:backup:INFO: PHASE END: Compression/Encryption

mybak-2013-08-27:backup:INFO: read-locks-time=00:00:01

mybak-2013-08-27:backup:INFO: flush-logs-time=00:00:01

mybak-2013-08-27:backup:INFO: compress-encrypt-time=00:00:20

mybak-2013-08-27:backup:INFO: backup-time=00:00:09

mybak-2013-08-27:backup:INFO: backup-status=Backup succeeded

mybak-2013-08-27:backup:INFO: Backup succeeded

mybak-2013-08-27:backup:INFO: PHASE START: Running post backup plugin

mybak-2013-08-27:backup:INFO: PHASE END: Running post backup plugin

mybak-2013-08-27:backup:INFO: PHASE START: Mailing backup report

mybak-2013-08-27:backup:INFO: PHASE END: Mailing backup report

mybak-2013-08-27:backup:INFO: PHASE START: Cleanup

mybak-2013-08-27:backup:INFO: PHASE END: Cleanup

mybak-2013-08-27:backup:INFO: END OF BACKUP

/usr/bin/mysql-zrm started successfully

[root@master ~]# du /backup/zrm/mybak-`date +%F`/* -sh

1.7M /backup/zrm/mybak-2013-08-27/20130827012922

执行增量备份：

[root@master mysql-zrm]# mysql-zrm-scheduler --now --backup-level 1 --backup-set mybak-`date +%F`

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

backup:INFO: ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:backup:INFO: START OF BACKUP

mybak-2013-08-27:backup:INFO: PHASE START: Initialization

mybak-2013-08-27:backup:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

mybak-2013-08-27:backup:INFO: backup-set=mybak-2013-08-27

mybak-2013-08-27:backup:INFO: backup-date=20130827013928

mybak-2013-08-27:backup:INFO: mysql-server-os=Linux/Unix

mybak-2013-08-27:backup:INFO: backup-type=regular

mybak-2013-08-27:backup:INFO: host=localhost

mybak-2013-08-27:backup:INFO: backup-date-epoch=1377538768

mybak-2013-08-27:backup:INFO: retention-policy=1W

mybak-2013-08-27:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:backup:INFO: mysql-version=5.5.28-log

mybak-2013-08-27:backup:INFO: backup-directory=/backup/zrm/mybak-2013-08-27/20130827013928

mybak-2013-08-27:backup:INFO: backup-level=1

mybak-2013-08-27:backup:INFO: PHASE END: Initialization

mybak-2013-08-27:backup:INFO: PHASE START: Running pre backup plugin

mybak-2013-08-27:backup:INFO: PHASE END: Running pre backup plugin

mybak-2013-08-27:backup:INFO: PHASE START: Flushing logs

mybak-2013-08-27:backup:INFO: PHASE END: Flushing logs

mybak-2013-08-27:backup:INFO: PHASE START: Creating incremental backup

src = /mydata/data/mysql-bin.000026

k = /backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026

mybak-2013-08-27:backup:INFO: incremental=mysql-bin.[0-9]*

mybak-2013-08-27:backup:INFO: PHASE END: Creating incremental backup

mybak-2013-08-27:backup:INFO: PHASE START: Calculating backup size & checksums

mybak-2013-08-27:backup:INFO: next-binlog=mysql-bin.000027

mybak-2013-08-27:backup:INFO: last-backup=/backup/zrm/mybak-2013-08-27/20130827012922

mybak-2013-08-27:backup:INFO: backup-size=0.00 MB

mybak-2013-08-27:backup:INFO: PHASE END: Calculating backup size & checksums

mybak-2013-08-27:backup:INFO: PHASE START: Compression/Encryption

mybak-2013-08-27:backup:INFO: compress=/usr/bin/gzip

mybak-2013-08-27:backup:INFO: backup-size-compressed=0.00 MB

mybak-2013-08-27:backup:INFO: PHASE END: Compression/Encryption

mybak-2013-08-27:backup:INFO: read-locks-time=00:00:00

mybak-2013-08-27:backup:INFO: flush-logs-time=00:00:01

mybak-2013-08-27:backup:INFO: compress-encrypt-time=00:00:00

mybak-2013-08-27:backup:INFO: backup-time=00:00:01

mybak-2013-08-27:backup:INFO: backup-status=Backup succeeded

mybak-2013-08-27:backup:INFO: Backup succeeded

mybak-2013-08-27:backup:INFO: PHASE START: Running post backup plugin

mybak-2013-08-27:backup:INFO: PHASE END: Running post backup plugin

mybak-2013-08-27:backup:INFO: PHASE START: Mailing backup report

mybak-2013-08-27:backup:INFO: PHASE END: Mailing backup report

mybak-2013-08-27:backup:INFO: PHASE START: Cleanup

mybak-2013-08-27:backup:INFO: PHASE END: Cleanup

mybak-2013-08-27:backup:INFO: END OF BACKUP

/usr/bin/mysql-zrm started successfully

[root@master mysql-zrm]# du /backup/zrm/mybak-`date +%F`/* -sh

40M /backup/zrm/mybak-2013-08-27/20130827012922

16K /backup/zrm/mybak-2013-08-27/20130827013928

增量备份只有16K

全备中不含binlog

[root@master mysql-zrm]# mysql-zrm-parse-binlogs --backup-set mybak-`date +%F` --source-directory /backup/zrm/mybak-2013-08-27/20130827012922/

parse-binlogs:INFO: ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:parse-binlogs:ERROR: /backup/zrm/mybak-2013-08-27/20130827012922/ contains a full backup and there are no binary logs in this directory.

增量备份中binlog信息中包新增记录：

[root@master mysql-zrm]# mysql-zrm-parse-binlogs --backup-set mybak-`date +%F` --source-directory /backup/zrm/mybak-2013-08-27/20130827013928/

parse-binlogs:INFO: ZRM for MySQL Community Edition - version 3.0

Log filename Log Position Timestamp Event Type Event
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 4 13-08-27 01:29:25 Start: binlog v 4, server v 5.5.28-log created 130827 1:29:25
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 107 13-08-27 01:33:00 Query /*!\C utf8 *//*!*/;
BEGIN
/*!*/;
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 179 13-08-27 01:33:00 Intvar
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 207 13-08-27 01:33:00 Query insert into shop.ecs_users(user_name,last_time) values('week0',now())
/*!*/;
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 343 13-08-27 01:33:00 Query COMMIT
/*!*/;
/backup/zrm/mybak-2013-08-27/20130827013928/mysql-bin.000026 416 13-08-27 01:39:28 Rotate to mysql-bin.000027 pos: 4

模拟误操作删除shop库：

mysql> drop database shop;

Query OK, 88 rows affected (0.06 sec)

还原测试：

看看备份集信息

[root@master mysql-zrm]# mysql-zrm-reporter --show restore-info --where backup-set=mybak-2013-08-27

REPORT TYPE : restore-info

backup_set backup_date backup_level backup_directory backup_status comment

-----------------------------------------------------------------------------------------------------------------------------------------------------

mybak-2013-08-27 Tue 27 Aug 2013 01:39:28 1 /backup/zrm/mybak-2013-08-27/20130827013 Backup succeeded ----

AM CST 928

mybak-2013-08-27 Tue 27 Aug 2013 01:29:22 0 /backup/zrm/mybak-2013-08-27/20130827012 Backup succeeded ----

AM CST 922

先用全备还原

[root@master mysql-zrm]# mysql-zrm --action restore --backup-set mybak-2013-08-27 --source-directory /backup/zrm/mybak-2013-08-27/20130827012922/

restore:INFO: ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:restore:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

mybak-2013-08-27:restore:INFO: Restored innodb log '/mydata/data/ib_logfile0'

mybak-2013-08-27:restore:INFO: Restored innodb log '/mydata/data/ib_logfile1'

mybak-2013-08-27:restore:INFO: Restored innodb data file '/mydata/data/ibdata1'

mybak-2013-08-27:restore:INFO: Restored database from raw backup: test

mybak-2013-08-27:restore:INFO: Restored database from raw backup: shop

mybak-2013-08-27:restore:INFO: Restored database from raw backup: mysql

mybak-2013-08-27:restore:INFO: Restored database from raw backup: performance_schema

mybak-2013-08-27:restore:INFO: Restored database from raw backup: bbs

mybak-2013-08-27:restore:INFO: Restore done in 42 seconds.

MySQL server has been shutdown. Please restart after verification.

[root@master ~]# service mysqld start

Starting MySQL... SUCCESS!

mysql> select user_id,user_name,last_time from shop.ecs_users;

+---------+-----------+---------------------+

| user_id | user_name | last_time |

+---------+-----------+---------------------+

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

+---------+-----------+---------------------+

4 rows in set (0.00 sec)

此时shop库已还原，但是全备后插入的记录(week0)还未恢复

用增量备份还原：

[root@master mysql-zrm]# mysql-zrm --action restore --backup-set mybak-2013-08-27 --source-directory /backup/zrm/mybak-2013-08-27/20130827013928/

restore:INFO: ZRM for MySQL Community Edition - version 3.0

mybak-2013-08-27:restore:WARNING: The lvm-snapshot option is deprecated. Use snapshot-size instead

mybak-2013-08-27:restore:INFO: BINLOG = mysqlbinlog --user="backup-user" --password="*****" --host="localhost" --socket="/tmp/mysql.sock" "/backup/zrm/mybak-2013-08-27/20130827013928/"/mysql-bin.[0-9]* >> /tmp/e88CEUVrrj

mybak-2013-08-27:restore:INFO: Incremental restore done

mybak-2013-08-27:restore:INFO: Restore done in 0 seconds.

mysql> select user_id,user_name,last_time from shop.ecs_users;

+---------+-----------+---------------------+

| user_id | user_name | last_time |

+---------+-----------+---------------------+

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 6 | week0 | 2013-08-27 01:33:00 |

+---------+-----------+---------------------+

5 rows in set (0.01 sec)

至此数据已找回，增备到误操作期间的数据需借助binlog人工恢复，这里不再演示

备份脚本：

#!/bin/bash

#

# Written by Sanyu ,not tested

#

Name1=lvm-month

Name2=lvm-week

mkdir /backup/zrm 2>/dev/null

mkdir /etc/mysql-zrm/$Name1

mkdir /etc/mysql-zrm/$Name2

mkconf () {

cat >$1<

backup-level=0

backup-mode=raw

lvm-snapshot=100M

snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl"

backup-type=regular

destination=/backup/zrm

retention-policy=1W

compress=1

compress-plugin=/usr/bin/gzip

all-databases=1

user="backup-user"

password="redhat"

host="localhost"

socket=/tmp/mysql.sock

mysql-binlog-path="/mydata/data"

mailto="mysqldba@sanyu.com"

END

}

mkconf /etc/mysql-zrm/$Name1/mysql-zrm.conf

mkconf /etc/mysql-zrm/$Name2/mysql-zrm.conf

sed -i 's/retention-policy=1W/retention-policy=1Y/' /etc/mysql-zrm/$Name1/mysql-zrm.conf

sed -i 's/retention-policy=1W/retention-policy=1M/' /etc/mysql-zrm/$Name1/mysql-zrm.conf

mysql-zrm-scheduler --add --interval monthly --day-of-month 1 --backup-level 0 --start-time 02:04 --backup-set $Name1

mysql-zrm-scheduler --add --interval weekly --day-of-week 0 --backup-level 0 --start-time 04:04 --backup-set $Name2

mysql-zrm-scheduler --add --interval daily --backup-level 1 --start-time 04:04 --backup-set $Name2

mysql-zrm-scheduler --query

[root@master ~]# ./zrm.sh

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

DONE

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

DONE

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

DONE

schedule:INFO: ZRM for MySQL Community Edition - version 3.0

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

4 2 1 * * /usr/bin/zrm-pre-scheduler --action backup --backup-set lvm-month --backup-level 0 --interval monthly (保存一年)

4 4 * * 0 /usr/bin/zrm-pre-scheduler --action backup --backup-set lvm-week --backup-level 0 --interval weekly (保存一月)

4 4 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set lvm-week --backup-level 1 --interval daily (保存一月)

伞褶蜥
关注
MySQL数据库备份和恢复方案小结
spb3732的博客
12-20 3703
这两天在调研MySQL数据库的备份和恢复方案，备份对象是对大量Innodb表，或者加上少量的MyISAM表。   InnoDB备份常见问题： 文件一致性：数据文件、缓存、日志文件必须保持严格一致。加锁的方法没法保证一致性，因为InnoDB后台刷新数据是异步进行的。 数据一致性：不能一次锁住一张表，这个备份的是不同时刻的表数据；如果一次锁住所有表，innodb的mvcc机制会增加undo日志...
实现数据库增删改产+界面效果2-----jsp
阿古朵
07-02 612
1.通过连接数据库完成用户登录模块。2.登录成功后查询出一张数据库表中的内容；登录不成功返回登录页面。3.登录页面端要有空值和非法字符验证。4.登录成功后对一张表中数据进行增加、删除、修改和查询操作。
zram
Fybon的专栏
06-12 4584
http://zh.wikipedia.org/wiki/Zram
mysql使用binlog日志和增量备份（这里用的是linux环境下的mariadb）
enthan809882的博客
11-22 413
查看binlog状态(off是关闭，on是启动）: show variables like 'log_bin'; #默认是off 开启binlog： 修改/etc/my.cnf 文件，在[mysqlId]下面加入： log_bin = /var/lib/mysql/bin-log # 日志文件名的位置和前缀 log_bin_index = /var/lib/mysql/mysql-bin.i...
利用ZRM(lvm+binlog方式)进行数据库备份及还原
weixin_34075551的博客
10-09 163
安装ZRM：[root@master ~]# yum -y install perl-DBI perl-DBD-MySQL perl-XML-Parser [root@master ~]# yum localinstall MySQL-zrm-3.0-1.noarch.rpm [root@master ~]# ls /etc/mysql-zrm/ mysql-zrm.conf...
mysql bin日志备份_mysql之binlog日志备份还原
weixin_39631572的博客
02-01 146
实例:在mysql有时执行了错误的update或者delete时导致大量数据错误恢复的办法。执行时没有开启事务，也没有对数据进行。这时就需要使用到sqlbinlog工具。sqlbinlog需要开启，具体的打开方法就不说了。使用sqlbinlog会产生bin文件，恢复就需要用到这些文件。文件中记录着数据库的所有操作。(此方法的操作是将数据库之前所执行的语句重新执行一次，以达到恢复效果)具体步骤：(注...
Linux MySQL数据库备份——mysqldump + binlog
weixin_55985097的博客
04-19 384
mysqldump工具是mysql自带的一个非常方便的一款小工具，存在mysql安装目录的/usr/local/mysql/bin下，是CentOS自带的一款工具，它还支持热备份。 优势：自动记录日志position位置（show master status\G;），可用性，一致性，具有锁表机制。 mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 库的范围包括 -A, --all-databases 所有库 school 数据库名 school
使用ZRM备份Mysql数据库
01-27
无论是初学者还是经验丰富的系统管理员，都可以利用ZRM高效地完成MySQL数据库的备份和恢复工作。通过本文的介绍，相信读者已经掌握了ZRM的基本使用方法和配置流程。在实际应用中，建议根据自身需求调整配置参数，以...
linux zrm数据库备份,Using ZRM备份mysql数据库
weixin_39747075的博客
05-03 205
ZRM是Zmanda Recovery Manager的缩写，这是一款备份mysql的开源软件。其官方网站是：http://www.zmanda.com。以下是如何安装、配置ZRM for mysql以及如何备份恢复mysql的.1、获取ZRM：最后的稳定版本是2.2如图所示： 这里使用的rpm包，下载客户端和服务器端软件包。在mysql数据库服务器端安装以上两个软件包： 检验ZRM的安装： 2...
zrm for mysql_ZRM for MySQL 使用指南
weixin_42492000的博客
01-19 554
MySQL是一个最常见和多功能的数据库软件，越来越多的用户选择了MySQL。MySQL支持从一个用户的应用程序到上百万用户的应用程序。遗憾的是它没有任何系统有效的管理和备份工具。大约30%-40%的MySQL数据库没有系统的备份，很多数据库管理用通过脚本来实现MySQLMySQL是一个最常见和多功能的数据库软件，越来越多的用户选择了MySQL。MySQL支持从一个用户的应用程序到上百万用户的应用程...
mysql zrm_mysql数据库备份—ZRM
weixin_34952710的博客
02-27 217
ZRM是Zmanda Recovery Manager的缩写，这是一款备份mysql的开源软件。一、安装#yum install -yMySQL-zrm二、创建备用用户#mysql -uroot -pmysql>grant select,insert,update,create,drop,reload,shutdown,alter,super,lock table,replicati...
zrm for mysql_如何使用ZRM for MySQL执行时间点恢复
weixin_39647186的博客
01-19 102
如何使用ZRM for MySQL执行时间恢复ZRM for MySQL是为所有存储引擎的MySQL数据库提供强大，灵活和强大的备份和恢复解决方案。 使用ZRM for MySQL，数据库管理员可以自动将逻辑或原始备份自动化到本地或远程磁盘。 在本方法中，我们尝试解释如何在任何给定时间点从用户错误中恢复。我们的场景：大约下午2:30，MovieID表中添加了5个表空间。 下午3点，你会收到一个用户...
LVM逻辑卷的管理--创建LVM、扩容，快照实战
weixin_33953384的博客
08-21 139
如果对于生产环境下的服务器来说,如果存储数据的分区磁盘空间不够了怎么办?因为如果要把一个分区的内容都拷贝到另一个分区上去，势必要首先卸载掉之前的那个分区，然后*对整个分区进行拷贝，如果服务器上运行着一个重要的服务，比如说WWW或者FTP，其要求是 7*24 小时运行正常的，那么卸载掉分区这是不可想象的，同时如果该分区保存的内容非常非常的多，那么在对分区进行转移时时间可能会耗费...
binglog日志追踪：数据备份并备份追踪
Miracle_ze的博客
07-17 1651
binglog日志的备份与追踪
第八章 mysql 数据物理备份percona-xtrabackup
weixin_44990801的博客
09-24 481
一.MySQL数据备份 所有备份数据都应放在非数据库本地，而且建议有多份副本。 测试环境中做日常恢复演练，恢复较备份更为重要。 备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。 冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。 备份过程中必须考虑因素： 1. 数据的一致性 2. 服务的可用性 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库.
ZRM for MySQL学习
Rebulid Myself
09-13 2817
ZRM for MySQL学习笔记01[技术] 七月 30th, 2009 发表在 数据库相关 本文作者：深夜的蚊子 mysql数据库现在应用越来越广了，所以mysql数据库的数据也是一个企业中相当重要的，对于mysql的备份与恢复就成为了一项很关键的任务。蚊
基于java的人事管理系统设计与实现.docx
09-13
基于java的人事管理系统设计与实现.docx
Spring Boot微博后端系统（spring-boot-tucao-weibo）.zip
最新发布
09-13
这是一个基于Spring Boot框架开发的微博后端系统，实现前后端分离，使得后台可以独立开发和调试。该系统采用Vue实现前端界面，并提供了丰富的功能，如发布微博、评论、点赞等。技术栈包括Spring Boot、Spring Data JPA和Stomp（WebSocket）。项目提供开发环境和生产环境的运行方式，支持H2内存数据库和mySQL数据库。 1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看reADME.md文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值