关键词：mysqldump原理，--single-transaction，mysql备份原理
转自：https://www.cnblogs.com/cchust/p/5452557.html
MySQL备份原理详解
备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。
衡量备份恢复有两个重要的指标：恢复点目标(RPO)和恢复时间目标(RTO)，前者重点关注能恢复到什么程度，而后者则重点关注恢复需要多长时间。
这篇文章主要讨论MySQL的备份方案，重点介绍几种备份方式的原理，包括文件系统快照(LVM)，逻辑备份工具Mysqldump，Mydumper，以及物理备份工具Xtrabackup，同时会详细讲解几种方案的优缺点，以及可能遇到的问题。
冷备份
最简单的备份方式就是，关闭MySQL服务器，然后将data目录下面的所有文件进行拷贝保存，需要恢复时，则将目录拷贝到需要恢复的机器即可。
这种方式确实方便，但是在生产环境中基本没什么作用。因为所有的机器都是要提供服务的，即使是Slave有时候也需要提供只读服务，所以关闭MySQL停服备份是不现实的。
与冷备份相对应的一个概念是热备份，所谓热备份是在不影响MySQL对外服务的情况下，进行备份，热备份是这篇文章讨论的重点。
【1】快照备份
首先要介绍的热备份是快照备份，快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的原理是将所有的数据库文件放在同一分区中，然后对该分区执行快照工作；
对于Linux而言，需要通过LVM(Logical Volumn Manager)来实现。LVM使用写时复制(copy-on-write)技术来创建快照；
例如，对整个卷的某个瞬间的逻辑副本，类似于数据库中的innodb存储引擎的MVCC，只不过LVM的快照在文件系统层面，而MVCC在数据库层面，而且仅支持innodb存储引擎。
LVM有一个快照预留区域，如果原始卷数据有变化时，LVM保证在任何变更写入之前，会复制受影响块到快照预留区域。
简单来说，快照区域内保留了快照点开始时的一致的所有old数据。对于更新很少的数据库，快照也会非常小。对于MySQL而言，为了使用快照备份，需要将数据文件，日志文件都放在一个逻辑卷中，然后对该卷快照备份即可。
由于快照备份，只能本地，因此，如果本地的磁盘损坏，则快照也就损坏了。快照备份更偏向于对误操作防范，可以将数据库迅速恢复到快照产生的时间点，然后结合二进制日志可以恢复到指定的时间点。基本原理如下图：
逻辑备份
冷备份和快照备份由于其弊端在生产环境中很少使用，使用更多是MySQL自带的逻辑备份和物理备份工具，这节主要讲逻辑备份，MySQL官方提供了Mysqldump逻辑备份工具，虽然已经足够好，但存在单线程备份慢的问题。在社区提供了更优秀的逻辑备份工具mydumper，它的优势主要体现在多线程备份，备份速度更快。
【2】Mysqldump
Mysqldump用于备份，不得不提两个关键的参数：
--single-transaction：在开始备份前，执行start transaction命令，以此来获取一致性备份，该参数仅对innodb存储引擎有效。
--master-data=2：主要用于记录一致性备份的位点。
理解Mysqldump工作原理，一定要将事务表(innodb)和非事务表(比如myisam)区别对待，因为备份的流程与此息息相关。
而且，到目前为止，我们也无法规避myisam表，即使我们的所有业务表都是innodb，因为mysql库中系统表仍然采用的myisam表。
备份的基本流程如下：
深入每一步的general log 信息参考：【5】
0.flush tables，刷脏页，关闭表（避免现有长查询或大事务，引起无法关闭表，导致下面1步骤阻塞其他表）
1.调用FTWRL(flush tables with read lock)，将脏页刷新到磁盘并获得只读锁，全局禁止写；
2.设置会话隔离级别为RR，开启快照读事务，获取此时的快照(仅对innodb表起作用)
3.备份非innodb表数据(*.frm,*.myi,*.myd等)
4.非innodb表备份完毕后，释放FTWRL锁
5.逐一备份innodb表数据
6.备份完成。
整个过程，可以参考我同事的一张图，但他的这张图只考虑innodb表的备份情况，实际上在unlock tables执行完毕之前，非innodb表已经备份完毕；
后面的t1,t2和t3实质都是innodb表，而且5.6的mysqldump利用保存点机制，每备份完一个表就将一个表上的MDL锁释放，避免对一张表锁更长的时间。这里可以参考我之前的blog：FLUSH TABLE WITH READ LOCK
大家可能有一个疑问，为啥备份innodb表之前，就已经将锁释放掉了，这实际上是利用了innodb引擎的MVCC机制，开启快照读后，就能获取那个时间的一致的数据，无论需要备份多长时间，直到整个事务结束(commit)为止。
【3】Mydumper
Mydumper原理与Mysqldump原理类似，最大的区别是引入了多线程备份，每个备份线程备份一部分表，当然并发粒度可以到行级，达到多线程备份的目的。这里要解决最大一个问题是，如何保证备份的一致性，其实关键还是在于FTWRL。对于非innodb表，在释放锁之前，需要将表备份完成。对于innodb表，需要确保多个线程都能拿到一致性位点，这个动作同样要在持有全局锁期间完成，因为此时数据库没有读写，可以保证位点一致。所以基本流程如下：
【4】物理备份(Xtrabackup)
【4.1】基本流程（innobackupex）
注意，mysql5.7 / xtrabackup2.4 ，Mysql8.0 / xtrabackup8.0+
相对于逻辑备份利用查询提取数据中的所有记录，物理备份更直接，拷贝数据库文件和日志来完成备份，因此速度会更快。当然，无论是开源的Mydumper还是官方最新的备份工具(5.7.11的mysqlpump)都支持了多线程备份，所以速度差异可能会进一步缩小，至少从目前生产环境来看，物理备份使用还是比较多的。由于Xtrabackup支持备份innodb表，实际生产环境中我们使用的工具是innobackupex，它是对xtrabackup的一层封装。innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，innobackupex的基本流程如下：
1.开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志；
2.开启idb文件拷贝线程，拷贝innodb表的数据
3.idb文件拷贝结束，通知调用FTWRL，获取一致性位点
4.备份非innodb表(系统表)和frm文件
5.由于此时没有新事务提交，等待redo日志拷贝完成
6.最新的redo日志拷贝完成后，相当于此时的innodb表和非innodb表数据都是最新的
7.获取binlog位点，此时数据库的状态是一致的。
8.释放锁，备份结束。
现象1：在xtrabackup拷贝innodb相关文件之前没有对数据库加锁
问题1：此时对表数据进行写入操作的话，有什么影响？
结论：没影响。
原因：因为xtrabackup在备份innodb相关数据时，开启了redo拷贝线程对redo log file也进行了备份。
XtraBackup复制InnoDB数据文件，这会导致内部不一致的数据，但是它会对文件执行崩溃恢复，以使其再次成为一个一致的可用数据库。
这样做是可行的，因为InnoDB维护一个REDO日志，也称为事务日志。REDO日志包含了InnoDB数据每次更改的记录。
当InnoDB启动时，REDO日志会检查数据文件和事务日志，并执行两个步骤。它将已提交的事务日志条目应用于数据文件，并对任何修改了数据但未提交的事务执行undo操作。
Percona XtraBackup会在启动时记住日志序列号（LSN），然后复制数据文件。这需要一些时间来完成，如果文件正在改变，那么它们会在不同的时间点反映数据库的状态。同时，Percona XtraBackup运行一个后台进程，用于监视事务日志文件，并从中复制更改。Percona XtraBackup需要持续这样做，因为事务日志是以循环方式写人的，并且可以在一段时间后重新使用。 PerconaXtraBackup开始执行后，需要复制每次数据文件更改对应的事务日志记录。
问题2：由于没有对数据库加锁，那么此时的DDL语句是被允许的，有什么影响？
结论：xtrabackup备份报错。
原因：xtrabackup在扫描innodb日志的LSN(log sequence number)时存在多次扫描，当发现lsn发生改变时就会报错。
综述：在备份（mysql5.7 / xtrabackup2.4）期间要禁止DDL操作。
【4.2】Xtrabackup的改进
从前面介绍的逻辑备份和物理备份来看，无论是哪种备份工具，为了获取一致性位点，都强依赖于FTWRL。这个锁杀伤力非常大，因为持有锁的这段时间，整个数据库实质上不能对外提供写服务的。
此外，由于FTWRL需要关闭表，如有大查询，会导致FTWRL等待，进而导致DML堵塞的时间变长。即使是备库，也有SQL线程在复制来源于主库的更新，上全局锁时，会导致主备库延迟。
从前面的分析来看，FTWRL这把锁持有的时间主要与非innodb表的数据量有关，如果非innodb表数据量很大，备份很慢，那么持有锁的时间就会很长。
即使全部是innodb表，也会因为有mysql库系统表存在，导致会锁一定的时间。
为了解决这个问题，Percona公司对Mysql的Server层做了改进，引入了BACKUP LOCK；
具体而言，通过"LOCK TABLES FOR BACKUP"命令来备份非innodb表数据；通过"LOCK BINLOG FOR BACKUP"来获取一致性位点，尽量减少因为数据库备份带来的服务受损。我们看看采用这两个锁与FTWRL的区别：
LOCK TABLES FOR BACKUP 作用：备份数据 1.禁止非innodb表更新 2.禁止所有表的ddl 优化点： 1.不会被大查询堵塞(关闭表) 2.不会堵塞innodb表的读取和更新，这点非常重要，对于业务表全部是innodb的情况，则备份过程中DML完全不受损 UNLOCK TABLES LOCK BINLOG FOR BACKUP 作用：获取一致性位点。 1.禁止对位点更新的操作 优化点： 1.允许DDl和更新，直到写binlog为止。 UNLOCK BINLOG
参考文档
http://mysql.taobao.org/monthly/2016/03/07/
https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/
http://www.wtoutiao.com/p/1cbstSx.html
http://www.wtoutiao.com/p/10cEnZ7.html
http://www.wtoutiao.com/p/125vVWi.html
http://www.wtoutiao.com/p/120AXSH.html
http://www.cnblogs.com/cchust/p/4603599.html
转自：https://blog.csdn.net/cug_jiang126com/article/details/49824471
【5】Mysqldump中为什么加了 --single-transaction 就能保证 innodb的数据是完全一致的？
在mysqldump过程中，之前其实一直不是很理解为什么加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。
理解master-data和--dump-slave
--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；
注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io_thread的状态
（5.1）深入理解--single-transaction
打开general_log，准备一个数据量较小的db，开启备份，添加--single-transaction和--master-data=2参数，查看general_log，信息如下，每一步添加了我的理解
整个dump过程是同一个连接id 32，这样能保证在设置session级别的变量的时候不影响到其他连接
thread_id: 32 argument: ucloudbackup@localhost on *************************** 14. row *************************** thread_id: 32 argument: /*!40100 SET @@SQL_MODE='' */ *************************** 15. row *************************** thread_id: 32 argument: /*!40103 SET TIME_ZONE='+00:00' */ *************************** 16. row *************************** thread_id: 32 argument: FLUSH /*!40101 LOCAL */ TABLES *************************** 17. row *************************** thread_id: 32 argument: FLUSH TABLES WITH READ LOCK 批注：因为开启了--master-data=2，这时就需要flush tables with read lock锁住全库，记录当时的master_log_file和master_log_pos点 *************************** 18. row *************************** thread_id: 32 argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 批注：--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，然而这个还不够，还需要看下一条 *************************** 19. row *************************** thread_id: 32 argument: START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 这时开启一个事务，并且设置WITH CONSISTENT SNAPSHOT为快照级别（如果mysql版本高于某一个版本值，我还不大清楚40100代表什么版本）。想象一下，如果只是可重复读，那么在事务开始时还没dump数据时，这时其他线程修改并提交了数据，那么这时第一次查询得到的结果是其他线程提交后的结果，而WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A，具体的测试看我下面的测试结果 *************************** 20. row *************************** thread_id: 32 argument: SHOW MASTER STATUS 这时候执行这个命令来记录当时的master_log_file和master_log_pos点，注意为什么这个时候记录，而不是再18 row和19 row之间就记录，个人认为应该都是可以的，这里是测试结果，start transaction并不会产生binlog的移动，而18 row和19 row的动作也在同一个thread id中 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 1690 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) *************************** 21. row *************************** thread_id: 32 argument: UNLOCK TABLES 等记录完成后，就立即释放了，因为现在已经在一个事务中了，其他线程再修改数据已经无所谓，在本线程中已经是可重复读，这也是这一步必须在19 rows之后的原因，如果20 rows和21 rows都在19 rows之前的话就不行了，因为这时事务还没开启，一旦释放，其他线程立即就可以更改数据，从而无法保证得到事务开启时最准确的pos点。*************************** 22. row *************************** thread_id: 32 argument: 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 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='mysql' AND TABLE_NAME IN ('user')
)
) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE
ORDER BY LOGFILE_GROUP_NAME *************************** 23. row *************************** thread_id: 32 argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE F
ROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN
(
SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME IN ('user')
) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME *************************** 24. row *************************** thread_id: 32 argument: mysql *************************** 25. row *************************** thread_id: 32 argument: SHOW TABLES LIKE 'user' *************************** 26. row *************************** thread_id: 32 argument: show table status like 'user' dump表以前都需要show一下各自信息，确保表，视图等不损坏，可用，每一步错了mysqldump都会报错并中断，给出对应的错误码，常见的myqldump错误请参考我的另外一篇 blog http://blog.csdn.net/cug_jiang126com/article/details/49359699 *************************** 27. row *************************** thread_id: 32 argument: SET OPTION SQL_QUOTE_SHOW_CREATE=1 *************************** 28. row *************************** thread_id: 32 argument: SET SESSION character_set_results = 'binary' *************************** 29. row *************************** thread_id: 32 argument: show create table `user` *************************** 30. row *************************** thread_id: 32 argument: SET SESSION character_set_results = 'utf8' *************************** 31. row *************************** thread_id: 32 argument: show fields from `user` *************************** 32. row *************************** thread_id: 32 argument: SELECT /*!40001 SQL_NO_CACHE */ * FROM `user` 这就是我们show processlist时看到的信息，而数据是怎么通过一条select语句就dump到本地文件里的呢，并且还转成成相应的create和insert语句，这就是mysqldump这个客户端工具的工作了，这里不做讨论 *************************** 33. row *************************** 最后并没有看到commit，因为在整个事务中，其实并没有修改任何数据，只是为了保证可重复读得到备份时间点一致性的快照，dump完成后提交不提交应该无所谓了。
（5.2）myisam引擎为什么无法保证在--single-transaction下得到一致性的备份？
因为它压根就不支持事务，自然就无法实现上述的过程，虽然添加了--single-transaction参数的myisam表处理过程和上面的完全一致，但是因为不支持事务，在整个dump过程中无法保证可重复读，无法得到一致性的备份。
而innodb在备份过程中，虽然其他线程也在写数据，但是dump出来的数据能保证是备份开始时那个binlog pos的数据。
（5.3）myisam引擎要保证得到一致性的数据的话，他是如何实现的呢？
它是通过添加--lock-all-tables，这样在flush tables with read lock后，直到整个dump过程结束，断开线程后才会unlock tables释放锁(没必要主动发unlock tables指令)，整个dump过程其他线程不可写，从而保证数据的一致性
如果我一定要在mysiam引擎中也添加--single-transaction参数，再用这个备份去创建从库或恢复到指定时间点，会有什么样的影响？
我个人的理解是如果整个dump过程中只有简单的insert操作，是没有关系的，期间肯定会有很多的主键重复错误，直接跳过或忽略就好了。
如果是update操作，那就要出问题了，分几种情况考虑
1） 如果是基于时间点的恢复，假设整个dump过程有update a set id=5 where id=4之类的操作，相当于重复执行两次该操作，应该问题不大
2） 如果是创建从库，遇到上面的sql从库会报错，找不到该记录，这时跳过就好
3）不管是恢复还是创建从库，如果dump过程中有update a set id=id+5 之类的操作，那就有问题，重复执行两次，数据全变了。
（5.4）深入理解--lock-all-tables
打开general_log，准备一个数据量较小的db，开启备份，添加--lock-all-tables(其实也是默认设置)和--master-data=2参数，查看general_log，信息如下，理解--lock-all-tables怎么保证数据一致性
mysql> select thread_id,argument from general_log where thread_id=185\G *************************** 1. row *************************** thread_id: 185 argument: ucloudbackup@10.10.108.15 on *************************** 2. row *************************** thread_id: 185 argument: /*!40100 SET @@SQL_MODE='' */ *************************** 3. row *************************** thread_id: 185 argument: /*!40103 SET TIME_ZONE='+00:00' */ *************************** 4. row *************************** thread_id: 185 argument: FLUSH /*!40101 LOCAL */ TABLES *************************** 5. row *************************** thread_id: 185 argument: FLUSH TABLES WITH READ LOCK 这里flush tables with read lock之后就不会主动unlock tables，保证整个dump过程整个db数据不可更改，也没有事务的概念了 *************************** 6. row *************************** thread_id: 185 argument: SHOW MASTER STATUS 同样记录主库的位置 *************************** 7. row *************************** thread_id: 185 argument: 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 GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME *************************** 8. row *************************** thread_id: 185 argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME *************************** 9. row *************************** thread_id: 185 argument: SHOW DATABASES *************************** 10. row *************************** thread_id: 185 argument: jjj *************************** 11. row *************************** thread_id: 185 argument: SHOW CREATE DATABASE IF NOT EXISTS `jjj`
（5.5）测试可重复读和快照读(WITH CONSISTENT SNAPSHOT )
准备工作3.1（测试可重读）
session 1:
| 时间点 | session1 | session 2 |
|
1 |
session1 mysql> select * from xx;
|
|
| 2 | session 2: mysql> insert into xx values (5); Query OK, 1 row affected (0.00 sec) |
|
| 3 | session 1: mysql> select * from xx; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) 批注：这时因为没有设置快照读，所以当session 2有数据更新时，可查到该数据 |
|
| 4 | 下来我们继续在session 2 插入数据 session 2: mysql> insert into xx values (6); Query OK, 1 row affected (0.00 sec) |
|
| 5 |
session1 mysql> select * from xx; |
|
| 6 | --快照读测试 | |
| 7 |
session 1 mysql> select * from xx; |
|
| 8 |
session 2：
|
|
| 9 | session 1： mysql> select * from xx; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) 发现还是只有一条数据，证明实现了快照读 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from xx; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) 事务1 提交后方可看见第二条记录 |
【参考文档】
xtrabackup8.0实践 ：https://blog.51cto.com/lee90/2502005
【5】部分的原文：https://blog.csdn.net/cug_jiang126com/article/details/49824471