mysqldump和xtrabackup备份原理实现说明

背景：

MySQL数据库备份分为逻辑备份和物理备份两大类，犹豫到底用那种备份方式的时候先了解下它们的差异：

逻辑备份的特点是：直接生成SQL语句，在恢复的时候执行备份的SQL语句实现数据库数据的重现。

物理备份的特点是：拷贝相关数据文件。

这二种备份差异  ：逻辑备份其备份、还原慢，但备份文件占用的空间小；物理备份其备份还原快，备份文件占用空间大。

到底选择那种备份方式，具体根据自己的实际情况，如需要的是热备还是冷备？数据量大不大？磁盘空间够不够等因素决定。

逻辑备份工具主要有：mysqldump、mysqlpump、mydumper，物理备份工具主要有：xtrabackup。

现在使用最多的备份就是mysqldump、xtrabackup（都支持支持热备），本文就mysqldump（mysqlpump）和xtrabackup的原理进行下大致的说明。

逻辑备份：mysqldump，第三方备份工具mydumper的备份原理看MySQL备份mydumper的原理

1）参数说明，具体的参数可以用mysqldump --help查看。

在说明mysqldump之前先了解下它的相关参数，可以通过mysqldump --help进行查看，也可以通过mysqldump、mysqlpump备份工具说明和mysqldump的流程进行了解。这里再重申一下几个比较重要的参数。

①：--single-transaction

通过将导出操作封装在一个事务(Repeatable Read)内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作；其他引擎不能保证导出是一致的。当导出开启了–single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后会自动关闭lock-tables。并且在mysql5..11之前，--default-parallelism大于1的时候和此参也互斥，必须使用--default-parallelism=。5.7.11之后解决了--single-transaction和--default-parallelism的互斥问题。

②：--master-data

这个选项可以把binlog的位置和文件名添加到输出中，如果等于1，将会打印成一个CHANGE MASTER命令；如果等于2，会加上注释前缀。并且这个选项会自动打开–lock-all-tables，除非同时设置了–single-transaction（这种情况下，全局读锁只会在开始dump的时候加上一小段时间，不要忘了阅读–single-transaction的部分）。在任何情况下，所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭–lock-tables。打开该参数需要有reload权限，并且服务器开启binlog。

③：--lock-all-tables ，-x

锁定所有库中所有的表。这是通过在整个dump的过程中持有全局读锁来实现的。会自动关闭–single-transaction 和 –lock-tables。

④：--lock-tables，-l

备份某个库就锁该库的所有表，用READ LOCAL来锁表。MyISAM允许并发写入，因为锁表只针对指定的数据库，不能保证物理上的一致性，不同库的表备份完成时会有不同的状态。用–skip-lock-tables来关闭。

⑤：--flush-logs，-F

在开始导出前刷新服务器的日志文件。注意，如果你一次性导出很多数据库（使用--databases= 或--all-databases 选项），导出每个库时都会触发日志刷新。例外是当使用了--lock-all-tables、--master-data或--single-transaction时：日志只会被刷新一次，那个时候所有表都会被锁住。所以如果你希望你的导出和日志刷新发生在同一个确定的时刻，你需要使用--lock-all-tables、--master-data和--single-transaction配合 –flush-logs。

⑥：--opt

该参数默认开启，表示快递启动--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset选项，通过 --skip-opt 关闭。

2）执行说明

①：逻辑备份就是导出SQL形式的文件，其的具体实现步骤可以直接打开genaral_log。

general_log_file        = /var/log/mysql/mysql.log

general_log             =

②：备份需要保证数据库的一致性，即在某一时刻，整个数据库的状态是一致的，这样可以通过备份进行恢复成为另一个从库。数据库目前使用最多的存储引擎是InnoDB，也有可能部分是MyISAM，建议把MyISAM存储引起改成InnoDB。现在重点说明关于InnoDB的备份。

MyISAM表的备份选项：上面提过因为mysqldump默认开启--opt选项，而--opt里包含--lock-tables的选项，这个选项不能保证在多个数据库下数据备份的一致性，所以要么--skip-opt，再把需要的选项添加进去，要么就再使用--lock-all-tables的选项（开启之后会关闭--lock-tables的选项），要是在从库备份则只需要添加--master-data选项（开启之后自动打开--lock-all-tables选项）即可。

备份myisam表的命令：

mysqldump -uroot -p123 --default-character-set=utf8 --master-data= -R -E --triggers -B dba_test dba_test2 > /home/dxy/dba_test.sql

因为开启了--lock-all-tables选项（--master-data），保证一致性读和数据的一致性。在备份开始时就会执行FLUSH TABLES WITH READ LOCK命令，这个命令是server层面的锁，这样后面任何存储引擎执行DML、DDL语句都会 Waiting for global read lock状态，这样就保证了从备份点之后数据的一致性。

InnoDB表的备份选项：和上面介绍MyISAM表的备份选项一样，在此基础上增加了--single-transaction的选项，这个选项保证了通过将导出操作封装在一个事务(Repeatable Read)内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作，其他引擎不能保证导出是一致的。这个选项开启后会自动关闭--lock-tables选项，而--master-data选项自动打开–lock-all-tables选项，在设置了--single-transaction这种情况下，全局读锁只会在开始dump的时候加上一小段时间（5.7之前），5.7之后不需要加锁了。

一致性快照，即一致性读取，那是如何保证一致性读的呢？具体的说明可以看MySQL 一致性读深入研究这篇文章。大致的说明可以看下面的测试说明：

测试1：

sesseion A	session B
mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)	mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.01 sec)
	mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec)
mysql> select * from t1; +----+------+ \| c1 \| c2 \| +----+------+ \| 1 \| 1 \| +----+------+ 1 row in set (0.00 sec)

结论1：RR隔离级别下的一致性读，不是以begin开始的时间点作为snapshot建立时间点，因为测试看出再begin一个事务的时候，表是没有数据的，sessionB写入数据之后，却能看到数据。

测试2：

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.00 sec)
mysql> begin; mysql> select * from t;
	mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec)
mysql> select * from t1; Empty set (0.00 sec)

结论2：RR隔离级别下的一致性读，是以第一条select语句的执行点作为snapshot建立的时间点的，即使是不同表的select语句。这里因为session A在insert之前对 t 表执行了select，所以建立了snapshot，所以后面的select * from t1 不能读取到insert的插入的值(snapshot的时候t1表没有数据)。

测试3：

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec)
mysql> begin;
mysql> select * from t1; Empty set (0.00 sec)	mysql> select * from t1; Empty set (0.00 sec)
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.01 sec)

结论3：session A 的第一条语句，发生在session B的 insert语句提交之前，所以session A中的第二条select还是不能读取到数据。因为RR中的一致性读是以事务中第一个select语句执行的时间点作为snapshot建立的时间点的。而此时，session B的insert语句还没有执行，所以读取不到数据。

测试4：

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read'; mysql> select * from t1; Empty set (0.00 sec)
mysql> select * from t1; Empty set (0.00 sec)
	mysql> insert into t1(c1,c2) values(1,1),(2,2); mysql> select * from t1; +----+------+ \| c1 \| c2 \| +----+------+ \| 1 \| 1 \| \| 2 \| 2 \| +----+------+ 2 rows in set (0.01 sec)
mysql> select * from t1; Empty set (0.00 sec)
mysql> update t1 set c2=100 where c1=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +----+------+ \| c1 \| c2 \| +----+------+ \| 1 \| 100 \| +----+------+ 1 row in set (0.00 sec)

结论4：本事务中进行修改的数据，即使没有提交，在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”，所以它可以修改成功。

通过上面的几个测试得出的结论：对同一个表或者不同表进行的第一次select语句建立了该事务中一致性读的snapshot，在snapshot建立之后提交的数据，一致性读就读不到，之前提交的数据就可以读到。事务一致性读的起始点其实是以执行的第一条语句为起始点的，而不是以begin作为事务的起始点的。

一般begin/start transaction是开始一个事务的标志，但不是事务开始的时间点，也就是说执行了start transaction之后的第一个语句（任何语句），事务才真正的开始。但是如果要达到将 start transaction作为事务开始的时间点，那么必须使用：

START TRANSACTION WITH consistent snapshot    ###mysqldump中的快照就是用这个实现的

这样开启事务效果等价于： start transaction 之后，马上执行一条 select 语句(此时会建立一致性读的snapshot)。

测试5：

session A	session B
mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +----+------+ \| c1 \| c2 \| +----+------+ \| 1 \| 1 \| +----+------+ 1 row in set (0.00 sec)

测试6：

mysql> set tx_isolation='repeatable-read';	mysql> set tx_isolation='repeatable-read';
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction with consistent snapshot;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.00 sec)

上面测试说明了 start transaction 和 start tansaction with consistent snapshot的区别。测试5说明，start transaction执行之后，一致性读并没有开始，所以可以读到session B插入的值。测试6说明，start transaction with consistent snapshot已经开始了一致性快照，所以insert语句发生在事务开始之后，所以读不到insert的数据。

所以事务一致性快照开始时间点，分为两种情况：

1）START TRANSACTION时，第一条语句的执行时间点，就是事务开始的时间点，第一条select语句建立一致性读的snapshot；

2）START TRANSACTION  WITH consistent snapshot时，则是立即建立本事务的一致性读snapshot，当然也开始事务了；

到此，大致说明了参数single-transaction的意义，其实就是通过start transaction with consistent snapshot实现一致性快照读：通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作，其他引擎（MyISAM）不能保证导出是一致的。如备份开启之后，通过一致性快照记录了mysql的binlog和position，此时往innodb表里写入数据，因为有一致性快照，备份读不到最新的记录，但是该操作会在之前记录binlog之后位置里记录，当还原的时候，直接应用之后的binlog记录即可。而myisam不支持事务，没有快照，备份直接取最新数据，而写入操作会持续记录到binlog里，所以还原的时候会导致一致性被破环。当还原到一个新从并开启同步的change（备份里面记录的点）之后，myisam表会出现主键冲突，而innodb表不会。

备份InnoDB表的命令：

mysqldump -uroot -p123 --default-character-set=utf8 --single-transaction --master-data= -R -E --triggers -B dba_test dba_test2 > /home/dxy/dba_test.sql

上面讲了这么多，现在通过general_log看看mysqlbinlog备份步骤：

--21T00::11.755486+:    Connect   root@localhost on  using Socket                         ##备份的连接方式
--21T00::11.793153+:    Query     /*!40100 SET @@SQL_MODE='' */                           ##备份的SQL_MODE
--21T00::11.815880+:    Query     /*!40103 SET TIME_ZONE='+00:00' */                      ##备份的时区，--tz-utc，用--skip-tz-utc关闭
##2016-08-21T00:08:11.815880+08:00   15 Query     FLUSH /*!40101 LOCAL */ TABLES                        ##刷新表，--master-data的作用

##2016-08-21T00:08:11.815880+08:00   15 Query     FLUSH TABLES WITH READ LOCK                           ##加全局读锁，--lock-all-tables的作用。
--21T00::11.815981+:    Query     SHOW STATUS LIKE 'binlog_snapshot_%'                    ##binlog的文件名和偏移量，--master-data的作用，或用SHOW MASTER STATUS表示
--21T00::11.822342+:    Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ##--single-transaction的作用，设置成RR级别
--21T00::11.822457+:    Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */  ##--single-transaction的作用，设置成一致性快照读
--21T00::11.822675+:    Query     SHOW VARIABLES LIKE 'gtid\_mode'

--21T00::11.868728+:    Query     SHOW STATUS LIKE 'binlog_snapshot_%'                    ##binlog的文件名和偏移量，--master-data的作用，或用SHOW MASTER STATUS表示
--21T00::11.868940+:    Query     UNLOCK TABLES                                           ##解锁表，印证了全局读锁只会在开始dump的时候加上一小段时间

#上面黄色背景表示备份前的一些准备工作：一致性快照、锁、二进制日志等信息。

2016--21T00::11.900984+:    Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dba_test','dba_test2'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE, EXTRA ORDER BY LOGFILE_GROUP_NAME

--21T00::12.000013+:    Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('dba_test','dba_test2')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

--21T00::12.000954+:    Query     SHOW VARIABLES LIKE 'ndbinfo\_version'

--21T00::12.001309+:    Init DB   dba_test

--21T00::12.001387+:    Query     SHOW CREATE DATABASE IF NOT EXISTS `dba_test`

--21T00::12.001505+:    Query     SAVEPOINT sp                                            ##--single-transaction的作用
--21T00::12.001564+:    Query     show tables

--21T00::12.001645+:    Query     show table status like 'abc'

--21T00::12.001747+:    Query     SET SQL_QUOTE_SHOW_CREATE=

--21T00::12.001772+:    Query     SET SESSION character_set_results = 'binary'

--21T00::12.001799+:    Query     show create table `abc`

--21T00::12.001835+:    Query     SET SESSION character_set_results = 'utf8'

--21T00::12.001864+:    Query     show fields from `abc`

--21T00::12.002013+:    Query     show fields from `abc`
--21T00::12.002150+:    Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `abc`            ##表示备份表的语句
--21T00::12.021228+:    Query     SET SESSION character_set_results = 'binary'

#上面蓝色背景表示备份一张表的过程

--21T00::12.021499+:    Query     use `dba_test`

--21T00::12.021549+:    Query     select @@collation_database

--21T00::12.021616+:    Query     SHOW TRIGGERS LIKE 'abc'                                ##备份触发器，--triggers

--21T00::12.039445+:    Query     SET SESSION character_set_results = 'utf8'

--21T00::12.039561+:    Query     ROLLBACK TO SAVEPOINT sp

...

...

--21T00::35.131333+:    Query     show events                                             ##备份事件，-E
--21T00::35.161440+:    Query     use `dba_test`

--21T00::35.161513+:    Query     select @@collation_database

--21T00::35.161582+:    Query     SET SESSION character_set_results = 'binary'

--21T00::35.161795+:    Query     SHOW FUNCTION STATUS WHERE Db = 'dba_test'              ##备份函数，-R

--21T00::35.190912+:    Query     SHOW PROCEDURE STATUS WHERE Db = 'dba_test'             ##备份存储过程，-R

--21T00::35.191683+:    Query     SET SESSION character_set_results = 'utf8'
2016-08-21T00:23:35.191683+08:00   16 Quit                                                              ##备份完成退出

3）实现过程

通过2）里的general_log的这些步骤，可以看到mysqldump的大致实现过程是：连接 -> 初始化信息 -> 刷新表（锁表）-> 记录偏移量 -> 开启事务（一致性快照）-> 记录偏移量 -> 解锁表，因为开启了一致性读，可以得到innodb的一致性，又因为解锁表了，MyISAM表一致性得不到保证，所以尽量别使用MyISAM表。

物理备份：xtrabackup

关于xtrabackup的备份原理可以看Percona XtraBackup 备份原理说明以及xtrabackup 使用说明（续）。这2篇文章已经讲的很详细了，本文就不再进行说明。

总结：

根据自己的实际情况，对备份原理、特性的认识，再选择到底是使用物理备份和逻辑备份。特别需要注意的是在用mysqldump备份时的一致性读的时机（begin/start transaction 后面的第一条语句）。

秒客网

mysqldump和xtrabackup备份原理实现说明

背景：

逻辑备份：mysqldump，第三方备份工具mydumper的备份原理看MySQL备份mydumper的原理

物理备份：xtrabackup

总结：

相关文章