运维首页 > 数据运维 > MySQL8.0中关于复制Replication的相关知识点补充

MySQL8.0中关于复制Replication的相关知识点补充

2023年 10月 23日 49.0k 0

适用范围

MySQL 8.0版本

8.0版本中关于复制的相关记录追踪

  MySQL发展到8.0版本后，关于Replication复制的信息也有了一个比较详细的扩展，不仅仅体现在 show slave statusG 中，而是在相关字典表中也记录了详细信息，请参考下面官网截图，关于复制的数据字典已经足足有10多个表：

MySQL8.0相关字典表：（replication开头的有15张表，比5.7版本多了7张表）

MySQL5.7相关字典表：（replication开头的只有8张表）

具体相关表简要描述：
  1. replication_connection_configuration：主从连接配置相关参数

root@localhost:performance_schema 03:06:08 >select * from replication_connection_configurationG
*************************** 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)

  1. replication_connection_status: 主从复制当前连接的状态

root@localhost:performance_schema 03:09:22 >select * from replication_connection_statusG
*************************** 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)

  1. 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)

  1. 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)

  1. 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)

  1. replication_applier_status_by_coordinator：协调线程的状态(单线程复制的从库为空)

root@localhost:performance_schema 03:21:07 >select * from replication_applier_status_by_coordinatorG
*************************** 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)

  1. replication_applier_status_by_worker：工作线程的状态

root@localhost:performance_schema 03:23:22 >select * from replication_applier_status_by_workerG
*************************** 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)

  1. 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)

  1. 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)

  1. 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)

  1. 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)

  1. replication_group_configuration_version ：组复制版本表

  2. replication_group_member_actions：组复制成员操作表

  3. replication_group_communication_information：组复制配置选项表

  4. replication_asynchronous_connection_failover_managed：从库异步复制故障转移管理表

小结：

  数据字典表的优势在于可以使用查询语句来执行查询主从复制的状态信息和配置信息，相比原来show replia statusG 而言，虽然show replia statusG 命令可以更直观看到结果，但是对于应用程序或者接口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_fullG
*************************** 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/

向阳逐梦
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

向阳逐梦

这个人很懒，什么都没有留下～

文章8411 评论0