首页 / 数据库 / MySQL / Percona XtraBackup 压缩备份集

压缩备份集

stream模式支持且只支持：tar 和 xbstream 两种格式，后者是xtrabackup提供的专有格式，解包时需要同名的专用命令处理innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password="backup" --stream=tar /tmp | gzip -> /data/mysqldata/backup/xtra_full.tar.gzinnobackupex: Created backup directory /tmp这段信息表明，流格式标准输出的数据会被临时保存到我们指定的/tmp目录innobackupex: You must use -i （--ignore-zeros） option for extraction of the tar stream.最后这条提示我们，解包时必须使用-i参数[mysql@master backup]$ du -sh *2.8G2015-07-07_17-11-0314M xtra_full.tar.gz打包压缩后的差距是很大的mkdir xbstreaminnobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password="backup" --stream=xbstream ./ > /data/mysqldata/backup/xbstream/incremental.xbstream./是以当前目录作为临时存放备份的目录解包[mysql@master backup]$ cd xbstream/[mysql@master xbstream]$ xbstream -x < incremental.xbstream[mysql@master xbstream]$ lsbackup-my.cnfibdata1 mysql sakilaxtrabackup_binlog_infoxtrabackup_infofandbincremental.xbstreamperformance_schematestxtrabackup_checkpointsxtrabackup_logfileIncremental Streaming Backups using xbstream and tar Incremental streaming backups can be performed with the xbstream streaming option. Currently backups are packed in custom xbstream format. With this feature taking a BASE backup is needed as well.Taking a base backup:
innobackupex /data/backupsTaking a local backup:
innobackupex --incremental --incremental-lsn=LSN-number --stream=xbstream ./ > incremental.xbstreamUnpacking the backup:
xbstream -x < incremental.xbstreamTaking a local backup and streaming it to the remote server and unpacking it:innobackupex--incremental --incremental-lsn=LSN-number --stream=xbstream ./ | /ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"测试:[mysql@master xbstream]$ xbstream -x -v < incremental.xbstream[mysql@master xbstream]$ lsbackup-my.cnfibdata1 mysql sakilaxtrabackup_binlog_infoxtrabackup_infofandbincremental.xbstreamperformance_schematestxtrabackup_checkpointsxtrabackup_logfile可以是用-C指定解压到哪个目录xbstream -x -v < incremental.xbstream -C /tmp[mysql@master xbstream]$ more xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 143684677last_lsn = 143684677compact = 0recover_binlog_info = 0官方文档示例有问题,最后改成这样:innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password="backup" --incremental --incremental-lsn=143684677 --stream=xbstream ./ | ssh mysql@192.168.255.202 "xbstream -x -C/data/mysqldata/backup/"远程端:[mysql@slave backup]$ lsbackup-my.cnfibdata1.deltamysql sakilaxtrabackup_binlog_infoxtrabackup_infofandbibdata1.meta performance_schematestxtrabackup_checkpointsxtrabackup_logfile

Compact Backups

当备份innodb表时,可以忽略secondary index pages.这回缩小备份集的大小.负面影响是,这回增加prepare用时,因为要重建secondary index
Compact Backups需开启innodb-file-per-tableinnobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password="backup" --compact /data/mysqldata/backup/查看xtrabackup_checkpoints可以看到compact = 1[mysql@master 2016-08-22_22-50-51]$ more xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 143789687last_lsn = 143789687compact = 1recover_binlog_info = 0

Preparing Compact Backups

prepare compact backup需要指定 --reduild-indexes参数innobackupex --apply-log --rebuild-indexes /data/mysqldata/backup/2016-08-2_22-50-51/

Restoring Compact Backups

innobackupex --copy-back /path/to/BACKUP-DIR

compress备份

通过--compress压缩备份,指定该参数后实际会传递给xtrabackup命令,故只能备份innodb文件,通过"quicklz"算法压缩
可以使用--compress-threads增加压缩并发,提高速度
使用这种方式备份会生成以.qp结尾的压缩文件,实测2.9G的备份压缩后52M开始备份innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password="backup" --stream=xbstream --compress --compress-threads=4 ./ > /data/mysqldata/backup/backup.xbstream解压xbstreamxbstream -x < backup.xbstream -C /data/mysqldata/backup/fan解压后,qpress文件并没有解压,在xtrabackup2.1.4之前使用命令进行解压 for bf in `find . -iname "*.qp"`; do qpress -d $bf $（dirname $bf） && rm $bf; donextrabackup2.1.4之后可以使用--decompress参数解压,这个参数实际调用了qpress命令,所以需要先安装qpress,下面通过percona的yum源来安装自动安装percona yum源rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm[root@master ~]# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm获取http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm准备中... ################################# [100%]正在升级/安装... 1:percona-release-0.0-1################################# [100%]手动配置percona yum源[percona]name = CentOS $releasever - Perconabaseurl=http://repo.percona.com/centos/$releasever/os/$basearch/enabled = 1gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-perconagpgcheck = 1安装[root@master ~]# yum install qpress已加载插件：fastestmirror, langpacksLoading mirror speeds from cached hostfile * base: ftp.sjtu.edu.cn * extras: mirrors.nwsuaf.edu.cn * updates: mirrors.neusoft.edu.cn正在解决依赖关系--> 正在检查事务---> 软件包 qpress.x86_64.0.11-1.el7 将被 安装--> 解决依赖关系完成依赖关系解决================================================================================================================================================== Package 架构版本源大小==================================================================================================================================================正在安装: qpressx86_6411-1.el7percona 31 k事务概要==================================================================================================================================================安装1 软件包总计：31 k安装大小：65 kIs this ok [y/d/N]: yDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transaction警告：RPM 数据库已被非 yum 程序修改。正在安装: qpress-11-1.el7.x86_641/1 验证中: qpress-11-1.el7.x86_641/1 已安装:qpress.x86_64 0:11-1.el7完毕！安装完毕,解压innobackupex --decompress /data/mysqldata/backup/fan/之后就可以prepare了更多XtraBackup相关教程见以下内容：MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htmMySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htmMySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm用XtraBackup实现MySQL的主从复制快速部署【主不锁表】 http://www.linuxidc.com/Linux/2012-10/71919p2.htm安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htmXtraBackup 的详细介绍：请点这里
XtraBackup 的下载地址：请点这里本文永久更新链接地址