MySQL Operator 面试题

mysql-operator (Deployment)
    │
    ├── Controller Manager
    │   ├── InnoDBCluster Controller
    │   ├── MySQLBackup Controller
    │   ├── MySQLRestore Controller
    │   └── MySQLUser Controller
    │
    ├── Sidecar Containers
    │   ├── mysql-agent (InnoDB Cluster 状态管理)
    │   ├── mysql-router (读写分离)
    │   └── mysqlsh-wrapper (MySQL Shell)
    │
    └── CRDs
        ├── InnoDBCluster (集群拓扑)
        ├── MySQLBackup (备份策略)
        ├── MySQLRestore (恢复任务)
        └── MySQLUser (用户权限)

InnoDBCluster CR 创建
    -> Controller Watch 事件
    -> 调谐循环（Reconcile Loop）
    -> 创建 StatefulSet（MySQL 实例）
    -> 创建 Service（ClusterIP / Headless）
    -> 创建 PVC（持久化存储）
    -> 配置 Group Replication
    -> 创建 Router Deployment
    -> 状态更新到 CR Status

1. Operator 创建 StatefulSet
2. mysql-agent 启动，等待 MySQL 就绪
3. 首个 Pod 执行 dba.createCluster() 引导集群
4. 后续 Pod 执行 cluster.addInstance() 加入集群
5. mysql-agent 持续监控集群状态
6. 故障时自动 rejoinInstance() 或 forceQuorum()

status:
  cluster:
    status: ONLINE              # ONLINE / OFFLINE / PARTIAL
    topology:
      mycluster-0:
        memberState: ONLINE     # ONLINE / RECOVERING / OFFLINE / ERROR
        memberRole: PRIMARY     # PRIMARY / SECONDARY
    groupReplication:
      groupName: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
      singlePrimaryMode: "ON"
      protocolVersion: "XCOM"

1. InnoDB Cluster 成员状态变更
2. mysql-agent 获取最新拓扑
3. 写入集群元数据表（mysql_innodb_cluster_metadata）
4. mysqlsh-wrapper 调用 Shell API 获取拓扑
5. 更新 Router 配置文件（/tmp/mysqlrouter/mysqlrouter.conf）
6. Router 热加载或重启
7. 应用通过 Router Service 连接

spec:
  router:
    instances: 2
    version: "8.2.0"
    podSpec:
      resources:
        requests:
          memory: "256Mi"
          cpu: "100m"
        limits:
          memory: "512Mi"
          cpu: "500m"

mysql-operator/
├── Chart.yaml
├── values.yaml
├── templates/
│   ├── deployment.yaml           # Operator Deployment
│   ├── serviceaccount.yaml       # ServiceAccount
│   ├── clusterrole.yaml          # ClusterRole 与 RBAC
│   ├── clusterrolebinding.yaml
│   └── crds/                     # CRD 定义
│       ├── innodbclusters.yaml
│       ├── mysqlbackups.yaml
│       └── mysqlrestores.yaml

helm repo add mysql-operator https://mysql.github.io/mysql-operator/
helm repo update
helm install mysql-operator mysql-operator/mysql-operator \
  --namespace mysql-operator \
  --create-namespace \
  --set image.tag=8.2.0-2.0.12 \
  --set envs.jemalloc=true \
  --set resources.limits.memory=512Mi

1. kubectl apply InnoDBCluster CR
2. Operator 接收事件 → Reconcile 开始
3. 创建 Headless Service（用于 Pod 间 DNS 发现）
4. 创建 ClusterIP Service（应用访问入口）
5. 根据 spec.instances 创建 StatefulSet
6. Init Container 初始化数据目录和配置文件
7. mysql-agent Sidecar 启动，监控 MySQL 进程
8. 首个 Pod（mycluster-0）就绪：
   - mysql-agent 调用 dba.createCluster()
   - 设定 Group Replication 参数
   - 写入集群元数据
9. 后续 Pod（mycluster-1, mycluster-2...）就绪：
   - mysql-agent 调用 cluster.addInstance()
   - 自动同步数据（Clone / Incremental Recovery）
10. Router 实例启动，注册读写端点
11. 更新 InnoDBCluster CR Status
12. 创建 MySQLBackup 定时备份（如配置）

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mycluster
spec:
  secretName: mycluster-secret
  tls:
    useSelfSigned: true
  instances: 3
  router:
    instances: 2
  datadirVolumeClaimTemplate:
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 50Gi
  edition: community
  version: "8.2.0"

spec:
  mycnf: |
    [mysqld]
    # Group Replication 基本配置
    loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
    loose-group_replication_start_on_boot=OFF
    loose-group_replication_local_address="mycluster-0.mycluster-instances.default.svc.cluster.local:33061"
    loose-group_replication_group_seeds="mycluster-0.mycluster-instances.default.svc.cluster.local:33061,mycluster-1.mycluster-instances.default.svc.cluster.local:33061,mycluster-2.mycluster-instances.default.svc.cluster.local:33061"
    
    # 一致性配置
    loose-group_replication_consistency=AFTER
    loose-group_replication_flow_control_mode=QUOTA
    loose-group_replication_member_weight=50
    
    # 事务认证
    loose-group_replication_transaction_size_limit=150000000
    loose-group_replication_compression_threshold=1000000    

PRIMARY 故障
    -> Group Replication 检测 Primary 不可达
    -> 自动选举新 Primary（member_weight + server_uuid）
    -> 剩余成员达成多数派
    -> 新 Primary 提升为 Read-Write
    -> mysql-agent 更新 Router 端点
    -> 应用流量切换到新 Primary
    -> 故障实例恢复后 rejoinInstance()

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
spec:
  backupSchedules:
    - name: daily-full-backup
      schedule: "0 2 * * *"          # 每天凌晨 2:00
      backupProfile:
        dumpInstance:
          dumpOptions:
            consistent: true         # 一致性备份
            ddlOnly: false
            maxRate: "10M"
          storage:
            ociObjectStorage:
              bucketName: "mysql-backups"
              credentials: "oci-backup-secret"
              prefix: "daily/"
      deleteBackupData: true
      enabled: true
      
    - name: weekly-incremental-backup
      schedule: "0 3 * * 0"          # 每周日凌晨 3:00
      backupProfile:
        dumpInstance:
          dumpOptions:
            consistent: true
            excludeSchemas: ["test", "tmp"]
            maxRate: "5M"
          storage:
            ociObjectStorage:
              bucketName: "mysql-backups"
              credentials: "oci-backup-secret"
              prefix: "weekly/"
      deleteBackupData: true
      enabled: true

apiVersion: mysql.oracle.com/v2
kind: MySQLBackup
metadata:
  name: pre-upgrade-backup
spec:
  clusterName: mycluster
  backupProfile:
    dumpInstance:
      dumpOptions:
        consistent: true
        ddlOnly: false
        maxRate: "20M"
      storage:
        ociObjectStorage:
          bucketName: "mysql-backups"
          credentials: "oci-backup-secret"
          prefix: "on-demand/"

MySQL Operator
    │
    ├── 创建 MySQLBackup CR
    ├── mysqlsh-wrapper 调用 dumpInstance()
    ├── 生成 dump 文件（.sql / .tsv）
    ├── 上传到 OCI Object Storage
    │       │
    │       ├── Bucket: mysql-backups
    │       ├── Prefix: daily/ / weekly/ / on-demand/
    │       └── Object: <cluster-name>/<timestamp>/dump.sql.gz
    │
    └── 更新 MySQLBackup CR Status

apiVersion: v1
kind: Secret
metadata:
  name: oci-backup-secret
  namespace: default
type: Opaque
stringData:
  tenancy: "ocid1.tenancy.oc1..aaaaaaa..."
  user: "ocid1.user.oc1..aaaaaaa..."
  fingerprint: "aa:bb:cc:dd:ee:ff:11:22:33:44:55:66:77:88:99:00"
  privateKey: |
    -----BEGIN RSA PRIVATE KEY-----
    MIIEpAIBAAKCAQEA...
    -----END RSA PRIVATE KEY-----    
  region: "ap-singapore-1"

apiVersion: mysql.oracle.com/v2
kind: MySQLRestore
metadata:
  name: restore-from-backup
spec:
  clusterName: mycluster-restored
  backupName: daily-full-backup-mycluster-20240526
  restoreProfile:
    dumpInstance:
      dumpOptions:
        threads: 4
        maxRate: "50M"
      storage:
        ociObjectStorage:
          bucketName: "mysql-backups"
          credentials: "oci-backup-secret"
          prefix: "daily/"

StatefulSet: mycluster
    │
    ├── Pod mycluster-0
    │   └── PVC datadir-mycluster-0  (50Gi)
    │       └── /var/lib/mysql/
    │           ├── ibdata1          (系统表空间)
    │           ├── ib_logfile0/1    (Redo Log)
    │           ├── undo_001/002     (Undo 表空间)
    │           └── <schema>/        (业务数据)
    │
    ├── Pod mycluster-1
    │   └── PVC datadir-mycluster-1  (50Gi)
    │
    └── Pod mycluster-2
        └── PVC datadir-mycluster-2  (50Gi)

spec:
  datadirVolumeClaimTemplate:
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 100Gi
    storageClassName: "oci-bv"        # 块存储（推荐）
    # storageClassName: "efs-sc"     # 文件存储（不推荐 MySQL）

# 在线扩容 PVC（需 StorageClass 支持 allowVolumeExpansion: true）
kubectl patch pvc datadir-mycluster-0 -p '{"spec":{"resources":{"requests":{"storage":"200Gi"}}}}'

# 步骤
kubectl patch innodbcluster mycluster --type=merge \
  -p '{"spec":{"instances":5}}'

# 内部流程
1. StatefulSet replicas: 3 → 5
2. 新 Pod（mycluster-3, mycluster-4）启动
3. Init Container 准备数据目录
4. MySQL 进程启动
5. mysql-agent 调用 cluster.addInstance()
6. Group Replication 自动 Clone 数据
7. Clone 完成后实例 ONLINE
8. Router 感知新成员，更新只读端点

spec:
  router:
    instances: 4               # 增加 Router 实例
    podSpec:
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 100
              podAffinityTerm:
                topologyKey: kubernetes.io/hostname
      resources:
        requests:
          memory: "256Mi"
          cpu: "100m"

spec:
  version: "8.3.0"             # 目标版本
  podSpec:
    updateStrategy:
      type: RollingUpdate
      rollingUpdate:
        partition: 0
        maxUnavailable: 1

1. 修改 InnoDBCluster.spec.version
2. Operator 检测版本变化
3. 从 PRIMARY 节点开始升级
   3.1 调用 cluster.setPrimaryInstance() 切换 Primary
   3.2 原 Primary 变为 Secondary
   3.3 停止 MySQL 进程
   3.4 替换镜像版本
   3.5 启动新版本 MySQL
   3.6 执行 mysql_upgrade（如需要）
   3.7 mysql-agent 调用 cluster.rejoinInstance()
   3.8 等待实例 ONLINE
4. 逐一升级剩余 Secondary 节点
5. 升级 Router 实例
6. 更新 Status 版本号

spec:
  tls:
    useSelfSigned: true

spec:
  tls:
    secretName: mycluster-tls-secret
    # Operator 期望 Secret 包含以下 Key：
    # - ca.crt   (CA 证书)
    # - tls.crt  (服务端证书)
    # - tls.key  (服务端私钥)
    # - tls.crt 复制到 tls-client.crt（客户端可选）

apiVersion: v1
kind: Secret
metadata:
  name: mycluster-tls-secret
type: kubernetes.io/tls
data:
  ca.crt:   <base64-encoded>
  tls.crt:  <base64-encoded>      # CN 需匹配 Service DNS
  tls.key:  <base64-encoded>

客户端  --TLS-->  Router (6446/6447)
                     │
                     └--Group Replication TLS-->  MySQL 实例间通信
                                                       │
                                                       └--Admin TLS-->  mysql-agent / mysql-shell

# Operator 自动设置
group_replication_ssl_mode=REQUIRED
group_replication_recovery_use_ssl=ON
group_replication_recovery_ssl_ca=.../ca.crt
group_replication_recovery_ssl_cert=.../tls.crt
group_replication_recovery_ssl_key=.../tls.key

apiVersion: mysql.oracle.com/v2
kind: MySQLUser
metadata:
  name: app-user
spec:
  clusterName: mycluster
  user:
    name: app_user
    host: "%"                      # 允许的来源 IP（% 为所有）
    authentication:
      type: MySQLNativePassword    # caching_sha2_password / mysql_native_password
      secret:
        name: app-user-secret
        key: password
    privileges:
      - scope: GLOBAL
        privileges:
          - SELECT
          - INSERT
          - UPDATE
          - DELETE
      - scope: SCHEMA
        schema: app_db
        privileges:
          - SELECT
          - INSERT
          - UPDATE
          - DELETE
          - CREATE
          - ALTER
          - INDEX

apiVersion: v1
kind: Secret
metadata:
  name: app-user-secret
type: Opaque
stringData:
  password: "SecureP@ssw0rd!"

创建 MySQLUser CR
    -> Operator 检测新增
    -> 连接到 PRIMARY 实例
    -> CREATE USER 'app_user'@'%' IDENTIFIED BY '...'
    -> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'app_user'@'%'
    -> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON app_db.* TO 'app_user'@'%'
    -> FLUSH PRIVILEGES
    -> 状态写入 MySQLUser CR Status

更新 MySQLUser CR（变更权限）
    -> Operator 检测差异
    -> REVOKE 旧权限
    -> GRANT 新权限
    -> FLUSH PRIVILEGES

删除 MySQLUser CR
    -> Operator 检测删除事件
    -> DROP USER 'app_user'@'%'

MySQL Pod
    │
    ├── mysql (容器)
    │   └── performance_schema (指标源)
    │
    ├── mysql-exporter (Sidecar)
    │   ├── 连接 MySQL 获取指标
    │   ├── 暴露 :9104/metrics
    │   └── 指标类型：
    │       ├── mysql_global_status_* (全局状态)
    │       ├── mysql_global_variables_* (配置参数)
    │       ├── mysql_innodb_* (InnoDB 引擎)
    │       ├── mysql_perf_schema_* (性能模式)
    │       └── mysql_slave_status_* (复制状态)
    │
    └── Prometheus
        ├── ServiceMonitor 自动发现
        └── metrics 采集

spec:
  exporter:
    image: mysql/mysql-exporter:8.2.0-2.0.12
    podSpec:
      resources:
        requests:
          memory: "64Mi"
          cpu: "50m"
    options:
      - "--collect.global_status"
      - "--collect.global_variables"
      - "--collect.slave_status"
      - "--collect.info_schema.innodb_metrics"
      - "--collect.info_schema.processlist"
      - "--collect.info_schema.tables"
      - "--collect.perf_schema.eventswaits"
      - "--collect.perf_schema.file_events"
      - "--collect.perf_schema.tablelocks"
      - "--collect.perf_schema.indexiowaits"

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: mysql-monitor
  namespace: default
  labels:
    release: prometheus          # 匹配 Prometheus Operator 的 serviceMonitorSelector
    app: mysql-operator
spec:
  selector:
    matchLabels:
      mysql.oracle.com/cluster: mycluster
  namespaceSelector:
    matchNames:
      - default
  endpoints:
    - port: metrics
      interval: 30s
      scrapeTimeout: 10s
      path: /metrics
      scheme: http
      relabelings:
        - sourceLabels: [__meta_kubernetes_pod_name]
          targetLabel: pod
        - sourceLabels: [__meta_kubernetes_pod_label_mysql_oracle_com_cluster]
          targetLabel: cluster
  jobLabel: mysql-exporter

apiVersion: monitoring.coreos.com/v1
kind: Prometheus
metadata:
  name: k8s
  namespace: monitoring
spec:
  serviceMonitorSelector:
    matchLabels:
      app: mysql-operator

apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: mysql-rules
  namespace: default
  labels:
    app: mysql-operator
    role: alert-rules
spec:
  groups:
    - name: mysql.rules
      rules:
        - alert: MySQLInstanceDown
          expr: mysql_up{job="mysql-exporter"} == 0
          for: 1m
          labels:
            severity: critical
          annotations:
            summary: "MySQL 实例 XQOPEN $labels.pod XQCLOSE 不可达"
            description: "集群 XQOPEN $labels.cluster XQCLOSE 中 Pod XQOPEN $labels.pod XQCLOSE 的 Exporter 已 1 分钟无响应"

        - alert: MySQLHighThreadsConnected
          expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "MySQL 连接数超过 80%"
            description: "当前连接数 XQOPEN $value | humanize XQCLOSE，最大连接数上限 XQOPEN $labels.max_connections XQCLOSE"

# MySQL Operator
Application -> Router:6446 (R/W) -> PRIMARY
Application -> Router:6447 (R/O) -> SECONDARY(轮询)

# Vitess Operator
Application -> VTGate -> vtgate 解析 VSchema
    │
    ├── R/W -> VTTablet (PRIMARY Shard)
    └── R/O -> VTTablet (REPLICA Shard)

spec:
  podSpec:
    nodeSelector:
      node-role.kubernetes.io/mysql: "true"
      disktype: ssd
    
    affinity:
      # 跨节点反亲和（避免同一节点运行多个 MySQL 实例）
      podAntiAffinity:
        requiredDuringSchedulingIgnoredDuringExecution:
          - labelSelector:
              matchLabels:
                mysql.oracle.com/cluster: mycluster
            topologyKey: kubernetes.io/hostname
      
      # 节点亲和（优先调度到 SSD 节点）
      nodeAffinity:
        requiredDuringSchedulingIgnoredDuringExecution:
          nodeSelectorTerms:
            - matchExpressions:
                - key: node-role.kubernetes.io/mysql
                  operator: In
                  values:
                    - "true"
        preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            preference:
              matchExpressions:
                - key: disktype
                  operator: In
                  values:
                    - "nvme-ssd"
    
    # 容忍 GPU/专有节点污点
    tolerations:
      - key: "mysql-dedicated"
        operator: "Equal"
        value: "true"
        effect: "NoSchedule"

spec:
  router:
    instances: 3
    podSpec:
      # Router 调度到不同节点，避免与 MySQL 实例竞争
      nodeSelector:
        node-role.kubernetes.io/router: "true"
      
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchLabels:
                  mysql.oracle.com/cluster: mycluster
                  mysql.oracle.com/role: router
              topologyKey: kubernetes.io/hostname

apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: mycluster-pdb
spec:
  minAvailable: 2              # 至少保持 2 个实例可用
  # maxUnavailable: 1          # 或最多 1 个不可用
  selector:
    matchLabels:
      mysql.oracle.com/cluster: mycluster
      mysql.oracle.com/role: instance

kubectl drain node-1（节点维护）
    │
    ├── Eviction API 检查 PDB
    ├── PDB minAvailable: 2 → 当前可用 3 → 允许驱逐 mycluster-0
    ├── mycluster-0 被驱逐，Pod 重新调度到 node-4
    ├── mycluster-0 通过 Clone 重新加入集群
    ├── 集群状态恢复 ONLINE
    │
    ├── 继续驱逐 node-2 上的 mycluster-1
    ├── PDB minAvailable: 2 → 当前可用 2 → 拒绝驱逐（违反 PDB）
    │
    └── 待 mycluster-0 恢复 ONLINE → 可继续驱逐

InnoDB Cluster 元数据表（mysql_innodb_cluster_metadata）
    │
    ├── clusters（集群定义）
    ├── instances（实例拓扑 / 地址 / 角色）
    ├── routers（Router 注册信息）
    └── v2_this_instance（当前实例属性）

mysqlsh-wrapper（Sidecar，在 Router Pod 中运行）
    │
    ├── 定期查询元数据表（默认每 5 秒）
    ├── 检测拓扑变更：
    │   ├── PRIMARY 变更（选举 / 手动切换）
    │   ├── 成员加入 / 离开
    │   └── 实例状态变更（ONLINE / RECOVERING / ERROR）
    │
    ├── 生成新 Router 配置
    └── 触发 Router 热加载（SIGHUP 或 REST API）

[DEFAULT]
connect_timeout=30
read_timeout=30

[logger]
level=INFO

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

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

[http_server]
port=8443

PRIMARY 故障（T0）
    -> T0+5s: Group Replication 检测并开始选举
    -> T0+15s: 新 PRIMARY 选举完成
    -> T0+20s: mysqlsh-wrapper 查询元数据获取新拓扑
    -> T0+22s: Router 配置文件更新并热加载
    -> T0+23s: 客户端下一次连接请求路由到新 PRIMARY

```mermaid
graph TD
    D1["Donor<br/>（捐赠节点，通常为 SECONDARY）"] --> D2["Clone Donor 线程<br/>逐页读取 InnoDB 数据文件"]
    D2 -->|"网络传输<br/>MySQL Protocol"| R1["Recipient<br/>（接收节点，新加入的实例）"]
    R1 --> R2["Clone Recipient 线程<br/>逐页写入本地 InnoDB 数据文件"]
    R1 --> R3["Redo Log 实时同步<br/>（增量追赶）"]
    R1 --> R4["完成后启动 Group Replication"]

**Clone 与逻辑导入对比：**

| 维度 | Clone Plugin | 逻辑导入（mysqldump） |
| --- | --- | --- |
| 同步方式 | 物理页级别复制 | SQL 文本导入 |
| 速度 | 接近磁盘带宽 | 受 SQL 解析执行限制 |
| 增量追赶 | 支持（Redo Log 同步） | 不支持 |
| DDL 完整性 | 精确一致 | 依赖导出时刻快照 |
| GTID 连续性 | 完全保持 | 导入后重置 |
| 适用数据量 | 任意规模 | TB 级数据导入耗时长 |

**Clone Plugin 配置：**

```ini
# Donor 节面配置（Operator 自动设置）
[mysqld]
plugin-load-add=clone=mysql_clone.so
loose-clone_valid_donor_threshold=30     # 允许的最大延迟（秒）

# 手动触发 Clone（通常由 Operator 自动执行）
CLONE INSTANCE FROM 'donor_user'@'donor_host':3306
  IDENTIFIED BY 'password'
  DATA DIRECTORY '/var/lib/mysql';

1. 新 Pod 启动，数据目录为空
2. mysql-agent 选择最优 Donor（最低延迟 SECONDARY）
3. 在 Recipient 上执行 CLONE INSTANCE
4. Clone 完成后 Recipient MySQL 重启
5. mysql-agent 调用 cluster.addInstance()
6. Group Replication 通过 GTID 追赶增量
7. 实例状态 ONLINE

InnoDB 关键 I/O 操作：
    │
    ├── 数据文件写入（ibdata1, *.ibd）
    │   → 随机读写，需高 IOPS
    │   → O_DIRECT 绕过文件系统缓存
    │   → 块存储提供微秒级延迟
    │
    ├── Redo Log 写入（ib_logfile0/1）
    │   → 顺序写入，需低延迟
    │   → 每次事务提交触发 fsync
    │   → fsync 延迟直接影响 TPS
    │
    ├── Double Write Buffer
    │   → 顺序写入 128 页（2MB）
    │   → 防止部分写入（Torn Page）
    │
    └── Undo 表空间（undo_001/002）
        → 随机读写，UPDATE/DELETE 密集场景

spec:
  instances: 3
  mycnf: |
    [mysqld]
    # 启用 Multi-Primary
    loose-group_replication_single_primary_mode=OFF
    loose-group_replication_enforce_update_everywhere_checks=ON
    
    # 冲突检测配置
    loose-group_replication_transaction_size_limit=150000000
    loose-group_replication_compression_threshold=1000000
    
    # 一致性级别（Multi-Primary 下建议使用 BEFORE_ON_PRIMARY_FAILOVER）
    loose-group_replication_consistency=BEFORE_ON_PRIMARY_FAILOVER    

```mermaid
graph TD
    NA["Node-A 写入<br/>UPDATE user SET balance = balance - 100"] --> NA1["事务提交，广播 Write-Set"]
    NB["Node-B 写入<br/>UPDATE user SET balance = balance - 50"] --> NB1["事务提交，广播 Write-Set"]
    NA1 --> CERT["Certification 层<br/>（所有节点相同顺序）"]
    NB1 --> CERT
    CERT -->|"先到达"| OK["Node-A 事务<br/>认证通过 → 提交"]
    CERT -->|"后到达，冲突<br/>检测到同一行"| FAIL["Node-B 事务<br/>回滚<br/>客户端收到 ER_CERTIFICATION_FAILURE"]

**Multi-Primary 最佳实践：**

- 按业务拆分写入节点（不同业务写不同 Primary），避免跨节点修改同一行
- 避免热点更新（计数器、库存扣减等场景应使用 Single-Primary）
- 监控 group_replication_transactions_rollback 指标
- 应用程序需处理 ER_CERTIFICATION_FAILURE 错误并重试

1. 确认备份可用性
   kubectl get mysqlbackup <backup-name> -o yaml

2. 创建新 InnoDBCluster（空集群）
   kubectl apply -f new-cluster.yaml

3. 创建 MySQLRestore CR
   kubectl apply -f restore.yaml

4. Operator 执行恢复
   4.1 从 OCI Object Storage 下载备份文件
   4.2 在新集群 PRIMARY 上执行 dump 导入
   4.3 应用 Binlog（PITR 模式，恢复到指定时间点）
   4.4 验证数据一致性

5. 恢复完成
   kubectl get mysqlrestore restore-from-backup

apiVersion: mysql.oracle.com/v2
kind: MySQLRestore
metadata:
  name: pitr-restore
spec:
  clusterName: mycluster-recovery
  backupName: daily-full-backup-mycluster-20240526
  restoreProfile:
    dumpInstance:
      dumpOptions:
        threads: 8
        maxRate: "100M"
      storage:
        ociObjectStorage:
          bucketName: "mysql-backups"
          credentials: "oci-backup-secret"
          prefix: "daily/"
    # 时间点恢复
    pointInTimeRecovery:
      targetTimestamp: "2024-05-26T14:30:00Z"
      # 恢复到指定 GTID
      # targetGTID: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-10000"

apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: mysql-critical-rules
spec:
  groups:
    # 第一层：可用性告警（Critical）
    - name: mysql.availability
      rules:
        - alert: MySQLInstanceDown
          expr: mysql_up == 0
          for: 1m
          labels:
            severity: critical
          annotations:
            summary: "MySQL 实例不可达"

        - alert: MySQLGroupReplicationDegraded
          expr: sum by (cluster) (mysql_global_status_gr_member_state{member_state="ONLINE"}) < 3
          for: 1m
          labels:
            severity: critical
          annotations:
            summary: "Group Replication 在线成员不足 3 个"

    # 第二层：性能告警（Warning）
    - name: mysql.performance
      rules:
        - alert: MySQLHighReplicationLag
          expr: mysql_slave_status_seconds_behind_master > 60
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "从库延迟超过 60 秒"

        - alert: MySQLHighThreadsRunning
          expr: mysql_global_status_threads_running > 100
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "活跃线程数超过 100"

    # 第三层：资源告警（Warning）
    - name: mysql.resources
      rules:
        - alert: MySQLDiskSpaceLow
          expr: (mysql_global_variables_datadir_size / mysql_global_variables_disk_capacity) > 0.85
          for: 10m
          labels:
            severity: warning
          annotations:
            summary: "磁盘使用率超过 85%"

    # 第四层：复制告警
    - name: mysql.replication
      rules:
        - alert: MySQLReplicationChannelStopped
          expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0
          for: 1m
          labels:
            severity: critical
          annotations:
            summary: "复制通道异常中断"

graph LR
    subgraph RegionA["Region A (Primary Site)"]
        subgraph ClusterA["InnoDB Cluster（PRIMARY）"]
            P1["P(写)"]
            S1["S(只)"]
            S2["S(只)"]
        end
        RouterA["Router (6446/6447)"]
    end

    subgraph RegionB["Region B (DR Site)"]
        subgraph ClusterB["InnoDB Cluster（REPLICA）"]
            P2["P(只)"]
            S3["S(只)"]
            S4["S(只)"]
        end
        RouterB["Router (Standby)"]
    end

    ClusterA -->|"异步复制 (Async Replication)<br/>Channel: clusterset_replication"| ClusterB

# Primary Cluster（Region A）
apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: primary-cluster
spec:
  instances: 3
  # ... 常规配置

# Replica Cluster（Region B）
apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: dr-cluster
spec:
  instances: 3
  # ... 常规配置

# ClusterSet Replication Channel（通过 MySQL Shell 配置）
# mysqlsh> cluster1.createClusterSet('main-cs')
# mysqlsh> cluster2 = dba.getCluster('dr-cluster')
# mysqlsh> mainCS.addReplicaCluster('dr-cluster')

1. 确认 Primary Cluster 不可用
2. 检查 Replica Cluster 复制延迟（评估数据丢失）
3. 执行 Emergency Failover
   mysqlsh> cs.forcePrimaryCluster('dr-cluster')
4. 验证新 Primary Cluster 状态
5. 更新 DNS / 负载均衡指向新 Primary Router
6. 应用流量切换到 DR Site
7. 原 Primary Cluster 恢复后作为新 Replica 加入

spec:
  podSpec:
    resources:
      requests:
        memory: "4Gi"              # 调度保证资源
        cpu: "2"                    # 2 核 CPU（2000m）
      limits:
        memory: "8Gi"              # 硬限制，超出 OOM Kill
        cpu: "4"                    # 4 核 CPU 上限
        
  router:
    instances: 2
    podSpec:
      resources:
        requests:
          memory: "256Mi"
          cpu: "200m"
        limits:
          memory: "512Mi"
          cpu: "500m"

MySQL 总内存 = InnoDB Buffer Pool + 连接内存 + 其他缓冲

# 估算公式
InnoDB Buffer Pool = 物理内存 × 70% ~ 80%
连接内存 = max_connections × 每个连接开销
  每个连接内存 ≈ sort_buffer_size + read_buffer_size + join_buffer_size + binlog_cache_size
其他 = query_cache + tmp_table_size + AHI + Change Buffer

第一层：CR Status（快速概览）
    kubectl describe innodbcluster <name>
    → 检查 status.cluster.status
    → 检查 status.cluster.topology.<pod>.memberState

第二层：Pod 事件与状态（基础设施）
    kubectl describe pod <pod-name>
    → Events（调度失败、镜像拉取失败、OOM Kill）
    → State（Pending/CrashLoopBackOff）

第三层：容器日志（应用层）
    kubectl logs <pod-name> -c mysql        # MySQL 日志
    kubectl logs <pod-name> -c mysql-agent   # Agent 日志
    kubectl logs <pod-name> -c mysqlsh-wrapper # Shell 日志

第四层：MySQL Shell 排查（集群内部）
    kubectl exec -it <pod> -- mysqlsh --uri root@localhost
    > cluster.status()
    > cluster.describe()

# 集群整体状态
kubectl get innodbcluster -o wide
kubectl describe innodbcluster mycluster

# Pod 状态与日志
kubectl get pods -l mysql.oracle.com/cluster=mycluster
kubectl logs mycluster-0 -c mysql --tail=100
kubectl logs mycluster-0 -c mysql-agent --tail=50

# MySQL Shell 集群诊断
kubectl exec -it mycluster-0 -- mysqlsh --uri "root:$PWD@localhost:3306" \
  --js -e "print(dba.getCluster().status())"

# Group Replication 状态
kubectl exec -it mycluster-0 -- mysql -u root -p -e \
  "SELECT * FROM performance_schema.replication_group_members;"

# 实例错误日志
kubectl exec -it mycluster-0 -- tail -100 /var/lib/mysql/mysqld.err

# 备份状态
kubectl get mysqlbackup -l mysql.oracle.com/cluster=mycluster
kubectl describe mysqlbackup daily-full-backup-mycluster-20240526

# 3 节点 Single-Primary（标准生产配置）
spec:
  instances: 3                   # 3 节点满足仲裁与单节点容错
  router:
    instances: 2                 # Router 至少 2 实例，配置 HPA 自动伸缩
  version: "8.2.0"              # 固定版本，避免自动升级
  edition: "community"          # 或 "enterprise" 获取官方支持

spec:
  datadirVolumeClaimTemplate:
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 200Gi              # 预留 30% 增长空间
    storageClassName: "oci-bv-uhp"  # 高性能块存储

spec:
  tls:
    secretName: mycluster-tls-secret     # 自定义 CA 证书
  podSpec:
    annotations:
      "network.networking.k8s.io/network-policy": |
        ingress:
          - from:
              - podSelector:
                  matchLabels:
                    app: my-application
            ports:
              - port: 3306
              - port: 33060        

spec:
  backupSchedules:
    - name: daily-full
      schedule: "0 2 * * *"                # 每日凌晨 2 点全量备份
      backupProfile:
        dumpInstance:
          dumpOptions:
            consistent: true
            excludeSchemas: ["test", "_shadow"]
            maxRate: "20M"
          storage:
            ociObjectStorage:
              bucketName: "mysql-backups-prod"
              credentials: "oci-backup-secret"
              prefix: "daily/"
      deleteBackupData: true
      enabled: true

    - name: six-hourly-binlog
      schedule: "0 */6 * * *"               # 每 6 小时间增量备份
      backupProfile:
        dumpInstance:
          dumpOptions:
            consistent: false
            ddlOnly: false
          storage:
            ociObjectStorage:
              bucketName: "mysql-backups-prod"
              credentials: "oci-backup-secret"
              prefix: "binlog/"
      enabled: true