Percona XtraDB Cluster (PXC) 面试题

+------------------+       Write-Set        +------------------+
|   Node 1 (PXC)   | <---------------------> |   Node 2 (PXC)   |
|  MySQL + Galera  |                         |  MySQL + Galera  |
|  + wsrep Provider|                         |  + wsrep Provider|
+--------+---------+                         +--------+---------+
         |                                            |
         |          Group Communication (gcomm)        |
         +--------------------------------------------+
                              |
                    +------------------+
                    |   Node 3 (PXC)   |
                    |  MySQL + Galera  |
                    |  + wsrep Provider|
                    +------------------+

-- 查看流控状态
SHOW STATUS LIKE 'wsrep_flow_control%';

-- 查看接收队列长度
SHOW STATUS LIKE 'wsrep_local_recv_queue%';

-- 调整流控参数（运行时）
SET GLOBAL wsrep_slave_threads = 4;

-- 查看节点完整 wsrep 状态
SHOW GLOBAL STATUS LIKE 'wsrep%';

-- 关键字段
-- wsrep_cluster_state_uuid     : 集群唯一标识
-- wsrep_cluster_size            : 集群当前节点数
-- wsrep_cluster_status          : Primary / Non-Primary
-- wsrep_local_state_comment     : Synced / Donor / Joining
-- wsrep_ready                   : ON 表示节点可接受查询
-- wsrep_connected               : ON 表示与集群通信正常

# my.cnf 配置 SST
[mysqld]
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = "sstuser:sstpassword"

# gcache.size = (每小时写入量 × 最大允许停机时间) / 压缩比
# 示例：100GB/h 写入，允许停机 2h，压缩比 3:1
# gcache.size ≈ 200GB / 3 ≈ 67GB

[mysqld]
wsrep_provider_options = "gcache.size=2G;gcache.page_size=128M"

事务执行流程:
1. BEGIN
2. 执行 DML 语句（本地执行，InnoDB 写 undo/redo log）
3. COMMIT -> wsrep 拦截，提取写集合
4. 写集合广播至集群所有节点
5. 各节点并行认证（certification）
6. 认证通过 -> 源节点 commit + 其余节点 apply
7. 认证失败 -> 源节点 rollback + 返回死锁错误

# PerconaXtraDBCluster CR 示例
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
  name: cluster1
spec:
  crVersion: 1.15.0
  pxc:
    size: 3
    image: percona/percona-xtradb-cluster:8.0
    volumeSpec:
      persistentVolumeClaim:
        resources:
          requests:
            storage: 100Gi
  proxysql:
    size: 3
    image: percona/percona-xtradb-cluster-operator:1.15.0-proxysql
  backup:
    image: percona/percona-xtradb-cluster-operator:1.15.0-pxc8.0-backup
    storages:
      s3-us-east:
        type: s3
        s3:
          bucket: pxc-backups
          region: us-east-1

                    Service (ClusterIP)
                           |
              +------------+-----------+
              |            |           |
         ProxySQL-0   ProxySQL-1   ProxySQL-2
              |            |           |
         PXC Pod-0    PXC Pod-1    PXC Pod-2
        (Writer)     (Reader)     (Reader)
              \          |           /
               Galera Group Communication

spec:
  backup:
    image: percona/percona-xtradb-cluster-operator:1.15.0-pxc8.0-backup
    schedule:
      - name: daily-backup
        schedule: "0 2 * * *"
        keep: 7
        storageName: s3-us-east
    storages:
      s3-us-east:
        type: s3
        s3:
          bucket: pxc-backups
          region: us-east-1

# 1. 从 S3 下载最新备份
# 2. 准备备份（apply-log）
xtrabackup --prepare --target-dir=/backup/

# 3. 启动新节点（自动触发 SST 加入集群）
# 4. 或通过 Operator 从备份恢复：
kubectl apply -f restore.yaml

# restore.yaml
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore-daily
spec:
  pxcCluster: cluster1
  backupName: daily-backup

# ServiceMonitor 示例
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: pxc-monitor
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: percona-xtradb-cluster
  endpoints:
    - port: metrics
      interval: 30s

groups:
  - name: pxc_alerts
    rules:
      - alert: PXCNodeNotSynced
        expr: mysql_global_status_wsrep_local_state != 4
        for: 2m
        annotations:
          summary: "PXC 节点 XQOPEN $labels.pod XQCLOSE 状态异常"

      - alert: PXCClusterNonPrimary
        expr: mysql_global_status_wsrep_cluster_status != 0
        for: 1m
        annotations:
          summary: "PXC 集群进入 Non-Primary 状态"

      - alert: PXCFlowControlPaused
        expr: rate(mysql_global_status_wsrep_flow_control_paused_ns[5m]) > 0.1
        for: 5m
        annotations:
          summary: "PXC 流控暂停比例过高"

-- 定义后端 PXC 节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, 'pxc-node-0.pxc', 3306, 1, 1000),
       (10, 'pxc-node-1.pxc', 3306, 1, 1000),
       (10, 'pxc-node-2.pxc', 3306, 1, 1000);

-- 读写分离规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 10, 1),       -- 读请求路由至 Reader 组
       (2, 1, '^INSERT|^UPDATE|^DELETE', 10, 1); -- 写请求路由至 Writer 组

-- Galera 健康检查用户
SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = 'monitor_password';
SET mysql-monitor_galera_healthcheck = 1;

-- 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

frontend pxc_frontend
    bind *:3306
    default_backend pxc_backend

backend pxc_backend
    balance roundrobin
    option httpchk GET /check
    server pxc-node-0 pxc-node-0.svc:3306 check port 9200 inter 2s
    server pxc-node-1 pxc-node-1.svc:3306 check port 9200 inter 2s
    server pxc-node-2 pxc-node-2.svc:3306 check port 9200 inter 2s

# 在 my.cnf 中配置节点权重
[mysqld]
wsrep_provider_options = "pc.weight=2"

-- 查看当前集群 views 和仲裁状态
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';
SHOW STATUS LIKE 'wsrep_provider_name';
SHOW STATUS LIKE 'wsrep_provider_vendor';

# 启动 garbd
garbd --group=pxc_cluster --address="gcomm://10.0.0.1,10.0.0.2" \
      --option="pc.weight=1"

-- XA 事务示例
XA START 'xid1';
INSERT INTO orders VALUES (1, 'order_data');
INSERT INTO orders_archive VALUES (1, 'order_data');
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

-- TOI 模式（默认）
ALTER TABLE large_table ADD COLUMN new_col INT;
-- 全局所有节点同时执行，阻塞 DML 直到完成

-- RSU 模式
SET GLOBAL wsrep_OSU_method = 'RSU';
ALTER TABLE large_table ADD COLUMN new_col INT;
SET GLOBAL wsrep_OSU_method = 'TOI';
-- 仅本地节点执行，执行完成后逐节点重复操作

-- 检查当前模式
SHOW VARIABLES LIKE 'wsrep_OSU_method';

           +----------+          +----------+
           |  PXC-1   |          |  PXC-2   |
           | (DC1-R1) |          | (DC1-R2) |
           +----+-----+          +-----+----+
                |                      |
                |   Galera Replication |
     +----------+----------+----------+----------+
     |          |          |          |          |
+----+----+ +---+----+ +---+----+ +---+----+
|  PXC-3  | | PXC-4  | | PXC-5  | | PXC-6  |
| DC2-R1  | | DC2-R2 | | DC3-R1  | | DC3-R2  |
+---------+ +--------+ +--------+ +--------+

# DC1 节点
wsrep_provider_options = "gmcast.segment=1;pc.weight=2"
# DC2 节点
wsrep_provider_options = "gmcast.segment=2;pc.weight=1"
# DC3 节点
wsrep_provider_options = "gmcast.segment=3;pc.weight=1"

                  ProxySQL VIP
                       |
            +----------+----------+
            |                     |
    +-------+--------+   +-------+--------+
    |   Data Center 1 |   |   Data Center 2 |
    |  (Primary Site) |   | (Disaster Rec.) |
    +-----------------+   +-----------------+
    | PXC-1 (w=2,s=1) |   | PXC-3 (w=1,s=2) |
    | PXC-2 (w=2,s=1) |   | PXC-4 (w=1,s=2) |
    +-----------------+   +-----------------+
            |                     |
            +---- WAN Link -------+

[mysqld]
wsrep_provider_options = "
  gcache.size=4G;
  gcs.fc_limit=32;
  gcs.fc_factor=0.8;
  gcs.max_packet_size=64500;
  evs.inactive_check_period=PT1S;
  evs.suspect_timeout=PT10S;
  evs.inactive_timeout=PT30S;
  evs.keepalive_period=PT3S
"

# 增大网络缓冲区
sysctl -w net.core.rmem_max=16777216
sysctl -w net.core.wmem_max=16777216

1. 节点故障（崩溃、网络断开）
2. 其余节点维持 Primary，集群正常运行
3. 故障节点恢复，检测到 gcache 覆盖的 seqno 范围
4. 优先 IST：从集群节点拉取增量事务
5. 否则 SST：从 Donor 节点拉取全量快照
6. 节点状态 Joining -> Joined -> Synced

# 1. 确认最后运行的节点（拥有最高 seqno）
# 在每个节点上执行
mysqld --wsrep-recover

# 2. 查看最后一个节点输出的 seqno（grastate.dat 或错误日志）：
cat /var/lib/mysql/grastate.dat
# seqno: 12345

# 3. 以 bootstrap 模式启动 seqno 最高的节点（设为 safe_to_bootstrap: 1）
# 在 grastate.dat 中设置后再启动，或直接：
mysqld --wsrep-new-cluster

# 4. 其余节点正常启动，自动加入集群
systemctl start mysql

# 1. 从 S3 拉取最新备份
aws s3 cp s3://pxc-backups/latest/ /backup/

# 2. 准备备份
xtrabackup --prepare --target-dir=/backup/

# 3. 初始化节点数据目录
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/

# 4. 创建 grastate.dat
echo "safe_to_bootstrap: 1" > /var/lib/mysql/grastate.dat

# 5. 启动 bootstrap 节点
mysqld --wsrep-new-cluster

# 6. 其余节点从备份节点的 SST 恢复

# 全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量备份（基于上一次全量/增量）
xtrabackup --backup --target-dir=/backup/inc1 \
           --incremental-basedir=/backup/full \
           --user=root --password=xxx

# 准备备份（apply-log）
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
           --incremental-dir=/backup/inc1

# 恢复
xtrabackup --copy-back --target-dir=/backup/full

# 单事务备份（PXC 专用参数）
mysqldump --single-transaction \
          --skip-lock-tables \
          --all-databases \
          --routines --triggers --events \
          > /backup/dump.sql

[mysqld]
wsrep_provider_options = "
  socket.ssl_key=/etc/mysql/certs/server-key.pem;
  socket.ssl_cert=/etc/mysql/certs/server-cert.pem;
  socket.ssl_ca=/etc/mysql/certs/ca-cert.pem;
  socket.ssl_cipher=AES128-SHA256
"

[mysqld]
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = "sstuser:sstpassword"

[sst]
encrypt = 4  # xtrabackup 加密算法（AES256）
ssl-mode = VERIFY_CA
ssl-ca = /etc/mysql/certs/ca-cert.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key = /etc/mysql/certs/server-key.pem

[mysqld]
ssl-ca = /etc/mysql/certs/ca-cert.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key = /etc/mysql/certs/server-key.pem
require_secure_transport = ON

# 生成 CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

# 生成 Server 证书
openssl req -newkey rsa:2048 -days 3650 -nodes \
    -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out server-cert.pem

spec:
  pxc:
    sslSecretName: pxc-ssl
    sslInternalSecretName: pxc-ssl-internal

+--------+     +--------+     +--------+
| PXC-1  | <-> | PXC-2  | <-> | PXC-3  |
|Master  |     |Master  |     |Master  |
+---+----+     +--------+     +--------+
    | 异步复制
    v
+--------+
| Async   |
| Slave   |
+--------+

-- 1. 在 PXC 节点上启用 binlog（主库）
-- my.cnf
-- [mysqld]
-- log_bin = mysql-bin
-- log_slave_updates = ON
-- binlog_format = ROW
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- 2. 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 3. 在 Slave 上配置复制
CHANGE MASTER TO
  MASTER_HOST = 'pxc-node-1',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 4. 验证
SHOW SLAVE STATUS\G

[mysqld]
wsrep_provider_options = "
  gcache.size=4G;
  gcache.page_size=256M;
  gcs.fc_limit=32;
  gcs.fc_factor=0.8;
  gcs.max_packet_size=64500;
  evs.inactive_check_period=PT1S;
  evs.suspect_timeout=PT10S;
  evs.inactive_timeout=PT30S;
  evs.keepalive_period=PT3S;
  pc.weight=1;
  gmcast.segment=1
"

spec:
  crVersion: 1.15.0
  upgradeOptions:
    versionServiceEndpoint: https://check.percona.com
    apply: disabled              # disabled / recommended / latest / 8.0.35-27.1
    schedule: "0 4 * * *"        # 定时升级窗口

# 1. 确认当前集群状态
kubectl get pxc -o yaml

# 2. 检查节点状态
kubectl exec -it cluster1-pxc-0 -- mysql -e "SHOW STATUS LIKE 'wsrep%'"

# 3. 确认备份可用（升级前必做备份）
kubectl get pxc-backup

# 4. 设置 apply 策略
# spec.upgradeOptions.apply = recommended

# 5. 监控升级过程
kubectl logs -f deployment/percona-xtradb-cluster-operator
kubectl get pods -w

spec:
  pxc:
    volumeSpec:
      persistentVolumeClaim:
        storageClassName: local-ssd
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 500Gi

spec:
  pxc:
    volumeSpec:
      persistentVolumeClaim:
        storageClassName: ceph-rbd
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 500Gi

# Operator 自动生成的 PDB
apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: cluster1-pxc
spec:
  maxUnavailable: 1
  selector:
    matchLabels:
      app.kubernetes.io/name: percona-xtradb-cluster
      app.kubernetes.io/instance: cluster1

spec:
  maxUnavailable: 2  # 可同时中断 2 个 Pod，仍保有 3 节点 quorum

# 1. 确认集群状态
kubectl exec cluster1-pxc-0 -- mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

# 2. 检查 PDB 状态
kubectl get pdb cluster1-pxc

# 3. 执行节点 drain（受 PDB 保护）
kubectl drain node-1 --ignore-daemonsets --delete-emptydir-data

# 4. 观察 Pod 驱逐过程
kubectl get pods -w

# 5. 维护完成后 uncordon 节点
kubectl uncordon node-1

spec:
  pxc:
    resources:
      requests:
        memory: "4Gi"
        cpu: "2"
      limits:
        memory: "8Gi"
        cpu: "4"

spec:
  pxc:
    affinity:
      antiAffinityTopologyKey: kubernetes.io/hostname  # 每个物理节点最多 1 个 PXC Pod

# sysctl 参数（通过 initContainer 设置）
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

spec:
  pxc:
    # 非 root 运行
    runAsUser: 1001
    runAsGroup: 1001
    # 启用 SSL
    sslSecretName: pxc-ssl
    sslInternalSecretName: pxc-ssl-internal

# Operator 自动配置的探针逻辑
# 就绪探针：wsrep_ready=ON AND wsrep_local_state=4
# 存活探针：mysqld 进程存活
# 启动探针：允许 SST 期间的较长启动时间

spec:
  pxc:
    readinessDelaySec: 15
    livenessDelaySec: 60

spec:
  backup:
    schedule:
      - name: daily-backup
        schedule: "0 2 * * *"
        keep: 7
    # 定期验证备份可用性
    # 通过 restore job 验证

spec:
  pmm:
    enabled: true
    image: percona/pmm-client:2
    serverHost: monitoring-service

-- 排查步骤
-- 1. 检查网络连通性
-- 2. 检查 wsrep_cluster_address 配置
-- 3. 检查 SST 用户权限
-- 4. 查看错误日志

SHOW STATUS LIKE 'wsrep_local_state_comment';
-- 长时间显示 Joining -> 检查 SST 日志

# 检查 SST 日志
grep -i sst /var/log/mysql/error.log

# 常见原因
# - xtrabackup 版本不匹配
# - Donor 磁盘空间不足
# - SST 用户认证失败

# 手动触发 SST
rm -f /var/lib/mysql/grastate.dat
systemctl restart mysql

-- 诊断
SHOW STATUS LIKE 'wsrep_flow_control%';
SHOW STATUS LIKE 'wsrep_local_recv_queue%';

-- 优化
SET GLOBAL wsrep_slave_threads = 8;

-- 监控冲突
SHOW STATUS LIKE 'wsrep_local_bf_aborts';
SHOW STATUS LIKE 'wsrep_local_cert_failures';

-- 应对
-- 1. 应用层实现重试逻辑
-- 2. 将热点数据写入路由至同一节点
-- 3. 排查是否有跨节点并发更新同一行

-- 检查 gcache 使用率
-- gcache 溢出表现为 IST 不可用，节点回退至 SST

-- 增加 gcache 大小
-- wsrep_provider_options = "gcache.size=8G"

# 根因定位
# 1. 确认存活节点数
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

# 2. 检查网络分区
# 3. 检查是否所有节点同时崩溃

# 恢复步骤
# 确认 safe_to_bootstrap 节点
cat /var/lib/mysql/grastate.dat
# 将 seqno 最高的节点设为 safe_to_bootstrap: 1

# Bootstrap 集群
mysqld --wsrep-new-cluster

# 其余节点正常启动
systemctl start mysql

-- 综合健康检查
SHOW GLOBAL STATUS LIKE 'wsrep%';

-- 集群基本信息
SELECT * FROM performance_schema.replication_group_members;  -- Group Replication
-- PXC 无对应表，使用 SHOW STATUS

-- 检查 InnoDB 状态（冲突检测日志）
SHOW ENGINE INNODB STATUS\G