mysql dump文件6g_【mysql】mysqldump和innobackupex备份

很好的对比总结文章，一共连续4篇：

https://blog.csdn.net/joy0921/article/details/80130548

https://blog.csdn.net/woqutechteam/article/details/75116292

https://blog.csdn.net/woqutechteam/article/details/75116314

https://blog.csdn.net/joy0921/article/details/80130860

上面4篇文章的整合，其实这篇文章的排版更好

https://www.cnblogs.com/janehoo/p/8079126.html

mysqldump：

https://www.cnblogs.com/janehoo/p/8079126.html

extrabackup

https://blog.csdn.net/wfs1994/article/details/80398234

https://www.cnblogs.com/wangxiaoqiangs/p/5961413.html

https://www.cnblogs.com/polestar/p/5618791.html

包含innobackupex的执行过程图

内核月报&补充说明

http://mysql.taobao.org/monthly/2016/03/07/

https://chuansongme.com/n/372118651979

1、innobackupex说明

innobackup，通过读取物理文件进行备份，因此速度上比mysqldump的效率要高。

读取物理文件，主要是innodb的表空间文件。只有innodb的表空间文件，才支持快照，并且必须是RR级别，RC级别没有快照。

原理：

innobackupex进行物理备份，原理是先拷贝 redo log，之后拷贝ibd文件，idb文件cp完毕后执行flush tables with read lock， fush no_write_to_binlog engine logs, show master status,  cp myisam表， 拷贝redo log，最后 unlock tables

这里的redo和innodb表的拷贝是以page为粒度，并且会进行checksum，myisam的拷贝是cp命令方式。

整个过程可以参考mysql内核月报：http://mysql.taobao.org/monthly/2016/03/07/

补充说明：https://chuansongme.com/n/372118651979

所以，在备份myisam表时，会加全局读锁，如果有很多myisam表，并且是主库，就不建议这么做，如果是备库，则有可能会导致主从复制延迟。

备份的时间点位，也是取决于FTWRL语句的执行时间。当这句语句执行后，不会有新的更新，从cp idb文件开始到FTWRL之间的变化，都记录在redo的增量里，此时加锁后，继续cp新增的redo即可。

备份出来的文理文件：ibdata1表空间文件，ib_buffer_pool文件，undo文件，database的独立表空间文件，和myisam表文件

当备份myisam文件时，为了保证数据一致性，需要加上全局读锁，即flush table with read lock

1.1、全量备份 and  增量备份

支持全量备份和增量备份。全量备份会记录备份的binlog点位，gtid，和buffer pool的checkpoint SLN；

增量备份，需要指定全量备份出来的数据文件路径。增量备份可以进行多次，每次会备份表空间(ibdata1，ib_buffer_pool，独立表空间文件，undo文件)的增量部分，即备份出delta文件，myisam每次都是全量备份(因为不支持快照)，系统表有很多都是myisam，所以每次都是全量备份。

对于innodb文件，innobackupex可以支持增量备份，即对innodb的表空间文件(ibdata1，ib_buffer_pool，独立表空间文件，undo文件)进行增量备份。

1.2、恢复数据

恢复数据时，有两个阶段：prepare和recover

prepare阶段，命令直接作用在备份数据上。把redo log中的已经提交的事务replayed， 把未提交的事务rollback

recover阶段，就是把prepare阶段恢复好的数据，cp back或mv back到mysql的data目录

之后修改 数据文件的权限， 修改conf文件，即可。

1.3、prepare阶段注意事项——增量备份

如果存在增量备份，则在恢复全备数据的时候， 要加上 --redo-only选项，否则后续的redo和undo等checkpoint就无法衔接上。

只有在做最后一个增量备份的prepare时，不需要加--redo-only选项。(如果加了，也没关系，mysql在启动的时候会做 已提交事务的commit和 未提交事务的rollback)

1.4、缺陷

只能本地执行：innobackupex 只能在实例的机器上做备份， 备份出来的文件也只能放在本地，如果磁盘IO负载较高，或空间不足(当然，可以通过挂载新的网盘解决，eg，挂载块存储网盘，用完后再cp出去)。恢复数据时，虽然不用一定在本地执行(可以在其他机器上执行后再scp)，但仍需要把数据cp到目标机器上。

恢复时每次都是全量数据。当需要恢复少量库和表时，这种方式就比较笨拙。

mysqldump出来的sql文件，可以通过匹配找到目标库表；

用mydumper工具导出的是单个表，更方便，在数据恢复时就比较灵活；

从binlog中过滤出目标库表相关的event，replicate binlog即可。此时需要binlog里面没有跨库表的操作，eg，update xx.xx这种

2、innobackupex实践

环境信息，工具目录/data01/tools/backup/innobackupex

mysql的data路径  /data01/mysql/inst/3306/data/

全量备份路径 /data01/mysql/backup/full/${number}

增量备份路径 /data01/mysql/backup/incre/${number}

2.1、执行全备

/data01/tools/innobackupex  --defaults-file=/data01/mysql/inst/3306/data/my.cnf  --no-timestamp  --ftwrl-wait-timeout=60  --parallel=4  --login-path=USERA   /data01/mysql/backup/full/record_1/    >   /data01/mysql/backup/full/record1.log  2>&1

注：也可以加入参数  --stream=tar  之后管道给 gzip 直接压缩。但这种方式会降低速度，建议先全备后，再压缩。

命令执行完毕后，正常情况下，会在record_1目录下有对应的文件，同时包含本次备份的mysql相关信息，和extrabackup工具的一些信息。eg，binlog位点，gtid，sln，整个执行命令的参数列表等。

2.2、执行增量备份

/data01/tools/innobackupex  --defaults-file=/data01/mysql/inst/3306/data/my.cnf  --no-timestamp  --ftwrl-wait-timeout=60  --parallel=4  --login-path=USERA   --incremental-basedir=/data01/mysql/backup/full/record_1/  --incremental    /data01/mysql/backup/incre/record_1

如果还有第二次增量备份，则 --incremental-basedir 需要设置成上一次增量备份数据的目录

2.3、恢复，prepare阶段