凌晨 01:10，部署在香港葵涌机房考务平台的遇到问题，同事在电话那头声音发紧：“成绩写入延迟飙到 2.3s 了，别再抖了，后半场要开卷。”我把保温杯往机柜上一搁，盯着监控墙上那条抖得像心电图的 QPS 折线，心里只有一个念头：这套 MariaDB 的主从链路，今天必须‘稳’到能扛考试。

下面这份是我那晚（以及后续一周）在CentOS 7 + MariaDB 10.6（社区稳定分支）的落地部署与优化笔记，按“能直接复用”的标准写的；里面有我实际踩过的坑和当场的处置办法。无论你是要赶考务高峰，还是给任何强一致、低丢失容忍的业务上保险，照着跑一遍，心里会踏实很多。

现场硬件与目标

机房与硬件

目标约束

• RPO ≈ 0（成绩不能丢）
• 事务 P95 提交延迟 ≤ 25ms（高峰 ≤ 50ms）
• 在断电/重启等异常后，主从自动恢复到一致状态

1. 系统层基线（CentOS 7）

1.1 基础与时间同步

yum install -y epel-release chrony numactl lsof jq
systemctl enable --now chronyd
timedatectl set-timezone Asia/Hong_Kong
chronyc sources -v

1.2 文件句柄与内核参数

/etc/security/limits.conf

mysql soft nofile 1048576
mysql hard nofile 1048576

/etc/sysctl.d/99-db.conf

vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 8
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_tw_reuse = 1
fs.file-max = 2097152

sysctl --system

1.3 磁盘挂载（NVMe，避免写放大）

/etc/fstab 中为数据盘启用 noatime,nodiratime,discard（NVMe 支持在线 TRIM）：

UUID=<data-uuid>  /var/lib/mysql  xfs  noatime,nodiratime,discard  0 0

2. 安装 MariaDB 10.6 与备份工具

2.1 官方源

/etc/yum.repos.d/MariaDB.repo

# MariaDB 10.6 on CentOS 7
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.6/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum clean all && yum makecache fast
yum install -y MariaDB-server MariaDB-client MariaDB-backup
systemctl enable mariadb

3. 主库配置（GTID + 行级日志 + 崩溃安全）

/etc/my.cnf.d/server.cnf（主库）

[mysqld]
# 基础
server_id = 1001
bind-address = 0.0.0.0
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
skip_name_resolve = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default_time_zone = '+08:00'

# InnoDB
innodb_buffer_pool_size = 40G
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0

# 连接与临时
max_connections = 2000
table_open_cache = 8000
tmp_table_size = 256M
max_heap_table_size = 256M

# 二进制日志（行级，崩溃安全）
log_bin = mariadb-bin
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
expire_logs_days = 7

# GTID（MariaDB）
gtid_domain_id = 1
gtid_strict_mode = 1
log_slave_updates = 1

# 从库崩溃恢复友好
relay_log_recovery = 1

# （可选）半同步，在 SQL 里再启用变量
# plugin-load-add = rpl_semi_sync_master
# rpl_semi_sync_master_enabled = 1
# rpl_semi_sync_master_timeout = 1000

启动与初始化：

systemctl start mariadb
mysql_secure_installation

创建业务库与账号（示例：exam_db）：

CREATE DATABASE IF NOT EXISTS exam_db DEFAULT CHARSET=utf8mb4;
CREATE USER 'exam_rw'@'10.%' IDENTIFIED BY 'StrongP@ss!';
GRANT ALL PRIVILEGES ON exam_db.* TO 'exam_rw'@'10.%';
FLUSH PRIVILEGES;

创建复制账号（仅复制权限）：

CREATE USER 'repl'@'10.%' IDENTIFIED BY 'ReplP@ss!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.%';
FLUSH PRIVILEGES;

4. 从库配置（只读 + 并行复制）

/etc/my.cnf.d/server.cnf（从库）

[mysqld]
server_id = 2001
bind-address = 0.0.0.0
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip_name_resolve = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default_time_zone = '+08:00'

innodb_buffer_pool_size = 40G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
innodb_flush_neighbors = 0

read_only = ON
# MariaDB 的并行复制
slave_parallel_threads = 4
slave_parallel_mode = optimistic

log_bin = mariadb-bin
binlog_format = ROW
log_slave_updates = 1
sync_binlog = 1
relay_log = relay-bin
relay_log_recovery = 1

gtid_domain_id = 2
gtid_strict_mode = 1

systemctl start mariadb

5. 首次数据装载（用 mariabackup 做“冷静一致”快照）

经验：考试业务要求“绝不丢”，不要靠 mysqldump --single-transaction 直接在高写入高峰切，只需 5–10 分钟窗口选择低谷，用 mariabackup 做热备份一致性快照，恢复到从库，再切复制。

在主库：

mariabackup --backup \
  --target-dir=/backup/base-$(date +%F-%H%M) \
  --user=root --password='rootpass'
mariabackup --prepare --target-dir=/backup/base-2025-09-21-0100

把目录通过专线/rsync 传到从库：

rsync -aH --numeric-ids /backup/base-2025-09-21-0100/ root@db-slave-01:/backup/base/

在从库（停库、回拷、改权限、启动）：

systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/base
chown -R mysql:mysql /var/lib/mysql
restorecon -Rv /var/lib/mysql   # SELinux 开着的话
systemctl start mariadb

6. 建立 GTID 复制（MariaDB 语法）

在从库执行：

-- 只复制考试库（按需）
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='10.10.10.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='ReplP@ss!',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=3,
  MASTER_USE_GTID=current_pos;  -- MariaDB 的 GTID 开关
START SLAVE;

SHOW SLAVE STATUS\G

确认：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Using_Gtid: Current_Pos

半同步（可选但强烈推荐，用于降低 RPO）

不同版本插件名略有差异，先探测：

SHOW PLUGINS LIKE '%semi%';

若未启用，可：

-- 在主库
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master';
SET GLOBAL rpl_semi_sync_master_enabled=ON;
SET GLOBAL rpl_semi_sync_master_timeout=1000; -- 1s 等待从库 ACK

-- 在从库
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave';
SET GLOBAL rpl_semi_sync_slave_enabled=ON;

注：半同步会把单次事务延迟提高到主→从往返 RTT 的量级。我们在香港同城机房 RTT ≈ 0.3–0.6ms，可接受；跨境/跨地域请谨慎。

7. 业务侧“防丢分三板斧”

行级二进制日志 + 强刷新
binlog_format=ROW + sync_binlog=1 + innodb_flush_log_at_trx_commit=1
→ 写入在提交点必落盘，恢复能重放，不怕系统重启。

只写主、严控直连
考试系统所有写入都经 HAProxy/ProxySQL 指向主库；从库 read_only=ON，巡检拒绝直连写。

GTID + 半同步（可选）
极端情况下主库 crash 前的已提交事务，半同步保证至少一个从库已接收，RPO 接近 0。

8. 连接池与代理（可选强烈建议）

HAProxy 方案（简单稳健）

/etc/haproxy/haproxy.cfg 片段：

frontend mysql_3306
  bind *:3306
  mode tcp
  default_backend mysql_back

backend mysql_back
  mode tcp
  option mysql-check user haproxy_check
  balance source
  server dbm 10.10.10.11:3306 check inter 1000 rise 2 fall 3
  server dbs 10.10.10.12:3306 check backup

业务写访问 HAProxy 的 3306，主挂了自动切到从（切写前别忘了提权，见第 11 节）

9. 参数校准清单（关键项与建议值）

10. 校验与巡检脚本

10.1 延迟与状态巡检

mysql -e "SHOW GLOBAL STATUS LIKE 'Seconds_Behind_Master';"
mysql -e "SHOW SLAVE STATUS\G" | egrep 'Running|Using_Gtid|Seconds_Behind'

10.2 事务与表一致性抽查（低峰执行）

库级：CHECKSUM TABLE exam_db.score_table;

跨库：用 Percona Toolkit 的 pt-table-checksum / pt-table-sync（工具箱可选）

10.3 压测回放（业务 SQL 最小集）

-- 在测试库模拟：每次交卷一条评分记录
INSERT INTO exam_db.score (paper_id, user_id, score, ts)
VALUES (?,?,?,NOW());

上生产前用脚本回放 10–15 分钟，观测提交 P95。

11. 故障与演练：我当晚遇到的两个坑

坑 A：从库宕机后 Relay Log 异常，SQL_Running=No

症状：Last_SQL_Error: Could not parse relay log event entry

现场处置（从库）：

STOP SLAVE;
SET GLOBAL relay_log_recovery=ON;
START SLAVE;
SHOW SLAVE STATUS\G

原因：从库断电导致 relay log 尾部损坏。开启 relay_log_recovery 会抛弃未提交的 relay，直接根据主库位点重新抓，不影响已提交成绩。

坑 B：切主时业务还连着旧主，成绩“看似丢了”

症状：HAProxy 已切到从库，应用某些实例里还有直连旧主的连接池。

处置：

在旧主执行：SET GLOBAL read_only=ON;

代理层限流 + 断开旧主会话：KILL USER 'exam_rw'（或重启连接池）

清册：我们做了一个 CMDB 巡检，把所有连接串统一收敛到代理 VIP。

12. 一键提权切写（主挂后把从升为主）

这一步请先演练，非演练时再执行。

在从库（将成新主）：

STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only=OFF;
-- 可选：提升 GTID 域
SET GLOBAL gtid_domain_id=1;

在代理（HAProxy/Keepalived）把流量切到新主。

在原主修复后，用 mariabackup 重新作为新从库加入（回到第 5–6 节流程）。

13. 备份与恢复演练（把“后悔药”准备好）

全量：每日 02:30 用 mariabackup --backup 到本地 + 异地对象存储（7 天保留）

增量：每 4 小时一次 --incremental

binlog 归档：expire_logs_days=7 + mysqlbinlog 推送对象存储

恢复演练：每周在沙箱还原一次，并回放 30 分钟高峰期 binlog，核对成绩表行数与抽样内容

14. 安全与审计

账户分权：exam_rw 仅库级权限；repl 仅复制；管理操作走 root@mgmt 网段

审计日志（选）：MariaDB Audit Plugin，记录 DDL/特权操作

防误删：read_only=ON 的从库，所有 DDL 都禁止，定期核对 information_schema.tables 差异

15. 性能观测基线（我们现场实测）

16. 交付前的最终 Checklist

•  主从 SHOW SLAVE STATUS\G 无错误，Seconds_Behind_Master 稳定
•  半同步启用且 ACK 正常（如采用）
•  代理与连接池仅指向 VIP，无直连数据库实例
•  mariabackup 全量 + 最近一次增量可在沙箱成功还原
•  binlog 归档能检索到上一个考场窗口的所有文件
•  CMDB/告警订阅：复制中断、延迟 > 3s、磁盘剩余 < 20%、P95 > 50ms

那天夜里 03:05，主从延迟回到 0.4s，提交 P95 又落在 20ms 左右。我在走廊尽头看着自动售货机的灯箱发呆，想起去年某场考试里丢掉的那几条成绩所带来的追责和返工。数据库不是神奇的黑盒，它只是诚实地执行你设定的每一次“落盘”和“确认”。

这一次，我把所有“确认”都摆上了台面：ROW 级 binlog、sync_binlog=1、innodb_flush_log_at_trx_commit=1、GTID、（可选）半同步、relay_log_recovery，再加上可以随时复盘的 mariabackup。当你能从容地拔掉电源再插回去，依然保证考生的每一分都在，那一刻，机器也会回以温柔。

愿这份实操手记，能让你在真正的“考场”上，少颤一分，稳一点。

附：建表与索引示例（成绩表）

CREATE TABLE exam_db.score (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  paper_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  score DECIMAL(6,2) NOT NULL,
  submit_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_paper_user (paper_id, user_id),
  KEY idx_submit_at (submit_at)
) ENGINE=InnoDB ROW_FORMAT=Dynamic;

附：最小化读写分离示例（ProxySQL 选）

-- 假设 ProxySQL 已接到两个 backend：主、从
-- 写流量
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (10, 1, '^\\s*(INSERT|UPDATE|DELETE|REPLACE|BEGIN|COMMIT)', 10, 1);
-- 读流量
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (20, 1, '^\\s*(SELECT)', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

如果你已经在跑生产但还没做演练，也可以把你当前的 my.cnf 发我（敏感信息打码），我按上面的基线帮你“对表排雷”，把容易踩的坑提前堵上。