适用范围
MySQL 8.0版本
8.0版本中关于复制的相关记录追踪
MySQL发展到8.0版本后，关于Replication复制的信息也有了一个比较详细的扩展，不仅仅体现在 show slave status\G 中，而是在相关字典表中也记录了详细信息，请参考下面官网截图，关于复制的数据字典已经足足有10多个表：
MySQL8.0相关字典表：（replication开头的有15张表，比5.7版本多了7张表）
MySQL5.7相关字典表：（replication开头的只有8张表）
具体相关表简要描述：
- replication_connection_configuration：主从连接配置相关参数
root@localhost:performance_schema 03:06:08 >select * from replication_connection_configuration\G *************************** 1. row *************************** CHANNEL_NAME: HOST: 192.168.139.128 PORT: 8034 USER: repl NETWORK_INTERFACE: AUTO_POSITION: 1 SSL_ALLOWED: NO SSL_CA_FILE: SSL_CA_PATH: SSL_CERTIFICATE: SSL_CIPHER: SSL_KEY: SSL_VERIFY_SERVER_CERTIFICATE: NO SSL_CRL_FILE: SSL_CRL_PATH: CONNECTION_RETRY_INTERVAL: 60 CONNECTION_RETRY_COUNT: 86400 HEARTBEAT_INTERVAL: 30.000 TLS_VERSION: PUBLIC_KEY_PATH: GET_PUBLIC_KEY: YES NETWORK_NAMESPACE: COMPRESSION_ALGORITHM: uncompressed ZSTD_COMPRESSION_LEVEL: 3 TLS_CIPHERSUITES: NULL SOURCE_CONNECTION_AUTO_FAILOVER: 0 GTID_ONLY: 0 1 row in set (0.01 sec)复制
- replication_connection_status: 主从复制当前连接的状态
root@localhost:performance_schema 03:09:22 >select * from replication_connection_status\G *************************** 1. row *************************** CHANNEL_NAME: GROUP_NAME: SOURCE_UUID: 7ce9d1d6-60d8-11ee-9656-000c29ff0201 THREAD_ID: 183 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 481 LAST_HEARTBEAT_TIMESTAMP: 2023-10-23 15:09:23.069247 RECEIVED_TRANSACTION_SET: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:1-3804 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2023-10-23 13:54:22.753881 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2023-10-23 13:54:22.753899 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.01 sec)复制
- replication_asynchronous_connection_failover: 异步复制故障转移的源头列表（主库列表）
root@localhost:performance_schema 03:11:35 >select * from replication_asynchronous_connection_failover; Empty set (0.00 sec) root@localhost:performance_schema 03:11:39 >desc replication_asynchronous_connection_failover; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | CHANNEL_NAME | char(64) | NO | | NULL | | | HOST | char(255) | NO | | NULL | | | PORT | int | NO | | NULL | | | NETWORK_NAMESPACE | char(64) | YES | | NULL | | | WEIGHT | int unsigned | NO | | NULL | | | MANAGED_NAME | char(64) | NO | | | | +-------------------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)复制
- replication_applier_configuration：从库事物应用的配置参数
root@localhost:performance_schema 03:11:42 > select * from replication_applier_configuration; +--------------+---------------+-----------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+ | CHANNEL_NAME | DESIRED_DELAY | PRIVILEGE_CHECKS_USER | REQUIRE_ROW_FORMAT | REQUIRE_TABLE_PRIMARY_KEY_CHECK | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_TYPE | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_VALUE | +--------------+---------------+-----------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+ | | 0 | NULL | NO | STREAM | OFF | NULL | +--------------+---------------+-----------------------+--------------------+---------------------------------+---------------------------------------------+----------------------------------------------+ 1 row in set (0.00 sec)复制
- replication_applier_status：从库事物应用的当前状态
root@localhost:performance_schema 03:17:10 >select * from replication_applier_status; +--------------+---------------+-----------------+----------------------------+ | CHANNEL_NAME | SERVICE_STATE | REMAINING_DELAY | COUNT_TRANSACTIONS_RETRIES | +--------------+---------------+-----------------+----------------------------+ | | ON | NULL | 0 | +--------------+---------------+-----------------+----------------------------+ 1 row in set (0.00 sec)复制
- replication_applier_status_by_coordinator：协调线程的状态(单线程复制的从库为空)
root@localhost:performance_schema 03:21:07 >select * from replication_applier_status_by_coordinator\G *************************** 1. row *************************** CHANNEL_NAME: THREAD_ID: 184 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_PROCESSED_TRANSACTION: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP: 2023-10-23 13:54:22.753920 LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP: 2023-10-23 13:54:22.753931 PROCESSING_TRANSACTION: PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.00 sec)复制
- replication_applier_status_by_worker：工作线程的状态
root@localhost:performance_schema 03:23:22 >select * from replication_applier_status_by_worker\G *************************** 1. row *************************** CHANNEL_NAME: WORKER_ID: 1 THREAD_ID: 185 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-10-23 13:54:22.752503 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2023-10-23 13:54:22.753972 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2023-10-23 13:54:22.756103 APPLYING_TRANSACTION: APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 *************************** 2. row *************************** CHANNEL_NAME: WORKER_ID: 2 THREAD_ID: 186 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION: LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION: APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 *************************** 3. row *************************** CHANNEL_NAME: WORKER_ID: 3 THREAD_ID: 187 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION: LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION: APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 *************************** 4. row *************************** CHANNEL_NAME: WORKER_ID: 4 THREAD_ID: 188 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION: LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION: APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 4 rows in set (0.00 sec)复制
- replication_applier_filters：特定复制通道上的复制过滤信息
root@localhost:performance_schema 03:23:25 >select * from replication_applier_filters; Empty set (0.00 sec) root@localhost:performance_schema 03:24:59 >desc replication_applier_filters; +---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | CHANNEL_NAME | char(64) | NO | | NULL | | | FILTER_NAME | char(64) | NO | | NULL | | | FILTER_RULE | longtext | NO | | NULL | | | CONFIGURED_BY | enum('STARTUP_OPTIONS','CHANGE_REPLICATION_FILTER','STARTUP_OPTIONS_FOR_CHANNEL','CHANGE_REPLICATION_FILTER_FOR_CHANNEL') | NO | | NULL | | | ACTIVE_SINCE | timestamp(6) | NO | | NULL | | | COUNTER | bigint unsigned | NO | | 0 | | +---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)复制
- replication_applier_global_filters：全局复制过滤信息
root@localhost:performance_schema 03:26:15 >select * from replication_applier_global_filters; Empty set (0.00 sec) root@localhost:performance_schema 03:26:18 >DESC replication_applier_global_filters; +---------------+-----------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------------------------------------------+------+-----+---------+-------+ | FILTER_NAME | char(64) | NO | | NULL | | | FILTER_RULE | longtext | NO | | NULL | | | CONFIGURED_BY | enum('STARTUP_OPTIONS','CHANGE_REPLICATION_FILTER') | NO | | NULL | | | ACTIVE_SINCE | timestamp(6) | NO | | NULL | | +---------------+-----------------------------------------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)复制
- replication_group_members：组复制成员的网络和状态信息
root@localhost:performance_schema 03:26:26 >select * from replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | d391e9ee-2691-11ec-bf61-00059a3c7a00 | example1 | 4410 | ONLINE | PRIMARY | 8.0.27 | XCom | | group_replication_applier | e059ce5c-2691-11ec-8632-00059a3c7a00 | example2 | 4420 | ONLINE | SECONDARY | 8.0.27 | XCom | | group_replication_applier | ecd9ad06-2691-11ec-91c7-00059a3c7a00 | example3 | 4430 | ONLINE | SECONDARY | 8.0.27 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.0007 sec)复制
- replication_group_member_stats：组复制成员的状态和事物执行信息
root@localhost:performance_schema 03:28:06 >select * from replication_group_member_stats; Empty set (0.00 sec) root@localhost:performance_schema 03:33:02 >desc replication_group_member_stats; +--------------------------------------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------------------+-----------------+------+-----+---------+-------+ | CHANNEL_NAME | char(64) | NO | | NULL | | | VIEW_ID | char(60) | NO | | NULL | | | MEMBER_ID | char(36) | NO | | NULL | | | COUNT_TRANSACTIONS_IN_QUEUE | bigint unsigned | NO | | NULL | | | COUNT_TRANSACTIONS_CHECKED | bigint unsigned | NO | | NULL | | | COUNT_CONFLICTS_DETECTED | bigint unsigned | NO | | NULL | | | COUNT_TRANSACTIONS_ROWS_VALIDATING | bigint unsigned | NO | | NULL | | | TRANSACTIONS_COMMITTED_ALL_MEMBERS | longtext | NO | | NULL | | | LAST_CONFLICT_FREE_TRANSACTION | text | NO | | NULL | | | COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE | bigint unsigned | NO | | NULL | | | COUNT_TRANSACTIONS_REMOTE_APPLIED | bigint unsigned | NO | | NULL | | | COUNT_TRANSACTIONS_LOCAL_PROPOSED | bigint unsigned | NO | | NULL | | | COUNT_TRANSACTIONS_LOCAL_ROLLBACK | bigint unsigned | NO | | NULL | | +--------------------------------------------+-----------------+------+-----+---------+-------+ 13 rows in set (0.00 sec)复制
-
replication_group_configuration_version ：组复制版本表
-
replication_group_member_actions：组复制成员操作表
-
replication_group_communication_information：组复制配置选项表
-
replication_asynchronous_connection_failover_managed：从库异步复制故障转移管理表
小结：
数据字典表的优势在于可以使用查询语句来执行查询主从复制的状态信息和配置信息，相比原来show replia status\G 而言，虽然show replia status\G 命令可以更直观看到结果，但是对于应用程序或者接口API而言确显得更复杂，更不好提取数据信息，而字典表则可以通过查询将结果保存在表中以供进一步分析或者通过变量传给程序或者在存储过程中使用。
额外脚本
这里引用一个来自于lefred大神（https://github.com/lefred）的一个脚本，将MySQL8.0的视图做进一步的加工，集成在sys库下： mysql_8_replication_observability.sql
-- mysql_8_replication_observability.sql 脚本的内容如下：（可以直接在mysql库中执行生成sys库的表进行使用）： use sys; DROP VIEW IF EXISTS replication_status_full; CREATE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW replication_status_full AS SELECT concat(s.channel_name, ' (', w.worker_id,')') AS channel, c.host, c.port, c.user, s.source_uuid, s.group_name, s.last_heartbeat_timestamp, c.heartbeat_interval, s.service_state io_state, st.processlist_state io_thread_state, s.last_error_number io_errno, s.last_error_message io_errmsg, s.last_error_timestamp io_errtime, co.service_state co_state, cot.processlist_state co_thread_state, co.last_error_number co_errno, co.last_error_message co_errmsg, co.last_error_timestamp co_errtime, w.service_state w_state, wt.processlist_state w_thread_state, w.last_error_number w_errno, w.last_error_message w_errmsg, w.last_error_timestamp w_errtime, TIMEDIFF(NOW(6), IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, s.LAST_HEARTBEAT_TIMESTAMP) >= 0, s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, s.LAST_HEARTBEAT_TIMESTAMP) ) as time_since_last_message, IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION, 'IDLE', 'APPLYING') as applier_busy_state, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP ) ) ) AS lag_from_original, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP ) ) ) AS lag_from_immediate, format_pico_time((unix_timestamp(LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) - unix_timestamp(LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)) * 100000000000) transport_time, format_pico_time((unix_timestamp(LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP) - unix_timestamp(LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP)) * 1000000000000) time_to_relay_log, format_pico_time((unix_timestamp(w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP) - unix_timestamp(w.LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP)) * 1000000000000) apply_time, w.LAST_APPLIED_TRANSACTION AS last_applied_transaction, s.LAST_QUEUED_TRANSACTION AS last_queued_transaction, GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, (select variable_value from performance_schema.global_variables where variable_name='gtid_executed') ) as queued_gtid_set_to_apply FROM performance_schema.replication_connection_configuration c JOIN performance_schema.replication_connection_status s ON c.channel_name = s.channel_name LEFT JOIN performance_schema.replication_applier_status_by_coordinator co ON c.channel_name = co.channel_name JOIN performance_schema.replication_applier_status a ON c.channel_name = a.channel_name JOIN performance_schema.replication_applier_status_by_worker w ON c.channel_name = w.channel_name LEFT JOIN ( SELECT * FROM performance_schema.replication_applier_status_by_worker LIMIT 1 ) latest_w ON c.channel_name = latest_w.channel_name LEFT JOIN performance_schema.threads st ON s.thread_id = st.thread_id LEFT JOIN performance_schema.threads cot ON co.thread_id = cot.thread_id LEFT JOIN performance_schema.threads wt ON w.thread_id = wt.thread_id; DROP VIEW IF EXISTS x$replication_status_full; CREATE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW x$replication_status_full AS SELECT concat(s.channel_name, ' (', w.worker_id,')') AS channel, c.host, c.port, c.user, s.source_uuid, s.group_name, s.last_heartbeat_timestamp, c.heartbeat_interval, s.service_state io_state, st.processlist_state io_thread_state, s.last_error_number io_errno, s.last_error_message io_errmsg, s.last_error_timestamp io_errtime, co.service_state co_state, cot.processlist_state co_thread_state, co.last_error_number co_errno, co.last_error_message co_errmsg, co.last_error_timestamp co_errtime, w.service_state w_state, wt.processlist_state w_thread_state, w.last_error_number w_errno, w.last_error_message w_errmsg, w.last_error_timestamp w_errtime, TIMEDIFF(NOW(6), IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, s.LAST_HEARTBEAT_TIMESTAMP) >= 0, s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, s.LAST_HEARTBEAT_TIMESTAMP) ) as time_since_last_message, IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION, 'IDLE', 'APPLYING') as applier_busy_state, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP ) ) ) AS lag_from_original, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP ) ) ) AS lag_from_immediate, ((unix_timestamp(LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) - unix_timestamp(LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)) * 100000000000) transport_time, ((unix_timestamp(LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP) - unix_timestamp(LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP)) * 1000000000000) time_to_relay_log, ((unix_timestamp(w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP) - unix_timestamp(w.LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP)) * 1000000000000) apply_time, w.LAST_APPLIED_TRANSACTION AS last_applied_transaction, s.LAST_QUEUED_TRANSACTION AS last_queued_transaction, GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, (select variable_value from performance_schema.global_variables where variable_name='gtid_executed')) as queued_gtid_set_to_apply FROM performance_schema.replication_connection_configuration c JOIN performance_schema.replication_connection_status s ON c.channel_name = s.channel_name LEFT JOIN performance_schema.replication_applier_status_by_coordinator co ON c.channel_name = co.channel_name JOIN performance_schema.replication_applier_status a ON c.channel_name = a.channel_name JOIN performance_schema.replication_applier_status_by_worker w ON c.channel_name = w.channel_name LEFT JOIN ( SELECT * FROM performance_schema.replication_applier_status_by_worker LIMIT 1 ) latest_w ON c.channel_name = latest_w.channel_name LEFT JOIN performance_schema.threads st ON s.thread_id = st.thread_id LEFT JOIN performance_schema.threads cot ON co.thread_id = cot.thread_id LEFT JOIN performance_schema.threads wt ON w.thread_id = wt.thread_id; DROP VIEW IF EXISTS replication_status; CREATE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW replication_status AS SELECT concat(s.channel_name, ' (', w.worker_id,')') AS channel, s.service_state io_state, co.service_state co_state, w.service_state w_state, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP ) ) ) AS lag_from_original, IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP) = 0, 'none', TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP ) ) ) AS lag_from_immediate FROM performance_schema.replication_connection_configuration c JOIN performance_schema.replication_connection_status s ON c.channel_name = s.channel_name LEFT JOIN performance_schema.replication_applier_status_by_coordinator co ON c.channel_name = co.channel_name JOIN performance_schema.replication_applier_status a ON c.channel_name = a.channel_name JOIN performance_schema.replication_applier_status_by_worker w ON c.channel_name = w.channel_name LEFT JOIN ( SELECT * FROM performance_schema.replication_applier_status_by_worker LIMIT 1 ) latest_w ON c.channel_name = latest_w.channel_name LEFT JOIN performance_schema.threads st ON s.thread_id = st.thread_id LEFT JOIN performance_schema.threads cot ON co.thread_id = cot.thread_id LEFT JOIN performance_schema.threads wt ON w.thread_id = wt.thread_id; DROP VIEW IF EXISTS replication_lag; CREATE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW replication_lag AS SELECT s.channel_name, max( IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) = 0, 0, TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP ) ) )) AS max_lag_from_original, max(IF(co.SERVICE_STATE = 'OFF' OR s.SERVICE_STATE = 'OFF', 'null', IF( GTID_SUBTRACT(s.LAST_QUEUED_TRANSACTION, w.LAST_APPLIED_TRANSACTION) = '' OR UNIX_TIMESTAMP(w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP) = 0, 0, TIMEDIFF( NOW(6),w.APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP ) ) )) AS max_lag_from_immediate FROM performance_schema.replication_connection_configuration c JOIN performance_schema.replication_connection_status s ON c.channel_name = s.channel_name LEFT JOIN performance_schema.replication_applier_status_by_coordinator co ON c.channel_name = co.channel_name JOIN performance_schema.replication_applier_status_by_worker w ON c.channel_name = w.channel_name GROUP BY 1 order by 2 desc, 3 desc; DROP VIEW IF EXISTS replication_lag_human; CREATE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW replication_lag_human AS SELECT channel_name, format_pico_time(MAX(seconds_behind_source)*1000000) lag_behind_source FROM ( SELECT CHANNEL_NAME, MAX(TIMESTAMPDIFF(MICROSECOND, APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP, NOW())) AS seconds_behind_source FROM performance_schema.replication_applier_status_by_worker GROUP BY CHANNEL_NAME UNION SELECT w.CHANNEL_NAME, MIN(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = '', 0, TIMESTAMPDIFF(MICROSECOND, LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP, NOW()))) AS seconds_behind_source FROM performance_schema.replication_applier_status_by_worker AS w JOIN performance_schema.replication_connection_status GROUP BY w.CHANNEL_NAME ) a GROUP BY CHANNEL_NAME;复制
执行上面脚本就等于在MySQL中创建了一些可以安装在sys中的视图，方便获取我们需要的内容信息，举例如下：
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 | +---------------------------+-----------------------+------------------------+ 2 row in set (0.01 sec) 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 | +----------------------------+-----------------------+------------------------+ 2 row in set (0.01 sec) root@localhost:performance_schema 03:58:10 >select * from sys.replication_lag; +--------------+-----------------------+------------------------+ | channel_name | max_lag_from_original | max_lag_from_immediate | +--------------+-----------------------+------------------------+ | | 0 | 0 | +--------------+-----------------------+------------------------+ 1 row in set (0.01 sec) root@localhost:performance_schema 03:58:05 >select * from sys.replication_status; +---------+----------+----------+---------+-------------------+--------------------+ | channel | io_state | co_state | w_state | lag_from_original | lag_from_immediate | +---------+----------+----------+---------+-------------------+--------------------+ | (1) | ON | ON | ON | none | none | | (2) | ON | ON | ON | none | none | | (3) | ON | ON | ON | none | none | | (4) | ON | ON | ON | none | none | +---------+----------+----------+---------+-------------------+--------------------+ 4 rows in set, 6 warnings (0.00 sec) root@localhost:sys 04:01:31 >select * from replication_status_full\G *************************** 1. row *************************** channel: (1) host: 192.168.139.128 port: 8034 user: repl source_uuid: 7ce9d1d6-60d8-11ee-9656-000c29ff0201 group_name: last_heartbeat_timestamp: 2023-10-23 16:01:23.295663 heartbeat_interval: 30.000 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: 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: 00:00:11.307698 applier_busy_state: IDLE lag_from_original: none lag_from_immediate: none transport_time: 137.80 us time_to_relay_log: 18.00 us apply_time: 2.13 ms last_applied_transaction: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 last_queued_transaction: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 queued_gtid_set_to_apply: *************************** 2. row *************************** channel: (2) host: 192.168.139.128 port: 8034 user: repl source_uuid: 7ce9d1d6-60d8-11ee-9656-000c29ff0201 group_name: last_heartbeat_timestamp: 2023-10-23 16:01:23.295663 heartbeat_interval: 30.000 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: 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: 00:00:11.307698 applier_busy_state: IDLE lag_from_original: none lag_from_immediate: none transport_time: 137.80 us time_to_relay_log: 18.00 us apply_time: 0 ps last_applied_transaction: last_queued_transaction: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 queued_gtid_set_to_apply: *************************** 3. row *************************** channel: (3) host: 192.168.139.128 port: 8034 user: repl source_uuid: 7ce9d1d6-60d8-11ee-9656-000c29ff0201 group_name: last_heartbeat_timestamp: 2023-10-23 16:01:23.295663 heartbeat_interval: 30.000 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: 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: 00:00:11.307698 applier_busy_state: IDLE lag_from_original: none lag_from_immediate: none transport_time: 137.80 us time_to_relay_log: 18.00 us apply_time: 0 ps last_applied_transaction: last_queued_transaction: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 queued_gtid_set_to_apply: *************************** 4. row *************************** channel: (4) host: 192.168.139.128 port: 8034 user: repl source_uuid: 7ce9d1d6-60d8-11ee-9656-000c29ff0201 group_name: last_heartbeat_timestamp: 2023-10-23 16:01:23.295663 heartbeat_interval: 30.000 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: 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: 00:00:11.307698 applier_busy_state: IDLE lag_from_original: none lag_from_immediate: none transport_time: 137.80 us time_to_relay_log: 18.00 us apply_time: 0 ps last_applied_transaction: last_queued_transaction: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:3804 queued_gtid_set_to_apply: 4 rows in set, 6 warnings (0.00 sec)复制
参考文档
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-replication-tables.html
https://lefred.be/
https://gist.github.com/lefred/
最后修改时间：2023-10-24 10:06:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1253次阅读
2025-03-13 11:40:53
MySQL8.0统计信息总结
闫建(Rock Yan)
470次阅读
2025-03-17 16:04:03
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
448次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
441次阅读
2025-03-04 21:56:13
SQL优化 - explain查看SQL执行计划（一）
金同学
382次阅读
2025-03-13 16:04:22
MySQL突然崩溃？教你用gdb解剖core文件，快速锁定“元凶”!
szrsu
329次阅读
2025-03-13 00:29:43
MySQL生产实战优化（利用Index skip scan优化性能提升257倍）
chengang
319次阅读
2025-03-17 10:36:40
一键装库脚本3分钟极速部署，传统耗时砍掉95%！
IT邦德
229次阅读
2025-03-10 07:58:44
MySQL8.0直方图功能简介
Rock Yan
216次阅读
2025-03-21 15:30:53
MySQL 有没有类似 Oracle 的索引监控功能？
JiekeXu
196次阅读
2025-03-19 23:43:22
