1.主从复制的重要视图
select * from performance_schema.replication_applier_status_by_worker limit 1\G;
mysql> select * from performance_schema.replication_applier_status_by_worker limit 1\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: 266
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:378062598
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-07-26 11:01:15.111633
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-07-26 11:01:15.111633
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2025-07-28 09:55:57.166381
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-07-28 09:55:57.470992
APPLYING_TRANSACTION: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:378062600
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-07-26 11:01:15.419982
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-07-26 11:01:15.419982
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2025-07-28 09:55:57.522078
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
1 row in set (0.01 sec)
2.为什么在做读写分离的MySQL主从上从出现找不到记录呢。
java的集群模式做负载均衡，由Java程序去识别主从库，如果从库是可以读写的，那么写可能会落入从库，导致从库不一致，最终导致主从中断。
所以在这种情况下，从库必须设置为为只读 read_only=on,super_read_only=on;
很多时候不推荐使用JAVA的负载均衡功能，因为可能导致主从不一致，如果要做读写分离，建议使用读写分离中间件。
3.主从相关的视图
performance_schema.replication_applier_configuration
performance_schema.replication_applier_filters
performance_schema.replication_applier_global_filters
performance_schema.replication_applier_status
performance_schema.replication_applier_status_by_coordinator
performance_schema.replication_applier_status_by_worker
performance_schema.replication_asynchronous_connection_failover
performance_schema.replication_asynchronous_connection_failover_managed
performance_schema.replication_connection_configuration
performance_schema.replication_connection_status
mysql> select * from performance_schema.replication_applier_configuration\G ;
*************************** 1. row ***************************
CHANNEL_NAME:
DESIRED_DELAY: 0
PRIVILEGE_CHECKS_USER: NULL
REQUIRE_ROW_FORMAT: NO
REQUIRE_TABLE_PRIMARY_KEY_CHECK: STREAM
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_TYPE: OFF
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_VALUE: NULL
1 row in set (0.02 sec)
select *from performance_schema.replication_applier_filters ;
Empty set (0.00 sec)
mysql> select * from performance_schema.replication_applier_global_filters ;
Empty set (0.00 sec)
mysql> select * from performance_schema.replication_applier_status ;
+--------------+---------------+-----------------+----------------------------+
| CHANNEL_NAME | SERVICE_STATE | REMAINING_DELAY | COUNT_TRANSACTIONS_RETRIES |
+--------------+---------------+-----------------+----------------------------+
| | ON | NULL | 0 |
+--------------+---------------+-----------------+----------------------------+
1 row in set (0.12 sec)
select *from performance_schema.replication_applier_status_by_coordinator ;
mysql> select *from performance_schema.replication_applier_status_by_coordinator\G
*************************** 1. row ***************************
CHANNEL_NAME:
THREAD_ID: 265
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_PROCESSED_TRANSACTION: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:378071368
LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-07-26 11:17:17.419847
LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-07-26 11:17:17.419847
LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP: 2025-07-28 10:13:07.491812
LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP: 2025-07-28 10:13:07.491871
PROCESSING_TRANSACTION: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:378071369
PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-07-26 11:17:17.791263
PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-07-26 11:17:17.791263
PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP: 2025-07-28 10:13:07.491878
1 row in set (0.13 sec)
select * from performance_schema.replication_asynchronous_connection_failover ;
Empty set (0.53 sec)
select * from performance_schema.replication_asynchronous_connection_failover_managed;
Empty set (0.00 sec)
mysql> select * from performance_schema.replication_connection_configuration\G ;
*************************** 1. row ***************************
CHANNEL_NAME:
HOST: 192.168.1.1
PORT: 3305
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: NO
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)
mysql> select * from performance_schema.replication_connection_status\G ;
*************************** 1. row ***************************
CHANNEL_NAME:
GROUP_NAME:
SOURCE_UUID: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643
THREAD_ID: 306
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 2
LAST_HEARTBEAT_TIMESTAMP: 2025-07-14 10:43:21.577131
RECEIVED_TRANSACTION_SET: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:369093461-379491074
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION: 29a8eb3a-3cf1-11ee-8f45-fa163e3a3643:379491074
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-07-28 10:35:16.390179
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-07-28 10:35:16.390179
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2025-07-28 10:35:16.464099
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2025-07-28 10:35:16.464154
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.00 sec)
#由此可见，主从复制比较重要的几个视图如下:
performance_schema.replication_connection_status #复制主库的情况。
performance_schema.replication_connection_configuration #心跳间隔和复制情况
performance_schema.replication_applier_status_by_coordinator #正常处理的事务的GTID,及事务的提交时间。
performance_schema.replication_applier_status_by_worker #主从复制的详细情况。
4.组复制相关视图
#组复制相关的视图,在普通主从中查询不到有用的信息。
performance_schema.replication_group_communication_information
performance_schema.replication_group_configuration_version
performance_schema.replication_group_member_actions
performance_schema.replication_group_member_stats
performance_schema.replication_group_members select * from performance_schema.replication_group_communication_information ;
select * from performance_schema.replication_group_configuration_version;
select * from performance_schema.replication_group_member_actions ;
mysql> select * from performance_schema.replication_group_configuration_version;
+----------------------------------+---------+
| name | version |
+----------------------------------+---------+
| replication_group_member_actions | 1 |
+----------------------------------+---------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.replication_group_member_actions ;
+------------------------------------------+------------------------+---------+----------+----------+----------------+
| name | event | enabled | type | priority | error_handling |
+------------------------------------------+------------------------+---------+----------+----------+----------------+
| mysql_disable_super_read_only_if_primary | AFTER_PRIMARY_ELECTION | 1 | INTERNAL | 1 | IGNORE |
| mysql_start_failover_channels_if_primary | AFTER_PRIMARY_ELECTION | 1 | INTERNAL | 10 | CRITICAL |
+------------------------------------------+------------------------+---------+----------+----------+----------------+
2 rows in set (0.01 sec)
select * from performance_schema.replication_group_member_stats;
mysql> select * from performance_schema.replication_group_member_stats\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
VIEW_ID:
MEMBER_ID:
COUNT_TRANSACTIONS_IN_QUEUE: 0
COUNT_TRANSACTIONS_CHECKED: 0
COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS:
LAST_CONFLICT_FREE_TRANSACTION:
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
COUNT_TRANSACTIONS_REMOTE_APPLIED: 0
COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
1 row in set (0.00 sec)
select * from performance_schema.replication_group_members;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.01 sec)、
扫一扫
1697
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
