MySQL详解7.复制

---

点击进入我的博客

1 概述

MySQL内建的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库的方式来进行数据同步。复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。

复制的功能

复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性灾难恢复、备份以及数据仓库等工作的基础。通常用复制来实现以下功能：数据分布、负载均衡、备份、高可用性和故障切换、MySQL升级测试。

主备的开销

• 复制通常不会增加主库的开销，主要是启用二进制日志带来的开销。
• 每个备库也会对主库增加一些负载（例如网络IO开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。
• 另外锁竞争也可能阻碍事务的提交。
• 最后，如果是从一个高吞吐量的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。

复制与主备

• 一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
• 通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。在一主库多备库的架构中，写操作会被执行多次，这时候整个系统的性能取决于写入最慢的那部分。
• 当使用一主库多备库的架构时，可能会造成一些浪费，因为本质上它会复制大量不必要的重复数据。

2 复制的工作流程

复制的流程

2.1 复制有三个步骤

1. 在主库上把数据更改记录到二进制日志(Binary Log)中。
2. 备库将主库上的日志复制到自己的中继日志(Relay Log)中。
3. 备库读取中继日志中的事件，将其重放到备库数据之上。

记录二进制文件

在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。 MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后,主库会告诉存储引擎可以提交事务了。

复制到中继日志

首先，备库会启动一个IO线程，IO线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储(binlog dump)线程，这个进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库IO线程会将接收到的事件记录到中继日志中。

读取并重放

备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上IO线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。

2.2 配置复制

配置复制的步骤

1. 在每台服务器上创建复制账号。
2. 配置主库和备库。
3. 通知备库连接到主库并从主库复制数据。

创建复制账号

MySQL会赋予一些特殊的权限给复制线程。在备库运行的IO线程会建立一个到主库的TCP/IP连接，这意味着必须在主库创建一个用户，并赋予其合适的权限。备库IO线程以该用户名连接到主库并读取其二进制日志。如下：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@ 192. 168.0.% IDENTIFIED BY 'password';

配置主库和从库

主要是配置主库和从库的my.cnf文件。

# 主库
log_bin = mysql-bin
server_id =10 # 必须明确地指定一个唯一的服务器ID

# 从库
log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin # 中继日志位置
log_slave_updates = 1 # 允许备库将其重放的事件记录到二进制文件
read_only = 1

启动复制

下一步是告诉备库如何连接到主库并重放其二进制日志。这一步不要通过修改my.cnf来配置，而是使用CHANGE MASTER TO语句，该语句完全替代了my.cnf中相应的设置，并且允许以后指向别的主库时无须重启备库。

CHANGE MASTER TO MASTER_HOST='server1',
MASTER USER='repl',
MASTER PASSWORD='password',
MASTER LOG FILE='mysql-bin 000001',
MASTER LOG POS=0;

3 复制的原理

3.1 两种复制方式

MySQL支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。这意味着,在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

基于语句的复制

在 MySQL5.0 及之前的版本中只支持基于语句的复制，基于语句的复制模式下，主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍。

• 优点：实现相当简单、二进制日志里的事件更加紧凑、不会使用太多带宽。
• 缺点：同一条SQL在主库和备库上执行的时间可能不同、在着一些无法被正确复制的SQL、存储过程和触发器在使用基于语句的复制模式时也可能存在问题、更新必须是串行的（这需要更多的锁）

基于行的复制

MySQL5.1开始支持基于行的复制，这种方式会将实际数据记录在二进制日志中。

• 优点：可以正确地复制每一行；无须重放更新主库数据的查询，使用基于行的复制模式能够更高效地复制数据。
• 缺点：重放一些查询的代价可能会很高；很难进行时间点恢复，但这并非不可能。

3.2 备库也可以作为主库

log_slave_updates选项可以让备库变成其他服务器的主库。在设置该选项后，MySQL会将其执行过的事件记录到它自己的二进制日志中。这样它的备库就可以从其日志中检索并执行事件。

image.png

3.3 复制过滤器

复制过滤选项允许你仅复制服务器上一部分数据，不过这可能没有想象中那么好用。有两种复制过滤方式：在主库上过滤记录到二进制日志中的事件，以及在备库上过滤记录到中继日志的事件。使用选项 binlog_do_db和 binlog_ ignore db来控制过滤，一般不需要开启它们。

4 复制拓扑

复制拓扑的基本原则

1. 一个MySQL备库实例只能有一个主库
2. 每个备库必须有一个唯一的服务器ID
3. 一个主库可以有多个备库（即一个备库可以有多个兄弟备库）
4. 如果打开了log_slave updates选项，一个备库可以把其主库上的数据变化传播到
   其他备库。

4.1 一主多备

一主多备的结构和一主一备差不多简单，因为备库之间根本没有交互，它们仅仅是连接到同一个主库上。在有少量写和大量读时，这种配置是非常有用的，可以把读分摊到多个备库上，直到备库给主库造成了太大的负担，或者主备之间的带宽成为瓶颈为止。

4.2 主动-主动模式下的主-主复制

主-主模式

主-主复制（也叫双主复制或双向复制）包含两台服务器，每一个都被配置成对方的主库和备库，换句话说，它们是一对主库。

应用场景

主动-主动模式下主-主复制有一些应用场景，但通常用于特殊的目的。一个可能的应
用场景是两个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。

问题

这种配置最大的问题是如何解决冲突，两个可写的互主服务器导致的问题非常多。这通常发生在两台服务器同时修改一行记录，或同时在两台服务器上向一个包含AUTO INCREMENT列的表里插入数据。总的来说，允许向两个服务器上写入所带来的麻烦远远大于其带来的好处。

4.3 主动-被动模式下的主-主复制

这是前面描述的主-主结构的变体，它能够避免我们之前讨论的问题。这也是构建容错性和高可用性系统的非常强大的方式，主要区别在于其中的一台服务器是只读的被动服务器

这种方式使得反复切换主动和被动服务器非常方便，因为服务器的配置是对称的。这使得故障转移和故障恢复很容易。它也可以让你在不关闭服务器的情况下执行维护、优化表、升级操作系统(或者应用程序、硬件等)或其他任务。然而，你不会获得比单台服务器更好的写性能。

如何设置

1. 确保两台服务器上有相同的数据。
2. 启用二进制日志，选择唯一的服务器ID，并创建复制账号。
3. 启用备库更新的日志记录，这是故障转移和故障恢复的关键。
4. 把被动服务器配置成只读，防止可能与主动服务器上的更新产生冲突，这一点是可
   选的。
5. 启动每个服务器的MySQL实例。
6. 将每个主库设置为对方的备库，使用新创建的二进制日志开始工作。

主动服务器上更新时发生的事件

1. 主动服务器上更新时，更新被记录到二进制日志中，通过复制传递给被动服务器的中继日志中。
2. 被动服务器执行查询并将其记录到自己的二进制日志中，由于事件的服务器ID与主动服务器的相同,因此主动服务器将忽略这些事件。

4.4 拥有备库的主-主结构

拥有备库的主-主结构

另外一种相关的配置是为每个主库增加一个备库。这种配置的优点是增加了冗余，对于不同地理位置的复制拓扑，能够消除站点单点失效的问题。你也可以像平常一样,将读查询分配到备库上。如果在本地为了故障转移使用主-主结构，这种配置同样有用。当主库失效时，用备库来代替主库还是可行的，虽然这有点复杂。

4.5 环形复制

环形复制

双主结构实际上是环形结构的一种特例。环形结构可以有三个或更多的主库。每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库。
环形结构没有双主结构的一些优点，例如对称配置和简单的故障转移，并且完全依赖于环上的每一个可用节点，这大大增加了整个系统失效的几率。总地来说，环形结构非常脆弱，应该尽量避免。

4.6 分发主库

分发主库

我们之前提到当备库足够多时，会对主库造成很大的负载。每个备库会在主库上创建一个线程，并执行binlog dump命令，该命令会读取二进制日志文件中的数据并将其发送给备库。每个备库都会重复这样的工作，它们不会共享 binlog dump的资源。如果有很多备库，并且有大的事件时，主库上的负载会显著上升；另一方面，如果备库请求的数据不在文件系统的缓存中，可能会导致大量的磁盘检索，这同样会影响主库的性能并增加锁的竞争。
因此，如果需要多个备库，一个好办法是从主库移除负载并使用分发主库。分发主库事实上也是一个备库，它的唯一目的就是提取和提供主库的二进制日志。多个备库连接到分发主库，这使原来的主库摆脱了负担。

4.7 树或金字塔型

树形

5 复制其他细节

复制的局限性

• 复制只能扩展读操作，无法扩展写操作。

主备延时

最好的解决办法是使用heartbeat record，这是一个在主库上会每秒更新一次的时间戳。为了计算延时，可以直接用备库当前的时间戳减去心跳记录的值。

主备一致性

• 在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为My SQL自身的特性导致数据不一致。
• MySQL并没有内建的方法来比较一台服务器与别的服务器的数据是否相同。它提供了一些组件来为表和数据生成校验值，例如 CHECKSUM TABLE。但当复制正在进行时，这种方法是不可行的。
• Percona Toolkit里的pt-table-checksum能够解决上述几个问题。其主要特性是用于确认备库与主库的数据是否一致。

从主库重新同步备库

• 传统的修复不一致的办法是关闭备库，然后重新从主库复制一份数据。
• 最简单的办法是使用mysqldump转储受影响的数据并重新导入。
• pt-table-sync是 Percona Toolkit中的另外一个工具，可以解决该问题。该工具能够高效地查找并解决表之间的不同。