9102b75afd11571b77350b5fd44ae8ed.gif

作者 | Frederic Descamps

来源 |https://dev.mysql.com/blog-archive/mysql-8-and-replication-observability/

大家好,我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 MySQL 8 主从延迟监控(复制可观测性),欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号,标星或置顶,更多干货第一时间到达。

我们中的许多老 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 REPLICA STATUS\G

mysql> show replica 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 REPLICA 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_%';

5d152659349a440c4a6e523f22d6a804.png

+------------------------------------------------------+
| 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)

但确实,理解这些指标的含义以及在哪里寻找对我们 MySQL DBA 重要的有用信息并不总是那么容易:我的复制是否落后于源库?

原作者创建了一些可以安装在 sys 中的视图,以使用这些指标中的大多数来获取与我们 DBA 相关的东西,作者的 315 行脚本中包含五个视图:replication_status_full、x$replication_status_full、replication_status、replication_lag、replication_lag_human。大家可以在墨天轮【https://www.modb.pro/doc/117989】或者公众号后台回复脚本名【mysql_8_replication_observability.sql】获取。

37baec84dec9f4501038a35d5f565cde.png

让我们仔细看看这些视图。

复制延迟 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 站点的主要成员为例:

e40663e4fef8a61523b60a8042b8d979.png

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;

27bc87ac78a9b90fcd0ed2e8033232a9.png

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 提供了大量信息。也许现在是更改查看或监视复制方式的好时机。

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

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

欢迎关注我公众号【JiekeXu DBA之路】,第一时间一起学习新知识!以下三个地址可以找到我,其他地址都属于盗版侵权爬取我的文章,而且代码格式、图片等均有错乱,不方便阅读,欢迎来我公众号或者墨天轮地址关注我,第一时间收获最新消息。

————————————————————————————
公众号:JiekeXu DBA之路
CSDN :https://blog.csdn.net/JiekeXu
墨天轮:https://www.modb.pro/u/4347
————————————————————————————

36dc0549c25a4390f94141e0f810c11b.gif

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理
 
 

Oracle 19c RAC 遇到的几个问题

OGG|Oracle 数据迁移后比对一致性

OGG|Oracle GoldenGate 微服务架构

Oracle 查询表空间使用率超慢问题一则

国产数据库|TiDB 5.4 单机快速安装初体验

Oracle ADG 备库停启维护流程及增量恢复

Linux 环境搭建 MySQL8.0.28 主从同步环境

从国产数据库调研报告中你都能了解哪些信息及我的总结建议

Logo

数据库是今天社会发展不可缺少的重要技术,它可以把大量的信息进行有序的存储和管理,为企业的数据处理提供了强大的保障。

更多推荐