MySQL 8 主从延迟监控- Biubiu加速器,Biubiuapp,Biubiu免费版

我们中的许多老 MySQL DBA 都会使用 SHOW REPLICA STATUS 中Seconds_Behind_Source 来查找(异步)复制的状态和监控延迟。请注意新术语，以前的版本中我们执行 SHOW SLAVE STATUS，我相信我们都使用过旧术语，MySQL 8.0.22 版本开始，START SLAVE、STOP SLAVE、SHOW SLAVE STATUS、SHOW SLAVE HOSTS、RESET SLAVE 命令被弃用，取而代之的是 START REPLICA、STOP REPLICA、SHOW REPLICA STATUS、SHOW REPLICAS、RESET REPLICA。

对于主库，执行 SHOW MASTER STATUS 。

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000002 |  1632628 |              |                  | bd6b3216-04d6-11ec-b76f-000c292c1f7b:1-3306 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

SHOW MASTER STATUS 的输出中重点关注 File 和 Position 这两个指标的值。

对于从库，执行 SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
              ...
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1632628
          ...
        Relay_Master_Log_File: mysql-bin.000002
          ...
          Exec_Master_Log_Pos: 1632628
          ...

SHOW SLAVE STATUS 的输出中重点关注 Master_Log_File，Read_Master_Log_Pos，Relay_Master_Log_File，Exec_Master_Log_Pos 这四个指标的值。

接下来，重点比较以下两对值。

第一对：( File , Position ) & ( Master_Log_File , Read_Master_Log_Pos )

这里面，( File , Position ) 记录了主库 binlog 的位置。( Master_Log_File , Read_Master_Log_Pos ) 记录了 IO 线程当前正在接收的二进制日志事件在主库 binlog 中的位置。

如果 ( File , Position ) 大于 ( Master_Log_File , Read_Master_Log_Pos ) ，则意味着 IO 线程存在延迟。

第二对：( Master_Log_File , Read_Master_Log_Pos ) & ( Relay_Master_Log_File , Exec_Master_Log_Pos )

这里面，( Relay_Master_Log_File, Exec_Master_Log_Pos ) 记录了 SQL 线程当前正在重放的二进制日志事件在主库 binlog 的位置。

如果 ( Relay_Master_Log_File, Exec_Master_Log_Pos ) < ( Master_Log_File, Read_Master_Log_Pos ) ，则意味着 SQL 线程存在延迟。

但是，MySQL 复制已经发展了很多，官方复制团队已经努力包含有关 MySQL 可用的所有复制风格的许多有用信息。

例如，我们添加了并行复制、组复制等。旧版“显示副本状态”结果中缺少所有这些信息。

有更好的方法来监视和观察使用 Performance_Schema 复制过程。

目前在 Performance_Schema 中，有 15 个与复制检测相关的表：

select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS 
from information_schema.TABLES 
where TABLE_SCHEMA='performance_schema' and table_name like 'replication_%';

+------------------------------------------------------+
| Tables_in_performance_schema (replication%)          |
+------------------------------------------------------+
| replication_applier_configuration                    |
| replication_applier_filters                          |
| replication_applier_global_filters                   |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_communication_information          |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| replication_group_member_stats                       |
| replication_group_members                            |
+------------------------------------------------------+
15 rows in set (0.0038 sec)

replication_lag

select * from sys.replication_lag;
+---------------------------+-----------------------+------------------------+
| channel_name              | max_lag_from_original | max_lag_from_immediate |
+---------------------------+-----------------------+------------------------+
| clusterset_replication    | 00:00:04.963223       | 00:00:04.940782        |
| group_replication_applier | 0                     | 0                      |
+---------------------------+-----------------------+------------------------+

从上面的输出中，我们可以看到 MySQL 实例是一个异步副本，但它也是组复制集群的一部分。

事实上，这是 InnoDB ClusterSet 中 DR 集群的主要成员。

我们还可以看到，这个副本晚了将近 5 秒（滞后）。

然后，我们有了复制通道的名称以及原始提交者和直接源（在级联复制的情况下）的最大延迟/滞后（因为在并行复制的情况下可能有几个工作线程）。

在组复制集群（InnoDB 集群）的辅助成员上，我们可以看到以下输出：

select * from sys.replication_lag;
+----------------------------+-----------------------+------------------------+
| channel_name               | max_lag_from_original | max_lag_from_immediate |
+----------------------------+-----------------------+------------------------+
| group_replication_recovery | null                  | null                   |
| group_replication_applier  | 00:00:02.733008       | 00:00:02.733008        |
+----------------------------+-----------------------+------------------------+

我们可以看到用于恢复的通道（读取丢失的二进制日志事件、事务、节点加入组时）未被使用，并且组复制的应用程序有点滞后。

replication_status

此视图更完整，每个工作人员都有一行。

让我们以 InnoDB ClusterSet 的 DR 站点的主要成员为例：

select * from replication_status;
+-------------------------------+----------+----------+---------+-------------------+--------------------+
| channel                       | io_state | co_state | w_state | lag_from_original | lag_from_immediate |
+-------------------------------+----------+----------+---------+-------------------+--------------------+
| group_replication_applier (1) | ON       | ON       | ON      | none              | none               |
| group_replication_applier (2) | ON       | ON       | ON      | none              | none               |
| group_replication_applier (3) | ON       | ON       | ON      | none              | none               |
| group_replication_applier (4) | ON       | ON       | ON      | none              | none               |
| clusterset_replication (1)    | ON       | ON       | ON      | 00:00:15.395870   | 00:00:15.380884    |
| clusterset_replication (2)    | ON       | ON       | ON      | 00:00:15.395686   | 00:00:15.380874    |
| clusterset_replication (3)    | ON       | ON       | ON      | 00:00:15.411204   | 00:00:15.388451    |
| clusterset_replication (4)    | ON       | ON       | ON      | 00:00:15.406154   | 00:00:15.388434    |
+-------------------------------+----------+----------+---------+-------------------+--------------------+

我们可以看到，来自Primary集群的并行(异步)复制使用了4个并行工作者。

我们也可以看到他们延迟了……

你可能已经注意到有 3 个状态(都是 ON)。使用 SHOW REPLICA STATUS 我们只能看到:
Replica_IO_Running: Yes
Replica_SQL_Running: Yes

对于并行复制，我们在应用二进制日志事件期间有另一个线程参与复制：coordinator thread 协调器线程。

Replication Status Full

当然，我们也可以提供有关复制的更多详细信息。

让我们看一个示例结果：

select * from sys.replication_status_full\G
*************************** 1. row ***************************
                 channel: group_replication_applier (1)
                    host: <NULL>
                    port: 0
                    user:
             source_uuid: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
              group_name: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
last_heartbeat_timestamp: 0000-00-00 00:00:00.000000
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: NULL
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Replica has read all relay log; waiting for more updates
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: Waiting for an event from Coordinator
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 03:36:40.474223
      applier_busy_state: IDLE
       lag_from_original: none
      lag_from_immediate: none
          transport_time: 1.80 us
       time_to_relay_log: 12.00 us
              apply_time: 784.00 us
last_applied_transaction: 7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:3
 last_queued_transaction: 7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:3
queued_gtid_set_to_apply:
*************************** 2. row ***************************
                 channel: group_replication_applier (2)
                    host: <NULL>
                    port: 0
                    user:
             source_uuid: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
              group_name: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
last_heartbeat_timestamp: 0000-00-00 00:00:00.000000
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: NULL
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Replica has read all relay log; waiting for more updates
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: Waiting for an event from Coordinator
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 03:36:40.474223
      applier_busy_state: IDLE
       lag_from_original: none
      lag_from_immediate: none
          transport_time: 1.80 us
       time_to_relay_log: 12.00 us
              apply_time:   0 ps
last_applied_transaction:
 last_queued_transaction: 7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:3
queued_gtid_set_to_apply:
*************************** 3. row ***************************
                 channel: group_replication_applier (3)
                    host: <NULL>
                    port: 0
                    user:
             source_uuid: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
              group_name: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
last_heartbeat_timestamp: 0000-00-00 00:00:00.000000
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: NULL
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Replica has read all relay log; waiting for more updates
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: Waiting for an event from Coordinator
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 03:36:40.474223
      applier_busy_state: IDLE
       lag_from_original: none
      lag_from_immediate: none
          transport_time: 1.80 us
       time_to_relay_log: 12.00 us
              apply_time:   0 ps
last_applied_transaction:
 last_queued_transaction: 7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:3
queued_gtid_set_to_apply:
*************************** 4. row ***************************
                 channel: group_replication_applier (4)
                    host: <NULL>
                    port: 0
                    user:
             source_uuid: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
              group_name: 7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e
last_heartbeat_timestamp: 0000-00-00 00:00:00.000000
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: NULL
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Replica has read all relay log; waiting for more updates
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: Waiting for an event from Coordinator
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 03:36:40.474223
      applier_busy_state: IDLE
       lag_from_original: none
      lag_from_immediate: none
          transport_time: 1.80 us
       time_to_relay_log: 12.00 us
              apply_time:   0 ps
last_applied_transaction:
 last_queued_transaction: 7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:3
queued_gtid_set_to_apply:
*************************** 5. row ***************************
                 channel: clusterset_replication (1)
                    host: 127.0.0.1
                    port: 3310
                    user: mysql_innodb_cs_b0adbc6c
             source_uuid: 2cb77a02-40eb-11ee-83f4-c8cb9e32df8e
              group_name:
last_heartbeat_timestamp: 2023-08-22 18:48:41.037817
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: Waiting for source to send event
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Waiting for replica workers to process their queues
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: waiting for handler commit
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 00:00:00.001134
      applier_busy_state: APPLYING
       lag_from_original: 00:00:01.799071
      lag_from_immediate: 00:00:01.783404
          transport_time: 2.26 ms
       time_to_relay_log: 19.00 us
              apply_time: 14.63 ms
last_applied_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105180
 last_queued_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105547
queued_gtid_set_to_apply: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105184-105547
*************************** 6. row ***************************
                 channel: clusterset_replication (2)
                    host: 127.0.0.1
                    port: 3310
                    user: mysql_innodb_cs_b0adbc6c
             source_uuid: 2cb77a02-40eb-11ee-83f4-c8cb9e32df8e
              group_name:
last_heartbeat_timestamp: 2023-08-22 18:48:41.037817
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: Waiting for source to send event
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Waiting for replica workers to process their queues
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: waiting for handler commit
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 00:00:00.001134
      applier_busy_state: APPLYING
       lag_from_original: 00:00:01.797743
      lag_from_immediate: 00:00:01.783390
          transport_time: 2.26 ms
       time_to_relay_log: 19.00 us
              apply_time: 21.47 ms
last_applied_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105181
 last_queued_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105547
queued_gtid_set_to_apply: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105184-105547
*************************** 7. row ***************************
                 channel: clusterset_replication (3)
                    host: 127.0.0.1
                    port: 3310
                    user: mysql_innodb_cs_b0adbc6c
             source_uuid: 2cb77a02-40eb-11ee-83f4-c8cb9e32df8e
              group_name:
last_heartbeat_timestamp: 2023-08-22 18:48:41.037817
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: Waiting for source to send event
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Waiting for replica workers to process their queues
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: waiting for handler commit
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 00:00:00.001134
      applier_busy_state: APPLYING
       lag_from_original: 00:00:01.786087
      lag_from_immediate: 00:00:01.767563
          transport_time: 2.26 ms
       time_to_relay_log: 19.00 us
              apply_time: 21.58 ms
last_applied_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105182
 last_queued_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105547
queued_gtid_set_to_apply: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105184-105547
*************************** 8. row ***************************
                 channel: clusterset_replication (4)
                    host: 127.0.0.1
                    port: 3310
                    user: mysql_innodb_cs_b0adbc6c
             source_uuid: 2cb77a02-40eb-11ee-83f4-c8cb9e32df8e
              group_name:
last_heartbeat_timestamp: 2023-08-22 18:48:41.037817
      heartbeat_interval: 30
                io_state: ON
         io_thread_state: Waiting for source to send event
                io_errno: 0
               io_errmsg:
              io_errtime: 0000-00-00 00:00:00.000000
                co_state: ON
         co_thread_state: Waiting for replica workers to process their queues
                co_errno: 0
               co_errmsg:
              co_errtime: 0000-00-00 00:00:00.000000
                 w_state: ON
          w_thread_state: waiting for handler commit
                 w_errno: 0
                w_errmsg:
               w_errtime: 0000-00-00 00:00:00.000000
 time_since_last_message: 00:00:00.001134
      applier_busy_state: APPLYING
       lag_from_original: 00:00:01.785881
      lag_from_immediate: 00:00:01.767550
          transport_time: 2.26 ms
       time_to_relay_log: 19.00 us
              apply_time: 29.59 ms
last_applied_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105183
 last_queued_transaction: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105547
queued_gtid_set_to_apply: 54d83026-40eb-11ee-a5d3-c8cb9e32df8e:105184-105547

通过此视图，我们有更多详细信息，例如复制检测信号。我们还概述了 GTID（排队、已应用等）。

我们还会看到有关传输时间（网络）、写入中继日志的时间以及最后的应用时间的信息。

当然，您可以使用视图显示所需的任何内容，例如：

select channel,time_since_last_massage time_last_msg,lag_from_original lag_from_org,
transport_time tsp_time,time_to_relay_log to_relay,apply_time appl_time,last_applied_transaction 
from sys.replication_status_full;

MySQL InnoDB Cluster， ClusterSet， Read Replicas

如果您使用由 Admin API 和 MySQL Shell 管理的不错的集成解决方案，则所有这些信息都可以使用 status（）方法获得。

status（）方法可以用 3 个值进行扩展：

1：包括有关元数据版本、组协议版本、组名称、集群成员 UUID、集群成员角色和组复制报告的状态以及受防护系统变量列表的信息;
2：包括有关连接和
应用者处理的交易的信息;
3：包括有关每个

集群成员的复制机制的更详细的统计信息;

让我们看一个带有扩展选项 3 个群集示例：

JS> cs.status({extended:3})
{
    "clusters": {
        "cluster2": {
            "clusterRole": "REPLICA",
            "clusterSetReplication": {
                "applierQueuedTransactionSet": "54d83026-40eb-11ee-a5d3-c8cb9e32df8e:137385-138500",
                "applierQueuedTransactionSetSize": 1116,
                "applierState": "ON",
                "applierStatus": "APPLYING",
                "applierThreadState": "waiting for handler commit",
                "applierWorkerThreads": 4,
                "coordinatorState": "ON",
                "coordinatorThreadState": "Waiting for replica workers to process their queues",
                "options": {
                    "connectRetry": 3,
                    "delay": 0,
                    "heartbeatPeriod": 30,
                    "retryCount": 10
                },
                "receiver": "127.0.0.1:4420",
                "receiverStatus": "ON",
                "receiverThreadState": "Waiting for source to send event",
                "receiverTimeSinceLastMessage": "00:00:00.002737",
                "replicationSsl": null,
                "source": "127.0.0.1:3310"
            },
            "clusterSetReplicationStatus": "OK",
            "communicationStack": "MYSQL",
            "globalStatus": "OK",
            "groupName": "7b6bf13d-40ed-11ee-bfdd-c8cb9e32df8e",
            "groupViewChangeUuid": "7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e",
            "paxosSingleLeader": "OFF",
            "receivedTransactionSet": "54d83026-40eb-11ee-a5d3-c8cb9e32df8e:129-138500",
            "ssl": "REQUIRED",
            "status": "OK_NO_TOLERANCE",
            "statusText": "Cluster is NOT tolerant to any failures.",
            "topology": {
                "127.0.0.1:4420": {
                    "address": "127.0.0.1:4420",
                    "applierWorkerThreads": 4,
                    "fenceSysVars": [
                        "read_only",
                        "super_read_only"
                    ],
                    "memberId": "c3d726ac-40ec-11ee-ab38-c8cb9e32df8e",
                    "memberRole": "PRIMARY",
                    "memberState": "ONLINE",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "00:00:05.420247",
                    "replicationLagFromOriginalSource": "00:00:05.433548",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.1.0"
                },
                "127.0.0.1:4430": {
                    "address": "127.0.0.1:4430",
                    "applierWorkerThreads": 4,
                    "fenceSysVars": [
                        "read_only",
                        "super_read_only"
                    ],
                    "memberId": "709b15ea-40ed-11ee-a9b3-c8cb9e32df8e",
                    "memberRole": "SECONDARY",
                    "memberState": "ONLINE",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "00:00:00.038075",
                    "replicationLagFromOriginalSource": "00:00:05.432536",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.1.0"
                }
            },
            "transactionSet": "2cb77a02-40eb-11ee-83f4-c8cb9e32df8e:1-4,54d83026-40eb-11ee-a5d3-c8cb9e32df8e:1-137384,54d8329c-40eb-11ee-a5d3-c8cb9e32df8e:1-5,7b6bf4f0-40ed-11ee-bfdd-c8cb9e32df8e:1-3",
            "transactionSetConsistencyStatus": "OK",
            "transactionSetErrantGtidSet": "",
            "transactionSetMissingGtidSet": "54d83026-40eb-11ee-a5d3-c8cb9e32df8e:137385-138552"
        },
        "myCluster": {
            "clusterRole": "PRIMARY",
            "communicationStack": "MYSQL",
            "globalStatus": "OK",
            "groupName": "54d83026-40eb-11ee-a5d3-c8cb9e32df8e",
            "groupViewChangeUuid": "54d8329c-40eb-11ee-a5d3-c8cb9e32df8e",
            "paxosSingleLeader": "OFF",
            "primary": "127.0.0.1:3310",
            "ssl": "REQUIRED",
            "status": "OK",
            "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
            "topology": {
                "127.0.0.1:3310": {
                    "address": "127.0.0.1:3310",
                    "applierWorkerThreads": 4,
                    "fenceSysVars": [],
                    "memberId": "2cb77a02-40eb-11ee-83f4-c8cb9e32df8e",
                    "memberRole": "PRIMARY",
                    "memberState": "ONLINE",
                    "mode": "R/W",
                    "readReplicas": {
                        "127.0.0.1:4410": {
                            "address": "127.0.0.1:4410",
                            "applierStatus": "APPLYING",
                            "applierThreadState": "waiting for handler commit",
                            "applierWorkerThreads": 4,
                            "receiverStatus": "ON",
                            "receiverThreadState": "Waiting for source to send event",
                            "replicationSources": [
                                "PRIMARY"
                            ],
                            "replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3",
                            "role": "READ_REPLICA",
                            "status": "ONLINE",
                            "version": "8.1.0"
                        }
                    },
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.1.0"
                },
                "127.0.0.1:3320": {
                    "address": "127.0.0.1:3320",
                    "applierWorkerThreads": 4,
                    "fenceSysVars": [
                        "read_only",
                        "super_read_only"
                    ],
                    "memberId": "327cb102-40eb-11ee-9904-c8cb9e32df8e",
                    "memberRole": "SECONDARY",
                    "memberState": "ONLINE",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "00:00:04.536190",
                    "replicationLagFromOriginalSource": "00:00:04.536190",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.1.0"
                },
                "127.0.0.1:3330": {
                    "address": "127.0.0.1:3330",
                    "applierWorkerThreads": 4,
                    "fenceSysVars": [
                        "read_only",
                        "super_read_only"
                    ],
                    "memberId": "3d141d7e-40eb-11ee-933b-c8cb9e32df8e",
                    "memberRole": "SECONDARY",
                    "memberState": "ONLINE",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "00:00:04.652745",
                    "replicationLagFromOriginalSource": "00:00:04.652745",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.1.0"
                }
            },
            "transactionSet": "2cb77a02-40eb-11ee-83f4-c8cb9e32df8e:1-4,54d83026-40eb-11ee-a5d3-c8cb9e32df8e:1-138552,54d8329c-40eb-11ee-a5d3-c8cb9e32df8e:1-5"
        }
    },
    "domainName": "myClusterSet",
    "globalPrimaryInstance": "127.0.0.1:3310",
    "metadataServer": "127.0.0.1:3310",
    "primaryCluster": "myCluster",
    "status": "HEALTHY",
    "statusText": "All Clusters available."
}

MySQL HeatWave

如果您在 OCI 上的 MySQL HeatWave 中使用任何类型的复制，则可以使用相同的视图，但您需要在不同的数据库上创建它们，因为 sys 是写保护的。

因此，如果您使用的是 HA、只读副本或手动复制通道，您还可以使用相同的视图来准确了解复制。

结论

复制可观测性非常详细，并为 MySQL 8 提供了大量信息。也许现在是更改查看或监视复制方式的好时机。

另外，直接使用如下 SQL 也可以查看主从复制延迟 seconds_behind_master

mysql> select case 
    -> when min_commit_timestamp is null then 0
    -> else unix_timestamp(now(6)) - unix_timestamp(min_commit_timestamp)
    -> end as seconds_behind_master
    -> from (
    -> select min(applying_transaction_original_commit_timestamp) as min_commit_timestamp
    -> from  performance_schema.replication_applier_status_by_worker
    -> where applying_transaction <> ''
    -> ) t;
+-----------------------+
| seconds_behind_master |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

英文原文： https://dev.mysql.com/blog-archive/mysql-8-and-replication-observability/

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~