mysql主从数据库视图_【Mysql高可用架构(2)】主从管理的系统视图

## 主从管理的视图表

mysql的主从管理信息可以在*performance\_schema*库中的以replication开头的表查看，这些表就是我们进行主从管理的系统视图！

**进入172.30.0.3从数据库**

~~~

mysql> use performance_schema;

Database changed

mysql> show tables like 'replication%';

+---------------------------------------------+

| Tables_in_performance_schema (replication%) |

+---------------------------------------------+

| replication_applier_configuration |

| replication_applier_status |

| replication_applier_status_by_coordinator |

| replication_applier_status_by_worker |

| replication_connection_configuration |

| replication_connection_status |

| replication_group_member_stats |

| replication_group_members |

+---------------------------------------------+

8 rows in set (0.02 sec)

~~~

## 主从延迟设置

~~~

# 设置主从延迟 (在docker里面测试没有效果)

# 一个小时后才从主复制到从,注意：要先停止从之后才能设置

mysql> stop slave;

mysql> change master to master_delay=3600;

mysql> start slave;

mysql> select * from replication_applier_configuration \G

*************************** 1. row ***************************

CHANNEL_NAME: # 链路名称：用来在多链路复制时区分不同的链路，默认空字符串

DESIRED_DELAY: 3600 # 主从延迟：用来设置复制过程中设置的主从延迟，默认为0不延迟

mysql> select * from replication_applier_status \G

*************************** 1. row ***************************

CHANNEL_NAME:

SERVICE_STATE: ON

REMAINING_DELAY: 3496 # 主有变化，还剩多少时间到从

~~~

## 多线程复制查看

~~~

# 多线程复制是控制器的情况

mysql> select * from replication_applier_status_by_coordinator \G

# 多线程复制时的线程, 几行数据代表几个线程在工作

mysql> select * from replication_applier_status_by_worker \G

~~~

## 多源复制查看

~~~

# 查看各个channel的连接配置信息：host、port、user、auto_position等

mysql> select * from replication_connection_configuration \G

# 查看各个channel的连接信息

mysql> select * from replication_connection_status \G

~~~

## 组复制信息查看

~~~

mysql> select * from replication_group_member_stats \G

mysql> select * from replication_group_member \G

~~~