MySQL-原理篇-⑤日志

学习核心

• 慢查询日志

  • 慢查询日志分析
  • show profile 查看SQL执行成本

• 3种日志

  • undo log 日志
  • bin log 日志
  • redo log 日志
  • 3种日志分析（*）
    • undo log、redo log、binlog 的区别
    • 什么是两阶段提交？为什么需要两阶段提交？过程分析

• 数据库备份

学习资料

• 21 数据库备份：备份文件也要检查

慢查询日志

👻MySQL中配置修改可能遇到的问题

​ 在修改MySQL配置的过程中，可能经常会遇到一些“配置不生效”的情况，实际上还是要理解系统变量的配置含义，区分修改操作的作用范围（是针对当前会话有效、还是针对全局有效）

​ 一般来说在查询或者修改一些系统变量的时候，如果没有显示声明是global、session，则默认是session

# 查看系统变量
show [global|session] variables like '';

# 查看指定的系统变量
select @@[global|session].变量名;

# 设置系统变量
方式1：set [global|session] 变量名=值;
方式2：
	set @@global.变量名=值  # 全局变量
	set @@变量名=值  # 会话变量

会话变量--服务器为每个连接(会话)提供的客户端提供了系统变量，作用域为当前连接(会话)
全局变量--服务器层面上的，必须拥有 super 权限才能为系统变量赋值，作用域为整个服务器，对所有连接(会话)有效

1.慢查询日志分析

​ 慢查询日志用于查找慢SQL

​ MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。l

​ ong_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了最大忍耐时间值。它的主要作用是，协助发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，可检查一下慢查询日志，找到那些慢查询SQL，结合explain进行全面分析以解决问题。

​ 默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数（如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响），慢查询日志支持将日志记录写入文件。

开启慢查询日志

# 查看慢查询日志是否开启
show variables like '%slow_query_log%';
- slow_query_log   慢查询日志开启状态
- slow_query_log_file 慢查询日志存放位置

# 1.开启慢查询日志(开启后可再次查看状态进行确认)
set global slow_query_log='ON';

# 2.修改long_query_time阈值（执行时间超出这个阈值的SQL会被记录在慢查询日志中）
show variables like '%long_query_time%'; # 默认是10s
set global long_query_time = 1; # 可自定义该阈值（注意该值修改后生效的时机，例如设置global的方式对当前session的long_query_time失效，对新连接的客户端有效）

# 3.查看当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查询日志分析工具：mysqldumpslow

​ 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。查看mysqldumpslow的帮助信息：mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：
  -a: 不将数字抽象成N，字符串抽象成S
  -s: 是表示按照何种方式排序：
  	c: 访问次数
		l: 锁定时间
		r: 返回记录
		t: 查询时间
		al:平均锁定时间
		ar:平均返回记录数
		at:平均查询时间 （默认方式）
		ac:平均查询次数
	-t: 即为返回前面多少条的数据；
	-g: 后边搭配一个正则匹配模式，大小写不敏感的

-- 工作常用参考
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/mine-slow.log
 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/mine-slow.log
 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mine-slow.log
 
# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/mine-slow.log | more

关闭慢查询日志

​ 除了调优场景，正常情况下建议不要开启慢查询日志，MySQL提供了两种方式停止慢查询日志

• 方式1：永久性方式（通过修改配置）

[mysqld]
slow_query_log=OFF

# 设置slow_query_log的属性值为OFF 或者 注释slow_query_log配置

• 方式2：临时性方式（通过set语句）

# 关闭慢查询日志
set global slow_query_log='OFF';
# 重启MySQL服务，并确认状态是否更新完成
show variables like '%slow_query_log%';
show variables like '%long_query_time%';

删除慢查询日志

​ 通过show variables指令查看慢查询日志相关参数，可以定位到慢查询日志的生成路径，可以手动进入到指定目录进行删除。

​ 可借助mysqladmin flush-logs来重新生成查询日志文件mysqladmin -uroot -p flush-logs slow(也可通过该指令重置其他日志)

​ 慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时-定要注意，一旦执行了这个命令，慢 查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份

SQL执行成本

​ 通过show profile指令查看SQL执行成本，它是一个可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果

# 查看show profile启用状态
show variables like '%profiling%';

# 开启show profile（如果没有指定global，当前设定只针对当前会话有效）
set profiling='ON';

# 执行show profiles
show profiles; # 查看当前会话有哪些profiles(执行了哪些SQL)

# 查看最近一次查询的开销
show profile;

# 查询指定Query ID的开销
show profile cpu,block io for query 10;

show profile 常用查询参数

① ALL：显示所有的开销信息
② BLOCK IO：显示块IO开销
③ CONTEXT SWITCHES：上下文切换开销
④ CPU：显示CPU开销信息
⑤ IPC：显示发送和接收开销信息
⑥ MEMORY：显示内存开销信息
⑦ PAGE FAULTS：显示页面错误开销信息
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
⑨ SWAPS：显示交换次数开销信息

通过SQL语句执行拆解3种日志的作用

​ 以【update类型的SQL执行过程】进行分析，拆解3种日志的作用。UPDATE t_user SET name = 'noob' WHERE id = 1;，其执行流程分析如下所示：

• 客户端通过连接器建立连接，校验用户身份、权限
• 查询缓存：这是一个update操作，会将查询缓存清空，此处不需要经过查询缓存（MySQL8.0之后已将查询缓存清掉了）
• 解析器：通过解析器进行词法解析、语法解析
• 执行：
  • 预处理器：判断表和字段是否存在
  • 优化器：确定并生成执行计划
  • 执行器：根据生成的执行计划负责具体执行，找到满足条件的记录并执行更新操作

​ 更新语句的流程会涉及到undo log（回滚日志）、redo log（重做日志）、bin log（归档日志）这三种日志：

• undo log（回滚日志）： Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志）： Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志）： Server 层生成的日志，主要用于数据备份和主从复制；

1.为什么需要undo log？（InnoDB存储引擎）

​ 在执行执行一条“增删改”语句的时候，虽然没有输入 begin 开启事务和 commit 提交事务，但是 MySQL 会隐式开启事务来执行“增删改”语句的，执行完就自动提交事务的，这样就保证了执行完“增删改”语句后，可以及时在数据库表看到“增删改”的结果。执行一条语句是否自动提交事务，是由 autocommit 参数决定的，默认是开启。所以，执行一条 update 语句也是会使用事务的

​ undo log 的引入主要有两方面的作用：

• 实现事务回滚，保证事务ACID特性中的原子性
  • 事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态
• 通过Read VIew + undo log 实现MVCC（多版本并发控制），以支持事务ACID特性中的隔离性，用于解决并发场景的脏读、不可重复读问题
  • MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录

如何确保原子性？

​ undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面（类似日志中记录可以用于“反向操作”的数据，以支持事务回滚），当事务回滚时，可以利用 undo log 来进行回滚。例如：

• 在插入一条记录时，把这条记录的主键值记下来，后续回滚时只需要把这个主键值对应的记录删掉；
• 在删除一条记录时，把这条记录中的内容都记下来，后续回滚时再把由这些内容组成的记录插入到表中；
• 在更新一条记录时，把被更新的列的旧值记下来，后续回滚时再把这些列更新为旧值；

​ 当发生回滚时，读取 undo log 里的数据，然后做原先相反操作。不同的操作，需要记录的内容也是不同的，所以不同类型的操作（修改、删除、新增）产生的 undo log 的格式也是不同的。一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：（可参考MySQL原理篇中事务的隔离机制中对undo log的使用）

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链

如何实现MVCC？（以支持隔离性）

​ 对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，其区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务（解决脏读问题，但仍存在不可重复读、虚读/幻读问题）
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录（解决脏读、不可重复读问题）

​ 这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）

buffer pool 的 引入

（1）为什么引入buffer pool？

​ MySQL 的数据都是存在磁盘中的，当要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？缓存起来的好处在于下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据。为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

​ 当引入buffer pool 之后：

• 读取数据：如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取
• 修改数据：如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘

（2）buffer pool 缓存什么内容？

​ InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

​ 在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

​ 所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

undo 页 记录的是什么？

​ 开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面

查询一条记录就只缓冲一条记录吗？

​ 并不是，当查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

​ 基于MySQL的预读机制可能还会将记录所在页的邻页也一并加载返回

2.为什么需要redo log？（InnoDB存储引擎）

为什么引入redo log？（redo log的作用）

​ 基于上述场景分析，buffer pool 的引入是为了提高读写效率，但也可能会存在一个问题：Buffer Pool是基于内存的，而内存在一些场景中不一定可靠，如果出现断电重启，则那些还没来得及落盘的脏页数据就会丢失。

​ 为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

​ WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

为什么要引入redo log？redo log 的作用：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

什么是redo log？

​ redo log 是物理日志，记录了某个数据页做了什么修改，用于保证事务AICD特性中的持久性。比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

​ 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘

​ 当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态

undo 页面被修改，需要记录对应redo log吗？

​ 开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。在内存修改该 Undo 页面后，需要记录对应的 redo log。

redo log 和 undo log 的区别

两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

​ 有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。即 redo log 保证了事务四大特性中的持久性。

redo log 要写到磁盘、数据也要写到磁盘，为什么要多此一举？

​ 写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

​ 针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

​ 这也是WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上 。

redo log 如何运作？（刷盘时机）

产生的 redo log 是直接写入磁盘的吗？（并不是）

​ 实际上， 执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘如下图：

​ redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态地调整大小，增大它的大小可以让 MySQL 处理「大事务」时不必写入磁盘，进而提升写 IO 性能

redo log 什么时候刷盘？

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？主要有下面几个时机：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘；
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）；

innodb_flush_log_at_trx_commit 控制的是什么？

​ 单独执行一个更新语句的时候，InnoDB 引擎会自己启动一个事务，在执行更新语句的过程中，生成的 redo log 先写入到 redo log buffer 中，然后等事务提交的时候，再将缓存在 redo log buffer 中的 redo log 按组的方式「顺序写」到磁盘。此场景 redo log 刷盘时机是在事务提交的时候默认的行为。

除此之外，InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

• 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
• 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
• 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（专门用来缓存文件数据的），所以写入「 redo log文件」意味着写入到了操作系统的文件缓存

innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？

InnoDB 的后台线程每隔 1 秒：

• 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
• 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失

加入了后台线程后，innodb_flush_log_at_trx_commit 的刷盘时机如下图

innodb_flush_log_at_trx_commit 参数不同配置的应用场景

这三个参数的数据安全性和写入性能的比较如下：

• 数据安全性：参数 1 > 参数 2 > 参数 0
• 写入性能：参数 0 > 参数 2> 参数 1

所以，数据安全性和写入性能是不可兼得的，要不追求数据安全性，牺牲性能；要不追求性能，牺牲数据安全性。

• 在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1
• 在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作
• 安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高

redo log 文件写满了怎么办？

​ 默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1。在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。

​ 重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件

​ redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如下图：

• write pos 和 checkpoint 的移动都是顺时针方向；
• write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
• check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

​ 如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

​ 所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程

3.为什么需要bin log？

​ undo log、redo log 这两个日志都是InnoDB存储引擎生成的。MySQL在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

为什么有了bin log还要有redo log？

​ 这个问题跟 MySQL 的时间线有关系。最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，通过 redo log 来扩展实现MySQL的 crash-safe 能力

bin log VS redo log

【1】适用对象不同：

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的日志；

【2】文件格式不同：

• binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

  • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现
  • ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了）
  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

  STATEMENT 与 ROW两者的不同主要在于：

  • STATEMENT记录的是原语句，虽然节省了存储空间，但会存在动态函数的问题，例如用了 uuid 或者 now 这些函数，会导致在主从库执行的结果不一致，这种随时在变的函数会导致复制的数据不一致；
  • ROW记录的是最终修改完成的行记录，不会出现使用动态函数导致主从复制不一致的问题，但其会记录每行数据的变化结果，当出现批量update语句时，更新多少行数据就会change产生多少条记录，会使 binlog 文件过大

• redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

【3】写入方式不同：

• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
• redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

【4】用途不同：

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

​ 不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

​ 因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

​ binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据

主从复制如何实现？

​ MySQL的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 binlog：主库写 binlog 日志，提交事务，并更新本地存储数据
• 同步 binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
• 回放 binlog：回放 binlog，并更新存储引擎中的数据

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性

在完成主从复制之后，就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行

从库是不是越多越好

​ 并不是。因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主）=》一主多从的 MySQL 集群结构

MySQL主从复制还有哪个模型？

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果（该方式在实际项目中基本无法使用）

  • 性能差：要复制到所有节点才返回响应；
  • 可用性差：主库和所有从库任何一个数据库出问题，都会影响业务

• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失

• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险

bin log 如何刷盘？

​ 事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中

​ 一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程只能同时有一个事务在执行的设定，所以每当执行一个 begin/start transaction 的时候，就会默认提交上一个事务。如果一个事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的

​ MySQL 给每个线程分配了一片内存用于缓冲 binlog ，该内存叫 binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘

什么时候 binlog cache 会写到 binlog 文件？

​ 在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件，结合图示进行说明：

• write：指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O
• fsync：将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

• sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

​ 在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失

​ 而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

​ 如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

4.两阶段提交

为什么需要两阶段提交？

​ 事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

​ 举个例子，假设 id = 1 这行数据的字段 name 的值原本是 'jay'，然后执行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 两个日志的过程中，出现了半成功状态，那么就有两种情况：

• 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
• 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

​ 可以看到，在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致性。因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

​ MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

​ 两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段。

举个拳击比赛的例子，两位拳击手（参与者）开始比赛之前，裁判（协调者）会在中间确认两位拳击手的状态，类似于问你准备好了吗？

• 准备阶段：裁判（协调者）会依次询问两位拳击手（参与者）是否准备好了，然后拳击手听到后做出应答，如果觉得自己准备好了，就会跟裁判说准备好了；如果没有自己还没有准备好（比如拳套还没有带好），就会跟裁判说还没准备好。
• 提交阶段：如果两位拳击手（参与者）都回答准备好了，裁判（协调者）宣布比赛正式开始，两位拳击手就可以直接开打；如果任何一位拳击手（参与者）回答没有准备好，裁判（协调者）会宣布比赛暂停，对应事务中的回滚操作。

两阶段提交过程？

​ 在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

​ 当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交，如下图：

从图中可看出，事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
• commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

异常重启会出现什么现象？

​ 不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

• 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
• 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

​ 可以看到，对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

​ 所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID。

处于 prepare 阶段的 redo log 加上完整 binlog，重启就提交事务，MySQL 为什么要这么设计?

​ binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

事务没提交的时候，redo log 会被持久化到磁盘吗？

​ 会的。事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。

​ 那么此处思考一个问题：如果 mysql 崩溃了，还没提交事务的 redo log 已经被持久化磁盘了，mysql 重启后，数据不就不一致了？

​ 但实际上，这种情况 mysql 重启会进行回滚操作，因为事务没提交的时候，binlog 是还没持久化到磁盘的。所以， redo log 可以在事务没提交之前持久化到磁盘，但是 binlog 必须在事务提交之后，才可以持久化到磁盘。

两阶段提交存在什么问题？

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：

• 磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘
• 锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致

为什么两阶段提交的磁盘 I/O 次数会很高？

​ binlog 和 redo log 在内存中都对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer，它们持久化到磁盘的时机分别由下面这两个参数控制。一般为了避免日志丢失的风险，会将这两个参数设置为 1：

• 当 sync_binlog = 1 的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘；
• 当 innodb_flush_log_at_trx_commit = 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；

​ 可以看到，如果 sync_binlog 和 当 innodb_flush_log_at_trx_commit 都设置为 1，那么在每个事务提交过程中， 都会至少调用 2 次刷盘操作，一次是 redo log 刷盘，一次是 binlog 落盘，所以这会成为性能瓶颈。

为什么锁竞争激烈？

​ 在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作。通过加锁虽然完美地解决了顺序一致性的问题，但在并发量较大的时候，就会导致对锁的争用，性能不佳。

组提交

binlog 组提交

​ MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

• flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
• sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
• commit 阶段：各个事务按顺序做 InnoDB commit 操作；

​ 上面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束

​ 对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，锁粒度减小了，使得多个阶段可以并发执行，从而提升效率

有 binlog 组提交，那有 redo log 组提交吗？

​ 结合 MySQL 版本具体分析，MySQL 5.6 没有 redo log 组提交，MySQL 5.7 有 redo log 组提交。

​ 在 MySQL 5.6 的组提交逻辑中，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就没办法对 redo log 进行组提交。

​ 所以在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式，为 redo log 做了一次组写入，这样 binlog 和 redo log 都进行了优化。

​ 接下来介绍每个阶段的过程，注意下面的过程针对的是“双 1” 配置（sync_binlog 和 innodb_flush_log_at_trx_commit 都配置为 1）。

5.UPDATE语句执行过程总结

当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

【1】检索数据：执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：

• 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
• 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器

【2】校验是否需要进行更新操作：执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

• 如果一样的话就不进行后续更新流程；
• 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

【3】开启事务，更新undo log：开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log（更新操作：需要把被更新的列的旧值记下来）即生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log

【4】更新记录，并更新redo log：InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。即引入WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。至此，一条记录更新完了

【5】记录更新完成，更新binlog：在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘

【6】事务提交：「两阶段提交」

• 准备（prepare）阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘
• 提交（commit）阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）

6.MySQL 磁盘I/O很高的场景优化

​ 基于上述概念分析，可以知道事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：

• 设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
• 将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。

todo 有个餐馆赊账的案例帮助理解这两个日志的作用

数据库备份

​ 复制技术（Replication）或 InnoDB Cluster 只负责业务的可用性，保障数据安全除了线上的副本数据库，我们还要构建一个完整的离线备份体系。这样即使线上数据库被全部破坏，用户也可以从离线备份恢复出数据。所以，第一步要做好：线上数据库与离线备份系统的权限隔离。即可以访问线上数据库权限的DBA一定不能访问离线备份系统，反之亦然。否则，如果两边的数据都遭受破坏，依然无法恢复数据。而对于 MySQL 数据库来说，数据库备份分为全量备份、增量备份。

​ 全量备份：指备份当前时间点数据库中的所有数据，根据备份内容的不同，全量备份可以分为逻辑备份、物理备份两种方式

​ 增量备份：指备份数据库的逻辑内容，就是每张表中的内容通过 INSERT 语句的形式进行备份

1.全量备份-逻辑备份

​ MySQL 官方提供的逻辑备份工具有 mysqldump 和 mysqlpump。

​ 虽然 mysqldump 简单易用，但因为它备份是单线程进行的，所以速度会比较慢，于是 MySQL 推出了 mysqlpump 工具

mysqldump

通过 mysqldump 进行备份，可以使用以下 SQL 语句：

mysqldump -A --single-transaction > backup.sql

通过 mysqldump 进行全量的逻辑备份：

• 参数 -A 表示备份所有数据库；
• 参数 –single-transaction 表示进行一致性的备份。

​ 特别强调，参数 –single-transaction 是必须加的参数，否则备份文件的内容不一致，这样的备份几乎没有意义。如果担心总是忘记参数 –single-transaction，可以在 MySQL 的配置文件中加上如下提示：配置完成，每当在服务器上运行命令时 mysqldump 就会自动加上参数 –single-transaction

# my.cnf 
[mysqldump]
single-transaction

​ 执行指令，最终备份的文件名称为backup.sql。文件 backup.sql 本质就是一个文本文件，里面记录的就是一条条 SQL 语句，而这就是我们说的逻辑备份。要恢复逻辑备份非常简单，就是执行文件中的 SQL 语句mysql < backup.sql

mysqlpump

​ 命令 mysqlpump 的使用几乎与 mysqldump 一模一样，唯一不同的是它可以设置备份的线程数，如：

mysqlpump -A --single-transaction --default-parallelism=8 > backup.sql
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 25/37 tables, 881632/42965650 rows
Dump progress: 25/37 tables, 1683132/42965650 rows
......

​ 上面的命令显示了通过 mysqlpump 进行备份。参数 –default-parallelism 表示设置备份的并行线程数。此外，与 mysqldump 不同的是，mysqlpump 在备份过程中可以查看备份的进度。

不过在真正的线上生产环境中，一般不推荐你使用 mysqlpump， 因为当备份并发线程数超过 1 时，它不能构建一个一致性的备份。见 mysqlpump 的提示

mydumper 工具

​ mysqlpump 的备份多线程是基于多个表的并行备份，如果数据库中存在一个超级大表，那么对于这个表的备份依然还是单线程的。那么有没有一种基于记录级别的并行备份，且支持一致性的逻辑备份工具呢？那就是开源的 mydumper 工具，地址：https://github.com/maxbube/mydumper。mydumper 的强大之处在于：

• 支持一致性的备份；
• 可以根据表中的记录进行分片，从而进行多线程的备份；
• 对于恢复操作，也可以是多线程的备份；
• 可以指定单个表进行多线程的恢复。

mydumper 几乎是一个完美的逻辑备份工具，是构建备份系统的首选工具

# 简单的 mydumper 的使用方法
mydumper -o /bak -r 100000 --trx-consistency-only -t 8

上面的命令表示，将备份文件保存到目录 /bak 下，其中：

• 参数 -r 表示每张表导出 100000 条记录后保存到一张表；
• 参数 –trx-consistency-only 表示一致性备份；
• 参数 -t 表示 8 个线程并行备份；

可以看到，即便对于一张大表，也可以以 8 个线程，按照每次 10000 条记录的方式进行备份，这样大大提升了备份的性能

2.全量备份-物理备份

​ 逻辑备份虽然好，但是它所需要的时间比较长，因为本质上逻辑备份就是进行 INSERT … SELECT … 的操作。

​ 而物理备份直接备份数据库的物理表空间文件和重做日志，不用通过逻辑的 SELECT 取出数据。所以物理备份的速度，通常是比逻辑备份快的，恢复速度也比较快。但它不如 mydumper 的是，物理备份只能恢复整个实例的数据，而不能按指定表进行恢复。

​ MySQL 8.0 的物理备份工具可以选择官方的 Clone Plugin。Clone Plugin 是 MySQL 8.0.17 版本推出的物理备份工具插件，在安装完插件后，就可以对MySQL 进行物理备份了。要使用 Clone Plugin 就要先安装 Clone Plugin 插件，推荐在配置文件中进行如下设置：

[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

​ 通过如下命令进行物理备份：在 mysql 命令行下输入 clone 命令，进行本地实例的 MySQL 物理备份

mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/clone_dir';

​ Clone Plugin 插件强大之处还在于其可以进行远程的物理备份，命令如下所示：Clone Plugin 支持指定的用户名密码，备份远程的物理备份到当前服务器上，根据 Clone Plugin 可以非常容易地构建备份系统

CLONE INSTANCE FROM 'user'@'host':port

IDENTIFIED BY 'password'

[DATA DIRECTORY [=] 'clone_dir']

[REQUIRE [NO] SSL];

​ 对于 MySQL 8.0 之前的版本，可以使用第三方开源工具 Xtrabackup。不过，物理备份实现机制较逻辑备份复制很多，需要深入了解 MySQL 数据库内核的实现，一般建议使用 MySQL 官方的物理备份工具，开源第三方物理备份工具只作为一些场景的辅助手段

3.增量备份

​ 实际运行中的系统，数据库中的数据不断变化，不可能每时每分对数据库进行增量的备份。因此需要通过采用："全量备份" + "增量备份"的方式构建完整的备份策略。

​ **增量备份就是对日志文件进行备份，在 MySQL 数据库中就是二进制日志文件。**因为二进制日志保存了对数据库所有变更的修改，所以“全量备份 + 增量备份”，就可以实现基于时间点的恢复（point in time recovery），也就是“通过全量 + 增量备份”可以恢复到任意时间点

​ 全量备份时会记录这个备份对应的时间点位，一般是某个 GTID 位置，增量备份可以在这个点位后重放日志，这样就能实现基于时间点的恢复。

​ 如果二进制日志存在一些删库的操作，可以跳过这些点，然后接着重放后续二进制日志，这样就能对极端删库场景进行灾难恢复了。

​ 想要准实时地增量备份 MySQL 的二进制日志，可以使用下面的命令：

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000001

​ 可以看到，增量备份就是使用 binlog，但这次额外加上了参数 –read-from-remote-server，表示可以从远程某个 MySQL 上拉取二进制日志，这个远程 MySQL 就是由参数 –host 指定。

​ 参数 –raw 表示根据二进制的方式进行拉取，参数 –stop-never 表示永远不要停止，即一直拉取一直保存，参数 binlog.000001 表示从这个文件开始拉取。

​ MySQL 增量备份的本质是通过 mysqlbinlog 模拟一个 slave 从服务器，然后主服务器不断将二进制日志推送给从服务器，利用复制技术，实现数据库的增量备份。

​ 增量备份的恢复，就是通过binlog 解析二进制日志，然后进行恢复，如：

mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

4.备份策略

• 设定全量备份频率

​ 首先，要设置全量备份的频率，因为全量备份比较大，所以建议设置 1 周 1 次全量备份，实时增量备份的频率。这样最坏的情况就是要恢复 7 天前的一个全备，然后通过 7 天的增量备份恢复。

• 备份"备份文件"

​ 对于备份文件，也需要进行备份。不能认为备份文件的存储介质不会损坏。所以，至少在 2 个机房的不同存储服务器上存储备份文件，即备份文件至少需要 2 个副本。至于备份文件的保存期限，取决于每个公司自己的要求（比如有的公司要求永久保存，有的公司要求保留至少近 3 个月的备份文件）

• 备份文件的检查

​ 备份系统非常关键，并不亚于线上的高可用系统。例如线上主从复制的高可用架构，还需要进行主从之间的数据核对，用来确保数据是真实一致的。对于备份文件，也需要进行校验，才能确保备份文件的正确的，当真的发生灾难时，可通过备份文件进行恢复。因此，备份系统还需要一个备份文件的校验功能。备份文件校验的大致逻辑是恢复全部文件，接着通过增量备份进行恢复，然后将恢复的 MySQL实例连上线上的 MySQL 服务器作为从服务器，然后再次进行数据核对。牢记，只有当核对是 OK 的，才能证明你的备份文件是安全的。所以备份文件同样要检查