MySQL 8.4 Release Notes
MySQL 支持延迟复制，使得从服务器故意比源服务器晚至少指定时间执行事务。本节介绍如何在从服务器上配置复制延迟，以及如何监控复制延迟。MySQL supports delayed replication such that a replica server deliberately executes transactions later than the source by at least a specified amount of time. This section describes how to configure a replication delay on a replica, and how to monitor replication delay.
在 MySQL 8.4 中，延迟复制的方法依赖于两个时间戳，immediate_commit_timestamp 和 original_commit_timestamp（请参见 复制延迟时间戳）；延迟复制是使用这些时间戳来测量的。如果即时源或副本中的任意一方未使用这些时间戳，则使用 MySQL 5.7 的延迟复制实现（请参见 延迟复制）。本节描述了所有服务器都使用这些时间戳的情况下的延迟复制。In MySQL 8.4, the method of delaying replication
depends on two timestamps,
immediate_commit_timestamp and
original_commit_timestamp (see
Replication Delay Timestamps); delayed
replication is measured using these timestamps. If either the
immediate source or replica is not using these timestamps, the
implementation of delayed replication from MySQL 5.7 is used (see
Delayed Replication). This section
describes delayed replication between servers which are all using
these timestamps.
默认的复制延迟为 0 秒。使用 CHANGE
REPLICATION SOURCE TO
SOURCE_DELAY=N 语句将延迟设置为 N 秒。从源接收到的事务直到源的提交至少 N 秒后才会执行。延迟是按事务发生的（而不是像以前的 MySQL 版本按事件发生），实际的延迟只施加在 gtid_log_event 或 anonymous_gtid_log_event 上。事务中的其他事件始终紧随这些事件执行，不会对其施加等待时间。The default replication delay is 0 seconds. Use a
CHANGE
REPLICATION SOURCE TO
SOURCE_DELAY= statement to
set the delay to NN seconds. A
transaction received from the source is not executed until at
least N seconds later than its commit
on the immediate source. The delay happens per transaction (not
event as in previous MySQL versions) and the actual delay is
imposed only on gtid_log_event or
anonymous_gtid_log_event. The other events in
the transaction always follow these events without any waiting
time imposed on them.
Note
START REPLICA 和 STOP REPLICA 立即生效并忽略任何延迟。RESET
REPLICA 将延迟重置为 0。START REPLICA and
STOP REPLICA take effect
immediately and ignore any delay. RESET
REPLICA resets the delay to 0.
replication_applier_configuration 性能模式表包含 DESIRED_DELAY 列，该列显示使用 SOURCE_DELAY 选项配置的延迟时间。replication_applier_status 性能模式表包含 REMAINING_DELAY 列，该列显示剩余的延迟秒数。The replication_applier_configuration
Performance Schema table contains the
DESIRED_DELAY column which shows the delay
configured using the SOURCE_DELAY option. The
replication_applier_status
Performance Schema table contains the
REMAINING_DELAY column which shows the number
of delay seconds remaining.
延迟复制可以用于多种用途：Delayed replication can be used for several purposes:
防止源端用户错误。通过延迟，可以将延迟副本回滚到错误发生之前的时间。To protect against user mistakes on the source. With a delay you can roll back a delayed replica to the time just before the mistake.
测试系统在出现延迟时的行为。例如，在一个应用程序中，延迟可能是由于副本的负载过重引起的。然而，生成这种负载水平可能很困难。延迟复制可以模拟延迟而无需模拟负载。它也可以用于调试与延迟副本相关的情况。To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the replica. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging replica.
检查数据库过去的样子，而无需重新加载备份。例如，通过将副本配置为延迟一周，如果您需要查看最后几天开发之前数据库的状态，可以检查延迟副本。To inspect what the database looked like in the past, without having to reload a backup. For example, by configuring a replica with a delay of one week, if you then need to see what the database looked like before the last few days' worth of development, the delayed replica can be inspected.
MySQL 8.4 提供了一种新的方法来测量复制拓扑中的延迟（也称为复制滞后），该方法依赖于写入二进制日志的每个事务的 GTID 相关的以下时间戳（而不是每个事件）。MySQL 8.4 provides a new method for measuring delay (also referred to as replication lag) in replication topologies that depends on the following timestamps associated with the GTID of each transaction (instead of each event) written to the binary log.
original_commit_timestamp：事务写入（提交）到原始源的二进制日志时的自纪元以来的微秒数。original_commit_timestamp: the number of microseconds since epoch when the transaction was written (committed) to the binary log of the original source.immediate_commit_timestamp：事务写入（提交）到直接源的二进制日志时的自纪元以来的微秒数。immediate_commit_timestamp: the number of microseconds since epoch when the transaction was written (committed) to the binary log of the immediate source.
mysqlbinlog 的输出以两种格式显示这些时间戳：从纪元开始的微秒以及基于用户定义时区的 TIMESTAMP 格式，以便于阅读。例如：The output of mysqlbinlog displays these
timestamps in two formats, microseconds from epoch and also
TIMESTAMP format, which is based on the user
defined time zone for better readability. For example:
#170404 10:48:05 server id 1 end_log_pos 233 CRC32 0x016ce647 GTID last_committed=0 \ sequence_number=1 original_committed_timestamp=1491299285661130 immediate_commit_timestamp=1491299285843771 # original_commit_timestamp=1491299285661130 (2017-04-04 10:48:05.661130 WEST) # immediate_commit_timestamp=1491299285843771 (2017-04-04 10:48:05.843771 WEST) /*!80001 SET @@SESSION.original_commit_timestamp=1491299285661130*//*!*/; SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'/*!*/; # at 233
通常情况下，original_commit_timestamp 在应用事务的所有副本上总是相同的。在源-副本复制中，事务在（原始）源的二进制日志中的 original_commit_timestamp 总是与其 immediate_commit_timestamp 相同。在副本的中继日志中，事务的 original_commit_timestamp 和 immediate_commit_timestamp 与源的二进制日志中相同；而在副本自己的二进制日志中，事务的 immediate_commit_timestamp 对应的是副本提交该事务的时间。As a rule, the original_commit_timestamp is
always the same on all replicas where the transaction is
applied. In source-replica replication, the
original_commit_timestamp of a transaction in
the (original) source’s binary log is always the same as its
immediate_commit_timestamp. In the
replica’s relay log, the
original_commit_timestamp and
immediate_commit_timestamp of the transaction
are the same as in the source’s binary log; whereas in its own
binary log, the transaction’s
immediate_commit_timestamp corresponds to
when the replica committed the transaction.
在组复制设置中，当原始源是组的成员时，original_commit_timestamp 会在事务准备提交时生成。换句话说，当它在原始源上执行完毕并且其写集准备发送到组中的所有成员进行认证时。如果原始源是组外的服务器，original_commit_timestamp 会被保留。特定事务的相同 original_commit_timestamp 会被复制到组中的所有服务器，以及从组内成员进行复制的任何组外副本。事务的每个接收方也会在其二进制日志中使用 immediate_commit_timestamp 存储本地提交时间。In a Group Replication setup, when the original source is a
member of a group, the
original_commit_timestamp is generated when
the transaction is ready to be committed. In other words, when
it finished executing on the original source and its write set
is ready to be sent to all members of the group for
certification. When the original source is a server outside the
group, the original_commit_timestamp is
preserved. The same original_commit_timestamp
for a particular transaction is replicated to all servers in the
group, and to any replica outside the group that is replicating
from a member. Each recipient of the transaction also stores the
local commit time in its binary log using
immediate_commit_timestamp.
视图更改事件是 Group Replication 独有的特殊情况。包含这些事件的事务由每个组成员生成，但共享相同的 GTID（因此，它们并不是先在源端执行再复制到组中，而是组的所有成员执行并应用相同的事务）。组成员为与视图更改事件相关的事务设置本地时间戳值。View change events, which are exclusive to Group Replication, are a special case. Transactions containing these events are generated by each group member but share the same GTID (so, they are not first executed in a source and then replicated to the group, but all members of the group execute and apply the same transaction). Group members set local timestamp values for transactions associated with view change events.
One of the most common ways to monitor replication delay (lag)
in previous MySQL versions was by relying on the
Seconds_Behind_Master field in the output of
SHOW REPLICA STATUS. However,
this metric is not suitable when using replication topologies
more complex than the traditional source-replica setup, such as
Group Replication. The addition of
immediate_commit_timestamp以及original_commit_timestamp to MySQL 8 provides
a much finer degree of information about replication delay. The
recommended method to monitor replication delay in a topology
that supports these timestamps is using the following
Performance Schema tables.One of the most common ways to monitor replication delay (lag)
in previous MySQL versions was by relying on the
Seconds_Behind_Master field in the output of
SHOW REPLICA STATUS. However,
this metric is not suitable when using replication topologies
more complex than the traditional source-replica setup, such as
Group Replication. The addition of
immediate_commit_timestamp and
original_commit_timestamp to MySQL 8 provides
a much finer degree of information about replication delay. The
recommended method to monitor replication delay in a topology
that supports these timestamps is using the following
Performance Schema tables.
replication_connection_status：到源的连接的当前状态，提供有关连接线程排入中继日志的最后和当前事务的信息。replication_connection_status: current status of the connection to the source, provides information on the last and current transaction the connection thread queued into the relay log.replication_applier_status_by_coordinator：协调线程的当前状态，仅在使用多线程副本时显示信息，提供关于协调线程缓冲到工作线程队列的最后事务，以及它当前正在缓冲的事务的信息。replication_applier_status_by_coordinator: current status of the coordinator thread that only displays information when using a multithreaded replica, provides information on the last transaction buffered by the coordinator thread to a worker’s queue, as well as the transaction it is currently buffering.replication_applier_status_by_worker：应用来自源的事务的线程当前状态，提供由复制 SQL 线程或在使用多线程副本时由每个工作线程应用的事务信息。replication_applier_status_by_worker: current status of the thread(s) applying transactions received from the source, provides information about the transactions applied by the replication SQL thread, or by each worker thread when using a multithreaded replica.
使用这些表，你可以监控有关相应线程处理的最后一个事务以及该线程当前处理的事务的信息。此信息包括：Using these tables you can monitor information about the last transaction the corresponding thread processed and the transaction that thread is currently processing. This information comprises:
事务的 GTIDa transaction’s GTID
事务的
original_commit_timestamp和immediate_commit_timestamp，从副本的中继日志中获取a transaction'soriginal_commit_timestampandimmediate_commit_timestamp, retrieved from the replica’s relay log线程开始处理事务的时间the time a thread started processing a transaction
对于最后处理的事务，线程完成处理它的时间for the last processed transaction, the time the thread finished processing it
除了性能模式表之外，SHOW REPLICA STATUS 的输出还有三个字段显示：In addition to the Performance Schema tables, the output of
SHOW REPLICA STATUS has three
fields that show:
SQL_Delay：一个非负整数，表示通过CHANGE REPLICATION SOURCE TO SOURCE_DELAY=N配置的复制延迟，其中N以秒为单位。SQL_Delay: A nonnegative integer indicating the replication delay configured usingCHANGE REPLICATION SOURCE TO SOURCE_DELAY=, whereNNis measured in seconds.SQL_Remaining_Delay：当Replica_SQL_Running_State为Waiting until SOURCE_DELAY seconds after master executed event时，该字段包含一个整数，表示剩余延迟的秒数。在其他情况下，该字段为NULL。SQL_Remaining_Delay: WhenReplica_SQL_Running_StateisWaiting until SOURCE_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field isNULL.Replica_SQL_Running_State：一个字符串，表示 SQL 线程的状态（类似于Replica_IO_State）。该值与 SQL 线程在State中显示的SHOW PROCESSLIST值相同。Replica_SQL_Running_State: A string indicating the state of the SQL thread (analogous toReplica_IO_State). The value is identical to theStatevalue of the SQL thread as displayed bySHOW PROCESSLIST.
当复制 SQL 线程在等待延迟时间结束以执行事件时，SHOW
PROCESSLIST 会将其 State 值显示为 Waiting until SOURCE_DELAY seconds after
master executed event。When the replication SQL thread is waiting for the delay to
elapse before executing an event, SHOW
PROCESSLIST displays its State
value as Waiting until SOURCE_DELAY seconds after
master executed event.