第十二章 复制
# 第十二章 复制
MySQL实现了异步主从复制。主库记录更新日志，从库读取日志并按顺序执行。本章将讨论MySQL复制的一些细节。
# 概述
MySQL复制相对简单直接。一台服务器可以充当主库或从库。主库维护一个更新日志，由于历史原因，这个日志被称为二进制日志（binary log）。二进制日志记录事件，每个事件包含一些相关信息，以便从库能够以与主库完全相同的方式执行更新。大多数事件只是以某种方式更新数据库的SQL查询。不过，还必须存储一些元数据，从库需要这些元数据来重新创建更新的上下文环境，从而使更新查询能产生与主库相同的结果。
从库连接到主库，并在读取主库的二进制日志时开始执行更新。从库上有两个线程来完成这项工作：I/O线程和SQL线程。I/O线程下载主库二进制日志的内容，并将其本地存储在称为中继日志（relay log）的临时文件中。SQL线程处理中继日志，重新创建原始的执行上下文并执行更新。
从库通过两个参数跟踪其在复制过程中的位置：当前日志名称和当前日志位置。如果从库与主库断开连接后重新连接，它将请求从当前日志中的当前位置开始获取更新。主库跟踪日志序列顺序，在二进制日志传输过程中，一旦到达最后一个日志的末尾，它会自动切换到下一个日志。在初始连接时，从库请求读取主库已知的第一个日志。也可以使用CHANGE MASTER TO命令让从库从任意位置开始复制。
复制是异步的。这意味着在未来某个时刻，从库会追赶上主库的当前状态，但主库通常不会等待从库追上来。从库和主库之间的延迟取决于许多因素，包括连接两台服务器的网络速度、正在运行的更新查询类型、主库和从库的处理能力以及两台服务器的负载情况 。
可以通过组合使用FLUSH TABLES WITH READ LOCK、SHOW MASTER STATUS和SELECT MASTER_POS_WAIT()查询，以编程方式同步主库和从库。虽然这种技术在许多情况下可能有用，但通常不太实用。网络延迟和中断、主库或从库的负载峰值以及其他可能的情况，都可能给应用程序用户带来不可接受的延迟。
# 基于语句的复制与基于行的复制
在两个SQL数据库之间复制数据可以在SQL层面或行层面进行。在基于语句的复制方法中，主库会记录每一条可能修改数据的SQL语句。然后，这些语句在从库上针对相同的初始数据集和相同的上下文环境重新执行。在基于行的复制方法中，主库记录每一行的修改，然后在从库上应用这些修改。这两种方法各有优缺点。基于语句的复制通常在主库和从库之间传输的数据较少，并且在更新日志中占用的空间也较小。它无需处理行的格式问题，数据传输的紧凑性通常使其性能更好。另一方面，为了使更新在从库上产生与在主库上相同的结果，必须记录大量的执行上下文信息。在某些情况下，可能无法提供这样的上下文。基于语句的复制也更难维护，因为新的SQL功能的添加通常需要大量的代码更新才能正确进行复制。
基于行的复制更为直接。它不需要上下文信息，只需要知道正在更新哪条记录以及向该记录写入了什么内容。在有良好代码基础的情况下，基于行的复制的维护也相当简单。由于日志记录发生在较低的级别，新代码自然会执行修改数据库的必要低级例程，这些例程会进行日志记录，而无需额外的代码更改。然而，在一个频繁执行诸如UPDATE customer SET status='Current' WHERE id BETWEEN 10000 and 20000这类查询的系统中，基于行的复制会产生不必要的大量更新日志，并在主库和从库之间产生大量不必要的网络流量。它需要深入了解记录的内部物理格式，并且仍然需要处理模式修改问题。在某些情况下，与增加的I/O相关的性能开销可能变得不可接受。
MySQL最初采用基于语句的复制。在5.0版本之前，开发者设法解决了这种方法的缺点。他们发明了一些创造性的技术，以便在困难情况下正确复制执行上下文。然而，随着存储过程的引入，这种方式变得难以维持。存储过程能够根据多种条件以多种不同方式进行分支。从5.1.5版本开始，MySQL复制开发者通过添加逐行复制的选项来解决这个问题。
从5.1.8版本开始，MySQL可以使用三种复制模式：行模式、语句模式和混合模式。该模式由配置变量binlog_format控制。在行模式下，只要可行，复制就是基于物理行的：当实际的行被更新时，整个更新后的行都会被写入二进制日志。然而，当创建、删除或更改新表时，会记录实际的SQL语句。
在语句模式下，复制的工作方式与早期版本相同：每一次更新都会记录SQL语句。在混合模式下，主库会根据每个查询决定使用基于语句的日志记录还是基于行的日志记录。
# 双线程从库
最初的从库实现（3.23版本）只使用一个线程，该线程负责从主库读取二进制日志并将其应用到从库数据中。对于可靠的主从库且从库滞后不严重的情况，这种方法是可行的。然而，在某些情况下，这种方法并不够。假设从库不知何故滞后主库一天，而此时主库又突然完全无法使用，这将导致一天的数据丢失。
在雅虎的杰里米·扎沃迪（Jeremy Zawodny）的建议下，4.0版本重写了从库代码，使用两个线程。I/O线程负责从主库读取二进制日志并将其存储在临时中继日志中，SQL线程则读取中继日志并将更新应用到从库数据中。
在很大程度上，这消除了从库滞后且主库不可用时大量数据丢失的风险。大多数情况下，从库滞后的原因不是I/O问题，而是更新执行速度慢。例如，从库通常用于执行对时间要求不高的读取操作，从而减轻主库的负担。因此，从库可能会遇到负载峰值，这将延迟应用来自主库的更新。
另一种可能的情况是，在二进制日志中遇到一个需要很长时间才能执行的更新查询，比如需要三个小时。假设主库和从库在硬件能力和负载方面相同，可能会出现以下场景。主库执行一个三小时的更新，更新完成后被写入二进制日志。然后从库读取该更新并开始执行。在此期间，主库有三个小时的时间可能执行了大量的更新。当从库完成这个长时间的更新查询时，它已经滞后主库三个小时的更新量。
在这两种情况下，双线程从库架构允许在从库执行三小时的长时间更新时，主库继续通过二进制日志传输额外的更新。如果主库在这段时间内恰好发生无法修复的故障，中继日志中的更新数据将包含最新的更新。由于网络延迟，仍然可能会错过故障前的一些更新，但这比丢失几个小时的数据要好得多。
# 多主模式
MySQL复制最初在设计时并未考虑多主支持。从库天生只能复制一个主库的数据。可以创建一个相当简单的补丁，允许一个从库从多个主库收集更新，但不具备冲突解决功能。这种方法曾经实现过，但由于多种原因，并没有被纳入源代码的主分支。曾经有人计划开发一个更复杂的补丁来实现一定的冲突解决功能，但由于多种原因，该计划未能进入开发阶段。不过，在不久的将来，它仍有可能被实现。
与此同时，有一种非常流行的配置本质上可以实现多主模式。两台服务器建立相互的主从关系。具体来说，服务器A启用二进制日志并配置为服务器B的从库，而服务器B也启用二进制日志并作为服务器A的从库。这样，就可以向任意一台服务器写入数据，并且更新会在两台服务器上都出现。然而，只有在保证更新流无论顺序如何都能产生相同结果，或者更新是序列化的情况下，这种配置才能维护一致的数据快照。考虑以下示例：
- 服务器A执行：
UPDATE customer SET balance = 50 WHERE id = 9 - 服务器B执行：
UPDATE customer SET balance = 100 WHERE id = 9
服务器B有可能在执行自己的更新之前收到来自服务器A的更新事件。在这种情况下，服务器B中id为9的记录的balance最终会被设置为100。然而，服务器B也有可能需要一段时间才能收到来自服务器A的更新，并先执行自己的更新。在这种情况下，服务器B中id为9的记录的balance最终会是50，而不是100。但应用程序的逻辑会预期它是100，因为这是最后执行的查询。
不过，如果所有更新都是与顺序无关的，那么这种相互主从关系的配置将产生一致的结果。
这种配置也可用于热故障转移。应用程序总是向选定的一台服务器写入数据。如果该服务器出现故障，应用程序会切换到另一台服务器。当原来的服务器恢复正常后，它会自动追赶上数据（除非发生严重崩溃导致数据丢失），并可以作为备用服务器。
# 有助于理解复制的SQL命令
探索和理解MySQL复制工作原理的一个好方法是查看一些复制监控命令的输出。首先，通过启用log-bin选项并使用server-id选项将服务器ID设置为某个唯一的数字（例如IP地址的最后一个字节），将一台服务器配置为主库。然后在命令行客户端中运行：
SHOW MASTER STATUS\G
我们使用\G选项来启用列的垂直显示，这会使输出更易于阅读。该命令产生的输出类似于：
*************************** 1. row **************************
File: laforge-bin.011
Position: 566920603
Binlog_do_db:
Binlog_ignore_db:
2
3
4
5
File字段是主库当前正在写入的二进制日志的名称。Position字段显示当前二进制日志中下一个事件将被写入的偏移量，换句话说，就是当前二进制日志的大小。
Binlog_do_db和Binlog_ignore_db字段显示主库配置中相应选项的值。可以指示主库只记录在某些选定的默认数据库（Binlog_do_db，包含规则）中进行的更新，或者排除在指定数据库列表（Binlog_ignore_db，排除规则）中进行的所有更新。请注意，这些规则适用于线程的默认数据库（通过USE命令选择，或者在客户端API中调用mysql_select_db()选择），而不是实际进行更新的数据库。
File和Position字段的组合有时被称为复制坐标（replication coordinates）。复制坐标允许从库在连接主库时告诉主库从哪里开始发送二进制日志。它们还可以用于跟踪从库应用更新的进度，并可用于同步主库和从库。
现在来配置一个从库。首先，在主库上创建一个复制用户：
GRANT REPLICATION SLAVE ON *.* TO 'rpl_user@slave-host' IDENTIFIED BY 'rpl_pass';
在这个例子中，slave-host应该替换为从库的主机名或IP地址。
然后配置从库。选择一个唯一的服务器ID（IP地址的最后一个字节很合适），从主库加载当前数据集，并使用以下命令指示从库主库的位置：
CHANGE MASTER TO MASTER_HOST='master-host', MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass';
master-host应该替换为主库的主机名或IP地址。
然后可以启动从库线程：
START SLAVE;
接下来运行：
SHOW SLAVE STATUS\G
如果没有遇到任何问题，我们将看到类似以下的输出：
Slave_IO_State: Waiting for master to send event
Master_Host: www1.internal
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: www1-bin.107
Read_Master_Log_Pos: 403398225
Relay_Log_File: slave-relay-bin.000894
Relay_Log_Pos: 92576794
Relay_Master_Log_File: www1-bin.107
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 403398225
Relay_Log_Space: 92576794
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
表12 - 1对每个字段进行了简要说明。
| 字段名称 | 描述 |
|---|---|
Slave_IO_State | 从库I/O线程当前操作的文本描述。 |
Master_User | 从库I/O线程以该用户身份连接到主库。 |
Master_Port | 从库I/O线程连接主库时使用的TCP/IP端口。 |
Connect_Retry | 如果从库I/O线程与主库的连接丢失，它将在该参数指定的超时时间后重试。 |
Master_Log_File | SQL线程当前正在处理的主库日志文件的名称。 |
Read_Master_Log_Pos | I/O线程当前在主库日志中的位置。 |
Relay_Log_File | SQL线程当前正在处理的中继日志的名称。 |
Relay_Log_Pos | SQL线程当前在中继日志中的位置。 |
Relay_Master_Log_File | I/O线程当前正在写入的日志文件。 |
Slave_IO_Running | 显示I/O线程是否正在运行。 |
Slave_SQL_Running | 显示SQL线程是否正在运行。 |
Replicate_Do_DB | 显示根据replicate-do-db规则要复制的数据库。 |
Replicate_Ignore_DB | 显示根据replicate-ignore-db规则要忽略的数据库。 |
Replicate_Do_Table | 显示根据replicate-do-table规则要复制的表。 |
Replicate_Ignore_Table | 显示根据replicate-ignore-table规则要忽略的表。 |
Replicate_Wild_Do_Table | 显示根据replicate-wild-do-table规则要复制的表。 |
Replicate_Wild_Ignore_Table | 显示根据replicate-wild-ignore-table规则要忽略的数据库。 |
Last_Errno | 导致复制停止的最后一个错误的错误代码。 |
Last_Error | 导致复制停止的最后一个错误的错误消息文本。 |
Skip_Counter | SQL线程要跳过的后续事件的数量。这主要用于复制因某些问题或数据库管理员的疏忽而中断的情况，为了按计划继续复制，需要跳过某个查询。 |
Exec_Master_Pos | 与SQL线程当前位置对应的主库日志中的位置。 |
Relay_Log_Space | 中继日志占用的磁盘空间大小（以字节为单位）。 |
Until_Condition | 有时可以指示从库复制直到达到主库或中继日志中的某个位置。该参数表示是否存在UNTIL条件，以及该条件是针对主库日志还是中继日志。 |
Until_Log_File | UNTIL条件中的日志文件名称。 |
Until_Log_Pos | UNTIL条件中的日志文件位置。 |
Master_SSL_Allowed | 指示从库I/O线程是否应通过SSL连接到主库。 |
Master_SSL_CA_File | 从库连接主库时使用的证书颁发机构文件的路径名。 |
Master_SSL_CA_Path | 包含可信SSL CA证书（pem格式）的目录的路径名。 |
Master_SSL_Cert | 证书文件的路径名。 |
Master_SSL_Cipher | SSL连接中使用的密码。 |
Master_SSL_Key | SSL密钥文件的路径名。 |
Seconds_Behind_Master | 显示当前时间与最后执行事件的主库时间戳之间的秒数差，如果SQL线程滞后于I/O线程，则会根据主库和从库之间可能的时钟差异进行调整。否则，它将显示0，但这可能并不总是准确的，因为I/O线程可能需要一些时间从主库读取事件。 |
对于细心的读者来说，表12 - 1中的状态变量揭示了复制的工作原理。例如，Master_connect相关参数表明从库连接到主库并充当常规的MySQL客户端。Connect_retry参数的存在说明从库能够处理连接中断的情况。Slave_IO_Running和Slave_SQL_Running参数向我们展示了双线程从库复制模型。Relay_相关参数解释了从库如何将数据临时存储在中继日志中。SSL相关参数告诉我们主库和从库之间的I/O可以通过SSL加密。Replicate_相关选项则展示了从库进行选择性复制的能力。
# 二进制日志格式
了解二进制日志格式的一些细节，有助于深入理解复制的内部机制。处理二进制日志记录的代码位于sql/log_event.h和sql/log_event.cc中。
二进制日志以一个4字节的魔数（magic number）开头，在sql/log_event.h中的以下这行代码中进行设置：
#define BINLOG_MAGIC "\xfe\x62\x69\x6e"
读取二进制日志的代码会使用这个魔数进行快速的完整性检查，以确保使用的是有效的二进制日志。Unix系统的文件工具也会用到它来识别文件类型。
魔数之后是一系列的事件条目。所有事件都有一个公共的头部，按顺序包含表12 - 2中列出的字段。第二个字段代表事件的类型代码，其含义在表12 - 3中进行解释。头部中的所有整数均采用小端序格式（低位字节在前）存储。这个头部由sql/log_event.cc中的Log_event::write_header()函数写入。
表12 - 2 二进制日志事件头部
| 描述 | 大小（字节） |
|---|---|
| 4 | 事件的时间戳。自1970年1月1日起的秒数，由libc的time()函数返回。 |
| 1 | 事件的类型代码。该代码的值和含义在表12 - 3中解释。 |
| 4 | 服务器ID（ServerID）。在复制的服务器集群中唯一标识该服务器，主要用于避免无限更新循环。 |
| 4 | 整个事件的长度（包括头部），以字节为单位。 |
| 4 | 该事件在日志中的偏移量，以字节为单位。 |
| 2 | 事件标志。详细信息见sql/log_event.h；搜索与LOG_EVENT_*_F模式匹配的宏。 |
| 表12 - 3 二进制日志事件类型代码 | |
| 数值 | 名称 |
| --- | --- |
| 1 | Start |
| 2 | Query |
| 3 | Stop |
| 4 | Rotate |
| 5 | Intvar |
| 6 | Load |
| 7 | Slave |
| 8 | Create File |
| 9 | Append Block |
| 10 | Exec Load |
| 11 | Delete File |
| 12 | New Load |
| 13 | Rand |
| 14 | User Var |
| 表12 - 3 二进制日志事件类型代码（续） | |
| 数值 | 名称 |
| --- | --- |
| 15 | Format Description |
| 16 | XID |
| 17 | Begin Load Query |
| 18 | Execute Load Query |
| 19 | Table Map |
| 20 | Write Rows |
| 21 | Update Rows |
| 22 | Delete Rows |
事件头部之后是事件主体。事件主体的结构因事件类型的不同而有很大差异。
同样，对二进制日志格式的研究揭示了许多与复制相关的隐藏细节和挑战。虽然主从复制的基本概念很简单，即主服务器记录更新日志，从服务器重新执行这些更新，但细节决定成败，二进制日志格式的复杂性和事件类型的多样性就是证明。我们如何处理日志轮换？如何处理从服务器断开连接后重新连接的情况？如何处理自增字段？如何复制对时间戳敏感的查询？如何复制LOAD DATA INFILE操作？如何复制使用随机数的更新操作？在复杂的复制拓扑结构中，当主服务器同时作为另一台服务器的从服务器时，如何避免更新事件从主服务器发出后又通过从服务器回到主服务器，从而导致无限复制循环？
对这些具体细节感兴趣的读者，可参考sql/log_event.cc中的源代码。事件主体的写入过程是，先调用Log_event::write_data_header()写入固定长度的特定事件类型信息，接着调用Log_event::write_data_body()写入可变长度的特定事件类型信息。Log_event是负责处理每种事件的一系列类的基类。write_data_body()和write_data_header()是虚方法，每个类都会以自己的方式实现这些方法，来处理特定事件类型的数据存储。这些类的名称以事件名称开头，以_log_event结尾。因此，如果你想探究查询事件是如何存储的，应该查看Query_log_event::write_data_header()和Query_log_event::write_data_body()。
mysqlbinlog是一个非常有用的工具，可用于分析和理解复制二进制日志。它接受日志名称作为参数，并以SQL格式输出日志内容，同时附带一些关于日志细节的注释。例如：
$ mysqlbinlog /var/lib/mysql/www1-bin.001
可能会产生类似如下的输出：
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; # at 4
#060809 21:03:48 server id 1 end_log_pos 102
Start: binlog v 4, server v 5.1.11-beta-log created 060809 21:03:48 at startup ROLLBACK;
# at 102
#060809 21:03:48 server id 1 end_log_pos 197 Query thread_id=2
exec_time=0 error_code=0
use test;
SET TIMESTAMP=1155179028;
SET @@session.foreign_key_checks=1,
@@session.sql_auto_is_null=1, @@session.unique_checks=1; SET @@session.sql_mode=0;
/*!\C latin1 */;
SET @@session.character_set_client=8,
@@session.collation_connection=8,@@session.collation_server=8; drop table if exists t1,t2,t3,t4;
# at 197
#060809 21:03:48 server id 1 end_log_pos 306 Query thread_id=2
exec_time=0 error_code=0 SET TIMESTAMP=1155179028;
create table t1(n int, m int) type=oreilly_csv;
# at 306
#060809 21:03:48 server id 1 end_log_pos 439
Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1155179028;
create table t2(name char(20), age int, comment text) type=oreilly_csv;
# at 439
#060809 21:03:48 server id 1 end_log_pos 548
Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1155179028;
create table t3(s text,n int) type=oreilly_csv;
# at 548
#060809 21:03:48 server id 1 end_log_pos 660
Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1155179028;
create table t4(s1 text,s2 text) type=oreilly_csv;
# at 660
#060809 21:03:48 server id 1 end_log_pos 755
Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1155179028;
drop table if exists t1,t2,t3,t4;
# at 755
#060809 21:03:48 server id 1 end_log_pos 774 Stop
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
输出中的每个事件都以一个注释行开头，该行指示其在二进制日志中的偏移量。下一行也是注释，显示事件的时间戳、产生该事件的服务器ID、下一个事件的位置以及事件的类型。随后是一组SQL查询，这些查询在从服务器上对数据库产生的更改，与原始事件在主服务器上产生的更改相同。可以使用--start-position、--stop-position、--start-datetime和--stop-datetime参数，仅查看二进制日志的部分内容。
# 创建自定义复制工具
通常，仅通过SQL命令就可以成功管理复制。这是推荐的方法，并且在可能的情况下应充分使用。但偶尔也会出现仅靠SQL命令不够的情况。例如，你可能只想复制某些特定的事件子集，而这些子集很难用标准的复制表匹配规则来定义。或者，你可能需要在复制某些查询之前对其进行重写。在这种情况下，MySQL的开源特性就派上用场了。有两种方法可以实现。
如果操作正确，能获得最佳性能并且提供更可靠解决方案的方法是，修改sql/slave.cc中SQL线程代码的循环，或者在调用层级的其他相关位置进行修改。相关代码位于handle_slave_sql()函数中，该函数会调用exec_relay_log_event()。然而，这项任务适合有勇气的程序员来做。一个小错误不仅可能破坏复制功能，还可能导致整个从服务器崩溃。紧跟MySQL的新版本发布进行代码更新也可能会很麻烦。总体而言，完成这项任务需要对MySQL源代码有更深入的理解 。
更简单的方法是以mysqlbinlog的源代码（位于client/mysqlbinlog.cc）为基础创建一个自定义客户端，并根据需要添加一些自定义功能。mysqlbinlog已经可以读取远程日志。可以根据需要修改dump_remote_log_entries()中的输出循环，以执行自定义的事件过滤。
这种方法的优点是，对MySQL源代码的了解要求较低，侵入性也较小。缺点是，无法利用原生从服务器事件管理和处理代码已被验证的可靠性，并且由于存在中间层，会产生大量不必要的I/O操作。