Innobackupex 和 mydumper，mysql备份工具

------------------------------------------------

------物理备份工具Innobackupex------

------------------------------------------------

官方Manual：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

主要用于热备采用InnoDB、MyISAM等引擎存储的数据，备份时将要备份的数据加载到内存中，再写入磁盘上backup datafile中。对于备份期间变更的数据，同redo log恢复的方式一样增补到备份文件中。

================================================================================================

innobackupex全备过程：

1、启用xtrabackup_logfile。用于在整个热备过程中，InnoDB存储引擎下新的DML操作产生数据变更时，在xtrabackup_logfile中实时记录这些新的数据变化，记录格式同redo log

2、以page为单位复制InnoDB存储的数据文件：共享表空间ibdataX和.ibd文件。由于复制时page可能正在被写入，page的头、尾checksum值将会不同。故之后生成备份文件在时间使用前先需要apply log，修复部分不完整的page。

3、flush tables with read lock。对MyISAM表加读锁，用于复制非事务引擎MyISAM存储的数据

4、复制 .frm、.MYD、.MYI文件。

5、获取备份完成那一刻，binlog走到的最新位置点：xtrabackup_binlog_info（InnoDB数据文件可能有更新）。

6、unlock tables;

7、（1）备份结束，记录启动备份所需要的最小参数到backup-my.cnf

（2）记录LSN到xtrabackup_logfile。

（3）记录备份类型（full-backuped：全量、incremental：增量；已经apply log过的备份将会修改为full-prepared）等信息到xtrabackup_checkpoints。

（4）记录其他一些备份信息：xtrabackup_info

下面整理一下除了拷贝数据目录下的库表数据、表空间文件（ibdata）、redo log（ib_logfile）之外，全备生成的文件：

（1）backup-my.cnf

（2）xtrabackup_binlog_info：夹杂MyISAM进行数据备份时，较xtrabackup_binlog_pos_innodb更准缺

（3）xtrabackup_binlog_pos_innodb：apply log之后新生成的文件，只记录innodb的binlog位点，而不会计算MyISAM产生的binlog

（4）xtrabackup_checkpoints

（5）xtrabackup_info

（6）xtrabackup_logfile（核心文件）

（7）xtrabackup_slave_info（从库备份重要文件）：需要在备份时加上--slave-info选项，将在该文件中记录 “change master to ...” 信息。使用备份文件对从库进行恢复后，将会依赖该信息重新指向主库进行同步。

================================================================================================

innobackupex增量备份过程

innobackupex在增量备份InnoDB表数据的时候，相较于全备的过程，增备在复制page的时候，将会对比备份文件与当前数据的page的LSN，有变更的数据相关的page，其LSN就会增长。所以innobackupex只需要备份LSN有变化的page。

而备份MyISAM时，仍然执行的是全备的操作。

================================================================================================

备份语句示例

备份账户需要的权限：RELOAD、LOCK TABLES、REPLICATION CLIENT

（1）全备：

step1：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=username --password='user_passwd' --host=【HOST】--port=【PORT】 --no-timestamp /tmp/innobackup_all

step2：

innobackupex --apply-log --defaults-file=/tmp/innobackup_all/backup-my.cnf --user=username --password='user_passwd' --host=【HOST】--port=【PORT】 --/tmp/innobackup_all

（2）部分备份（Partial Backups）：备份形如：mydatabase.mytable

step1：

使用--include配合正则表达式

innobackupex --include='^mydatabase[.]mytable' /path/to/backup --no-timestamp

使用--tables-file配合一份记录完整表名的文本文件（每行一个表名）

echo "mydatabase.mytable" > /tmp/tables.txt

innobackupex --tables-file=/tmp/tables.txt /path/to/backup --no-timestamp

使用--databases指定库、表（例如备份表：mydatabase.mytable和库：mysql）

innobackupex --databases="mydatabase.mytable mysql" /path/to/backup --no-timestamp --user=backup --password=backup

step2：

prepare部分备份：innobackupex --apply-log --export /path/to/backup/

（--databases未指定的库表，在prepare阶段会提示“does note exist”，可以忽略此信息）

（3）增量备份（假设已有全备，路径：$FULLBACKUP）

step1：

第一次增量备份（基于全备）：innobackupex --incremental $INCREMENTALBACKUP_1 --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

第二次增量备份（基于第一次增量备份）：innobackupex --incremental $INCREMENTALBACKUP_2 --incremental-basedir=NCREMENTALBACKUP_1 --user=USER --password=PASSWORD

（......）

第N次

step2：prepare

innobackupex --apply-log --redo-only $FULLBACKUP --use-memory=1G --user=USER --password=PASSWORD

innobackupex --apply-log --redo-only $FULLBACKUP--incremental-dir=$INCREMENTALBACKUP_1 --use-memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log --redo-only $FULLBACKUP --incremental-dir=$INCREMENTALBACKUP_2 --use-memory=1G --user=DVADER --password=D4RKS1D3

（......）

innobackupex --apply-log--redo-only  $FULLBACKUP --incremental-dir=$INCREMENTALBACKUP_N --use-memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log $FULLBACKUP --use-memory=1G --user=$USERNAME --password=$PASSWORD

--use-memory：指定prepare可以使用的内存，与 --apply-log 配合使用，加快prepare的速度

prepare阶段，第一次全备及增量备份整合过程中需要添加 --redo-only。最后，在所有增量备份整合过，需要再一次prepare整合进增量备份的全备文件。

================================================================================================

其他一些常用参数：

搭配使用：--stream=xbstream --compress --compress-threads=8 --parallel=4 > backupfile.xbstream（xbstream选项会逐个对表的ibd文件进行compress和stream，故需要innodb-file-per-table参数打开）

--parallel：备份并发数（指的是拷贝ibd文件，区别于compress-threads是执行压缩的线程数）

--stream：tar、xbstream。常常如此搭配使用：innobackupex [...] --stream=tar /backupdir/ | gzip - > backupfile.tar.gz

--tmpdir：在stream到远程机器前，暂存的目录位置

--encryption：备份加密。实际情况下更常用的是

（1）openssl，在上述tar+gz的方式上加入加密选项：innobackupex [...] --stream=tar /backupdir/ | gzip -  | openssl aes-256-cbc -k "abc" > backupfile.tar.gz.aes-256-cbc

（2）des3，innobackupex [...] --stream=tar /backupdir/ | gzip -  | openssl des3 -salt -k "abc" > backupfile.tar.gz.des3

================================================================================================

innobackupex恢复过程

1、innobackupex --apply-log，目的是从xtrabackup_log获取redo日志，更新部分不完整的page，使头尾checksum值，而LSN更新到备份过程中最新的LSN号；（实际应划分到备份过程中）

2、复制备份数据到数据库数据目录；

3、修改数据目录权限，并启动。

================================================================================================

恢复语句示例：

1、恢复前关闭实例

2、备份原有数据目录（redo log、undo log分立出去的话也需要备份）

3、innobackupex --copy-back --user=username --password='user_passwd' --socket=/usr/local/mysql/run/mysqld.sock --defaults-file=/usr/local/mysql/my.cnf  /tmp/innobackup_all （或直接拷贝prepared过的备份文件）

4、修改目录权限，启动mysql

================================================================================================

从全备中导出单表数据（前提是需要开启innodb_file_per_table选项）

With the Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server with XtraDB or MySQL 5.6 (The source doesn’t have to be XtraDB or or MySQL 5.6, but the destination does). This only works on individual .ibd files, and cannot export a table that is not contained in its own .ibd file.

是需要在prepare阶段通过 --export选项将单表导出：

Once a full backup is created, prepare it with the --export option:

$ innobackupex --apply-log --export /path/to/backup

This will create for each InnoDB with its own tablespace a file with .exp extension.

将会为每个innodb表的表空间创建一个以.exp结尾的文件

输出文件形如：

/data/backups/mysql/test/export_test.exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

在其他服务器进行表的导入时，需要先create table（是因为独立表文件内无表结构信息）：

mysqlfrm --diagnostic /data/2017-03-22_16-13-00/yayun/t1.frm（使用mysql-utilities工具中的mysqlfrm从备份文件中读取表结构）

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;（依据之前读取的表结构，去create table）

删除表空间文件：

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

将导出的 .ibd 和 .exp文件复制到数据目录下：

After this, copy mytable.ibd and mytable.exp ( or mytable.cfg if importing to MySQL 5.6) files to database’s home

再import tablespace：

mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

--------------------------------------------

------逻辑备份工具mydumper------

--------------------------------------------

部分英文文献摘自GitHub上的README：https://github.com/maxbube/mydumper

在5.5/5.6版本的MySQL数据库中，相较于采用官方提供的mysqldump进行单线程备份，多线程备份工具mydumper有着得天独厚的优势。（MySQL 5.7.11之后的版本，官方终于修复了并行逻辑备份工具 mysqlpump 一致性备份的问题。关于mysqlpump可参考姜承尧大牛的介绍：http://www.tuicool.com/articles/E77bYz7 ）

劣势：较难以并发流的方式备份到远程备份中心，更多的是直接本地落盘。

== How does consistent snapshot work? ==
This is all done following best MySQL practices and traditions:

* As a precaution, slow running queries on the server either abort the dump, or get killed
* Global write lock is acquired ("FLUSH TABLES WITH READ LOCK")
* Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")
* Other threads connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT")
** On pre-4.1.8 it creates dummy InnoDB table, and reads from it.
* Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.

mydumper对于一致性的实现机制：

* 遇到有慢查询的情况，要么dump停止执行，要么mydumper将慢查询kill掉。（由--long-query-guard 参数来约定一个慢查询的时间、默认为60秒，--kill-long-queries加入该参数则主动kill掉慢查询，不加则mydumper在遇到慢查询的情况下将停止运行）
* 使用"FLUSH TABLES WITH READ LOCK" 施加全局的读锁，会阻止DML语句
* 查看元数据："SHOW SLAVE STATUS","SHOW MASTER STATUS"

* "START TRANSACTION WITH consistent snapshot"：start transaction开启事务的同时，立即建立当前事务一致性读的快照。没有with选项的话，会等到执行到事务中的第一条语句时，才真正开始事务、建立一致性读的快照

* 4.1.8版本开始，mydumper创建InnoDB类型的虚表，从中读数据

* 一旦所有的线程都反馈一致性快照建立完成，则执行 "UNLOCK TABLES"，并开启队列任务。

备份语句示例：

mydumper --user=username --password=user_passwd --socket=/... --regex '^(?!(mysql))' --output=/backupdir --compress --verbose=3 --logfile=/backupdir/mydumper_backup.log

常用参数释疑：

--database 指定需要备份的库
--tables-list 指定需要备份的表，用，分隔（与regex option冲突时，以regex为准）

--regex '^(?!(mysql|test))'：数据库过滤选项

--output=/backupdir：备份文件输出路径

--compress：压缩方式输出文件（.gz后缀）

--verbose=3：输出日志级别info，便于观察备份情况（0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2）

--logfile=/backupdir/mydumper_backup.log：指定mydumper运行日志文件的位置

--threads 指定备份时使用的线程数，默认为4

--statement-size：限制sql语句的最大长度（mydumper在备份时会合并sql）
--rows ： 按行数分割表。提高myloader时的并发性能
--chunk-filesize ： 按输出文件的大小分割表数据。提高myloader时的并发性能
--no-locks ： 不锁表（可能数据不一致）
--binlogs ： 备份binlog。当备份失败时，可以查看备份的binlog，在备份时位置点附近寻找出错原因

输出备份文件目录：

* 库结构：dbname-schema-create.sql.gz

* 表结构：dbname.tblname1-schema.sql.gz

* 表数据：dbname.tblname1.sql.gz

（每个库、表都有自己独立的备份文件。当仅需进行单表恢复时，通过mydumper恢复单表全量数据+binlog恢复增量）

* metadata：包含备份时，binlog当前位置点

----------------------------------------------------------------

Started dump at: 2017-07-04 09:45:57
SHOW MASTER STATUS:
Log: mysql-bin.000048
Pos: 107
GTID:(null)
Finished dump at: 2017-07-04 09:45:57

----------------------------------------------------------------

* mydumper_backup.log：记录了备份程序运行的情况

恢复命令myloader相关参数释疑
--directory 备份文件位置
--queries-per-transaction 每个事务执行的sql数，默认为1000
--overwrite-tables 已存在的表先drop掉再恢复（要求备份文件时候要备份表结构）
--database 指定需要还原的数据库
--enable-binlog 为还原数据的操作记录binlog
--threads 指定还原时使用的线程数，默认为4

--enable-binlog：恢复已备份的binlog

注：myloader只能在库级别层面进行恢复，单表恢复可以直接调用备份文件中对应的含有sql语句的文件

此外，innobackupex备份的是备份完整时，这一个时间点之前的数据，而mydumper（包括mysqldump、mysqlpump等）备份的数据的时间点是备份开始时的时间。

提一点恢复的主体思想：无论是采用物理备份，还是逻辑备份，最稳妥的恢复前提是数据库需暂时禁止数据的写入。然后先恢复全量备份，应用增量备份到最近的故障点，再应用binlog日志并跳过故障点。

始终要考虑，为了在单个表上的几条误操作语句而在线上服务器上停止写操作、通过全量+增量恢复的方法未免有点兴师动众，得不偿失。若没有带有复制延迟的策略的备库的话，采用mydumper备份的文件去恢复单表、或者退一步利用flashback才是又快又好的处理方法。

Tips：

在使用 Innobackupex 或 mydumper恢复大部分数据后，利用mysqlbing补齐上述备份程序无法覆盖到的数据部分。

mysqlbinlog参数释疑：

–start-position=N（读取时包含）
从二进制日志中第1个位置等于N参量时的事件开始读。
–stop-position=N（读取时不包含）
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

使用mysqlbinlog应用binlog日志时，如果需要跨多个文件，则同时读取多个，start-position为第一个binlog文件的起始点，stop-position为最后一个文件的终止点。

例：mysql-bin.000048（pos856），mysql-bin.000051（pos1042）

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000048 mysql-bin.000049 mysql-bin.000050 mysql-bin.000051 --start-position=856 --stop-position=1042 > /tmp/backup1/backup_new.sql

Tips：

始终要有备份监控；

上面两款备份工具的备份对象主要包含在数据目录中，要注意的是binlog中也含有一部分数据，对binlog也需要做好备份。

关于备份策略简单提一点，我们制定的备份策略是要根据业务类型来决定的。

对于数据增长型业务采取的是全量+增量的策略，而对于数据更新型则采用全量备份。

为了进行MySQL版本升级或单表恢复等操作常常采用的逻辑备份。

综上考虑，线上数据库一般是采取物理备份为主，逻辑备份为辅，再加上对binlog的备份。

参考文档：

innobackupex备份生成文件说明：

http://fordba.com/xtrabackup-produce-file-intruduction.html

Recipes for innobackupex：

https://www.percona.com/doc/percona-xtrabackup/LATEST/how-tos.html#recipes-ibk

如何从innobackupex生成的全备中恢复单表：

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html

https://www.percona.com/blog/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

https://www.percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/

实际恢复单表案例：

http://www.cnblogs.com/gomysql/p/6600616.html

创建及恢复Partial Backups：

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

mysqldump结合binlog进行恢复的案例：

http://blog.chinaunix.net/uid-25135004-id-1761635.html

http://www.cnblogs.com/hanyifeng/p/5756462.html

使用mysqldump全备文件进行单库恢复（待测试）

https://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file