MySQL 8.4 Release Notes
为了使复制能够应对服务器的意外停止（有时称为崩溃安全），必须能够让副本在停止前恢复其状态。本节描述了副本在复制期间意外停止的影响，以及如何配置副本以获得最佳恢复机会，从而继续复制。In order for replication to be resilient to unexpected halts of the server (sometimes described as crash-safe) it must be possible for the replica to recover its state before halting. This section describes the impact of an unexpected halt of a replica during replication, and how to configure a replica for the best chance of recovery to continue replication.
在副本意外停止后，重新启动时，复制 SQL 线程必须恢复已执行事务的信息。恢复所需的信息存储在副本的应用器元数据仓库中。该仓库默认由一个 InnoDB 表创建，名为 mysql.slave_relay_log_info。通过使用这种事务存储引擎，这些信息在重启时总是可以恢复。对应用器元数据仓库的更新与事务一起提交，这意味着副本在该仓库中记录的进度信息总是与已应用到数据库的内容一致，即使在服务器意外停止的情况下也是如此。有关应用器元数据仓库的更多信息，请参见 第 19.2.4 节，“中继日志和复制元数据仓库”。After an unexpected halt of a replica, upon restart the
replication SQL thread must recover information about which
transactions have been executed already. The information required
for recovery is stored in the replica's applier metadata
repository. This repository is created by default as an
InnoDB table named
mysql.slave_relay_log_info. By using this
transactional storage engine the information is always recoverable
upon restart. Updates to the applier metadata repository are
committed together with the transactions, meaning that the
replica's progress information recorded in that repository is
always consistent with what has been applied to the database, even
in the event of an unexpected server halt. For more information on
the applier metadata repository, see
Section 19.2.4, “Relay Log and Replication Metadata Repositories”.
DML 事务以及原子 DDL 会在应用更改到数据库的同时，将复制位置更新到副本的执行器元数据仓库中的 mysql.slave_relay_log_info 表中，作为一个原子操作。在所有其他情况下，包括非完全原子的 DDL 语句，以及不支持原子 DDL 的豁免存储引擎，如果服务器意外停止，mysql.slave_relay_log_info 表中可能缺少与复制数据相关的更新。在这种情况下，恢复更新是一个手动过程。有关 MySQL 8.4 中原子 DDL 支持的详细信息，以及某些语句复制的相应行为，请参见 第 15.1.1 节，“原子数据定义语句支持”。DML transactions and also atomic DDL update the replication
positions in the replica's applier metadata repository in the
mysql.slave_relay_log_info table together with
applying the changes to the database, as an atomic operation. In
all other cases, including DDL statements that are not fully
atomic, and exempted storage engines that do not support atomic
DDL, the mysql.slave_relay_log_info table might
be missing updates associated with replicated data if the server
halts unexpectedly. Restoring updates in this case is a manual
process. For details on atomic DDL support in MySQL
8.4, and the resulting behavior for the replication
of certain statements, see Section 15.1.1, “Atomic Data Definition Statement Support”.
副本从意外停止中恢复的过程因副本的配置而异。恢复过程的细节受所选择的复制方式、副本是单线程还是多线程以及相关系统变量设置的影响。恢复过程的总体目标是确定在意外停止发生之前哪些事务已经应用到副本的数据库中，并检索和应用副本在意外停止后错过的事务。The recovery process by which a replica recovers from an unexpected halt varies depending on the configuration of the replica. The details of the recovery process are influenced by the chosen method of replication, whether the replica is single-threaded or multithreaded, and the setting of relevant system variables. The overall aim of the recovery process is to identify what transactions had already been applied on the replica's database before the unexpected halt occurred, and retrieve and apply the transactions that the replica missed following the unexpected halt.
对于基于 GTID 的复制，恢复过程需要副本已经接收或提交的事务的 GTID。缺失的事务可以通过 GTID 自动定位从源获取，该方法会自动比较源的事务和副本的事务，并识别缺失的事务。For GTID-based replication, the recovery process needs the GTIDs of the transactions that were already received or committed by the replica. The missing transactions can be retrieved from the source using GTID auto-positioning, which automatically compares the source's transactions to the replica's transactions and identifies the missing transactions.
对于基于文件位置的复制，恢复过程需要一个准确的复制 SQL 线程（应用器）位置，以显示在副本上应用的最后一个事务。根据该位置，复制 I/O 线程（接收器）从源的二进制日志中检索从该点开始应在副本上应用的所有事务。For file position based replication, the recovery process needs an accurate replication SQL thread (applier) position showing the last transaction that was applied on the replica. Based on that position, the replication I/O thread (receiver) retrieves from the source's binary log all of the transactions that should be applied on the replica from that point on.
使用基于 GTID 的复制可以最容易地配置复制，使其能够应对意外中断。GTID 自动定位意味着即使应用事务的序列中存在间隙，副本也可以可靠地识别并检索缺失的事务。Using GTID-based replication makes it easiest to configure replication to be resilient to unexpected halts. GTID auto-positioning means the replica can reliably identify and retrieve missing transactions, even if there are gaps in the sequence of applied transactions.
以下信息提供了适用于不同类型副本的设置组合，以确保在复制可控范围内的恢复。The following information provides combinations of settings that are appropriate for different types of replica to guarantee recovery as far as this is under the control of replication.
Important
一些在复制控制之外的因素可能会影响复制恢复过程以及恢复过程后的整体复制状态。特别是，影响各个存储引擎恢复过程的设置可能会导致在副本意外停止的情况下事务丢失，从而无法用于复制恢复过程。下面列表中提到的 innodb_flush_log_at_trx_commit=1 设置，是使用带事务的 InnoDB 进行复制时的关键设置。然而，其他特定于 InnoDB 或其他存储引擎的设置，尤其是与刷新或同步相关的设置，也可能产生影响。始终检查并遵循所选存储引擎关于崩溃安全设置的建议。Some factors outside the control of replication can have an
impact on the replication recovery process and the overall state
of replication after the recovery process. In particular, the
settings that influence the recovery process for individual
storage engines might result in transactions being lost in the
event of an unexpected halt of a replica, and therefore
unavailable to the replication recovery process. The
innodb_flush_log_at_trx_commit=1
setting mentioned in the list below is a key setting for a
replication setup that uses InnoDB
with transactions. However, other settings specific to
InnoDB or to other storage engines,
especially those relating to flushing or synchronization, can
also have an impact. Always check for and apply recommendations
made by your chosen storage engines about crash-safe settings.
在副本上，以下设置组合对意外中断最具恢复力：The following combination of settings on a replica is the most resilient to unexpected halts:
当使用基于 GTID 的复制时（
gtid_mode=ON），设置SOURCE_AUTO_POSITION=1，这会为连接到源的会话激活 GTID 自动定位，以自动识别并获取缺失的事务。该选项通过CHANGE REPLICATION SOURCE TO语句设置。如果副本有多个复制通道，则需要为每个通道单独设置此选项。有关 GTID 自动定位的工作原理的详细信息，请参见 第 19.1.3.3 节，“GTID 自动定位”。当使用基于文件位置的复制时，不使用SOURCE_AUTO_POSITION=1，而是使用二进制日志位置或中继日志位置来控制复制的起始位置。When GTID-based replication is in use (gtid_mode=ON), setSOURCE_AUTO_POSITION=1, which activates GTID auto-positioning for the connection to the source to automatically identify and retrieve missing transactions. This option is set using aCHANGE REPLICATION SOURCE TOstatement. If the replica has multiple replication channels, you need to set this option for each channel individually. For details of how GTID auto-positioning works, see Section 19.1.3.3, “GTID Auto-Positioning”. When file position based replication is in use,SOURCE_AUTO_POSITION=1is not used, and instead the binary log position or relay log position is used to control where replication starts.当使用基于 GTID 的复制 (
gtid_mode=ON) 时，设置GTID_ONLY=1，这会使副本在恢复过程中仅使用 GTID，并停止在复制元数据存储库中保存二进制日志和中继日志的文件名及文件位置。这个选项通过CHANGE REPLICATION SOURCE TO语句来设置。如果副本有多个复制通道，则需要为每个通道单独设置此选项。使用GTID_ONLY=1时，在恢复期间，文件位置信息会被忽略，并使用 GTID 自动跳过功能来跳过已经提供过的事务，而不是确定正确的文件位置。只要你使用relay_log_purge的默认设置清除中继日志，这种策略会更高效，这意味着只需要检查一个中继日志文件。When GTID-based replication is in use (gtid_mode=ON), setGTID_ONLY=1, which makes the replica use only GTIDs in the recovery process, and stop persisting binary log and relay log file names and file positions in the replication metadata repositories. This option is set using aCHANGE REPLICATION SOURCE TOstatement. If the replica has multiple replication channels, you need to set this option for each channel individually. WithGTID_ONLY=1, during recovery, the file position information is ignored and GTID auto-skip is used to skip transactions that have already been supplied, rather than identifying the correct file position. This strategy is more efficient provided that you purge relay logs using the default setting forrelay_log_purge, which means only one relay log file needs to be inspected.设置
sync_relay_log=1，它指示复制接收线程在每个接收到的事务写入中继日志后将其同步到磁盘。这意味着副本中记录的当前从源二进制日志读取的位置（在应用元数据存储中）永远不会超过中继日志中保存的事务记录。请注意，虽然此设置最安全，但由于涉及大量磁盘写入，它也是最慢的。使用sync_relay_log > 1或sync_relay_log=0（此时同步由操作系统处理），在副本意外中止的情况下，可能存在已提交但尚未同步到磁盘的事务。如果恢复副本在基于中继日志中最后同步到磁盘的信息尝试重新提取并应用这些事务而不是跳过它们时，这些事务可能导致恢复过程失败。对于多线程副本，设置sync_relay_log=1尤为重要，因为如果事务序列中存在的间隔无法使用中继日志中的信息填补，恢复过程将失败。对于单线程副本，只有当应用元数据存储中没有相关信息时，恢复过程才需要使用中继日志。Setsync_relay_log=1, which instructs the replication receiver thread to synchronize the relay log to disk after each received transaction is written to it. This means the replica's record of the current position read from the source's binary log (in the applier metadata repository) is never ahead of the record of transactions saved in the relay log. Note that although this setting is the safest, it is also the slowest due to the number of disk writes involved. Withsync_relay_log > 1, orsync_relay_log=0(where synchronization is handled by the operating system), in the event of an unexpected halt of a replica there might be committed transactions that have not been synchronized to disk. Such transactions can cause the recovery process to fail if the recovering replica, based on the information it has in the relay log as last synchronized to disk, tries to retrieve and apply the transactions again instead of skipping them. Settingsync_relay_log=1is particularly important for a multi-threaded replica, where the recovery process fails if gaps in the sequence of transactions cannot be filled using the information in the relay log. For a single-threaded replica, the recovery process only needs to use the relay log if the relevant information is not available in the applier metadata repository.设置
innodb_flush_log_at_trx_commit=1，它会在每个事务提交之前将InnoDB日志同步到磁盘。这个设置是默认的，它确保InnoDB表和InnoDB日志被保存到磁盘上，因此不再需要中继日志中关于事务的信息。与设置sync_relay_log=1结合使用时，该设置进一步确保InnoDB表和InnoDB日志的内容始终与中继日志的内容一致，从而在意外停止的情况下，清理中继日志文件也不会在副本的事务历史中造成无法填补的空白。Setinnodb_flush_log_at_trx_commit=1, which synchronizes theInnoDBlogs to disk before each transaction is committed. This setting, which is the default, ensures thatInnoDBtables and theInnoDBlogs are saved on disk so that there is no longer a requirement for the information in the relay log regarding the transaction. Combined with the settingsync_relay_log=1, this setting further ensures that the content of theInnoDBtables and theInnoDBlogs is consistent with the content of the relay log at all times, so that purging the relay log files cannot cause unfillable gaps in the replica's history of transactions in the event of an unexpected halt.设置
relay_log_info_repository = TABLE，它将复制 SQL 线程的位置存储在InnoDB表mysql.slave_relay_log_info中，并与事务提交一起更新，以确保记录始终准确。此设置为默认值；FILE已被弃用。系统变量本身也已弃用，因此可以省略，让其采用默认值。如果使用FILE，信息将存储在数据目录中的文件中，并在事务应用后更新。这会带来在副本事务处理过程中停止的阶段可能丢失与源同步的风险，甚至可能导致文件本身损坏。使用relay_log_info_repository = FILE时，无法保证恢复。Setrelay_log_info_repository = TABLE, which stores the replication SQL thread position in theInnoDBtablemysql.slave_relay_log_info, and updates it together with the transaction commit to ensure a record that is always accurate. This setting is the default;FILEis deprecated. The system variable itself is also deprecated, so omit it and allow it to assume the default. IfFILEis used, the information is stored in a file in the data directory that is updated after the transaction has been applied. This creates a risk of losing synchrony with the source depending at which stage of processing a transaction the replica halts at, or even corruption of the file itself. Withrelay_log_info_repository = FILE, recovery is not guaranteed.设置
relay_log_recovery = ON，它在服务器启动后立即启用自动中继日志恢复。该全局变量默认值为OFF，且在运行时为只读，但在副本意外停止后，你可以在副本启动时使用--relay-log-recovery选项将其设置为ON。请注意，此设置会忽略现有的中继日志文件，以防这些文件损坏或不一致。中继日志恢复过程会启动一个新的中继日志文件，并从应答器元数据存储中记录的复制 SQL 线程位置开始从源获取事务。以前的中继日志文件会被副本的正常清理机制随着时间移除。Setrelay_log_recovery = ON, which enables automatic relay log recovery immediately following server startup. This global variable defaults toOFFand is read-only at runtime, but you can set it toONwith the--relay-log-recoveryoption at replica startup following an unexpected halt of a replica. Note that this setting ignores the existing relay log files, in case they are corrupted or inconsistent. The relay log recovery process starts a new relay log file and fetches transactions from the source beginning at the replication SQL thread position recorded in the applier metadata repository. The previous relay log files are removed over time by the replica's normal purge mechanism.
对于多线程副本，设置relay_log_recovery = ON会自动处理从中继日志执行的事务序列中的任何不一致和间隙。当使用基于文件位置的复制时，可能会出现这些间隙。（有关更多详细信息，请参见第 19.5.1.34 节，“复制与事务不一致”。）中继日志恢复过程使用与START
REPLICA UNTIL SQL_AFTER_MTS_GAPS语句相同的方法处理间隙。当副本达到状态一致且无间隙时，中继日志恢复过程会继续从源头获取从复制 SQL 线程位置开始的进一步事务。当使用基于 GTID 的复制时，多线程副本首先检查SOURCE_AUTO_POSITION是否设置为ON，如果是，则跳过计算应跳过或不应跳过的事务这一步，从而在恢复过程中不需要旧的中继日志。For a multithreaded replica, setting
relay_log_recovery = ON
automatically handles any inconsistencies and gaps in the sequence
of transactions that have been executed from the relay log. These
gaps can occur when file position based replication is in use.
(For more details, see
Section 19.5.1.34, “Replication and Transaction Inconsistencies”.)
The relay log recovery process deals with gaps using the same
method as the START
REPLICA UNTIL SQL_AFTER_MTS_GAPS statement would. When
the replica reaches a consistent gap-free state, the relay log
recovery process goes on to fetch further transactions from the
source beginning at the replication SQL thread position. When
GTID-based replication is in use, a multithreaded replica checks
first whether SOURCE_AUTO_POSITION is set to
ON, and if it is, omits the step of calculating
the transactions that should be skipped or not skipped, so that
the old relay logs are not required for the recovery process.