mysql:5.6.29
xtrabackup:2.2.10
mysql数据目录:/data/mysql
mysql备份目录:/data/dbbak/full #确保有足够的磁盘空间
1、安装依赖
yum -y install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL rsync.x86_64
2、安装xtrabackup
rpm -ivh percona-xtrabackup-2.2.10-1.el6.x86_64.rpm
3、在数据库创建备份账号
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,Process ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
4、使用xbstream备份
time innobackupex --user=bkpuser --password=s3cret --no-timestamp --defaults-file=/etc/my.cnf /data/dbbak/full --tmpdir=/data/dbbak/full -stream=xbstream|gzip - > /data/dbbak/full/full.xbstream.gz
161124 10:08:50 innobackupex: Connection to database server closed
161124 10:08:50 innobackupex: completed OK! real 0m37.613s
user 0m28.506s
sys 0m3.186s
解压
time gzip -d full.xbstream.gz
real 0m26.678s
user 0m13.405s
sys 0m2.354s
time xbstream -x <full.xbstream
real 1m7.379s
user 0m6.055s
sys 0m11.173s
5、使用tar备份
time innobackupex --user=bkpuser --password=s3cret --no-timestamp --defaults-file=/etc/my.cnf /data/dbbak/full --tmpdir=/data/dbbak/full --stream=tar |gzip - > /data/dbbak/full/full.tar.gz
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
161124 10:09:53 innobackupex: completed OK! real 0m34.934s
user 0m26.500s
sys 0m2.816s
解压
time tar -ixzvf /data/dbbak/full/full.tar.gz real 0m27.310s
user 0m12.818s
sys 0m3.735s 或者
gzip -d /data/dbbak/full/full.tar.gz
tar -ixvf /data/dbbak/full/full.tar.gz
推荐使用tar -ixzvf /data/dbbak/full/full.tar.gz 解压
6、对比2种导出的文件大小
[root@VM_166_129 full]# ll
total 95232
-rw-r--r-- 1 root root 48704253 Nov 24 10:09 full.tar.gz
-rw-r--r-- 1 root root 48809971 Nov 24 10:23 full.xbstream.gz
7、总结
使用--stream=tar备份，压缩、解压、已经压缩后的大小都优于-stream=xbstream,推荐使用--stream=tar方式压缩,解压时还可以配合tar。