%%{init: {"themeVariables": {"fontSize": "18px"}, "flowchart": {"nodeSpacing": 30, "rankSpacing": 40}}}%%
flowchart TB
    U(["用户浏览器<br/>https://app.example.com"])
    VIP{{"业务 VIP 10.10.10.10:443"}}
    U ==> VIP

    subgraph SA["服务器 A · 10.10.10.11"]
        direction TB
        KA["Keepalived 优先级 150<br/>持有 VIP"]
        NA["Nginx :443"]
        HA["HAProxy<br/>:18080 应用 / :13306 写 / :13307 读"]
        AA["App-A :8080"]
        CA["健康检查代理 :9201/:9202"]
        MA[("MySQL M1<br/>PRIMARY")]
        RA["Redis R1 主 :6379<br/>Redis r3 副本 :6380"]
        KA ~~~ NA ~~~ HA ~~~ AA ~~~ CA ~~~ MA ~~~ RA
    end

    subgraph SB["服务器 B · 10.10.10.12"]
        direction TB
        KB["Keepalived 优先级 140<br/>BACKUP"]
        NB["Nginx :443"]
        HB["HAProxy<br/>:18080 应用 / :13306 写 / :13307 读"]
        AB["App-B :8080"]
        CB["健康检查代理 :9201/:9202"]
        MB[("MySQL M2<br/>SECONDARY")]
        RB["Redis R2 主 :6379<br/>Redis r1 副本 :6380"]
        KB ~~~ NB ~~~ HB ~~~ AB ~~~ CB ~~~ MB ~~~ RB
    end

    subgraph SC["服务器 C · 10.10.10.13"]
        direction TB
        KC["Keepalived 优先级 130<br/>BACKUP"]
        NC["Nginx :443"]
        HC["HAProxy<br/>:18080 应用 / :13306 写 / :13307 读"]
        AC["App-C :8080"]
        CC["健康检查代理 :9201/:9202"]
        MC[("MySQL M3<br/>SECONDARY")]
        RC["Redis R3 主 :6379<br/>Redis r2 副本 :6380"]
        KC ~~~ NC ~~~ HC ~~~ AC ~~~ CC ~~~ MC ~~~ RC
    end

    VIP ==>|"当前持有"| SA
    VIP -.->|"① 漂移"| SB
    VIP -.->|"① 漂移"| SC

%%{init: {"themeVariables": {"fontSize": "18px"}}}%%
flowchart LR
    U(["用户浏览器"]) ==> VIP{{"VIP<br/>10.10.10.10:443"}}
    VIP ==>|"当前持有"| NA["A：Nginx :443"]
    VIP -.->|"① 漂移"| NB["B：Nginx :443"]
    VIP -.->|"① 漂移"| NC["C：Nginx :443"]
    NA ==> HA["A：HAProxy :18080<br/>就绪检查"]
    HA ==>|"③"| AA["App-A :8080"]
    HA ==>|"③"| AB["App-B :8080"]
    HA ==>|"③"| AC["App-C :8080"]
    NA -.->|"② backup"| AA
    NA -.->|"② backup"| AB
    NA -.->|"② backup"| AC

%%{init: {"themeVariables": {"fontSize": "18px"}}}%%
flowchart TB
    subgraph APP["每台 App → 本机 HAProxy :13306"]
        direction LR
        HDA["A：HAProxy"]
        HDB["B：HAProxy"]
        HDC["C：HAProxy"]
    end
    subgraph MGR["MySQL MGR 单主组"]
        direction LR
        MB[("M2 · B<br/>SECONDARY<br/>weight 60")]
        MA[("M1 · A<br/>PRIMARY<br/>weight 70")]
        MC[("M3 · C<br/>SECONDARY<br/>weight 50")]
    end
    HDA ==>|"④ 写"| MA
    HDB ==>|"④ 写"| MA
    HDC ==>|"④ 写"| MA
    MA <-.->|"⑤ 组复制"| MB
    MB <-.->|"⑤"| MC
    MA <-.->|"⑤"| MC

%%{init: {"themeVariables": {"fontSize": "18px"}}}%%
flowchart LR
    R1["R1 主 :6379<br/>服务器 A"] -.->|"⑥ 复制"| r1["r1 副本 :6380<br/>服务器 B"]
    R2["R2 主 :6379<br/>服务器 B"] -.->|"⑥ 复制"| r2["r2 副本 :6380<br/>服务器 C"]
    R3["R3 主 :6379<br/>服务器 C"] -.->|"⑥ 复制"| r3["r3 副本 :6380<br/>服务器 A"]

%%{init: {"themeVariables": {"fontSize": "16px"}, "flowchart": {"nodeSpacing": 40, "rankSpacing": 60}}}%%
flowchart TB
    U(["用户浏览器"])
    OPS(["报表 / 运维工具"])
    VIP{{"业务 VIP 10.10.10.10:443"}}

    subgraph L1["接入层（三台各一套，仅 VIP 所在节点承接流量）"]
        direction LR
        KP["Keepalived<br/>VRRP 决定 VIP 持有者<br/>只漂移地址，不参与数据库选主"]
        NG["Nginx :443<br/>TLS 终止 · 提供 Vue 静态文件<br/>/api 反向代理"]
    end

    subgraph L2["流量调度层：HAProxy（三台各一套，配置一致）"]
        direction LR
        HPA["app_pool :18080<br/>应用负载均衡＋就绪检查"]
        HPW["mysql_rw :13306<br/>只转发到当前 PRIMARY"]
        HPR["mysql_ro :13307<br/>转发到 SECONDARY（默认不接入应用）"]
        HPL["haproxy_alive :18404<br/>HAProxy 自身存活探针"]
    end

    subgraph L3["应用层：Spring Boot × 3（无状态，同时服务）"]
        APP["App-A / App-B / App-C :8080<br/>业务逻辑 · MyBatis＋HikariCP · Lettuce<br/>暴露就绪检查，支持优雅停机"]
    end

    subgraph L4["数据层"]
        direction LR
        AGT["MGR 健康检查代理（每台一个）<br/>:9201 本机是否可写 PRIMARY<br/>:9202 本机是否可读 SECONDARY"]
        MGR[("MySQL MGR 单主组 M1/M2/M3<br/>持久业务数据<br/>组复制 :33061 · 多数派自动选主")]
        RDS[("Redis Cluster R1/R2/R3＋r1/r2/r3<br/>缓存 · 16384 槽位分片<br/>集群总线 · 副本自动提升")]
    end

    U ==>|"HTTPS"| VIP ==> NG
    KP -.->|"绑定 / 漂移 VIP"| VIP
    KP -.->|"check_entry.sh 探测"| NG
    KP -.->|"探测 /alive"| HPL

    NG ==>|"/api 请求"| HPA
    HPA ==>|"轮询分发"| APP
    HPA -.->|"就绪检查，失败摘流"| APP
    NG -.->|"HAProxy 不可用时 backup 直连"| APP

    APP ==>|"JDBC 127.0.0.1:13306"| HPW
    HPW ==>|"读写事务"| MGR
    OPS -.->|"只读查询"| HPR
    HPR -.-> MGR
    HPW -.->|"httpchk /rw :9201"| AGT
    HPR -.->|"httpchk /ro :9202"| AGT
    AGT -.->|"查询成员角色与积压"| MGR

    APP ==>|"Lettuce 直连节点，按槽位路由<br/>自适应刷新拓扑"| RDS

    classDef app fill:#fff3cd,stroke:#d39e00,stroke-width:3px
    classDef data fill:#e8f4fd,stroke:#1f77b4,stroke-width:2px
    classDef ctrl fill:#f2f2f2,stroke:#888,stroke-dasharray:4 3
    class APP app
    class MGR,RDS data
    class KP,AGT,HPL ctrl

[mysqld]
port=3306
server_id=101
log_bin=mysql-bin
log_slave_updates=ON
binlog_format=ROW
binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
sync_binlog=1
innodb_flush_log_at_trx_commit=1
plugin_load_add=group_replication.so
group_replication_group_name="3ef332c0-9318-4e52-bc03-7508bda927ab"
group_replication_local_address="10.10.10.11:33061"
group_replication_group_seeds="10.10.10.11:33061,10.10.10.12:33061,10.10.10.13:33061"
group_replication_ip_whitelist="10.10.10.0/24"
group_replication_single_primary_mode=ON
group_replication_enforce_update_everywhere_checks=OFF
group_replication_member_weight=70
group_replication_exit_state_action=READ_ONLY
group_replication_unreachable_majority_timeout=30
group_replication_start_on_boot=OFF
group_replication_bootstrap_group=OFF

SELECT m.MEMBER_STATE,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'group_replication_primary_member') = @@server_uuid AS is_primary,
       @@super_read_only AS sro,
       (SELECT COUNT(*) FROM performance_schema.replication_group_members
         WHERE MEMBER_STATE = 'ONLINE') AS online_cnt
FROM performance_schema.replication_group_members m
WHERE m.MEMBER_ID = @@server_uuid;

port 6379
cluster-enabled yes
cluster-config-file nodes-6379.conf
cluster-node-timeout 5000
cluster-announce-ip 10.10.10.11
cluster-announce-port 6379
cluster-announce-bus-port 16379
cluster-require-full-coverage yes
appendonly yes
appendfsync everysec
maxmemory 4gb
maxmemory-policy volatile-lru

spring:
  session:
    store-type: redis
    timeout: 30m
    redis:
      namespace: prod:myapp:session
server:
  servlet:
    session:
      cookie:
        secure: true
        http-only: true
        same-site: lax

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:13306/${MYSQL_DB}?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&connectTimeout=5000&socketTimeout=60000&tcpKeepAlive=true
    hikari:
      max-lifetime: 1500000     # 小于 HAProxy 与 MySQL wait_timeout
      keepalive-time: 300000
      connection-timeout: 5000
      validation-timeout: 3000
  redis:
    cluster:
      nodes: 10.10.10.11:6379,10.10.10.12:6379,10.10.10.13:6379
    password: ${REDIS_PASSWORD}
    timeout: 2s
    connect-timeout: 2s
    lettuce:
      cluster:
        refresh:
          adaptive: true
          period: 30s
server:
  forward-headers-strategy: framework

# http 块：本机 HAProxy 为主，三台 App 直连为兜底
upstream app_entry {
    server 127.0.0.1:18080 max_fails=1 fail_timeout=5s;
    server 10.10.10.11:8080 backup;
    server 10.10.10.12:8080 backup;
    server 10.10.10.13:8080 backup;
}

# 放在 HTTPS server 中；另行配置证书、80 跳转、日志与限流
root /srv/vue/current;
location / { try_files $uri $uri/ /index.html; }
# 默认只代理 /api；启用第 5 节登录时，将正则扩展为
# ^/(api(?:/|$)|oauth2(?:/|$)|login(?:/|$)|logout(?:/|$))
location ~ ^/api(?:/|$) {
    proxy_pass http://app_entry;
    proxy_next_upstream error timeout;
    proxy_connect_timeout 2s;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-Forwarded-Port $server_port;
    proxy_set_header X-Forwarded-For $remote_addr;
}

defaults
    mode tcp
    timeout connect 3s
    timeout client 30m
    timeout server 30m
frontend haproxy_alive
    bind 127.0.0.1:18404
    mode http
    monitor-uri /alive
listen app_pool
    bind 127.0.0.1:18080
    mode http
    timeout client 60s
    timeout server 60s
    balance roundrobin
    option httpchk GET /actuator/health/readiness
    http-check expect status 200
    server a 10.10.10.11:8080 check
    server b 10.10.10.12:8080 check
    server c 10.10.10.13:8080 check
listen mysql_rw
    bind 127.0.0.1:13306
    option httpchk GET /rw
    http-check expect status 200
    default-server inter 2s fall 2 rise 3 on-marked-down shutdown-sessions
    server m1 10.10.10.11:3306 check port 9201
    server m2 10.10.10.12:3306 check port 9201
    server m3 10.10.10.13:3306 check port 9201
listen mysql_ro
    bind 127.0.0.1:13307
    balance leastconn
    option httpchk GET /ro
    http-check expect status 200
    default-server inter 2s fall 2 rise 3 on-marked-down shutdown-sessions
    server m1 10.10.10.11:3306 check port 9202
    server m2 10.10.10.12:3306 check port 9202
    server m3 10.10.10.13:3306 check port 9202

-- 主机组：10 写、20 备用写、30 读、40 离线
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (10,'10.10.10.11',3306),(10,'10.10.10.12',3306),(10,'10.10.10.13',3306);
INSERT INTO mysql_group_replication_hostgroups
  (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup,
   active, max_writers, writer_is_also_reader, max_transactions_behind)
VALUES (10, 20, 30, 40, 1, 1, 1, 100);
-- 应用账户默认发往写组，事务内语句固定在同一后端
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app_rw', '******', 10, 1);
-- 查询规则：加锁读走写组，普通 SELECT 走读组
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1),
       (2, 1, '^SELECT', 30, 1);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;   SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

#!/bin/bash
# /etc/keepalived/check_entry.sh
curl -fsS -m 2 -o /dev/null http://127.0.0.1:18404/alive || exit 1   # HAProxy 自身
curl -ksS -m 2 -o /dev/null https://127.0.0.1/ || exit 1              # Nginx 自身（本地静态首页）
exit 0

vrrp_script chk_entry {
    script "/etc/keepalived/check_entry.sh"
    interval 2
    timeout 3
    fall 2
    rise 2
}
vrrp_instance VI_APP {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 150            # B=140，C=130
    nopreempt
    advert_int 1
    virtual_ipaddress { 10.10.10.10/24 }
    track_script { chk_entry }
}

// MySQL Shell，JS 模式
dba.configureInstance('clusteradmin@10.10.10.11:3306')
dba.configureInstance('clusteradmin@10.10.10.12:3306')
dba.configureInstance('clusteradmin@10.10.10.13:3306')
shell.connect('clusteradmin@10.10.10.11:3306')
var c = dba.createCluster('jkqCluster', {memberWeight: 70})
c.addInstance('clusteradmin@10.10.10.12:3306', {recoveryMethod: 'clone', memberWeight: 60})
c.addInstance('clusteradmin@10.10.10.13:3306', {recoveryMethod: 'clone', memberWeight: 50})
c.status()

# 每台 App 所在服务器执行一次
mysqlrouter --bootstrap clusteradmin@10.10.10.11:3306 \
  --directory /srv/mysqlrouter --user=mysqlrouter --conf-bind-address=127.0.0.1
systemctl enable --now mysqlrouter

firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.10.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.10.0/24 port port=33061 protocol=tcp accept'
firewall-cmd --reload

mkdir -p /srv/mysql/{conf,init,scripts,data,logs}
chown -R 999:999 /srv/mysql/data /srv/mysql/logs

/srv/mysql/
├── docker-compose.yml          # 三台相同
├── .env                        # 密码；仅 MGR_BOOTSTRAP_NODE 不同；权限 600
├── conf/my.cnf                 # 三台不同（4 个参数）；权限 644
├── init/01-mgr-init.sh         # 三台相同；权限 644（需被 source，不能加执行权限）
├── scripts/entrypoint-mgr.sh   # 三台相同；权限 755
├── scripts/mgr-start.sh        # 三台相同；权限 755
├── scripts/healthcheck.sh      # 三台相同；权限 755
├── data/                       # 数据目录，属主 999
└── logs/                       # 错误日志与慢日志，属主 999

services:
  mysql:
    image: mysql:5.7.38
    container_name: mysql-mgr
    network_mode: host
    restart: unless-stopped
    env_file: .env
    entrypoint: ["/opt/mgr/entrypoint-mgr.sh"]
    command: ["mysqld"]
    volumes:
      - ./conf/my.cnf:/etc/mysql/conf.d/zz-mgr.cnf:ro
      - ./init:/docker-entrypoint-initdb.d:ro
      - ./scripts:/opt/mgr:ro
      - ./data:/var/lib/mysql
      - ./logs:/var/log/mysql
    ulimits:
      nofile:
        soft: 65535
        hard: 65535
    stop_grace_period: 180s
    healthcheck:
      test: ["CMD", "/opt/mgr/healthcheck.sh"]
      interval: 10s
      timeout: 5s
      retries: 3
      start_period: 300s
    logging:
      driver: json-file
      options:
        max-size: "100m"
        max-file: "5"

TZ=Asia/Shanghai
MYSQL_ROOT_PASSWORD=请替换为强密码
# root 仅允许容器内本机登录，不创建 root@'%'
MYSQL_ROOT_HOST=127.0.0.1
MYSQL_INITDB_SKIP_TZINFO=1
MGR_REPL_USER=mgr_repl
MGR_REPL_PASSWORD=请替换为强密码
MGR_CHECK_PASSWORD=请替换为强密码
# 仅 M1 为 true；建组完成后改为 false（见 B.4 第 4 步）
MGR_BOOTSTRAP_NODE=true
# 入组失败的重试次数，每次间隔 30 秒
MGR_JOIN_RETRY=20

[client]
default-character-set=utf8mb4
socket=/var/run/mysqld/mysqld.sock

[mysql]
default-character-set=utf8mb4

[mysqld]
# ---------- 本机差异项（M1） ----------
server_id=101
report_host=10.10.10.11
loose-group_replication_local_address="10.10.10.11:33061"
loose-group_replication_member_weight=70

# ---------- 基础 ----------
user=mysql
port=3306
report_port=3306
bind-address=0.0.0.0
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
skip-name-resolve
default-time-zone='+08:00'
log_timestamps=SYSTEM
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# 以下两项必须与现网 SHOW VARIABLES 结果一致，三台相同
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=1000
max_allowed_packet=64M
default_storage_engine=InnoDB
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

# ---------- InnoDB ----------
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=8
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1

# ---------- 日志 ----------
log_error=/var/log/mysql/error.log
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1

# ---------- binlog 与 GTID（MGR 必需） ----------
log_bin=mysql-bin
relay_log=relay-bin
log_slave_updates=ON
binlog_format=ROW
binlog_checksum=NONE
binlog_row_image=FULL
sync_binlog=1
expire_logs_days=7
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
binlog_transaction_dependency_tracking=WRITESET
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
slave_preserve_commit_order=ON

# ---------- Group Replication 单主 ----------
plugin_load_add=group_replication.so
loose-group_replication_group_name="3ef332c0-9318-4e52-bc03-7508bda927ab"
loose-group_replication_group_seeds="10.10.10.11:33061,10.10.10.12:33061,10.10.10.13:33061"
loose-group_replication_ip_whitelist="10.10.10.0/24,127.0.0.1/8"
loose-group_replication_single_primary_mode=ON
loose-group_replication_enforce_update_everywhere_checks=OFF
loose-group_replication_start_on_boot=OFF
loose-group_replication_bootstrap_group=OFF
loose-group_replication_exit_state_action=READ_ONLY
loose-group_replication_unreachable_majority_timeout=30

server_id=102
report_host=10.10.10.12
loose-group_replication_local_address="10.10.10.12:33061"
loose-group_replication_member_weight=60

server_id=103
report_host=10.10.10.13
loose-group_replication_local_address="10.10.10.13:33061"
loose-group_replication_member_weight=50

# 由官方入口在数据目录首次初始化时 source 执行（文件不能有执行权限）
# 此时 mysqld 为 --skip-networking 的临时实例，本脚本只做本地准备，不启动组复制
docker_process_sql <<-EOSQL
	SET SQL_LOG_BIN=0;
	CREATE USER '${MGR_REPL_USER}'@'10.10.10.%' IDENTIFIED BY '${MGR_REPL_PASSWORD}';
	GRANT REPLICATION SLAVE ON *.* TO '${MGR_REPL_USER}'@'10.10.10.%';
	CREATE USER 'mgr_check'@'127.0.0.1' IDENTIFIED BY '${MGR_CHECK_PASSWORD}';
	GRANT SELECT ON performance_schema.* TO 'mgr_check'@'127.0.0.1';
	FLUSH PRIVILEGES;
	SET SQL_LOG_BIN=1;
	CHANGE MASTER TO MASTER_USER='${MGR_REPL_USER}', MASTER_PASSWORD='${MGR_REPL_PASSWORD}'
	  FOR CHANNEL 'group_replication_recovery';
	RESET MASTER;
EOSQL

#!/bin/bash
# 包装官方入口：前台保持 mysqld，后台执行自动入组，并把停止信号转发给 mysqld
docker-entrypoint.sh "$@" &
MYSQLD_PID=$!

term() {
  kill -TERM "$MYSQLD_PID" 2>/dev/null
  wait "$MYSQLD_PID"
  exit $?
}
trap term TERM INT

/opt/mgr/mgr-start.sh &

wait "$MYSQLD_PID"
exit $?

#!/bin/bash
# 每次容器启动时执行：已在组内则退出；否则入组；仅 M1 首次建组时 bootstrap
log() { echo "[mgr-start] $(date '+%F %T') $*"; }
export MYSQL_PWD="${MYSQL_ROOT_PASSWORD}"
sql() { mysql -uroot -h127.0.0.1 -P3306 --protocol=tcp -N -s -e "$1"; }
FLAG=/var/lib/mysql/.mgr_bootstrapped
RETRY=${MGR_JOIN_RETRY:-20}

# 初始化阶段的临时实例不监听 TCP，这里等到正式实例就绪
until sql "SELECT 1" >/dev/null 2>&1; do sleep 3; done
log "mysqld 已就绪"

member_state() {
  sql "SELECT MEMBER_STATE FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid"
}

for ((i = 1; i <= RETRY; i++)); do
  state=$(member_state)
  if [[ "$state" == "ONLINE" || "$state" == "RECOVERING" ]]; then
    log "已在组内：$state"
    exit 0
  fi

  if sql "START GROUP_REPLICATION" 2>/tmp/mgr-start.err; then
    log "入组成功，当前状态：$(member_state)"
    exit 0
  fi
  log "第 ${i}/${RETRY} 次入组失败：$(tr '\n' ' ' </tmp/mgr-start.err)"
  sql "STOP GROUP_REPLICATION" >/dev/null 2>&1

  if [[ "$MGR_BOOTSTRAP_NODE" == "true" && ! -f "$FLAG" && -z "$(sql 'SELECT @@GLOBAL.gtid_executed')" ]]; then
    log "首次建组：本机执行 bootstrap"
    sql "SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION;"
    rc=$?
    sql "SET GLOBAL group_replication_bootstrap_group=OFF"
    if [[ $rc -eq 0 ]]; then
      touch "$FLAG"
      log "bootstrap 完成，本机为 PRIMARY"
      exit 0
    fi
    log "bootstrap 失败，请检查 /var/log/mysql/error.log"
    exit 1
  fi

  sleep 30
done

log "入组重试已用尽，需人工处理（见 B.6）"
exit 1

#!/bin/bash
# 仅本机成员 ONLINE 时视为健康；用于 docker ps 观察，不触发自动重启
state=$(MYSQL_PWD="$MGR_CHECK_PASSWORD" mysql -umgr_check -h127.0.0.1 -P3306 --protocol=tcp -N -s -e \
  "SELECT MEMBER_STATE FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid" 2>/dev/null)
[[ "$state" == "ONLINE" ]]

mysql-exec() {
  docker exec -i mysql-mgr sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" exec mysql -uroot -t "$@"' mysql-exec "$@"
}

cd /srv/mysql
chmod 600 .env
chmod 644 conf/my.cnf init/01-mgr-init.sh
chmod 755 scripts/*.sh
docker compose config -q && echo "compose 配置正确"

cd /srv/mysql && docker compose up -d
docker logs -f mysql-mgr 2>&1 | grep mgr-start

cd /srv/mysql && docker compose up -d
docker logs -f mysql-mgr 2>&1 | grep mgr-start

mysql-exec <<'SQL'
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE,
       IF(MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                        WHERE VARIABLE_NAME = 'group_replication_primary_member'),
          'PRIMARY', 'SECONDARY') AS ROLE
FROM performance_schema.replication_group_members;
SQL

mysql-exec <<'SQL'
CREATE USER 'app_rw'@'10.10.10.%' IDENTIFIED BY '请替换';
CREATE USER 'ops_ro'@'10.10.10.%' IDENTIFIED BY '请替换';
SQL

GRANT SELECT, INSERT, UPDATE, DELETE ON `业务库名`.* TO 'app_rw'@'10.10.10.%';
GRANT SELECT, SHOW VIEW ON `业务库名`.* TO 'ops_ro'@'10.10.10.%';

-- 非 InnoDB 表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '业务库名' AND TABLE_TYPE = 'BASE TABLE' AND ENGINE <> 'InnoDB';
-- 无主键表
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA = '业务库名' AND t.TABLE_TYPE = 'BASE TABLE' AND c.CONSTRAINT_NAME IS NULL;

mysqldump -h<现网地址> -uroot -p \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF --default-character-set=utf8mb4 \
  --databases 业务库名 > /srv/mysql/biz.sql

mysql-exec < /srv/mysql/biz.sql

SELECT MEMBER_ID, COUNT_TRANSACTIONS_IN_QUEUE FROM performance_schema.replication_group_member_stats;

# 三台分别执行，同一张表的校验值应一致
mysql-exec -e "CHECKSUM TABLE 业务库名.表名;"
# 在 M2 或 M3 执行，预期报错 ERROR 1290 (super-read-only)
mysql-exec -e "CREATE DATABASE should_fail;"

mysql-exec <<'SQL'
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SQL

docker exec mysql-mgr sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" exec mysqldump -uroot \
  --single-transaction --routines --triggers --events --set-gtid-purged=ON \
  --databases 业务库名' > /srv/mysql/rebuild.sql

mysql-exec -e "RESET MASTER;"
mysql-exec < /srv/mysql/rebuild.sql

mysql-exec <<'SQL'
SET SQL_LOG_BIN=0;
CREATE USER 'app_rw'@'10.10.10.%' IDENTIFIED BY '与集群一致';
CREATE USER 'ops_ro'@'10.10.10.%' IDENTIFIED BY '与集群一致';
GRANT SELECT, INSERT, UPDATE, DELETE ON `业务库名`.* TO 'app_rw'@'10.10.10.%';
GRANT SELECT, SHOW VIEW ON `业务库名`.* TO 'ops_ro'@'10.10.10.%';
SET SQL_LOG_BIN=1;
SQL

for p in 6379 6380 16379 16380; do
  firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=172.16.1.0/24 port port=$p protocol=tcp accept"
done
firewall-cmd --reload

/srv/redis/
├── docker-compose.yml          # 三台相同
├── .env                        # 密码与三台 IP；仅 m1 有 COMPOSE_PROFILES=init；权限 600
├── prepare.sh                  # 三台相同；上传后执行一次
├── conf/redis-6379.conf        # 三台仅 cluster-announce-ip 不同
├── conf/redis-6380.conf        # 三台仅 cluster-announce-ip 不同
├── scripts/cluster-init.sh     # 三台相同；只在 m1 被执行
└── data/
    ├── 6379/                   # 主实例数据：AOF、nodes-6379.conf
    └── 6380/                   # 副本实例数据：AOF、nodes-6380.conf

x-redis: &redis
  image: redis:7.2.11
  network_mode: host
  restart: unless-stopped
  environment:
    TZ: Asia/Shanghai
    REDISCLI_AUTH: ${REDIS_PASSWORD}
  ulimits:
    nofile:
      soft: 65535
      hard: 65535
  stop_grace_period: 60s
  logging:
    driver: json-file
    options:
      max-size: "100m"
      max-file: "5"

services:
  redis-6379:
    <<: *redis
    container_name: redis-6379
    command: ["redis-server", "/usr/local/etc/redis/redis.conf",
              "--requirepass", "${REDIS_PASSWORD}", "--masterauth", "${REDIS_PASSWORD}"]
    volumes:
      - ./conf/redis-6379.conf:/usr/local/etc/redis/redis.conf:ro
      - ./data/6379:/data
    healthcheck:
      test: ["CMD-SHELL", "redis-cli -p 6379 ping | grep -q PONG"]
      interval: 10s
      timeout: 3s
      retries: 3

  redis-6380:
    <<: *redis
    container_name: redis-6380
    command: ["redis-server", "/usr/local/etc/redis/redis.conf",
              "--requirepass", "${REDIS_PASSWORD}", "--masterauth", "${REDIS_PASSWORD}"]
    volumes:
      - ./conf/redis-6380.conf:/usr/local/etc/redis/redis.conf:ro
      - ./data/6380:/data
    healthcheck:
      test: ["CMD-SHELL", "redis-cli -p 6380 ping | grep -q PONG"]
      interval: 10s
      timeout: 3s
      retries: 3

  # 一次性建集群容器：仅当 .env 中 COMPOSE_PROFILES=init（m1）时启动，执行完即退出
  redis-cluster-init:
    image: redis:7.2.11
    container_name: redis-cluster-init
    profiles: ["init"]
    network_mode: host
    restart: "no"
    env_file: .env
    environment:
      REDISCLI_AUTH: ${REDIS_PASSWORD}
    entrypoint: ["/bin/bash", "/opt/redis/cluster-init.sh"]
    volumes:
      - ./scripts:/opt/redis:ro
    depends_on:
      - redis-6379
      - redis-6380

REDIS_PASSWORD=请替换为强密码
REDIS_M1_IP=172.16.1.235
REDIS_M2_IP=172.16.1.244
REDIS_M3_IP=172.16.1.239
# 仅 m1 设置：启动时附带一次性建集群容器 redis-cluster-init
COMPOSE_PROFILES=init

# ---------- 本机差异项（m1） ----------
cluster-announce-ip 172.16.1.235

# ---------- 网络 ----------
port 6379
bind 0.0.0.0
protected-mode no
tcp-backlog 511
tcp-keepalive 300
timeout 0
maxclients 10000

# ---------- 集群 ----------
cluster-enabled yes
cluster-config-file nodes-6379.conf
cluster-node-timeout 5000
cluster-announce-port 6379
cluster-announce-bus-port 16379
cluster-require-full-coverage yes
cluster-replica-validity-factor 10
cluster-allow-reads-when-down no

# ---------- 内存 ----------
maxmemory 4gb
maxmemory-policy volatile-lru

# ---------- 持久化 ----------
dir /data
save ""
appendonly yes
appendfilename "appendonly.aof"
appenddirname "appendonlydir"
appendfsync everysec
aof-use-rdb-preamble yes
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 256mb

# ---------- 复制 ----------
repl-backlog-size 256mb
client-output-buffer-limit replica 1gb 256mb 60

# ---------- 日志 ----------
loglevel notice
logfile ""

port 6380
cluster-config-file nodes-6380.conf
cluster-announce-port 6380
cluster-announce-bus-port 16380

#!/bin/bash
# 一次性创建 Redis Cluster：3 主 3 从、跨机配对；幂等，可重复执行
log() { echo "[cluster-init] $(date '+%F %T') $*"; }
AUTH=(-a "$REDIS_PASSWORD" --no-auth-warning)
M1=$REDIS_M1_IP; M2=$REDIS_M2_IP; M3=$REDIS_M3_IP
MASTERS=("$M1:6379" "$M2:6379" "$M3:6379")
# 副本 主节点：副本与主节点分处不同服务器
PAIRS=("$M2:6380 $M1:6379" "$M3:6380 $M2:6379" "$M1:6380 $M3:6379")
ALL=("${MASTERS[@]}" "$M1:6380" "$M2:6380" "$M3:6380")
TIMEOUT=${CLUSTER_INIT_TIMEOUT:-1800}

cli() { local hp=$1; shift; redis-cli "${AUTH[@]}" -h "${hp%:*}" -p "${hp#*:}" "$@" | tr -d '\r'; }
cluster_field() { cli "$1" cluster info | awk -F: -v k="$2" '$1 == k { print $2 }'; }

# 1. 等待六个节点可连接
start=$(date +%s)
for n in "${ALL[@]}"; do
  until [[ "$(cli "$n" ping 2>/dev/null)" == "PONG" ]]; do
    if (( $(date +%s) - start > TIMEOUT )); then log "等待 $n 超时，请检查该节点与防火墙"; exit 1; fi
    sleep 5
  done
  log "$n 已就绪"
done

# 2. 创建三个主分片
if [[ "$(cluster_field "${MASTERS[0]}" cluster_slots_assigned)" == "16384" ]]; then
  log "槽位已全部分配，跳过创建主分片"
else
  for n in "${ALL[@]}"; do
    known=$(cluster_field "$n" cluster_known_nodes)
    keys=$(cli "$n" dbsize)
    if [[ "$known" != "1" || "$keys" != "0" ]]; then
      log "$n 不是空节点（cluster_known_nodes=$known，dbsize=$keys），中止，需人工检查"
      exit 1
    fi
  done
  log "创建主分片：${MASTERS[*]}"
  redis-cli "${AUTH[@]}" --cluster create "${MASTERS[@]}" --cluster-replicas 0 --cluster-yes || { log "创建失败"; exit 1; }
  sleep 5
fi

# 3. 按固定配对加入副本
for p in "${PAIRS[@]}"; do
  read -r replica master <<<"$p"
  if [[ "$(cluster_field "$replica" cluster_known_nodes)" != "1" ]]; then
    log "$replica 已在集群中，跳过"
    continue
  fi
  master_id=$(cli "$master" cluster myid)
  log "加入副本 $replica -> $master（$master_id）"
  redis-cli "${AUTH[@]}" --cluster add-node "$replica" "${MASTERS[0]}" \
    --cluster-slave --cluster-master-id "$master_id" || { log "加入 $replica 失败"; exit 1; }
  sleep 3
done

# 4. 等待副本同步完成并校验配对
for p in "${PAIRS[@]}"; do
  read -r replica master <<<"$p"
  for ((i = 0; i < 60; i++)); do
    info=$(cli "$replica" info replication)
    host=$(awk -F: '$1 == "master_host" { print $2 }' <<<"$info")
    link=$(awk -F: '$1 == "master_link_status" { print $2 }' <<<"$info")
    [[ "$host" == "${master%:*}" && "$link" == "up" ]] && break
    sleep 5
  done
  if [[ "$link" != "up" ]]; then log "$replica 未与 $master 完成同步"; exit 1; fi
  log "$replica 已复制 $master"
done

redis-cli "${AUTH[@]}" --cluster check "${MASTERS[0]}"
log "集群创建完成，cluster_state=$(cluster_field "${MASTERS[0]}" cluster_state)"

#!/bin/bash
# 上传到服务器后以 root 执行一次：bash prepare.sh
set -e
cd "$(dirname "$0")"

sed -i 's/\r$//' docker-compose.yml .env conf/*.conf scripts/*.sh prepare.sh
chmod 600 .env
chmod 644 docker-compose.yml conf/*.conf
chmod 755 scripts/*.sh prepare.sh

mkdir -p data/6379 data/6380
chown -R 999:999 data

cat > /etc/sysctl.d/99-redis.conf <<'EOF'
vm.overcommit_memory = 1
net.core.somaxconn = 1024
EOF
sysctl -p /etc/sysctl.d/99-redis.conf

cat > /etc/systemd/system/disable-thp.service <<'EOF'
[Unit]
Description=Disable Transparent Huge Pages for Redis
Before=docker.service

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled; echo never > /sys/kernel/mm/transparent_hugepage/defrag'

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable --now disable-thp.service

docker compose config -q
echo "准备完成，执行 docker compose up -d 启动"

rcli() { docker exec -i redis-6379 redis-cli "$@"; }

cd /srv/redis && bash prepare.sh

cd /srv/redis && docker compose up -d
docker compose ps        # redis-6379、redis-6380 为 running (healthy)

cd /srv/redis && docker compose up -d
docker logs -f redis-cluster-init

rcli cluster info | grep -E 'cluster_state|cluster_slots_assigned|cluster_known_nodes|cluster_size'
rcli cluster nodes

rcli -c set ha:test hello EX 60
rcli -c get ha:test

spring:
  redis:
    password: ${REDIS_PASSWORD}
    timeout: 2s
    connect-timeout: 2s
    cluster:
      nodes: 172.16.1.235:6379,172.16.1.244:6379,172.16.1.239:6379,172.16.1.235:6380,172.16.1.244:6380,172.16.1.239:6380
      max-redirects: 3
    lettuce:
      cluster:
        refresh:
          adaptive: true
          period: 30s

OLD_ID=<旧节点ID>
for n in 172.16.1.235:6379 172.16.1.244:6379 172.16.1.239:6379 172.16.1.235:6380 172.16.1.244:6380; do
  docker exec redis-6379 redis-cli -h "${n%:*}" -p "${n#*:}" cluster forget "$OLD_ID"
done

for p in 9201 9202; do
  firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=172.16.1.0/24 port port=$p protocol=tcp accept"
done
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=<运维网段> port port=18405 protocol=tcp accept'
firewall-cmd --reload

/srv/haproxy/                  # 三台完全相同
├── docker-compose.yml
├── .env                       # 检查账户密码、统计页账号；权限 600
├── install.sh                 # 安装脚本：校验、启动、自检
├── haproxy/haproxy.cfg
└── agent/mgr-health.py        # 9201/9202 健康检查代理

x-logging: &logging
  driver: json-file
  options:
    max-size: "100m"
    max-file: "5"

services:
  haproxy:
    image: haproxy:2.8
    container_name: haproxy
    network_mode: host
    restart: unless-stopped
    env_file: .env
    volumes:
      - ./haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    ulimits:
      nofile:
        soft: 65535
        hard: 65535
    logging: *logging

  mgr-health:
    image: mysql:5.7.38
    container_name: mgr-health
    network_mode: host
    restart: unless-stopped
    env_file: .env
    entrypoint: ["python2", "-u", "/opt/mgr-health/mgr-health.py"]
    volumes:
      - ./agent:/opt/mgr-health:ro
    logging: *logging

# 必须与 MySQL .env 中的 MGR_CHECK_PASSWORD 一致
MGR_CHECK_PASSWORD=请替换
# HAProxy 统计页账号
STATS_USER=admin
STATS_PASSWORD=请替换为强密码
# 只读端口允许的最大待应用事务积压
MGR_RO_MAX_QUEUE=100

global
    log stdout format raw local0 info
    maxconn 20000

defaults
    log global
    mode tcp
    option dontlognull
    timeout connect 3s
    timeout client 30m
    timeout server 30m
    timeout check 3s
    retries 2

# Keepalived 检查 HAProxy 自身是否存活，不依赖任何后端
frontend haproxy_alive
    bind 127.0.0.1:18404
    mode http
    monitor-uri /alive

listen stats
    bind *:18405
    mode http
    stats enable
    stats uri /stats
    stats refresh 5s
    stats auth "${STATS_USER}:${STATS_PASSWORD}"

listen app_pool
    bind 127.0.0.1:18080
    mode http
    option httplog
    timeout http-request 10s
    timeout client 300s
    timeout server 300s
    balance roundrobin
    option redispatch
    default-server inter 2s fall 3 rise 2
    # 当前为 TCP 端口检查；应用引入 actuator 后取消以下两行注释，切换为 HTTP 就绪检查
    # option httpchk GET /actuator/health/readiness
    # http-check expect status 200
    server app1 172.16.1.235:40001 check
    server app2 172.16.1.244:40001 check
    server app3 172.16.1.239:40001 check

listen mysql_rw
    bind 127.0.0.1:13306
    mode tcp
    balance first
    option tcpka
    option httpchk GET /
    http-check expect status 200
    default-server inter 2s fall 2 rise 3 on-marked-down shutdown-sessions
    server m1 172.16.1.235:3306 check port 9201
    server m2 172.16.1.244:3306 check port 9201
    server m3 172.16.1.239:3306 check port 9201

listen mysql_ro
    bind 127.0.0.1:13307
    mode tcp
    balance leastconn
    option tcpka
    option httpchk GET /
    http-check expect status 200
    default-server inter 2s fall 2 rise 3 on-marked-down shutdown-sessions
    server m1 172.16.1.235:3306 check port 9202
    server m2 172.16.1.244:3306 check port 9202
    server m3 172.16.1.239:3306 check port 9202

# -*- coding: utf-8 -*-
# MGR 健康检查代理：9201 判断本机是否为可写 PRIMARY，9202 判断本机是否为可读 SECONDARY
# 运行于 mysql:5.7.38 镜像（python2 + mysql 客户端），供 HAProxy option httpchk 调用
import os
import signal
import subprocess
import sys
import threading
import time
from BaseHTTPServer import BaseHTTPRequestHandler, HTTPServer
from SocketServer import ThreadingMixIn

MYSQL_HOST = os.environ.get('MGR_MYSQL_HOST', '127.0.0.1')
MYSQL_PORT = os.environ.get('MGR_MYSQL_PORT', '3306')
CHECK_USER = os.environ.get('MGR_CHECK_USER', 'mgr_check')
CHECK_PASSWORD = os.environ['MGR_CHECK_PASSWORD']
RW_PORT = int(os.environ.get('MGR_RW_PORT', '9201'))
RO_PORT = int(os.environ.get('MGR_RO_PORT', '9202'))
RO_MAX_QUEUE = int(os.environ.get('MGR_RO_MAX_QUEUE', '100'))
QUERY_TIMEOUT = float(os.environ.get('MGR_QUERY_TIMEOUT', '3'))

SQL = (
    "SELECT m.MEMBER_STATE,"
    " IF((SELECT VARIABLE_VALUE FROM performance_schema.global_status"
    "     WHERE VARIABLE_NAME = 'group_replication_primary_member') = @@server_uuid, 1, 0),"
    " @@super_read_only,"
    " (SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE = 'ONLINE'),"
    " IFNULL((SELECT COUNT_TRANSACTIONS_IN_QUEUE FROM performance_schema.replication_group_member_stats"
    "         WHERE MEMBER_ID = @@server_uuid), 0)"
    " FROM performance_schema.replication_group_members m WHERE m.MEMBER_ID = @@server_uuid"
)

last_result = {}
last_lock = threading.Lock()


def log(msg):
    sys.stdout.write('[mgr-health] %s %s\n' % (time.strftime('%Y-%m-%d %H:%M:%S'), msg))


def query_state():
    env = dict(os.environ, MYSQL_PWD=CHECK_PASSWORD)
    cmd = ['mysql', '-h', MYSQL_HOST, '-P', MYSQL_PORT, '-u', CHECK_USER, '--protocol=tcp',
           '--connect-timeout=2', '-N', '-B', '-e', SQL]
    proc = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, env=env)
    timer = threading.Timer(QUERY_TIMEOUT, proc.kill)
    timer.start()
    try:
        out, err = proc.communicate()
    finally:
        timer.cancel()
    if proc.returncode != 0:
        return None, 'mysql query failed: %s' % (err.strip().replace('\n', ' ') or 'timeout')
    fields = out.strip().split('\t')
    if len(fields) != 5:
        return None, 'member not in group view'
    state, is_primary, sro, online, queue = fields
    return {'state': state, 'primary': is_primary == '1', 'sro': sro == '1',
            'online': int(online), 'queue': int(queue)}, None


def check_rw(s):
    if s['state'] != 'ONLINE':
        return False, 'state=%s' % s['state']
    if not s['primary']:
        return False, 'not primary'
    if s['sro']:
        return False, 'super_read_only=ON'
    if s['online'] < 2:
        return False, 'online members=%d' % s['online']
    return True, 'primary ok'


def check_ro(s):
    if s['state'] != 'ONLINE':
        return False, 'state=%s' % s['state']
    if s['primary']:
        return False, 'is primary'
    if s['online'] < 2:
        return False, 'online members=%d' % s['online']
    if s['queue'] > RO_MAX_QUEUE:
        return False, 'applier queue=%d' % s['queue']
    return True, 'secondary ok, queue=%d' % s['queue']


def make_handler(port, checker):
    class Handler(BaseHTTPRequestHandler):
        def do_GET(self):
            state, err = query_state()
            ok, reason = (False, err) if state is None else checker(state)
            with last_lock:
                if last_result.get(port) != ok:
                    last_result[port] = ok
                    log('port %d -> %s (%s)' % (port, 'UP' if ok else 'DOWN', reason))
            body = reason + '\n'
            self.send_response(200 if ok else 503)
            self.send_header('Content-Type', 'text/plain')
            self.send_header('Content-Length', str(len(body)))
            self.send_header('Connection', 'close')
            self.end_headers()
            self.wfile.write(body)

        def log_message(self, fmt, *args):
            pass

    return Handler


class Server(ThreadingMixIn, HTTPServer):
    daemon_threads = True
    allow_reuse_address = True


def serve(port, checker):
    Server(('0.0.0.0', port), make_handler(port, checker)).serve_forever()


if __name__ == '__main__':
    signal.signal(signal.SIGTERM, lambda *args: sys.exit(0))
    rw = threading.Thread(target=serve, args=(RW_PORT, check_rw))
    rw.daemon = True
    rw.start()
    log('listening rw=%d ro=%d, mysql=%s:%s' % (RW_PORT, RO_PORT, MYSQL_HOST, MYSQL_PORT))
    serve(RO_PORT, check_ro)

#!/bin/bash
# 上传到服务器后以 root 执行：bash install.sh（可重复执行，用于更新配置后重建）
set -e
cd "$(dirname "$0")"

sed -i 's/\r$//' docker-compose.yml .env haproxy/haproxy.cfg agent/mgr-health.py install.sh
chmod 600 .env
chmod 644 docker-compose.yml haproxy/haproxy.cfg agent/mgr-health.py
chmod 755 install.sh

command -v docker >/dev/null || { echo "未安装 Docker"; exit 1; }
docker compose version >/dev/null 2>&1 || { echo "未安装 Docker Compose v2"; exit 1; }
for img in haproxy:2.8 mysql:5.7.38; do
  docker image inspect "$img" >/dev/null 2>&1 || docker pull "$img" \
    || { echo "镜像 $img 不存在且无法拉取，请离线导入"; exit 1; }
done

docker compose config -q
set -a; . ./.env; set +a
docker run --rm -e STATS_USER -e STATS_PASSWORD \
  -v "$PWD/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro" \
  haproxy:2.8 haproxy -c -f /usr/local/etc/haproxy/haproxy.cfg

docker compose up -d --force-recreate
sleep 5

echo "== 自检 =="
for p in 9201 9202; do
  printf '%s: ' "$p"
  curl -s -m 5 -w ' (HTTP %{http_code})\n' "http://127.0.0.1:$p/" || echo "无响应"
done
printf 'alive: '; curl -s -m 3 -o /dev/null -w 'HTTP %{http_code}\n' http://127.0.0.1:18404/alive || echo "无响应"
docker compose ps

# 把 HAProxy 目录上传为 /srv/haproxy 后
cd /srv/haproxy && bash install.sh

for ip in 172.16.1.235 172.16.1.244 172.16.1.239; do
  echo "$ip rw=$(curl -s -m 5 -o /dev/null -w '%{http_code}' http://$ip:9201/) ro=$(curl -s -m 5 -o /dev/null -w '%{http_code}' http://$ip:9202/)"
done

# 写端口：应返回当前 PRIMARY 的 server_id（初始为 101），super_read_only=0
docker exec -it mgr-health mysql -h127.0.0.1 -P13306 -uapp_rw -p -e "SELECT @@server_id, @@super_read_only;"
# 只读端口：多次执行应在 102、103 之间分配，super_read_only=1
docker exec -it mgr-health mysql -h127.0.0.1 -P13307 -uops_ro -p -e "SELECT @@server_id, @@super_read_only;"

MYSQL_HOST=127.0.0.1
MYSQL_PORT=13306

/srv/keepalived/               # 三台完全相同
├── .env                       # VIP、VRRP 组号、认证密码、三台 IP 与优先级、Nginx 检查开关
├── install.sh                 # 安装脚本：识别角色、安装、生成配置、放通防火墙、启动、自检
├── keepalived.conf.tpl        # 配置模板，由 install.sh 渲染为 /etc/keepalived/keepalived.conf
├── check_entry.sh             # 检查脚本，安装到 /etc/keepalived/
└── notify.sh                  # 状态切换通知脚本，安装到 /etc/keepalived/

# 业务 VIP 与掩码（占位值，部署前改为实际未占用的地址）
VIP=172.16.1.250
VIP_PREFIX=24
# VRRP 组号（1-255），同一网段内的不同 Keepalived 集群不能重复
VIRTUAL_ROUTER_ID=51
# VRRP 认证密码，最多 8 个字符，三台一致
AUTH_PASS=请替换
NODE1_IP=172.16.1.235
NODE2_IP=172.16.1.244
NODE3_IP=172.16.1.239
NODE1_PRIORITY=150
NODE2_PRIORITY=140
NODE3_PRIORITY=130
# 允许 VRRP 报文的来源网段（firewalld 规则使用）
VRRP_SOURCE_NET=172.16.1.0/24
# Nginx 部署并监听 0.0.0.0:443 后改为 true
CHECK_NGINX=false
NGINX_CHECK_URL=https://127.0.0.1/

! 由 install.sh 根据 .env 生成；修改 .env 后重新执行 install.sh
global_defs {
    router_id @ROUTER_ID@
    enable_script_security
    script_user root
    vrrp_garp_master_delay 1
    vrrp_garp_master_refresh 60
}

vrrp_script chk_entry {
    script "/etc/keepalived/check_entry.sh"
    interval 2
    timeout 3
    fall 2
    rise 2
}

vrrp_instance VI_APP {
    state BACKUP
    interface @IFACE@
    virtual_router_id @VRID@
    priority @PRIORITY@
    nopreempt
    advert_int 1
    unicast_src_ip @SRC_IP@
    unicast_peer {
@PEERS@
    }
    authentication {
        auth_type PASS
        auth_pass @AUTH_PASS@
    }
    virtual_ipaddress {
        @VIP@/@VIP_PREFIX@ dev @IFACE@
    }
    track_script {
        chk_entry
    }
    notify /etc/keepalived/notify.sh
}

#!/bin/bash
# 只检查本机入口组件自身是否存活，不检查应用或数据库
CHECK_NGINX=false
NGINX_CHECK_URL=https://127.0.0.1/
[ -f /etc/keepalived/check_entry.env ] && . /etc/keepalived/check_entry.env

curl -fsS -m 2 -o /dev/null http://127.0.0.1:18404/alive || exit 1
if [ "$CHECK_NGINX" = "true" ]; then
  # 只要 Nginx 有 HTTP 响应即视为存活，不关心状态码
  curl -ksS -m 2 -o /dev/null "$NGINX_CHECK_URL" || exit 1
fi
exit 0

#!/bin/bash
# Keepalived 状态切换通知：$1=类型 $2=实例名 $3=新状态 $4=优先级
msg="keepalived $2 -> $3 (priority $4)"
logger -t keepalived-notify "$msg"
echo "$(date '+%F %T') $msg" >> /var/log/keepalived-state.log

#!/bin/bash
# 上传到服务器后以 root 执行：bash install.sh
# 三台相同，按本机 IP 自动识别 m1/m2/m3；可重复执行（已运行时热加载配置，不释放 VIP）
set -e
cd "$(dirname "$0")"
sed -i 's/\r$//' .env keepalived.conf.tpl check_entry.sh notify.sh install.sh
[ "$(id -u)" -eq 0 ] || { echo "请以 root 执行"; exit 1; }
set -a; . ./.env; set +a
[ ${#AUTH_PASS} -le 8 ] || { echo "AUTH_PASS 最多 8 个字符"; exit 1; }

# 1. 识别本机角色、网卡与对端
LOCAL_IPS=$(ip -o -4 addr show | awk '{split($4, a, "/"); print a[1]}')
ROLE=""
for n in 1 2 3; do
  var="NODE${n}_IP"
  grep -qx "${!var}" <<<"$LOCAL_IPS" && ROLE=$n
done
[ -n "$ROLE" ] || { echo "本机 IP 不在 NODE1_IP/NODE2_IP/NODE3_IP 中，请检查 .env"; exit 1; }
src_var="NODE${ROLE}_IP"; SRC_IP=${!src_var}
prio_var="NODE${ROLE}_PRIORITY"; PRIORITY=${!prio_var}
IFACE=$(ip -o -4 addr show | awk -v ip="$SRC_IP" '{split($4, a, "/"); if (a[1] == ip) { print $2; exit }}')
PEERS=""
for n in 1 2 3; do
  [ "$n" = "$ROLE" ] && continue
  var="NODE${n}_IP"
  PEERS+="        ${!var}"$'\n'
done
PEERS=${PEERS%$'\n'}
echo "本机角色：m${ROLE}，IP=${SRC_IP}，网卡=${IFACE}，优先级=${PRIORITY}"

# 2. 安装 keepalived 与 curl
if ! command -v keepalived >/dev/null; then
  if command -v dnf >/dev/null; then dnf install -y keepalived
  elif command -v yum >/dev/null; then yum install -y keepalived
  elif command -v apt-get >/dev/null; then apt-get update && apt-get install -y keepalived
  else echo "无法识别包管理器，请手工安装 keepalived"; exit 1
  fi
fi
command -v curl >/dev/null || { echo "未安装 curl"; exit 1; }
keepalived --version 2>&1 | head -1

# 3. 生成配置与安装脚本
mkdir -p /etc/keepalived
conf=/etc/keepalived/keepalived.conf
[ -f "$conf" ] && cp -a "$conf" "$conf.bak.$(date +%Y%m%d%H%M%S)"
tpl=$(<keepalived.conf.tpl)
tpl=${tpl//@ROUTER_ID@/LB_m${ROLE}}
tpl=${tpl//@IFACE@/$IFACE}
tpl=${tpl//@VRID@/$VIRTUAL_ROUTER_ID}
tpl=${tpl//@PRIORITY@/$PRIORITY}
tpl=${tpl//@SRC_IP@/$SRC_IP}
tpl=${tpl//@PEERS@/$PEERS}
tpl=${tpl//@AUTH_PASS@/$AUTH_PASS}
tpl=${tpl//@VIP_PREFIX@/$VIP_PREFIX}
tpl=${tpl//@VIP@/$VIP}
printf '%s\n' "$tpl" > "$conf"
chmod 640 "$conf"
install -o root -g root -m 700 check_entry.sh notify.sh /etc/keepalived/
printf 'CHECK_NGINX=%s\nNGINX_CHECK_URL=%s\n' "$CHECK_NGINX" "$NGINX_CHECK_URL" > /etc/keepalived/check_entry.env
chmod 600 /etc/keepalived/check_entry.env

# 4. 防火墙放通 VRRP，SELinux 允许检查脚本发起连接
if systemctl is-active --quiet firewalld; then
  firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=${VRRP_SOURCE_NET} protocol value=vrrp accept" >/dev/null
  firewall-cmd --reload >/dev/null
  echo "firewalld 已放通 ${VRRP_SOURCE_NET} 的 VRRP"
else
  echo "firewalld 未运行：如使用其他防火墙，请手工放通 IP 协议 112（VRRP）"
fi
if command -v getenforce >/dev/null && [ "$(getenforce)" = "Enforcing" ] \
   && getsebool keepalived_connect_any >/dev/null 2>&1; then
  setsebool -P keepalived_connect_any 1
fi

# 5. 校验配置并启动
if keepalived --help 2>&1 | grep -q -- '--config-test'; then
  keepalived --config-test -f "$conf"
fi
if /etc/keepalived/check_entry.sh; then echo "检查脚本：通过"; else echo "警告：检查脚本未通过，本机将处于 FAULT，不会持有 VIP"; fi
systemctl enable keepalived >/dev/null 2>&1
if systemctl is-active --quiet keepalived; then
  systemctl reload keepalived && echo "keepalived 已热加载配置"
else
  systemctl start keepalived && echo "keepalived 已启动"
fi

# 6. 自检
sleep 5
if ip -o -4 addr show dev "$IFACE" | grep -qw "$VIP"; then
  echo "本机当前持有 VIP ${VIP}（MASTER）"
else
  echo "本机当前未持有 VIP ${VIP}（BACKUP 或 FAULT）"
fi
tail -n 3 /var/log/keepalived-state.log 2>/dev/null || true

# 把 Keepalived 目录上传为 /srv/keepalived 后
cd /srv/keepalived && bash install.sh

ip -4 addr | grep -w 172.16.1.250 || echo "未持有 VIP"
tail -n 5 /var/log/keepalived-state.log

客户端 → (NAT) → VIP 172.16.1.250:888 → 当前 MASTER 上的 Nginx
       → 本机 HAProxy 127.0.0.1:18080 → 三台应用 172.16.1.x:40001
       （本机 HAProxy 不可用时，Nginx 直连三台应用兜底）

# 三台服务器使用同一份配置；Nginx 须以 host 网络运行，才能访问本机 HAProxy 127.0.0.1:18080
# 本机 HAProxy 为主，三台应用直连为兜底：HAProxy 故障时在 VIP 漂移前继续服务
upstream app_entry {
  server 127.0.0.1:18080 max_fails=1 fail_timeout=5s;
  server 172.16.1.235:40001 backup;
  server 172.16.1.244:40001 backup;
  server 172.16.1.239:40001 backup;
}

server {
  # 监听本机所有地址，不要写成 172.16.1.250:888，否则 BACKUP 节点无法启动
  listen       888;
  server_name  _;
  root /usr/share/nginx/html/jkq;
  index index.html;
  location /api/{
    client_max_body_size 100M;
    proxy_pass   http://app_entry/;
    proxy_next_upstream error timeout;
    proxy_connect_timeout 2s;
    # 与 HAProxy 应用池 timeout server 300s 一致，避免 AI 流式输出被提前断开
    proxy_read_timeout 300s;
    proxy_send_timeout 300s;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-NginX-Proxy true;
    proxy_redirect off;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
  }
  location /gpt/ {
    proxy_pass          http://192.168.131.31:3000/api/;
    proxy_set_header    X-Real-IP $remote_addr;
    proxy_set_header    X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header    Host $host;
    proxy_set_header    X-Forwarded-Proto $scheme;
    proxy_set_header    X-NginX-Proxy true;
    proxy_redirect      off;
    proxy_http_version  1.1;
    proxy_set_header    Upgrade $http_upgrade;
    proxy_set_header    Connection "upgrade";
  }
  location /digital/ {
    proxy_pass https://jkqszr.bda.gov.cn:443/human/;
    # proxy_pass http://192.168.131.16:8099;
    proxy_ssl_verify    off;
    proxy_set_header    X-Real-IP $remote_addr;
    proxy_set_header    X-Forwarded-For $proxy_add_x_forwarded_for;
    # proxy_set_header    Host $host;
    proxy_set_header    X-Forwarded-Proto $scheme;
    proxy_set_header    X-NginX-Proxy true;
    proxy_redirect      off;
    proxy_http_version  1.1;
    proxy_set_header    Upgrade $http_upgrade;
    proxy_set_header    Connection "upgrade";
  }
  # VIP 会在三台之间漂移，三台的 /files/upload 内容必须一致
  location ^~ /upload/ {
          root /files;
  }
  location / {
      add_header Access-Control-Allow-Origin * always;
      add_header Access-Control-Allow-Headers *;
      add_header Access-Control-Allow-Methods "GET, POST, PUT, OPTIONS";
      gzip on;
      gzip_static on;
      gzip_min_length 1k;
      gzip_buffers 16 64k;
      gzip_http_version 1.1;
      gzip_comp_level 9;
      gzip_types text/plain text/javascript application/javascript image/jpeg image/gif image/png application/font-woff application/x-javascript text/css application/xml;
      gzip_vary on;
      try_files $uri /index.html;
  }
}
server {
  listen       888;
  server_name  oneapi.com;
  root /usr/share/nginx/html/jkq;
  index index.html;
  location /{
    client_max_body_size 100M;
    proxy_pass   http://192.168.131.31:3000/;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-NginX-Proxy true;
    proxy_redirect off;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
  }
}

# 三台分别执行：配置校验、首页、接口转发
nginx -t
curl -s -o /dev/null -w 'index %{http_code}\n' http://127.0.0.1:888/
curl -s -o /dev/null -w 'api %{http_code}\n' http://127.0.0.1:888/api/
# 在第四台机器上通过 VIP 访问，并在 HAProxy 统计页（18405）观察请求分布到三台应用
curl -s -o /dev/null -w 'vip %{http_code}\n' http://172.16.1.250:888/