MySQL 逻辑物理备份测试

---

目录

• 逻辑备份
  • mysqldump 普通备份
  • mysqlpump 并行备份
  • mysqlpump 压缩并行备份
  • mydumper 并行备份
  • mydumper 并行压缩备份
  • 小结
• 物理备份
  • xtrabackup 压缩备份
  • xtrabackup 并行压缩备份
  • innobackupex 并行压缩备份
  • innobackupex xbstream 流式备份
  • innobackupex xbstream 流式压缩并行备份
  • innobackupex tar 流式备份
  • innobackupex tar 流式压缩备份
  • innobackupex tar 流式压缩并行备份
  • 小结

---

逻辑备份

mysqldump 普通备份

time mysqldump -uadmin -padmin -h127.0.0.1 -P3306    --routines  --set-gtid-purged=OFF --triggers --events --single-transaction --master-data=2  --all-databases>/data/compressed/backup.sql

real 0m19.349s
user 0m3.879s
sys 0m0.951s

du -sh /data/compressed/ && rm -rf /data/compressed/*
362M /data/compressed/

mysqlpump 并行备份

time mysqlpump -uadmin -padmin -h127.0.0.1 -P3306   --set-gtid-purged=OFF --triggers --default-parallelism=4  --routines  --events --single-transaction   --all-databases>/data/compressed/backup.sql

real 0m6.942s
user 0m5.803s
sys 0m0.916s

du -sh /data/compressed/ && rm -rf /data/compressed/*
363M /data/compressed/

mysqlpump 压缩并行备份

time mysqlpump -uadmin -padmin -h127.0.0.1 -P3306   --set-gtid-purged=OFF --triggers --default-parallelism=4  --routines  --events --compress-output=LZ4 --single-transaction   --all-databases>/data/compressed/backup.sql

real 0m7.714s
user 0m7.149s
sys 0m0.611s

du -sh /data/compressed/
213M /data/compressed/

time lz4_decompress /data/compressed/backup.sql /data/backup.sql
ll -h /data/backup.sql
-rw-r--r-- 1 root root 363M Sep 16 19:53 /data/backup.sql

mydumper 并行备份

time mydumper -u admin -p admin -h 127.0.0.1 -P 3306 --routines    --triggers -t 4  --events     -o /data/compressed

real 0m7.582s
user 0m1.810s
sys 0m0.715s

du -sh /data/compressed/
371M /data/compressed/

mydumper 并行压缩备份

 time mydumper -u admin -p admin -h 127.0.0.1 -P 3306 -c --routines    --triggers -t 4  --events     -o /data/compressed

real 0m12.751s
user 0m26.136s
sys 0m0.418s

du -sh /data/compressed/
119M /data/compressed/

注意

mydumper备份MySQL GTID单独存放在metadata文件里

cat /data/compressed/metadata
Started dump at: 2018-09-16 17:33:32
SHOW MASTER STATUS:
Log: mysql-bin.000002
Pos: 47557983
GTID:898db7fe-f80a-11e7-b8d4-fa163efd7144:1-75

Finished dump at: 2018-09-16 17:33:45

小结

对2.3G datadir 分别进行逻辑备份，占用时间最短的是mysqlpump并行备份（7s）;占用空间最小的是mydumper并行压缩备份（119M），对于数据库较小可以采用mysqldpump或mydumper并行备份，备份速度都要优于mysqldump。

物理备份

xtrabackup --version
xtrabackup version 2.4.8

ll /usr/bin/innobackupex
lrwxrwxrwx. 1 root root 10 Jan 22 2018 /usr/bin/innobackupex -> xtrabackup
innobackupex 是xtrabackup的软链接，在percona-xtrabackup8.0版本中被移除

du -sh /opt/mysql/data/3306/
2.3G /opt/mysql/data/3306/

xtrabackup 压缩备份

time xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock   --backup --compress --target-dir=/data/compressed/

real 0m44.597s
user 0m4.905s
sys 0m1.562s

du -sh /data/compressed/
675M /data/compressed/

yum -y install qpress

time xtrabackup --decompress --target-dir=/data/compressed/
real 0m28.961s
user 0m4.767s
sys 0m5.995s

xtrabackup 并行压缩备份

time xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock   --backup --compress --compress-threads=4 --parallel=4 --target-dir=/data/compressed/

real 0m31.813s
user 0m5.159s
sys 0m1.803s

du -sh /data/compressed/
673M /data/compressed/

time xtrabackup --decompress --parallel=4 --target-dir=/data/compressed/

real 0m24.684s
user 0m5.384s
sys 0m8.028s

innobackupex 并行压缩备份

time /usr/bin/innobackupex  --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock    --compress --compress-threads=4 --parallel=4  /data/compressed/

real 0m29.864s
user 0m4.680s
sys 0m1.460s

du -sh /data/compressed/ && rm -rf /data/compressed/
673M /data/compressed/

innobackupex xbstream 流式备份

mkdir -p /data/backup
time innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock  --stream=xbstream /data/backup/ > /data/backup/backup.xbstream

real 0m42.509s
user 0m0.947s
sys 0m2.642s

du -ch /data/backup/
1.9G /data/backup/
1.9G total

time xbstream -x</data/backup/backup.xbstream -C /backup

real 0m28.451s
user 0m0.308s
sys 0m13.889s

innobackupex xbstream 流式压缩并行备份

time innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock  --stream=xbstream --compress   --compress-threads=4  --parallel=4  /data/backup/ > /data/backup/backup.xbstream

real 0m35.992s
user 0m4.988s
sys 0m1.909s

du -sh /data/backup/
672M /data/backup/

innobackupex tar 流式备份

time innobackupex  --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock --stream=tar /data/backup/ > /data/backup/out.tar

real 0m40.338s
user 0m0.641s
sys 0m2.726s

du -sh /data/backup/
1.9G /data/backup/

innobackupex tar 流式压缩备份

`

time innobackupex  --defaults-file=/opt/mysql/etc/3306/my.cnf  -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock --stream=tar /data/backup/  | gzip > /data/backup/out.tar.gz

real 1m47.964s
user 1m19.120s
sys 0m2.883s

du -ch /data/backup/
476M /data/backup/

time tar -zxf /data/backup/out.tar.gz -C /backup/

real 0m28.718s
user 0m11.294s
sys 0m18.175s

innobackupex tar 流式压缩并行备份

并行备份无效

time innobackupex  --defaults-file=/opt/mysql/etc/3306/my.cnf   --parallel=4   -uadmin -padmin --socket=/opt/mysql/data/3306/mysqld.sock --stream=tar /data/backup/  | gzip > /data/backup/out.tar.gz

real 1m50.192s
user 1m18.925s
sys 0m2.768s

xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
xtrabackup: warning: the --parallel option does not have any effect when streaming in the 'tar' format. You can use the 'xbstream' format instead.

小结

对2.3G datadir 分别进行物理备份，备份占用时间最短的是innobackupex压缩并行备份（30s）和xtrabackup 压缩并行备份（31s），其次是innobackupex xbstream 流式压缩并行备份（35s）；备份占用空间最小的是innobackupex tar流式压缩备份（476M），innobackupex tar流式备份不支持并行备份

若更在意备份时间，推荐xtrabackup/innobackupex 压缩并行备份，若更在意备份空间占用大小，推荐innobackupex tar流式压缩备份
数据量较大适合物理备份

参考
Percona XtraBackup - Documentation