Mysql刨根问底之五（数据复制）

mysql复制概念

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。 它的实现机制可以这样概括，首先在源服务器（source server）上，任何数据修改和数据结构变更的事件（event）都会被写入日志文件 中，然后，副本服务器从源服务器上的日志文件中读取这些事件并在本地重放执行。这是一个异步处理的过程，也就是说，并不能保证副 本服务器上的数据是最新的。复制延迟（副本数据和最新数据之间的时间差）也并没有上限。一个大的SQL查询语句可能会导致副本服务器 落后于源服务器几秒钟、几分钟，甚至是几小时。

复制用途

• 数据分发
• 读流量扩展
• 备份
• 分析与报告
• 高可用性和故障切换
• MYSQL升级测试

复制步骤

在详细介绍如何设置复制之前，让我们先看看MySQL实际上是如何复制 数据的。在这里，我们使用最简单的复制拓扑结构，单一源服务器和单一副本服务器。复制步骤：

1. 源端把数据更改记录记录到二进制日志中，称之为 二进制日志事件（binary log events）

2. 副本将源上的日志复制到自己的中继日志中

3. 副本服务中继日志中的事件，将其重放到副本数据之上

4. 

5. 在复制架构中，读取和重放日志事件是解耦的，这就允许读取日志和重放日志异步进行，也就是说，这里的IO线程和SQL线程都是可以独立运行的。

复制原理

选择复制格式
MySQL提供了三种不同的二进制日志格式用于复制：基于语句的、基于行的和混合模式。可以通过系统参数binlog_format控制日志写入时使用哪种日志格式。
基于语句的复制是通过记录所有在源端执行的数据变更语句来实现的。当副本从中继日志读取到事件并执行时，实际上是重新执行在源端执行过的SQL语句。其最大的弊端则在于会遇到某些具有“不确定性”的SQL语句问题。假设有一条语句删除了一张有1000行记录的表中的100行，但没有用ORDER BY子句。如果在源和副本上，记录的排序不同，这条SQL语句在源和副
本上删除的100条记录就会不同，这将导致数据不一致。
基于行的复制将事件写入二进制日志，该事件包含了该行记录发生了什么改变。这听起来很简单，但是，因为这种模式的确定性，相比基于语句的方式来说，其实发生了很大的变化。使用基于行的复制，通过查看二进制日志中的事件，可以看到究竟是哪一行记录发生了什么样的改变。在基于语句的复制模式下，SQL语句在执行时被解析，服务器在执行时找到所有需要变更的记录进行操作。而在基于行的复制模式下，每条被改变的记录都会作为事件被写入二进制日志，这可能会让二进制日志的大小发生巨大的增长。
**混合模式”（the mixed method）**试图结合以上两种格式的优点。在这种模式下，事件的写入，默认使用基于语句的格式，仅在需要时才切换到基于行的格式。
**最后建议：**我们建议坚持使用基于行的复制，除非某些场景下明确需要临时使用基于语句的复制。基于行的复制提供了最安全的数据复制方法。

全局事务标识

MySQL新增了另一种跟踪复制位点的方法：全局事务标识符（GTID）。使用GTID，源服务器提交的每个事务都被分配一
个唯一标识符。此标识符是由server_uuid [2] 和一个递增的事务编号组成的。当事务被写入二进制日志时，GTID也随之被写入。防止漏掉事件。GTID解决了运行MySQL复制的一个令人痛苦的问题：处理日志文件和位置。强烈建议始终按照MySQL官方文档中的说明，在数据库中启用GTID。

崩溃后的复制安全

为了尽量降低复制中断的可能性，建议MySQL的部分参数按照如下讲解内容进行配置。

innodb_flush_log_at_trx_commit参数

innodb_flush_log_at_trx_commit=0
表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。
innodb_flush_log_at_trx_commit=1，
表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。
innodb_flush_log_at_trx_commit=2，
表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

sync_binlog=1

该变量控制MySQL将二进制日志数据同步到磁盘的频率。将此值设置为1意味着在每次事务执行的时候都会把二进制日志同步写入磁
盘。这可以防止在服务器崩溃时丢失事务。就像之前的配置参数一样，它也会增加磁盘写入量。

relay_log_info_repository=TABLE

以前，MySQL的复制通常依赖磁盘上的文件来跟踪复制位置。这意味着，复制完成事务操作之后，还需要完成同步写入磁盘操作。
如果在事务提交和同步之间发生了服务器崩溃，此时，磁盘上的文件将可能包含错误的文件和位置信息。在该配置下，该信息将
被转移到MySQL本身的InnoDB表中，允许复制更新同一事务中的事务和中继日志信息。这会在一个原子操作中完成，并有助于崩溃
恢复。

relay_log_recovery=ON

简单地说，该参数使得副本服务器在检测到崩溃时会丢弃所有本地中继日志，并从源服务器中获取丢失的数据。这确保了在崩溃
中发生的磁盘上的任何损坏或不完整的中继日志都是可恢复的。配置该参数后，不再需要配置sync_relay_log，因为在发生崩溃
时，中继日志将被删除，也就无须花费额外的操作将它们同步到磁盘。

复制延迟

在一个拓扑结构中，某些副本有一些延迟反而是有好处的。在这个策略下，可以让副本中的数据保持在线并且持续运行，
但同时落后于源数据库数小时或者数天。延迟复制的配置语句是CHANGE REPLICATION SOURCE
TO，配置选项为SOURCE_DELAY。想象一下这样的场景，你正在处理大量数据，突然意外地做了一些变
更：删除了一个表。从备份中恢复可能需要几个小时。如果使用了延迟复制的副本，则可以找到DROP TABLE语句对应的GTID，使副本服务器的复制运行到表被删除之前的时间点，这会大大减少修复时间。

多线程复制

最新的MySQL版本则提供了多线程复制能力（参见图9-2），可以在副本端运行多个SQL线程，从而
加快本地中继日志的应用。

多线程复制有两种模式：DATABASE和LOGICAL_CLOCK。在DATABASE模式
下，可以使用多线程更新不同的数据库；但不会有两个线程同时更新同一个数据库。如果将数据分布在MySQL的多个数据库中，则可以同时并且一致地更新它们，这种模式非常有效。另一个模式LOGICAL_CLOCK允许对同一个数据库进行并行更新，只要它们都是同一个二进制日志组提交的一部分。此外，确保你的副本配置了参数replica_preserve_commit_order，这样就不会出现无序提交的问题。

复制过滤器

复制过滤选项可以让副本仅复制一部分数据，不过这个功能并没有想 象中那么实用。有两种复制过滤器：一种是从源上的二进制日志中过滤事件，另一种是从副本上的中继日志中过滤事件。图9-3展示了这两 种类型

控制二进制日志过滤的选项是binlog_do_db和binlog_ignore_db。事实上，并不建议启用这些参数，你可能会经常要向老板解释为什么数据会永久消失并且无法恢复。