MySQL InnoDB Cluster 面试题

graph TD
    App["Application"]
    Router["MySQL Router<br/>(Read-Write Split, 6446/6447)"]
    Node1["Node-1<br/>(PRIMARY)<br/>R/W"]
    Node2["Node-2<br/>(SECONDARY)<br/>R/O"]
    Node3["Node-3<br/>(SECONDARY)<br/>R/O"]
    Shell["MySQL Shell<br/>(Cluster Admin API)<br/>dba.createCluster()"]

    App -->|"Read/Write"| Router
    Router --> Node1
    Router --> Node2
    Router --> Node3
    Node1 --> Shell
    Node2 --> Shell
    Node3 --> Shell

graph TD
    L1["Certification Layer<br/>冲突检测（Write Set 比对）"]
    L2["Replication Layer<br/>事务广播与消息投递（Total Order）"]
    L3["Group Communication Engine (XCom)<br/>Paxos 引擎（Menzius 实现）"]
    L4["TCP / UDP (group_replication_)<br/>网络传输层"]
    L1 --- L2 --- L3 --- L4

// 从单主切换到多主
cluster.switchToMultiPrimaryMode();

// 从多主切换到单主（选举当前成员为 PRIMARY）
cluster.switchToSinglePrimaryMode("node-host:3306");

-- 查询成员状态与角色
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE,
       MEMBER_VERSION
FROM performance_schema.replication_group_members;

graph TD
    A["MySQL Router 启动"]
    B["连接 PRIMARY 查询<br/>mysql_innodb_cluster_metadata.instances"]
    C["缓存集群拓扑<br/>(host/port/role)"]
    D["定期轮询刷新拓扑<br/>(ttl 默认 0.5s)"]
    E["检测 PRIMARY 切换<br/>更新 6446 路由目标"]
    A --> B --> C --> D --> E

[routing:primary_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://mycluster/default?role=PRIMARY
routing_strategy = first-available
protocol = classic

[routing:secondary_ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://mycluster/default?role=SECONDARY
routing_strategy = round-robin
protocol = classic

[metadata_cache:mycluster]
router_id = 1
bootstrap_server_addresses = mysql://node1:3306,mysql://node2:3306,mysql://node3:3306
user = mysql_router1_user
metadata_cluster = mycluster
ttl = 0.5

graph TD
    A["PRIMARY 故障"]
    B["成员检测失败<br/>(心跳中断)"]
    C["多数派驱逐故障节点<br/>(Expel)"]
    D["Group Replication 选举新 PRIMARY<br/>(依据 member_weight > server_uuid)"]
    E["MySQL Router 元数据刷新<br/>(ttl 轮询)"]
    F["6446 端口路由切换到新 PRIMARY"]
    G["应用重连后恢复写入"]
    A -->|"超时 (member_expel_timeout)"| B --> C --> D --> E --> F --> G

graph TD
    A["事务 T 在 PRIMARY 执行完毕<br/>进入 Commit 阶段"]
    B["提取 Write Set<br/>(事务修改行的主键 hash 值集合)"]
    C["广播 Write Set + binlog event<br/>(通过 XCom)"]
    D["各节点收到消息<br/>进入 Certification 队列"]
    E["Certification 检查<br/>比对 Write Set 是否有交集"]
    F["无交集 → 认证通过<br/>加入 Certifier DB<br/>等待应用 binlog"]
    G["有交集 → 认证失败<br/>事务回滚<br/>(多主模式下发生在提交节点)"]
    A --> B --> C --> D --> E
    E -->|无冲突| F
    E -->|有冲突| G

Certification DB 内部数据结构：

stable_gtid_set: 已应用的事务 GTID 集合
certification_info: {
    <hash of primary key>: [<gtid_set of committed transactions>]
}

认证逻辑：
  对于每个 Write Set hash：
    IF hash 已存在于 certification_info 中
      IF 已提交事务的 GTID 与当前事务并发
        → 冲突！回滚本地事务
    ELSE
      → 无冲突，认证通过

-- 节点 A 和节点 B 同时执行：
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 两个事务的 Write Set 包含相同的主键 hash
-- Certification 阶段，后认证的事务检测到冲突 → 回滚

场景 1: UPDATE-UPDATE 冲突
  节点 A: UPDATE t SET c=1 WHERE id=100  (先行认证 → 成功)
  节点 B: UPDATE t SET c=2 WHERE id=100  (后认证 → 冲突 → 回滚)

场景 2: DELETE-UPDATE 冲突
  节点 A: DELETE FROM t WHERE id=100     (先行认证 → 成功)
  节点 B: UPDATE t SET c=1 WHERE id=100  (后认证 → 冲突 → 回滚)

场景 3: INSERT-INSERT 冲突（唯一键）
  节点 A: INSERT INTO t VALUES (100, 'a')
  节点 B: INSERT INTO t VALUES (100, 'b')
  → 唯一键约束冲突，后提交的事务回滚

-- 查询当前节点因冲突回滚的事务数
SELECT COUNT_TRANSACTIONS_ROWS_VALIDATING
FROM performance_schema.replication_group_member_stats
WHERE MEMBER_ID = @@server_uuid;

graph TD
    A["新节点请求加入 Group"]
    B["阶段 1: 选择 Donor<br/>- 基于 group_replication_group_seeds<br/>- ONLINE 且不为当前节点<br/>- 优先相同版本"]
    C{"阶段 2: 数据差距?"}
    D["场景 A: Clone Plugin<br/>- Donor 创建快照并传输<br/>- Joiner 应用快照并恢复"]
    E["场景 B: 增量同步<br/>- Donor 传输 binlog event<br/>- Joiner 通过 recovery channel 应用 relay log"]
    F["阶段 3: Catch Up<br/>- 在线事务通过正常 Channel 同步<br/>- RECOVERING → ONLINE"]
    A --> B --> C
    C -->|差距过大| D
    C -->|binlog 范围内| E
    D --> F
    E --> F

-- 查看当前 Recovery 状态与 Donor
SELECT CHANNEL_NAME, MEMBER_ID, DONOR_LIST
FROM performance_schema.replication_group_member_stats;

sequenceDiagram
    participant Donor as Donor Node
    participant Joiner as Joiner Node

    Donor->>Joiner: 1. COM_INIT（握手，协商版本）
    Donor->>Joiner: 2. Snapshot（创建 InnoDB 快照）
    Donor->>Joiner: 3. 传输数据文件（Page 级别）
    Donor->>Joiner: 4. 传输 Redo Log（快照期间的变更）
    Donor->>Joiner: 5. Clone 完成通知
    Note over Joiner: Joiner 自动重启

-- Donor 端
SET GLOBAL clone_autotune_concurrency = ON;   -- 自动调整并发
SET GLOBAL clone_donor_timeout_after_network_failure = 5;  -- 网络故障超时

-- Joiner 端（需在 my.cnf 或 SET PERSIST 中配置）
SET PERSIST clone_valid_donor_list = 'donor1:3306,donor2:3306';

// MySQL Shell 方式
cluster.addInstance('new-node:3306', {recoveryMethod: 'clone'});

// 手动 Clone
// Joiner 节点执行：
CLONE INSTANCE FROM 'repl_user'@'donor-host':3306 IDENTIFIED BY 'password';

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mycluster
spec:
  secretName: mycluster-secret
  tlsUseSelfSigned: true
  instances: 3
  version: 8.0.36
  router:
    instances: 2                    # MySQL Router 实例数
  datadirVolumeClaimTemplate:
    accessModes: ["ReadWriteOnce"]
    resources:
      requests:
        storage: 100Gi
  mycnf: |
    [mysqld]
    max_connections = 1000
    innodb_buffer_pool_size = 2G
    group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER    

Kubernetes Cluster
├── mysql-operator (Deployment)
│   ├── Controller-Manager (Reconcile Loop)
│   └── Sidecar Agent (监控、备份)
├── mycluster-0 (StatefulSet Pod)
│   └── mysql-server + mysql-agent
├── mycluster-1 (StatefulSet Pod)
│   └── mysql-server + mysql-agent
├── mycluster-2 (StatefulSet Pod)
│   └── mysql-server + mysql-agent
├── mycluster-router (Deployment)
│   └── mysql-router x2
└── mycluster Service
    ├── ClusterIP (RW)
    └── ClusterIP (RO)

helm repo add mysql-operator https://mysql.github.io/mysql-operator/
helm install mycluster mysql-operator/mysql-innodbcluster \
  --set credentials.root.user=root \
  --set credentials.root.password=secret123

-- 1. 成员状态与角色
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION
FROM performance_schema.replication_group_members;

-- 2. 复制通道状态（含延迟）
SELECT CHANNEL_NAME, RECEIVED_TRANSACTION_SET,
       COUNT_TRANSACTIONS_IN_QUEUE,                 -- 待应用事务数
       LAST_QUEUED_TRANSACTION,
       QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP  -- 事务提交时间戳
FROM performance_schema.replication_group_member_stats;

-- 3. Group 通信统计
SELECT COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE,
       COUNT_TRANSACTIONS_REMOTE_APPLIED,
       COUNT_TRANSACTIONS_LOCAL_PROPOSED,
       COUNT_TRANSACTIONS_LOCAL_ROLLBACK
FROM performance_schema.replication_group_member_stats;

-- 4. 流量控制
SELECT MEMBER_ID, COUNT_TRANSACTIONS_WAITING, MEMBER_QUOTA_AVAILABLE
FROM performance_schema.replication_group_member_stats;

# MySQL Exporter 配置
# my.cnf 中配置 Group Replication 指标采集
[client]
user = exporter
password = exporter_pass

# Prometheus 抓取配置
- job_name: 'mysql-innodb-cluster'
  static_configs:
    - targets: ['node1:9104', 'node2:9104', 'node3:9104']

1. 在 SECONDARY 节点上执行备份，避免影响 PRIMARY 写入性能
2. 逻辑备份参数建议：
   util.dumpInstance('/backup/path', {
       threads: 8,
       consistent: true,
       ddlOnly: false,
       dataOnly: false,
       users: true,
       ocimds: true,
       compatibility: ['strip_restricted_grants']
   })
3. 增量备份：每日全量（util.dumpInstance）+ 每小时 binlog 归档
4. PITR：全量恢复 + binlog 回放到指定时间点
5. 定期恢复演练：在独立的 Sandbox 环境验证备份可用性
6. 备份文件异地存储（S3/NFS/对象存储），保留策略 ≥ 30 天

// 全量逻辑备份
util.dumpInstance('/backup/20260101', {threads: 8, consistent: true});

// 仅备份 DDL（表结构）
util.dumpInstance('/backup/schema', {ddlOnly: true});

// 恢复
util.loadDump('/backup/20260101', {threads: 8});

// 使用 Clone Plugin 从远程节点物理克隆
// 新节点上执行：
// CLONE INSTANCE FROM 'repl'@'donor-host':3306 IDENTIFIED BY 'password';

// MySQL Shell 同时设置 Replication
cluster.addInstance('new-node:3306', {
    recoveryMethod: 'clone',
    waitRecovery: 3
});

-- 仅对当前 Session 设置强一致读写
SET SESSION group_replication_consistency = 'BEFORE_AND_AFTER';

-- 执行写入后，从 SECONDARY 读取保证看到最新数据
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 在另一个连接中查询 SECONDARY
SELECT balance FROM accounts WHERE id = 1;  -- 返回最新值

-- 操作完成后恢复默认
SET SESSION group_replication_consistency = 'EVENTUAL';

场景 1: 3 节点，Partition 1 含 2 节点，Partition 2 含 1 节点

    [Node-1, Node-2]  ←→  [Node-3]

    多数派 Group（2 节点）：
      - 满足 N/2+1（2 > 1.5）→ 继续服务
      - 如含 PRIMARY → 正常运行
      - 如不含 PRIMARY → 选举新 PRIMARY

    少数派 Group（1 节点）：
      - 不满足多数派 → 进入 ERROR 状态
      - 拒绝所有写入 → 转为只读
      - 等待网络恢复后自动重新加入

场景 2: 4 节点均分 Partition（2 vs 2）

    [Node-1, Node-2]  ←→  [Node-3, Node-4]

    两边各 2 节点，均不满足 N/2+1（2 < 2.5）→ **全部不可用**

-- 解决方案：在某个分区中手动设置单节点 Majority
-- 仅在确认另一分区完全故障时执行
SET GLOBAL group_replication_force_members = 'node1:3306,node2:3306';

-- 全局设置（对没有显式设置的所有 Session 生效）
SET GLOBAL group_replication_consistency = 'BEFORE_ON_PRIMARY_FAILOVER';

-- Session 级设置（优先级高于全局，仅影响当前连接）
SET SESSION group_replication_consistency = 'AFTER';

-- 查看当前设置
SELECT @@global.group_replication_consistency, @@session.group_replication_consistency;

graph TD
    subgraph Plugin["Group Replication Plugin"]
        MsgCache["group_replication_message_cache<br/>(size, 默认 1GB)<br/>binlog event 缓存<br/>供落后节点回放"]
        XComCache["XCom Cache<br/>(paxos_single_leader)<br/>Paxos 消息缓存<br/>存储共识协议消息"]
    end
    MsgCache <--> XComCache

当某个 SECONDARY 节点执行速度落后于 PRIMARY，且延迟超出 Message Cache 容量时：

  1. Group Replication 停止受理新事务（Flow Control 激活）
  2. 落后节点状态变为 UNREACHABLE → ERROR
  3. 节点被 Expel（驱逐）

缓解策略：
  - 增大 group_replication_message_cache_size
  - 优化 SECONDARY 节点应用线程：设置 slave_parallel_workers > 1
  - 调大 group_replication_member_expel_timeout
  - 监控 COUNT_TRANSACTIONS_IN_QUEUE 指标

-- 启用 Paxos Single Leader，减少 XCom 消息交互开销
SET GLOBAL group_replication_paxos_single_leader = ON;

-- 优点：消息广播流程从 N*(N-1) 降为 (N-1)*2
-- 限制：仅 Leader 可发起 Paxos 提议，适合单主模式

3 节点集群的自动配置：

  group_replication_auto_increment_increment = 7（默认值，固定）
  auto_increment_offset 按 server_id % 7 自动分配：

  Node-1 (server_id=1): auto_increment_offset = 1
    生成序列: 1, 8, 15, 22, 29, ...

  Node-2 (server_id=2): auto_increment_offset = 2
    生成序列: 2, 9, 16, 23, 30, ...

  Node-3 (server_id=3): auto_increment_offset = 3
    生成序列: 3, 10, 17, 24, 31, ...

步长固定为 7，最多支持 7 个节点
超出 7 节点 → 需手动调整 increment 值：
  SET GLOBAL group_replication_auto_increment_increment = 9;
  SET GLOBAL auto_increment_offset = <server_id % 9 + 1>;

主键空间浪费：
  3 节点集群，只有 offset 1/2/3 被使用，offset 4/5/6/7 浪费
  非连续性自增，不适合对主键连续性有业务要求的场景

UUID/雪花 ID 替代方案：
  CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    ...
  );
  -- 使用 UUID 作为主键，完全避免自增冲突

-- 节点 A 和节点 B 同时插入相同的唯一键值
INSERT INTO users (email, name) VALUES ('[email protected]', 'User A');
INSERT INTO users (email, name) VALUES ('[email protected]', 'User B');

-- 先认证的事务成功，后认证的事务回滚
-- 应用层需捕获 ER_LOCK_WAIT_TIMEOUT / ER_CERTIFICATION_FAILURE 并重试

选 InnoDB Cluster 的场景：
  - 使用 Oracle 官方 MySQL，需官方支持
  - 需单主模式 + 读写分离（MySQL Router）
  - 需 MySQL Shell 统一管理体验
  - 需配合 MySQL Operator 上 K8s

选 PXC/Galera 的场景：
  - 强同步复制需求（所有节点实时同步）
  - 多主写入是刚需且数据量巨大
  - 已有 Percona 技术栈和运维经验
  - 需要 IST 快速恢复

MHA 故障转移：
  检测故障 → SSH 确认 → 选择候选主 → 应用差异 Relay Log → 切换 VIP → 通知

Orchestrator 故障转移：
  持续拉取 Executed_Gtid_Set → 构建拓扑图 → 故障检测 →
  确认多数派可达 → 选择最优从库 → 执行 GTID 或 binlog 补齐 →
  切换拓扑 → Hook 通知

InnoDB Cluster 故障转移：
  心跳超时 → Paxos 多数派确认驱逐 → 协议选举新 PRIMARY →
  MySQL Router 感知 → 自动路由切换

graph TD
    App["Application"]
    VIP["VIP<br/>10.0.0.100<br/>(Keepalived 虚拟 IP)"]
    R1["Router1<br/>(Master)<br/>Keepalived"]
    R2["Router2<br/>(Backup)<br/>Keepalived"]
    R3["Router3<br/>(Backup)<br/>Keepalived"]

    subgraph Cluster["InnoDB Cluster"]
        N1["Node-1"]
        N2["Node-2"]
        N3["Node-3"]
    end

    App --> VIP
    VIP --> R1
    VIP --> R2
    VIP --> R3
    R1 --> Cluster
    R2 --> Cluster
    R3 --> Cluster

vrrp_script chk_mysql_router {
    script "/usr/bin/killall -0 mysqlrouter"
    interval 2
    weight -10
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass secret123
    }
    virtual_ipaddress {
        10.0.0.100/24
    }
    track_script {
        chk_mysql_router
    }
}

graph LR
    subgraph RegionA["地域 A (北京)"]
        subgraph ClusterA["InnoDB Cluster A"]
            A1["Node-A1 (PRIMARY)"]
            A2["Node-A2"]
            A3["Node-A3"]
        end
        RouterA["MySQL Router"]
    end

    subgraph RegionB["地域 B (上海)"]
        subgraph ClusterB["InnoDB Cluster B"]
            B1["Node-B1 (PRIMARY)"]
            B2["Node-B2"]
            B3["Node-B3"]
        end
        RouterB["MySQL Router"]
    end

    A1 -->|"异步复制 (GTID)"| B1

// ClusterSet：Cluster 级别复制
// 在北京集群上建立 ClusterSet Primary
var clusterA = dba.getCluster('beijingCluster');
clusterA.createClusterSet('globalCluster');

// 将上海集群作为 Replica Cluster 加入
var clusterB = dba.getCluster('shanghaiCluster');
clusterA.addReplicaCluster('shanghaiCluster', clusterB, {
    recoveryMethod: 'clone',
    timeout: 3600
});

-- 在地域 B 的 PRIMARY 上执行
CHANGE MASTER TO
    MASTER_HOST = 'beijing-primary',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1
FOR CHANNEL 'inter_region';

START SLAVE FOR CHANNEL 'inter_region';

-- 查看复制延迟
SHOW SLAVE STATUS FOR CHANNEL 'inter_region'\G

-- Group Replication 处理 DDL 的流程
-- 1. PRIMARY 上执行 DDL，Group Replication 将 DDL 以 View Change Log Event 广播
-- 2. 所有节点收到 DDL Event，按 Total Order 顺序执行
-- 3. 如果使用 INSTANT / INPLACE，各节点异步独立执行
-- 4. 如果使用 COPY，各节点均需全表重建

-- 查看 Group Replication 中的 DDL 执行状态
SELECT * FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = 'group_replication_applier';

1. 在 PRIMARY 节点上执行 DDL，不要在各节点分别执行
2. 优先使用 ALGORITHM=INSTANT 或 ALGORITHM=INPLACE
3. 大表 DDL 要评估对复制延迟的影响，控制在业务低峰期执
4. 使用 pt-online-schema-change（Percona Toolkit）规避长期锁表：
   pt-online-schema-change --alter "ADD COLUMN ..." \
     --host=primary-host --port=3306 \
     --user=admin --password=pass \
     D=db,t=big_table \
     --execute \
     --max-load Threads_running=50 \
     --critical-load Threads_running=100 \
     --chunk-time=0.5
5. DDL 执行后检查 SECONDARY 节点的复制延迟
6. 单主模式下无需设置 group_replication_enforce_update_everywhere_checks
7. 多主模式下 DDL 前需先暂停非执行节点的写入

graph TD
    A["节点 N 心跳超时<br/>(5s 无响应)"]
    B["其他节点感知到 N 不可达<br/>开始 Expel 流程"]
    C["多数派投票 → 驱逐 N<br/>(等待 expel_timeout)"]
    D["N 被驱逐 → MEMBER_STATE = ERROR<br/>- READ_ONLY: super_read_only=ON<br/>- OFFLINE_MODE: offline_mode=ON<br/>- ABORT_SERVER: 关闭 MySQL"]
    E["多数派 Group 继续正常服务"]
    A --> B --> C
    C --> D
    C --> E

// MySQL Shell: 自动重连（8.0.21+）
// 被驱逐节点设置 autorejoin_tries 后会自动尝试重新加入
// my.cnf:
// group_replication_autorejoin_tries = 3

// 手动重新加入（MySQL Shell）
var cluster = dba.getCluster();
cluster.rejoinInstance('node3:3306');

// 如果 rejoin 失败（数据差距过大），先 Clone 再加入
cluster.removeInstance('node3:3306', {force: true});
cluster.addInstance('node3:3306', {
    recoveryMethod: 'clone',
    waitRecovery: 3
});

-- 1. InnoDB 持久化统计信息
SET GLOBAL innodb_stats_persistent = ON;
SET GLOBAL innodb_stats_auto_recalc = ON;

-- 2. 手动更新统计信息（在 PRIMARY 上执行，自动复制到 SECONDARY）
ANALYZE TABLE orders;
ANALYZE TABLE customers;

-- 3. 确保统计信息在节点间一致
-- PRIMARY 执行 ANALYZE TABLE 后，通过 binlog 复制到所有 SECONDARY
-- SECONDARY 会自动应用 ANALYZE TABLE 事件

-- InnoDB Cluster 环境的查询优化配置
SET GLOBAL optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on';

-- 避免不稳定查询计划
SET GLOBAL optimizer_switch = 'prefer_ordering_index=off';

-- 直方图：在 PRIMARY 创建后，各节点手动同步
ANALYZE TABLE orders UPDATE HISTOGRAM ON order_date, status
WITH 100 BUCKETS;

graph TD
    A["Client → Server<br/>(端口 3306)<br/>MySQL Client SSL"]
    B["Group Communication (XCom)<br/>(端口 33061)<br/>group_replication_ssl_mode"]
    C["Recovery Channel<br/>(Group Replication 内部)<br/>group_replication_recovery_use_ssl"]

// 自动生成自签名证书并配置所有节点
dba.configureLocalInstance('root@node1:3306', {
    interactive: false
});

// MySQL Shell 8.0.21+ 一键配置
dba.configureInstance('root@node1:3306', {
    clusterAdmin: 'icadmin',
    clusterAdminPassword: 'secure-pass',
    clearReadOnly: true
});

[mysqld]
# ---------- Group Replication SSL (XCom) ----------
# SSL Mode（8.0.14+）
# REQUIRED: 强制 SSL
# DISABLED: 禁用 SSL
# VERIFY_CA: 验证 CA
# VERIFY_IDENTITY: 验证身份
group_replication_ssl_mode = REQUIRED

# SSL 证书路径
ssl_ca = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key = /etc/mysql/certs/server-key.pem

# 可选：Group Replication 使用独立证书
group_replication_ssl_ca = /etc/mysql/certs/gr_ca.pem
group_replication_ssl_cert = /etc/mysql/certs/gr_server-cert.pem
group_replication_ssl_key = /etc/mysql/certs/gr_server-key.pem

# ---------- Recovery Channel SSL ----------
group_replication_recovery_use_ssl = ON
group_replication_recovery_ssl_ca = /etc/mysql/certs/ca.pem
group_replication_recovery_ssl_cert = /etc/mysql/certs/server-cert.pem
group_replication_recovery_ssl_key = /etc/mysql/certs/server-key.pem

# ---------- MySQL Router → Server SSL ----------
# Router 配置中设置：
# [routing:primary_rw]
# client_ssl_mode = PREFERRED
# client_ssl_key = /etc/mysqlrouter/router-key.pem
# client_ssl_cert = /etc/mysqlrouter/router-cert.pem

-- 检查 Group Replication 是否使用 SSL
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT
FROM performance_schema.replication_group_members;

-- 检查所有连接是否使用 SSL
SELECT thd_id, conn_type, ssl_version, ssl_cipher
FROM performance_schema.threads
WHERE conn_type = 'SSL/TLS';

-- Group Replication 通道 SSL 状态
SHOW STATUS LIKE 'group_replication_ssl%';

-- 集群元数据 Schema
USE mysql_innodb_cluster_metadata;

-- 集群定义
SELECT * FROM clusters;
-- cluster_id, cluster_name, description, options

-- 实例信息
SELECT * FROM instances;
-- instance_id, cluster_id, hostname, port, role, weight

-- Router 注册信息
SELECT * FROM routers;

-- 异步复制 ClusterSet 信息
SELECT * FROM clustersets;

-- 查询当前集群拓扑
SELECT c.cluster_name,
       i.hostname,
       i.port,
       i.mysql_server_uuid,
       JSON_EXTRACT(i.attributes, '$.group_replication_group_name') AS group_name
FROM mysql_innodb_cluster_metadata.clusters c
JOIN mysql_innodb_cluster_metadata.instances i
  ON c.cluster_id = i.cluster_id;

-- 查询 Router 路由规则
SELECT r.router_name, r.hostname,
       JSON_EXTRACT(r.attributes, '$.version') AS router_version,
       JSON_EXTRACT(r.attributes, '$.routingRules') AS rules
FROM mysql_innodb_cluster_metadata.routers r;

REPEATABLE-READ 问题场景：

  事务 A（PRIMARY）:
    BEGIN;
    SELECT * FROM t WHERE id = 1;  -- 读取快照 S1
    -- （另一个事务 B 修改 id=1 并提交）
    UPDATE t SET c = 'new' WHERE id = 1;  -- 基于 S1 修改 → 认证阶段可能冲突

  事务 B 的 Write Set 先于事务 A 进入 Certification
  → 认证检测到冲突 → 事务 A 回滚

  Summary:
  - InnoDB REPEATABLE-READ 的快照在事务开始时确定
  - Certification 的快照在事务广播时确定
  - 两者不一致会导致事务在认证阶段因"不可见"冲突而回滚

-- InnoDB Cluster 推荐配置
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 如果必须使用 REPEATABLE-READ
SET GLOBAL group_replication_consistency = 'BEFORE_AND_AFTER';

-- Binlog 格式（强制要求）
SET GLOBAL binlog_format = ROW;  -- Group Replication 仅支持 ROW 格式
SET GLOBAL binlog_row_image = FULL;  -- 确保 Write Set 完整

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: production-mysql
  namespace: db
spec:
  secretName: mysql-root-secret
  tlsUseSelfSigned: true
  edition: community          # community / enterprise
  version: 8.0.36
  instances: 3
  podSpec:
    resources:
      requests:
        cpu: 4
        memory: 16Gi
      limits:
        cpu: 8
        memory: 32Gi
    affinity:
      podAntiAffinity:
        requiredDuringSchedulingIgnoredDuringExecution:
        - labelSelector:
            matchLabels:
              mysql.oracle.com/cluster: production-mysql
          topologyKey: kubernetes.io/hostname
  datadirVolumeClaimTemplate:
    accessModes: ["ReadWriteOnce"]
    resources:
      requests:
        storage: 500Gi
    storageClassName: ssd
  router:
    instances: 2
    podSpec:
      resources:
        requests:
          cpu: 500m
          memory: 512Mi
  backupProfiles:
    - name: daily-backup
      schedule: "0 2 * * *"
      dumpInstance:
        dumpOptions:
          excludeSchemas: ["test"]
    - name: hourly-binlog
      schedule: "0 * * * *"
  mycnf: |
    [mysqld]
    max_connections = 2000
    innodb_buffer_pool_size = 12G
    innodb_log_file_size = 2G
    group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER
    group_replication_member_expel_timeout = 10    

# 查看集群状态
kubectl get innodbcluster -n db

# 查看集群详细状态
kubectl describe innodbcluster production-mysql -n db

# 查看 MySQL Router 状态
kubectl get pod -n db -l mysql.oracle.com/component=router

# 连接集群
kubectl exec -it -n db production-mysql-0 -- mysql -u root -p

# 备份触发
kubectl create job --from=cronjob/production-mysql-daily-backup manual-backup -n db

# 查看 MySQL Operator 日志
kubectl logs -n mysql-operator deploy/mysql-operator-controller-manager

-- ========== 1. 集群成员状态概览 ==========
SELECT * FROM performance_schema.replication_group_members;

-- ========== 2. 成员统计与延迟 ==========
SELECT MEMBER_ID,
       COUNT_TRANSACTIONS_IN_QUEUE AS queue_size,           -- 待应用事务数
       COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE,
       COUNT_TRANSACTIONS_CHECKED AS cert_checked,
       COUNT_TRANSACTIONS_REMOTE_APPLIED,
       COUNT_TRANSACTIONS_LOCAL_PROPOSED,
       COUNT_TRANSACTIONS_LOCAL_ROLLBACK AS local_rollback,
       COUNT_TRANSACTIONS_ROWS_VALIDATING
FROM performance_schema.replication_group_member_stats;

-- ========== 3. 复制通道状态 ==========
SELECT CHANNEL_NAME,
       SERVICE_STATE,
       RECEIVED_TRANSACTION_SET,
       LAST_APPLIED_TRANSACTION,
       LAST_QUEUED_TRANSACTION
FROM performance_schema.replication_connection_status;

SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE,
       LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

-- ========== 4. GTID 一致性检查 ==========
SELECT @@global.gtid_executed;
SELECT @@global.gtid_purged;
-- 在所有节点执行，比对结果确认一致性

-- ========== 5. 成员网络连接 ==========
SELECT * FROM performance_schema.replication_group_communication_information;
-- 包含 WRITE_CONCURRENCY、READ_CONCURRENCY、CACHE_SIZE 等信息

-- ========== 6. 流量控制状态 ==========
SELECT MEMBER_ID,
       COUNT_TRANSACTIONS_WAITING,         -- 等待事务数
       COUNT_TRANSACTIONS_REQUIRED_QUOTA,  -- 所需配额
       MEMBER_QUOTA_USED,                  -- 已用配额
       MEMBER_QUOTA_AVAILABLE              -- 可用配额
FROM performance_schema.replication_group_member_stats;

-- ========== 7. 错误日志诊断 ==========
SHOW GLOBAL STATUS LIKE 'group_replication%';
-- 关注：
-- group_replication_primary_member（当前 PRIMARY UUID）
-- group_replication_view_change_uuid（最近一次 View Change）

-- ========== 8. 事务等待诊断 ==========
SELECT * FROM performance_schema.events_transactions_current
WHERE STATE = 'committing'
  AND THREAD_ID IN (
    SELECT THREAD_ID FROM performance_schema.threads
    WHERE PROCESSLIST_COMMAND = 'Query'
  );

// 检查集群状态
var cluster = dba.getCluster();
cluster.status({extended: 1});

// 详细拓扑信息
cluster.describe();

// 检查实例状态
cluster.checkInstanceState('node2:3306');

// 重新扫描集群拓扑
cluster.rescan();

// 一键修复模式一致性
cluster.forceQuorumUsingPartitionOf('node1:3306');