MySQL - MGR集群状态

最新推荐文章于 2024-05-20 14:31:07 发布

CodeHamal

最新推荐文章于 2024-05-20 14:31:07 发布

阅读量124

点赞数

文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/weixin_45517132/article/details/134314039

版权

MGR检查数据同步状态

集群构建

使用博客资源中的配置文件和脚本进行初始化

语句一

(1)ONLINE：
SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status WHERE  channel_name = 'group_replication_applier' UNION ALL SELECT variable_value FROM performance_schema.global_variables WHERE  variable_name = 'gtid_executed';
(2)RECOVERING：
SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status WHERE  channel_name = 'group_replication_recovery' UNION ALL SELECT variable_value
FROM performance_schema.global_variables WHERE  variable_name = 'gtid_executed';show master status\G；
(3)OFFLINE、ERROR、UNREACHABLE

group_replication_applier : 检查ONLINE状态下的主从同步
group_replication_recovery ：检查RECOVERING状态下的数据恢复

语句二

SELECT MEMBER_ID AS id, COUNT_TRANSACTIONS_IN_QUEUE AS trx_tobe_verified, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS trx_tobe_applied, COUNT_TRANSACTIONS_CHECKED AS trx_chkd, COUNT_TRANSACTIONS_REMOTE_APPLIED AS trx_done, COUNT_TRANSACTIONS_LOCAL_PROPOSED AS proposed FROM performance_schema.replication_group_member_stats;

COUNT_TRANSACTIONS_IN_QUEUE : 等待冲突检查的事务数（检查完毕后会去排队等待apply）
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE ：已经接收到的待apply的事务数
当二者有一个不为0时，证明有事务待应用（即复制存在延迟）。

其他监控表见mysql官方文档（下面引用参考）

引用参考

https://cloud.tencent.com/developer/article/1949033
https://dev.mysql.com/doc/refman/8.2/en/group-replication.html
https://dev.mysql.com/doc/refman/8.2/en/group-replication-monitoring.html