为了保证数据安全，都会对硬件做高可用，防止出现单点故障，但是无论如何都无法取代备份，尤其对于数据库中所保存的数据而言。

在此，介绍一下 MySQL 中常用的备份方法。

简介

MySQL 自带了 mysqldump、mysqlbinlog 两个备份工具，percona 又提供了强大的 XtraBackup，加上开源的 mydumper，还有基于主从同步的延迟备份、从库冷备等方式，以及基于文件系统快照的备份，其实选择已经多到眼花缭乱。

备份本身是为了能在出现故障时，可以迅速、准确恢复到原有状态，如果同时可以简单实用，那就最好不过了。接下来，我们看看这几种常用的备份工具。

mysqldump mydumper

mysqldump 是最简单的逻辑备份方式，在备份 myisam 表的时候，如果要得到一致的数据，就需要锁表。

备份 innodb 表时，加上 --master-data=1 --single-transaction 选项，在事务开始记录下 binlog pos 点，然后利用 mvcc 来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，将产生非常多的 undo，显著影响性能，所以要慎用。

这种方式可以针对单表备份，可以导出表结构。

mydumper 是 mysqldump 的开源加强版，支持压缩，支持并行备份和恢复，速度比要快很多，但是由于是逻辑备份，仍不是很快。

XtraBackup

这是 percona 开发的一个物理备份工具，可以在线对 InnoDB/XtraDB 引擎的表进行物理备份，相比于 mysqldump 来说，效率很不错。

XtraBackup 包含了两个主要的工具 xtrabackup、innobackupex，其中 xtrabackup 只能备份 InnoDB 和 XtraDB 两种数据表；innobackupex 则封装了 xtrabackup 同时可以备份 MyISAM 数据表。

如果通过源码进行编译，需要 boost 库才可以，为了简单起见，可以直接安装二进制文件。

# yum install libev
# rpm -ivh percona-xtrabackup-xxx.rpm

如果是源码安装，可以直接从 Github - Percona XtraBackup 下载。

innobackupex
xbcrypt
xbstream
xtrabackup

实际上，在 2.3 版本之前，innobackupex 是通过 perl 实现的，而在此之后则采用 C 进行了重写；为了保持向前兼容，该文件实际上是指向 xtrabackup 的一个符号连接。

apply-log

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。也就是说，此时数据文件仍处理不一致状态；需要回滚未提交的事务及同步已经提交的事务至数据文件，使得数据文件处于一致性状态。

innobakupex 命令通过的 --apply-log 选项可用于实现上述功能。

备份

如 MySQL 复制中所示，假设搭建了一个 Master 服务器。

全量备份

可以通过如下步骤进行全量备份。

----- 全量备份到/tmp/percon目录下
$ innobackupex --defaults-file=/tmp/my-master.cnf --user=root \
     --socket=/tmp/mysql-master.sock /tmp/percon
... ...
MySQL binlog position: filename 'mysql-bin.000001', position '1687589', GTID of the last change '0-1-5439'
161205 20:56:49 [00] Writing backup-my.cnf
161205 20:56:49 [00]        ...done
161205 20:56:49 [00] Writing xtrabackup_info
161205 20:56:49 [00]        ...done
xtrabackup: Transaction log of lsn (4397451) to (4397451) was copied.
161205 20:56:49 completed OK!

当结尾为 completed OK! 时，说明备份成功。备份完成之后，在目录下会有几个比较重要的元数据文件，简单介绍如下。

$ cat xtrabackup_checkpoints
backup_type = full-backuped          # 备份类型
from_lsn = 0
to_lsn = 4397451
last_lsn = 4397451                   # LSN号
compact = 0
recover_binlog_info = 0

$ cat xtrabackup_binlog_info         # 备份时binlog文件及其位置
mysql-bin.000001        1687589 0-1-5439

$ cat xtrabackup_binlog_pos_innodb   # 同上，不包括GTID信息
mysql-bin.000001        1687589

在使用 innobackupex 进行备份时，可以使用 --no-timestamp 选项来阻止命令自动创建一个以时间命名的目录；此时，该命令将会创建一个 BACKUP-DIR 目录来存储备份数据。

全量恢复

假设将其中的一个数据库删除掉，然后可以通过上述的备份恢复该数据库。

----- 关闭数据库
$ mysqladmin -uroot -S /tmp/mysql-master.sock shutdown

----- 备份源数据库，并新建相同目录
$ mv /tmp/mysql-master /tmp/mysql-master-bak
$ mkdir /tmp/mysql-master

----- 准备备份的全量数据
$ innobackupex --apply-log /tmp/percon/2015-08-23_20-55-32/
... ...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4406415
161205 21:27:15 completed OK!

----- 恢复全量数据
$ innobackupex --defaults-file=/tmp/my-master.cnf --copy-back --rsync /tmp/percon/2015-08-23_20-55-32/
... ...
161205 21:34:36 completed OK!

----- 启动服务器
$ /opt/mariadb/bin/mysqld --defaults-file=/tmp/my-master.cnf --basedir=/opt/mariadb \
       --datadir=/tmp/mysql-master

增量备份

注意，增量备份需要先有一个全量的备份，假设仍使用上述的备份。

mysql> INSERT INTO test.foobar VALUES(5, "Hobart"), (6, "Raymond");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

----- 执行增量备份
$ innobackupex --defaults-file=/tmp/my-master.cnf --user=root \
   --socket=/tmp/mysql-master.sock --incremental /tmp/percon/ \
    --incremental-basedir=/tmp/percon/2015-08-23_20-55-23/ --parallel=2
... ...
xtrabackup: Transaction log of lsn (4408143) to (4408143) was copied.
161205 21:49:34 completed OK!


mysql> INSERT INTO test.foobar VALUES(7, "Jeremy"), (8, "Philip");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

$ innobackupex --defaults-file=/tmp/my-master.cnf --user=root \
   --socket=/tmp/mysql-master.sock --incremental /tmp/percon/ \
    --incremental-basedir=/tmp/percon/2015-08-23_21-48-23/ --parallel=2
... ...
xtrabackup: Transaction log of lsn (4408743) to (4408743) was copied.
161205 21:59:20 completed OK!

增量备份恢复

对于如上的增量备份，恢复时需要执行如下的 3 个步骤。

准备完全备份；
将增量备份恢复到完全备份，开始恢复的增量备份要添加 --redo-only 参数，到最后一次增量备份要去掉该参数；
对整体的完全备份进行恢复，回滚未提交的数据。

mysql> DROP DATABASE TEST;

----- 将增量1应用到完全备份
$ innobackupex --apply-log --redo-only /tmp/percon/2015-08-23_20-55-23/ \
    --incremental-dir=/tmp/percon/2015-08-23_21-48-23/

----- 将增量2应用到完全备份
$ innobackupex --apply-log /tmp/percon/2015-08-23_21-48-23 --incremental-dir=/tmp/pecon/2015-08-23_21-58-40/

----- 把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据
$ innobackupex --apply-log /tmp/percon/2015-08-23_20-55-23/

----- 恢复数据
$ innobackupex --defaults-file=/tmp/my-master.cnf --copy-back --rsync /tmp/percon/2015-08-23_20-55-23/

另外，可以对数据进行压缩，在此暂不讨论。

执行流程

如下是 xtrabackup 执行的详细流程。

mysql percona xtrabackup procedure

mysqlbinlog

上述备份方式，都只能把数据库恢复到备份时的时间点：A) mysqldump、mydumper、snapshot 是备份开始的时间点；B) XtraBackup 是备份结束的时间点。

如果要实现 point in time 恢复，还必须备份 binlog；开启 binlog 非常简单，可以通过修改如下配置文件打开。

$ vi /etc/my.cnf
binlog_format = mixed
log-bin       = mysql-bin

其中 mysql 5.6 提供了远程备份 binlog 的能力，我么可以直接通过如下命令远程备份。

$ mysqlbinlog --raw --read-from-remote-server --stop-never

该命令会伪装成 mysql 从库，从远程获取 binlog 然后进行转储，不过这样状态监控需要单独部署；当然，还可以通过 blackhole 来备份全量的 binlog 。

常用解析

通过 mysqlbinlog 工具可以将 binlog 或者 relay-log 文件解析成文本文件，两者的格式相同。

$ mysqlbinlog binlog.0000003

如上命令将会解析 binlog ，并输出该文件中的所有语句及其相关信息，例如每个语句花费的时间、客户发出的线程ID、发出线程时的时间戳等等。

也可以通过 mysql 客户端查看 binlog 相关信息。

mysql> SHOW BINARY LOGS;                           # 查看binlog日志
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mysql-bin.000001|       409 |
| mysql-bin.000002|       346 |
+-----------------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW BINLOG EVENTS;                         # 查看执行的SQL，默认是1
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002';   # 查看指定binlog文件的内容
mysql> SHOW BINLOG EVENTS FROM 213;                # 指定位置binlog的内容

接下来看看如何通过 mysqlbinlog 方式提取 binlog 日志。

----- 提取整个binlog日志，压缩，执行
$ mysqlbinlog mysql-bin.000001
$ mysqlbinlog mysql-bin.000001 | gzip > foobar.sql.gz
$ mysqlbinlog mysql-bin.000001 | mysql -uroot -p

----- 提取指定位置的binlog日志
$ mysqlbinlog --start-position="120" --stop-position="332" mysql-bin.000001

----- 指定数据库、字符集、开始时间、结束时间的binlog并输出到日志文件
$ mysqlbinlog --database=test --set-charset=utf8 \
   --start-datetime="2015-08-23 21:15:23" --stop-datetime="2015-08-23 22:15:23" \
   --result-file=foobar.sql mysql-bin.000002 mysql-bin.000003

----- 远程提取日志，指定结束时间
# mysqlbinlog -uroot -p -h127.1 -P3306 --stop-datetime="2015-08-23 22:30:23" \
   --read-from-remote-server mysql-bin.000033 | less

----- 远程提取使用row格式的binlog日志并输出到本地文件
# mysqlbinlog -uroot -p -P3306 -h127.1 --read-from-remote-server -vv \
   mysql-bin.000005 > foobar.sql

常用参数：
   -p, --password[=name]      # 密码
   -P, --port[=num]           # 端口号
   -u, --user=name            # 登陆用户名
   -h, --host=name            # 主机地址
   -S, --socket=name          # 套接字文件
   -d, --database=name        # 只列出该数据库的条目(只适用本地日志)
   --protocol=name            # 连接协议
   --server-id[=num]          # 仅提取指定id的binlog日志

   --set-charset=name         # 添加SET NAMES character_set到输出
   -r, --result-file=name     # 输出指向给定文件
   -s, --short-form           # 只显示包含的语句

   --start-datetime=name      # 等于或大于该值的事件
   --stop-datetime=name       # 小于或等于该值的事件

   -j, --start-position[=num] # 从指定位置开始读取事件
   --stop-position[=num]      # 到该位置停止
   -o, --offset[=num]         # 跳过前num个条目

   -f, --force-read
       无法识别二进制事件，打印警告并忽略该事件继续；否则停止
   -H, --hexdump
       在注释中显示日志的十六进制转储，可以用于调试

mysqlbinlog 可以基于 server_id，以及基于数据库级别提取日志，不支持表级别。

参考

关于 XtraBackup 可以直接参考官方网站 Percona XtraBackup - Documentation。

MySQL 备份工具

简介 #

mysqldump mydumper #

XtraBackup #

apply-log #

备份 #

全量备份 #

全量恢复 #

增量备份 #

增量备份恢复 #

执行流程 #

mysqlbinlog #

常用解析 #

参考 #

简介