1. 复制概述

• MySQL支持两种复制方式：基于语句的复制和基于行的复制。这两种方式都是通过在主库上记录二进制日志，在备库重放日志的方式来实现异步的数据复制。

复制解决的问题

• 数据分布：可以通过复制将数据分布在不同地理位置的数据中心。
• 负载均衡：通过MySQL复制可以将读操作分布到多个服务器上，实现对读密集型应用的优化。
• 备份：复制是备份的一项技术补充。
• 高可用性和故障切换：复制能够帮助应用程序避免单点失败，缩短宕机时间。
• MySQL升级测试

复制如何工作

• 复制有三个步骤：
  1. 在主库上把数据更改记录到二进制日志（Bin Log）中。
  2. 备库将主库上的日志复制到自己的中继日志（Relay Log）。
  3. 备库读取中继日志中的事件，将其重放到备库数据之上。
     

2. 配置复制

创建复制账号

• 必须在主句中创建一个用户，并赋予其合适的权限。备库IO线程以该用户名连接到主库并读取其二进制日志。

配置主库和备库

• 主库需要打开二进制日志并指定一个独一无二的服务器ID（server ID）。
• 使用SHOW MASTER STATUS命令检查主库是否开启了二进制日志。
• 备库也是类似配置。除此之外，备库还有一些其他配置选项，如relay_log（指定中继日志的位置和命名）、log_slave_updates（运行备库将其重放的事件也记录到自身的二进制日志中）和 read_only（阻止任何没有特权权限的线程修改数据）。

启动复制

• 使用CHANGE MASTER TO语句连接主库。
• 使用SHOW SLAVE STATUS命令检查复制正确执行。
• 使用START SLAVE命令开始复制。

从另一个服务器开始复制

• 有几种办法初始化备库或者从其他服务器克隆数据到备库。包括从主库复制数据、从另外一台备库克隆数据，以及使用最近一次备份来启动备库。需要有三个条件让主库和备库保持同步：
  • 在某个时间点的主库的数据快照。
  • 数据快照在二进制日志文件中的偏移量。即日志文件坐标。
  • 从快照时间到现在的二进制日志。

其他推荐的复制配置

• 主库上二进制日志最重要的选项是sync_binlog。如果开启该选项，MySQL每次在提交事务前都会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件。
• skip_salve_start选项能够组织备库在崩溃后自动启动复制。如果备库在崩溃后自启动且处于不一致的状态，可能会导致更多的损坏。

3. 复制的原理

基于语句的复制

• 优点：
  • 实现更为简单。操作更加灵活。
  • 不会使用太多带宽。
• 缺点
  • 有一些语句无法正确地复制。如语句中使用了当前时间戳的语法等。

基于行的复制

• 优点
  • 可以正确地复制每一行。
  • 可以更高效地复制数据。有些查询重放的代价很高，但是可能最终只修改了几行数据。
• 缺点
  • 无法判断具体执行了哪些SQL。
  • 有一些语句使用基于行的复制代价会很大。比如更新了很多行的数据的语句。

复制文件

• 除了二进制文件和中继日志文件，有一些其他的文件也会在复制中用到。
• mysql-bin.index：该文件用于记录磁盘上的二进制日志文。这里的index并非表的索引，而是说这个文件的每一行包含了二进制文件的文件名。MySQL依赖这个文件识别二进制日志文件。
• mysql-relay-bin.index：中继日志文件的索引文件，和mysql-bin.index类似。
• master.info：保存备库连接到主库所需要的信息，格式为纯文本。
• relay-log.info：包含了当前备库复制的二进制日志和中继日志坐标。

发送复制事件到其他备库

• log_slave_updates选项可以让备库变成其他服务器的主库。在设置该选项后，MySQL会将其执行过的事件记录到它自己的二进制日志中。这样它的备库就可以从其日志中检索并执行事件。
• 作为源数据库的主库可以将其数据变化传递给没有与其直接相连的备库上。默认情况下这个选项是打开的。
  

复制过滤器

• 有两种复制过滤的方式：在主库上过滤记录到二进制日志中的事件，以及在备库上过滤记录到中继日志的事件。通常不要使用过滤器，因为它们可能会破坏复制的，还可能导致从某个时间点的备份进行数据恢复时失败。

4. 复制拓扑

• 可以在任意个主库和备库之间建立复制，只有一个限制，每一个备库只能有一个主库。

一主库多备库

• 在有少量写和大量读时，这种配置是非常有用的。它能够满足多种需求：
  • 为不同的角色使用不同的备库。(例如添加不同的索引或者使用不同的存储引擎，用以测试或数据分析等)
  • 将一台备库作为待用的主库，除了复制没有其他数据传输。
  • 将一台备库放到远程数据中心，用做灾难恢复。
• 结构图如下：
  

主 - 主复制

• 主 — 主复制包含两台服务器，每一个都被配置成对方的主库和备库。也就是一对主库。

• MySQL 不支持多主库复制。

• 主动 - 主动模式下的主 — 主复制中，两台服务器都可以写入数据并复制到对方服务器。但是这种模式会遇到数据冲突、数据不一致等问题，一般只用做特殊目的。它的结构如下：
  

• 主动 - 被动模式下的主 - 主复制中，其中一台服务器是只读的被动服务器，不接受除复制以外的写入。

  • 这种方式使得反复切换主动和被动服务器非常方便，因为服务器的配置是对称的。
  • 这种方式使得故障转移和故障恢复，执行维护，优化表等任务变得容易。
  • 这种 主动 - 被动的主 - 主结构在某种意义上类似于创建一个热备份，但是可以使用这个备份提高性能。
  • 它的结构图如下：
    

• 拥有备库的主 - 主结构是为每一个主库增加一个备库。这样做增加了冗余，对于不同地理位置的复制拓扑，能够消除单点失效的问题。
  

环形复制

• 环形复制结构图如下。双主结构实际上是环形结构的一种特例。环形结构可以用三个或更多的主库，每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库。环形结构非常脆弱，应该尽量避免。
  

主库、分发主库、以及备库

• 当备库足够多时，会对主库造成很大的负载。因为每个备库都会在主库上创建一个线程并读取二进制日志文件。
• 如果需要多个备库，一个好办法是从主库移除负载并使用分发主库。分发主库事实上也是一个备库，它唯一目的就是提取和提供主库的二进制日志。
• 按照通用准则，如果主库接近满负载，不应该为其建立10个以上备库。在某些情况下，可以通过设置 slave_compressed_protocol 来节约一些主库带宽。这对跨数据中心的复制很有好处。
• 在分发主库上的表使用 blackhole 存储引擎，可以支持更多的备库。因为 blockhole 存储引擎不会记录任何数据，只会记录二进制文件。
• 使用分发主库的另外一个缺点是无法使用一个备库来代替主库。由于分发主库的存在导致各个备库与原始主库的二进制日志坐标已不同。
• 分发主库结构如下：
  

树或金字塔型

• 树或金字塔形的结构如下。它的好处是减轻了主库的负担，缺点是中间层出现的任何错误都会影响到多个服务器。
  

定制的复制方案

• 选择性复制

  • 为了利用访问局部性原理(locality of reference)，并将需要读的工作集驻留在内存中，可以复制少量数据到备库中。如果每个备库只拥有主库的一部分数据，并且将读分配给备库，就可以更好地利用备库的内存。并且每个备库也只有主库一部分的负载写入，这样主库能力更强并且能保证备库延迟。
  • 如果读操作无法在备库上找到数据，还可以通过主库在查询，即使不能从主库上读取所有数据，也可以移除大量的主库读负担。

• 分离功能

  • 许多应用混合了在线事务处理(OLTP)和在线数据分析(OLAP)的查询。OLTP 查询比较短并且是事务型的，OLAP 则通常很大，也很慢。
  • 一个常见的方法是将 OLTP 服务器的数据复制到专门为 OLAP 工作负载准备的备库上。

• 数据归档

  • 可以在备库上实现数据归档，也就是在备库上保留主库上删除过的数据。确保主库上是通过delete 删除数据且确保 delete 语句不会同步到备库即可实现。
  • 有两种通常的办法：一种是在主库上选择性地禁止二进制日志，另一种是在备库上使用 replicate_ingore_db 规则。但是这两种方式都很危险。

• 模拟多主库复制

  • 当前 MySQL 不支持多主库复制。但是可以通过把一台备库轮流指向多台主库的方式来模拟这种结构。如果主库的负载很低，并且主库之间不会产生更新冲突，就会工作得很好。
  • 另一种模拟多主库复制的方式是，简单将 Server1复制到 Server2，再从 Server2复制到备库。如果 Server2上为从 Server1复制的数据使用 blackhole 存储引擎，就不会包含任何 Server1的数据。结构图如下：
    

• 创建日志服务器

  • MySQL 复制还可以用于创建没有数据的日志服务器。它唯一的目的就是更加容易重放并且过滤二进制日志事件。它对崩溃后重启复制很有帮助。

5. 复制和容量规划

• 复制只能拓展读操作，无法拓展写操作。
• 对数据进行分区是唯一可以拓展写操作的方法。
• 在构建一个大型应用时，有意让服务器不被充分使用，是一种聪明且划算的方式，尤其在使用复制的时候。有多余容量的服务器可以更好地处理负载尖峰，也有更多能力处理慢速查询和维护工作，并且能够更好地跟上复制。

6. 复制管理和维护

监控复制

• 可以使用SHOW MASTER STATUS命令来查看当前主库的二进制日志位置和配置。使用SHOW MASTER LOGS命令查看主库当前有哪些二进制日志是在磁盘上的，使用SHOW BINLOG EVENTS命令来查看复制事件。

测量备库延迟

• SHOW SLAVE STATUS命令输出的 Second_behind_master 列理论上显示了备库的延时，但是并不总是准确。
• 最好的方法是，使用 heartbeat record，这是在主库上每秒更新一次的时间戳。为了计算延时，可以直接用备库当前的时间戳减去心跳记录的值。

确定主备是否一致

• 主备一致应该是一种规范，而不是例外。
• Percona Toolkit 里的 pt-table-checksum 是一种主备一致校验的工具。它的方式是在主库上执行 INSERT ... SELECT查询，这些查询对数据进行校验并将结果插入一个表中。这些语句通过复制传递到备库，并在备库执行一遍，然后可以比较主备上的结果是否一致。由于该方法是通过复制工作的，可以给出一致的结果而无须同时把主备的表都锁上。

从主库重新同步备库

• 传统的修复不一致的办法是关闭备库，然后重新从主库复制一份数据。这样的缺点是不太方便，尤其是数据量特别大时。如果能找出并修复不一致的数据，要比重新克隆数据要有效的多。
• Percona Toolkit 里的 pt-table-checksum 是一种解决主备不一致的工具。它同样通过复制工作，在主库上执行查询，在备库上重新同步。

改变主库

• 如果要把备库指向一个新的主库，只需在备库使用CHANGE MASTER TO命令，并指定合适的值。整个过程中最难的是获取新主库上合适的二进制日志位置，这样备库才可以从和老主库相同的逻辑位置开始复制。
• 把备库提升为主库要更困难，有两种场景需要将备库替换为主库，一种是计划内的提升，一种是计划外的提升，
• 计划内的备库提升为主库按如下步骤操作：

1. 客户端停止当前主库的写操作。
2. 通过FLUSH TABLES WITH READ LOCK在主库上停止所有活跃地写入。或者在主库上设置 read_only 选项。从这一刻开始应该禁止向即将被替换的主库做任何写入。
3. 选择一个备库作为新的主库，并确保它已经完全跟上主库。
4. 确保新主库和旧主库数据是一致的。
5. 在新主库上执行STOP SLAVE。
6. 在新主库上执行CHANGE MASTER TO MASTER_HOST=''，然后再执行RESET SLAVE，使其断开与主库的链接，并丢弃 master.info 里的信息。
7. 执行SHOW MASTER STATUS记录新主库的二进制日志坐标。
8. 确保其他备库已经追上。
9. 关闭旧主库。
10. 激活新主库上事件。
11. 将客户端链接到新主库。
12. 在每台备库上执行CHANGE MASTER TO语句，使用第7步中获得的二进制日志坐标，来指向新的主库。

• 计划内的备库提升为主库。当主库崩溃时，需要提升一台备库来代替它。这种情况会有一些问题，比如主库已经发生修改，但是还没有更新到备库的。或者一条语句在主库上执行了回滚，但是备库上没有回滚，这样备库可能超过主库的逻辑复制位置。如下是此种情况的操作步骤：

1. 确定哪台备库的数据最新，检查每台备库上SHOW SLAVE STATUS命令输出。选择其中 Master_Log_File/Read_Master_Log_Pos 的值最新的那个。
2. 让所有备库执行完其从崩溃前的旧日志那获得的中继日志。
3. 执行前一小节的5~7步。
4. 比较每台备库和新主库上的Master_Log_File/Read_Master_Log_Pos 的值。
5. 执行前一小节的10~12步。
   • 假如已经在所有的备库上开启了 log_bin 和 log_slave_updates，可以帮助将所有的备库恢复到一个一致的时间点。

• 确定期望的日志位置
  • 如果有备库和新主库的日志位置不相同，则需要找到该备库最后一条执行的事件在新主库的二进制日志中相应的位置，然后再执行CHANGE MASTER TO。

在一个主 - 主配置中交换角色

• 主 - 主配置下切换角色时，必须确保任何时候只有一个服务器可以写入。否则可能导致写入冲突。可以通过以下步骤切换服务器角色，避免更新冲突的危险。

1. 停止主动服务器上的所有写入。
2. 在主动服务器上设置 read_only。
3. 在主动服务器上执行SHOW MASTER STATUS并记录二进制日志坐标。
4. 使用主动服务器上的二进制日志坐标在被动服务器上执行SELECT MASTER_POS_WAIT()该语句将阻塞住，直到复制跟上主动服务器。
5. 在被动服务器上关闭 read_only。
6. 修改应用程序，使其写入到新的主动服务器中。

7. 复制的问题和解决方案

数据损坏或丢失的错误

MySQL的复制并不能很好地从服务器崩溃、掉电、磁盘损坏、内存或网络错误中恢复。遇到这些问题时几乎可以肯定都需要从某个点开始重启复制。

• 主库意外关闭。主库意外关闭会导致备库IO线程可能处于一直读不到尚未写入磁盘的事件的状态中。解决方案是指定备库从下一个二进制日志的开头读日志。但是一些日志事件将因此永久丢失。
• 备库意外关闭。备库意外关闭重启后，会去读master.info文件以找到上次停止复制的位置。但是该文件并不是同步写到磁盘，文件中存储的信息可能是错误的。新版本MySQL提供了一些选项控制将master.info和其他文件刷新到磁盘。
• 主库的二进制日志损坏。主库上的二进制日志损坏，处理忽略损坏的位置别无选择。
• 备库的中继日志损害。如果主库上的日志是完好的，可以通过Change MASTER TO命令丢弃并重新获取损坏的事件。只需将备库指向它当前正在复制的位置。
• 二进制日志与InnoDB日志不同步。当主库崩溃时，InnoDB肯将一个事务标记为一体机，但是该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志以及保存，否则没有任何办法恢复到丢失的任务。可以设置sync_binlog选项防止该问题。

使用非事务型表

• 当对非事务型表的更新发生错误时，例如查询在完成前被kill，就可能导致主库和备库的数据不一致。

混合事务型表和非事务型表

• 如果混合使用事务型表和非事务型表，并且发生了一次回滚，MySQL能够回滚事务型表的更新，但非事务型表则无法处理。
• 防止该问题的唯一办法是避免混合使用事务型表和非事务型表。

不确定的语句

• 当使用基于语句的复制模式时，如果通过不确定的方式更改数据可能导致主备不一致。比如一条带LIMIT的UPDATE 语句更改的数据取决于查找行的顺序，除非保证主库上和备库上的顺序相同。
• 基于行的复制则没有上述限制。

主库和备库使用不同的存储引擎

• 如果发现主库和备库的某些表以及不同步，除了检查更新这些表的查询外，还需要检查两台服务器上使用的存储引擎是否相同。

备库发生数据改变

• 基于语句的复制方式前提是确保备库上有和主库相同的数据，因此不应该对备库数据做任何更改。
• 数据不一致还可能会在表之间传播，比如INSERT ... SELECT语句。
• 唯一的解决办法就是重新从主库同步数据。

不唯一的服务器ID

• 如果不小心为两台备库设置了相同的服务器ID。则主库上会发现两台备库中只有一台连接到主库。在备库的错误日志中，则会发现反复的重连和连接断开信息，但不会提交被错误配置的服务器ID。

未定义的服务器ID

• 如果没有在my.cnf里定义服务器ID，可以通过CHANGE MASTER TO语句来设置备库，但却无法启动复制。

对未复制数据的依赖性

• 如果在主库上备库不存在的数据库或表，复制会很容易意外中断。同样的，如果主库上创建一个备库上以及存在表，复制也可能中断。

丢失的临时表

• 如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。重启备库后， 在采用基于语句的复制方式时，所有依赖该临时表的语句都会失败。

InnoDB加锁读引起的锁争用

• 正常情况下，InnoDB的读操作是非阻塞的，但在某些情况下需要加锁。特别是在使用基于语句的复制方式时，执行INSERT ... SELECT语句会锁定源表上所有的行。MySQL需要加锁以确保该语句的执行结果在主库和备库上是一致的。实际上，加锁导致主库上的语句串行化，以确保和备库上的执行方式相符。
• 这种设计可能导致锁竞争、阻塞，以及锁等待超时等情况。一种缓解的办法就是避免让事务开启太久以减少阻塞。可以在主库上尽快提交事务以释放锁。
• 如果没有加锁，记录在日志中的事务顺序在主备上可能会产生不同的结果。
• 基于行的复制由于记录了数据的变化而非语句，因此不会存在这个问题。

在主 - 主复制结构中写入两台主库

• 有两个变量可以用于帮助解决AUTO_INCREMENT自增主键冲突的问题：auto_increment_increment和auto_increment_offset。可以通过设置这两个变量来错开主库和备库生成的数字，这样可以避免自增列的冲突。
• 也可以自己生成不冲突的主键值。一种办法是创建一个多个列的主键，第一列使用服务器ID值，但是这样会使主键的值变得更大。也可以使用只有一列的主键，在主键的高字节位存储服务器ID。

过大的复制延迟

• MySQL单线程的复制设计导致备库的效率相当低下。即使备库有很多磁盘、CPU或者内存，也会很容易落后于主库。因为备库的单线程通常只会有效地使用一个CPU和磁盘。而事实上，备库通常都会和主库使用相同配置的机器。
• 备库上的锁同样也是问题。其他在备库运行的查询肯会阻塞住复制线程。因为复制是单线程的。
• 除了购买更快的磁盘和CPU，备库没有太多的调优空间。
• 有一些原则可以尽可能避免过大的复制延迟：
  • 不要重复写操作中代价较高的部分。
  • 在复制之外并行写入。
  • 为复制线程预取数据。

来自主库过大的包

• 如果主库的max_allowed_packet的值和备库的不匹配，主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时，可能会碰到各种问题，包括无限报错和充实，或者中继日志损坏。

受限制的复制带宽

• 如果使用首先的带宽进行复制，可以开启备库上的salve_compressed_protocol选项。当备库连接主库时，会请求一个被压缩的连接，使用的压缩引擎是zlib，其代价是需要额外的CPU时间在主库上压缩数据和备库上解压数据。

8. MySQL复制的高级特性

• MySQL5.6增加了多线程（并行）复制一件事当前单线程复制的凭借
• 半同步复制
  • 半同步复制可以确保备库拥有主库数据的拷贝，减少了潜在的数据丢失风险。
  • 半同步复制在提交过程中增加了一个延迟：当提交事务时，在客户端接收到擦好像结束反馈前必须保证二进制日志已经传输到至少一台备库上。
  • 半同步复制中，主库已经完成提交，之上通知客户端被延迟了。
  • 备库在接受到事务后即发送反馈而非完成事务后发送。
  • 如果备库一直没有回应已收到事件，会超时并转化为正常的异步复制模式。
  • 半同步复制在某些常见下确实能够提供足够的灵活性以改善性能。写入远程的内存有时比写入本地的磁盘要更快。
• MySQL5.5提供了复制心跳，保证备库一直于主库相联系，避免悄无声息地断开连接。