mysql两种常用备份工具

一、mysqldump备份：

在开启GTID模式时，在master上执行的备份：

--set-gtid-purged=OFF

在开启GTID模式时，要在slave上执行，想重新搭建一套slave环境。那么必须这样备份：

--set-gtid-purged=ON

备份单个表：

mysqldump -uroot -p123456 -B test  --tables test > ~/test.sql　　　　　　备份test库下的test表

备份整个数据库：

mysqldump -uroot -p123456 -B test  > ~/test.sql

备份所有数据库，包括系统库：

mysqldump -uroot -p123456 -A > ~/test.sql

常用参数：

--events， -E

导出events。

使用--events contains CREATE EVENT 语句生成的输出来创建事件。但是，这些语句不包括事件创建和修改时间戳等属性，因此在重新加载事件时，会创建时间戳等于重新加载时间的事件。

如果您需要使用其原始时间戳属性创建事件，请不要使用--events。而是mysql.event使用具有适当mysql数据库权限的MySQL帐户直接转储和重新加载表 的内容 。

--routines， -R

在输出中包含转储数据库的存储例程（过程和函数）。此选项需要表的 SELECT权限 mysql.proc。

使用--routines contains CREATE PROCEDURE和 CREATE FUNCTION语句生成的输出来创建例程。但是，这些语句不包括例程创建和修改时间戳等属性，因此在重新加载例程时，会创建时间戳等于重新加载时间的例程。

如果您需要使用其原始时间戳属性创建例程，请不要使用 --routines。而是mysql.proc使用具有适当mysql数据库权限的MySQL帐户直接转储和重新加载表的内容。

--triggers

在输出中包含每个转储表的触发器。默认情况下启用此选项; 禁用它 --skip-triggers。

为了能够转储表的触发器，您必须具有TRIGGER该表的 权限。

在MySQL 5.7.2之前，表不能有多个具有相同触发事件（INSERT， UPDATE和 DELETE）和动作时间（BEFORE，AFTER）组合的触发器。MySQL 5.7.2解除了这个限制，允许多个触发器。mysqldump以激活顺序转储触发器，以便在重新加载转储文件时，以相同的激活顺序创建触发器。但是，如果是mysqldump转储文件包含具有相同触发事件和操作时间的表的多个触发器，尝试将转储文件加载到不支持多个触发器的旧服务器时会发生错误。（有关解决方法，请参见 第2.11.2.1节“影响MySQL 5.7降级的更改” ;您可以将触发器转换为与旧服务器兼容。）

--where='where_condition'， -w 'where_condition'

仅转储由给定WHERE条件选择的行 。如果条件包含空格或其他对命令解释程序特殊的字符，则必须引用该条件。

例子：

--where="user='jimf'"
-w"userid>1"
-w"userid<1"


二、innobackupex备份： 
依赖安装：

yum -y install libev-devel numactl-devel perl-DBD-MySQL numactl rsync libaio libaio-devel perl-Digest-MD5
libbev需要另外安装：
wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.13/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.10-1.el6.x86_64.rpm

 全量备份：

innobackupex --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp /backup/`date +%H-%M`/

增备：第一次
innobackupex --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp --incremental-basedir=/backup/`date +%H-%M`/ --incremental /backup/incre

--incremental-basedir：指向全量备份的目录。
--incremental：指定这一次是增量备份，后面的目录是指定这次增量备份的目录

第二次--incremental-basedir指定第一次增备目录 incremental指定第二次目录
innobackupex --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp --incremental-basedir=/backup/incre --incremental /backup/incre2

--incremental-basedir：指向第一次增量备份的目录
--incremental：指定这一次是增量备份，后面的目录是指定本次增量备份的目录，也就是第二次增备的目录

恢复：
1、关闭数据库：

service mysqld stop

2、备份原有数据，如果有的话。

3、恢复全备数据：

innobackupex --defaults-file=/mysql/mysqldata/my.cnf --apply-log --redo-only /backup/09-23/ 　　　　# 指定全备目录，--redo-only 对已提交的事务进行前滚，最后一次增备恢复不需要添加该参数

    恢复第一次增量备份：

innobackupex --apply-log --redo-only test_incre --incremental-dir=/backup/incre 　　　　# --incremental-dir 指定第一次增备目录 

    恢复第二次增量备份：

innobackupex --apply-log test_incre --incremental-dir=/backup/incre2 　　　　# 指定第二次增备目录，最后一次增备还原不添加 --redo-only 参数

    拷贝数据到配置文件中指定的数据目录路径：

innobackupex --defaults-file=/mysql/mysqldata/my.cnf --copy-back /backup/09-23 　　　　# 恢复整合后的数据文件

4、授权

chown -R mysql:mysql /usr/local/mysql/data

5、启动数据库，查看数据是否正常

service mysqld start

流备份模式：
1、全量备份：
xtrabackup -uroot -p123456 -S /tmp/mysql.sock --backup --extra-lsndir=/backup/checkpoint --parallel=8 --throttle=100  --stream=xbstream --compress --compress-threads=8 > ~/backup.xbstream

如果想要后续进行增量备份，需要添加参数：
--extra-lsndir  该参数是记录本次备份的checkpoint点的，后续增量备份要指定。
2、在全备的基础上进行增量备份：
xtrabackup -uroot -p123456 -S /tmp/mysql.sock --backup --extra-lsndir=~/checkpoint --incremental --parallel=8 --throttle=100 --incremental-basedir=~/checkpoint --stream=xbstream --compress --compress-threads=8 > ~/backup.incr

--incremental：指定该次备份为一次增量备份

--incremental-basedir：指定上一次备份的checkpoint目录

--extra-lsndir：指定该次备份的checkpoint目录（要放到哪里，然后下一次备份指定到这次放的目录）

解压缩备份：
xbstream格式：
xbstream -x < backup.xbstream -C /backup/all
qp格式（qpress需要自己安装）：
qpress -d ibdata1.qp ./
qp格式解压脚本：

#!/bin/bash
cd bk_incr
for i in `find . -iname '*\.qp'`;
do
qpress -d $i $(dirname $i) && rm $i;
done

恢复：
1、预备备份（通过--prepare参数来完成）：
已有备份：

/backups/base  全备

/backups/inc1  增备

/backups/inc2  增备

xtrabackup --prepare --apply-log-only --target-dir=/backups/all

  xtrabackup --prepare --apply-log-only --target-dir=/backups/all --incremental-dir=/backups/inc1

  xtrabackup --prepare --target-dir=/backups/all --incremental-dir=/backups/inc2    最后一次增备不要--apply-log-only参数，这里需要使用innodb的还原来完成恢复备份

 xtrabackup --copy-back --target-dir=/backups/all    最后copy数据到数据目录，指定全备目录