MySql 中文参考指南

Chapter 19 Replication

Table of Contents

复制能够将一个 MySQL 数据库服务器（称为源）中的数据复制到一个或多个 MySQL 数据库服务器（称为副本）。复制默认情况下是异步的；副本不需要永久连接以从源接收更新。根据配置，您可以在数据库中复制所有数据库、选定数据库，甚至选定表。

Replication enables data from one MySQL database server (known as a source) to be copied to one or more MySQL database servers (known as replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

MySQL 中复制的优势包括：

Advantages of replication in MySQL include:

Scale-out solutions - spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source is dedicated to updates), while dramatically increasing read speed across an increasing number of replicas.
Data security - because the replica can pause the replication process, it is possible to run backup services on the replica without corrupting the corresponding source data.
Analytics - live data can be created on the source, while the analysis of the information can take place on the replica without affecting the performance of the source.
Long-distance data distribution - you can use replication to create a local copy of data for a remote site to use, without permanent access to the source.

有关如何在这样的场景中使用复制的信息，请参阅 Section 19.4, “Replication Solutions” 。

For information on how to use replication in such scenarios, see Section 19.4, “Replication Solutions”.

MySQL 9.0 支持不同的复制方法。传统方法基于从源二进制日志中复制事件，需要源和副本之间的日志文件和位置同步。基于全局事务标识符 (GTID) 的较新方法是事务性的，因此不需要使用日志文件或这些文件中的位置，这极大地简化了许多常见的复制任务。只要源上提交的所有事务也已应用于副本，使用 GTID 的复制就能够保证源和副本的一致性。有关 GTID 和基于 GTID 的 MySQL 复制的更多信息，请参阅 Section 19.1.3, “Replication with Global Transaction Identifiers” 。有关使用二进制日志文件位置的复制的信息，请参阅 Section 19.1, “Configuring Replication” 。

MySQL 9.0 supports different methods of replication. The traditional method is based on replicating events from the source’s binary log, and requires the log files and positions in them to be synchronized between source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica. For more information about GTIDs and GTID-based replication in MySQL, see Section 19.1.3, “Replication with Global Transaction Identifiers”. For information on using binary log file position based replication, see Section 19.1, “Configuring Replication”.

MySQL 中的复制支持不同类型的同步。原始同步类型是单向异步复制，其中一台服务器充当源服务器，而其他一台或多台服务器充当副本。这与 synchronous 复制形成对比，后者是 NDB 集群的一个特点（参见 Chapter 25, MySQL NDB Cluster 9.0 ）。在 MySQL 9.0 中，除了内置的异步复制外，还支持半同步复制。通过半同步复制，在源服务器上执行的提交会在返回到执行事务的会话之前阻塞，直到至少有一个副本确认它已接收并记录事务事件；参见 Section 19.4.10, “Semisynchronous Replication” 。MySQL 9.0 还支持延迟复制，因此副本故意比源服务器延迟至少指定的时间量；参见 Section 19.4.11, “Delayed Replication” 。对于需要 synchronous 复制的情况，请使用 NDB 集群（参见 Chapter 25, MySQL NDB Cluster 9.0 ）。

Replication in MySQL supports different types of synchronization. The original type of synchronization is one-way, asynchronous replication, in which one server acts as the source, while one or more other servers act as replicas. This is in contrast to the synchronous replication which is a characteristic of NDB Cluster (see Chapter 25, MySQL NDB Cluster 9.0). In MySQL 9.0, semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the source blocks before returning to the session that performed the transaction until at least one replica acknowledges that it has received and logged the events for the transaction; see Section 19.4.10, “Semisynchronous Replication”. MySQL 9.0 also supports delayed replication such that a replica deliberately lags behind the source by at least a specified amount of time; see Section 19.4.11, “Delayed Replication”. For scenarios where synchronous replication is required, use NDB Cluster (see Chapter 25, MySQL NDB Cluster 9.0).

为服务器之间设置复制有很多解决方案，最佳方法取决于数据的存在和正在使用的引擎类型。有关可用选项的更多信息，请参阅 Section 19.1.2, “Setting Up Binary Log File Position Based Replication” 。

There are a number of solutions available for setting up replication between servers, and the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 19.1.2, “Setting Up Binary Log File Position Based Replication”.

复制格式有两种核心类型，基于语句的复制 (SBR)（复制整个 SQL 语句）和基于行的复制 (RBR)（仅复制已更改的行）。还可以使用第三种类型，即混合式复制 (MBR)。有关不同复制格式的更多信息，请参阅 Section 19.2.1, “Replication Formats” 。

There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You can also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 19.2.1, “Replication Formats”.

复制通过许多不同的选项和变量进行控制。有关更多信息，请参阅 Section 19.1.6, “Replication and Binary Logging Options and Variables” 。可以对复制拓扑应用其他安全措施，如 Section 19.3, “Replication Security” 中所述。

Replication is controlled through a number of different options and variables. For more information, see Section 19.1.6, “Replication and Binary Logging Options and Variables”. Additional security measures can be applied to a replication topology, as described in Section 19.3, “Replication Security”.

你可以使用复制来解决许多不同的问题，包括性能、支持不同数据库的备份以及作为减轻系统故障风险的较大解决方案的一部分。有关如何解决这些问题的信息，请参阅 Section 19.4, “Replication Solutions” 。

You can use replication to solve a number of different problems, including performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 19.4, “Replication Solutions”.

有关复制过程中不同的数据类型和语句的处理方式的注释和提示，包括复制功能、版本兼容性、升级以及潜在问题及其解决方案的详细信息，请参阅 Section 19.5, “Replication Notes and Tips” 。有关 MySQL 复制新手常问的一些问题的答案，请参阅 Section A.14, “MySQL 9.0 FAQ: Replication” 。

For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and potential problems and their resolution, see Section 19.5, “Replication Notes and Tips”. For answers to some questions often asked by those who are new to MySQL Replication, see Section A.14, “MySQL 9.0 FAQ: Replication”.

有关复制实现、复制工作方式、二进制日志的流程和内容、后台线程以及用于决定如何记录和复制语句的规则的详细信息，请参阅 Section 19.2, “Replication Implementation” 。

For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replicated, see Section 19.2, “Replication Implementation”.