mysql 备份校验_MySQL备份与恢复方案验证

mysqlbackup+xtrabackup

(RHEL6X86_64)

之前针对mysql的备份做了个简单测试，与大家分享下

目前关于MySQL备份工具最流行的主要有三种

1.xtrabackup  -----Percona opensource

2.mysqlbackup -----mysql Enterprise

3.mysqldump -----mysql 自带工具

三种工具都支持热备；全备和增备。

对于自带的mysqldump备份速度比较慢，备份出来的为sql语句即DDL+insert,增备利用的是mysqlbinlog,必须要开启二进制日志;

对于xtrabackup它利用的是ib_logfile和.ibd文件

对于mysqlbackup来说它可以只利用ib_logfile文件也可以利用ib_logfile和.ibd文件，主要介绍mysqlbackup和xtrabackup两种工具，如下是它们之间的一些异同点:

Feature

Percona XtraBackup

MySQL Enterprise Backup

(InnoDB Hot Backup)

License

GPL

Proprietary

Price

Free

Included in subscription at $5000 per Server

Open source

Streaming and encryption formats

Open source

Proprietary

Supported MySQL flavors

Percona Server, MySQL, MariaDB

MySQL

Non-blocking InnoDB backups 1

Blocking MyISAM backups

Incremental backups

Full compressed backups

Incremental compressed backups

Fast incremental backups 2

Incremental backups with archived logs

Backup locks 8

(虽然Percona写NO，但是从mysqlbackup的文档和Percona的对比，也应该是有的对于non-innodb read lock)

Encrypted backups

Streaming backups

Parallel local backups

Parallel streaming backups

Parallel compression

Parallel encryption

Parallel apply-log

Parallel copy-back

Partial backups

Throttling 4

Point-in-time recovery support

Safe slave backups

Compact backups 5

Buffer pool state backups

Individual tables export

Individual partitions export

Restoring tables to a different server 7

Data & index file statistics

InnoDB secondary indexes defragmentation

rsync support to minimize lock time

Improved FTWRL handling

Backup history table

Backup progress table

Offline backups

Tape backups with Oracle Secure Backup

2. 备份工具对比

功能

Mysqlbackup

xtrabackup

mysqldump

全备

已验证pass

已验证pass

已验证pass

增备

已验证pass

已验证pass

未验证

单(指定)表备份/导出

已验证pass

已验证pass

已验证pass

增量恢复

已验证pass

已验证pass

未验证

全量恢复

已验证pass

已验证pass

未验证

压缩

已验证pass

已验证pass

未验证

Tape backup

已验证pass

N/A

N/A

Aws cloude storage

未验证

N/A

N/A

Parallel backup

已验证pass

已验证pass

N/A

注：1.tts mysqlbackup 不支持分区表的导出

2.mysqlbackup压缩也仅仅支持全备，不支持增量(除了image文件--及tape需要的格式)

3.tapebackup mysqlbackup仅支持page-size=16k  不支持8K的page-size

3. mysqlbackup和xtrabackup备份性能对比

测试环境：10.45.53.6 mysql/mysql

存储V7000

对ccv80ch库进行备份,库占用大小：14735MB

默认不使用parallel options(mysqlbackup默认1:6:1)

全备

Cpu

Time cost

File size

iops

util

Mysqlbackup

3%

72s

6195MB

3800

≈100%

xtrabackup

2%

94

6184MB

680

≈100%

增备的前置条件为:update subs set update_date=now();

update acct set update_date=now();

每个表存在100W数据

增备

Cpu

Time cost

File size

iops

util

Mysqlbackup

2%

43s

286MB

3600

≈100%

xtrabackup

2%

272s

386MB

500

≈16%

数据分析：

全备其中相差的部分为其他库的frm文件,mysqlbackup include会拷贝除指定库外其他库的frm文件；

增备其中相差部分是xtrabackup生成了.delta,.meta(每个frm都对应一个),而mysqlbackup,只生成了产生数据变更的sub和acct表的.idb文件

Parallel 备份

Cpu

Time cost

File size

iops

util

Mysqlbackup

12

73s

6493MB

3900

100

xtrabackup

28

51s

6482MB

1900

100

数据分析:

mysqlbackup和xtrabackup的并发机制并不一样导致了这种结果，

mysqlbackup的并发指的是读写线程的并发，即在io足够的情况下分配多个cpu线程;

xtrabackup的并发是file level级别的通常指的就是一次批量的copy ibd文件之类；

通过xtrabackup可以提升备份性能

4. mysqlbackup和xtrabackup恢复性能对比

这里的恢复主要指的是apply-log(因为通常情况下不会使用到最后一步copy-log，而copy-log的实质就是一个copy paste的过程只有在真正数据库坏了的时候才会使用到)

Apply-log

Cpu

Time cost

File size

iops

util

Mysqlbackup

3%

35s

14387MB

788

≈100%

xtrabackup

2%

31s

14387MB

783

≈100%

apply-log其中相差部分是

xtrabackuk生成的一些xtrabackup_binlog_info, xtrabackup_binlog_pos_innodb,

xtrabackup_checkpoints,xtrabackup_info文件

性能基本一致

5. 备份锁测试

lock/default

fullbackup

increment backup

single table exp/imp

mysqlbackup

none

none

yes(--use-tts=(table metadata lock)

xtrabackup

none

none

yes(default) use --no-lock can diable lock 参考

2.2.3文档page37 --no-lock()

mysqldump

yes

None

yes(table-level)  但是可以禁用--single-transaction

经过验证(innodb)得出如上表格结论：

全量备份中除了mysqldump存在锁，mysqlbackup和mysqlbackup都不存在锁;

增量不存在锁(mysqldump是通过binlog，其他两个不是)

对于tts即单表表的导入导出，经过测试发现mysqlbackup存在锁虽然是粒度很小的read lock，但是未发现有参数可以禁用，但是对于xtrabackup和mysqldump虽然默认有锁，但是可以disable掉。

6. 建议和讨论

1.对于全量和增量备份使用mysqlbackup enterprise

2.对于单表的导入导出，数据量小建议使用mysqldump，比较方便；数据量较大例如分区表之类建议使用xtrabackup，虽然比较麻烦但是对系统性能影响更小效率更高

3.对于tape的支持oracle的SBT 是否存在这种需求，若则page-szie必须为16K

4.对于aws cloud storage的功能是否存在需求

5.对于增量的备份mysqlbackup默认2种方法一种是普通的不加参数的，另一种则是--incremental-redo-log-only

这是几年前的文章，如有问题请与指正