MySQL

MySQL 生产部署全套重点

·14 分钟阅读·5435 字
MySQL实操高阶场景题
📋 目录

MySQL 生产部署全套重点

核心概念 MySQL 生产部署涵盖硬件规划、系统内核优化、主从/MGR/容器 K8s 全覆盖，是运维落地核心文档。

1. 部署前环境规划（最容易踩坑前置项）

1.1. 服务器硬件规划

  1. 磁盘（优先级最高）

  • 生产强制使用SSD/NVMe，禁止机械盘；机械盘IO会直接造成主从延迟、SQL卡顿；

  • 磁盘拆分规范（多块盘分离，互不抢占IO）：

  • 盘1：系统盘 /

  • 盘2：数据目录 datadir（ibd表空间）

  • 盘3：redo log 独立高速盘

  • 盘4：binlog、备份文件

  • 关闭磁盘atime：mount -o noatime，减少磁盘写入损耗；

  • IO调度算法：SSD使用none/deadline，机械盘deadline。

  1. 内存分配总内存 50%~70% 分配给 innodb_buffer_pool_size；
    剩余内存留给系统、连接、redo buffer、排序临时缓存。
  2. CPU
    高并发写场景优先高主频CPU；多核心提升并行回放、并发查询；
    容器环境绑定CPU核，避免多实例争抢CPU抖动。
  3. 网络
    主从节点内网万兆网卡；关闭防火墙不必要拦截，开放3306、半同步通信端口；
    跨机房主从需评估网络延迟，延迟过高会放大主从滞后。

1.2. 系统内核部署优化（装机必须配置）

  1. 内存相关
# 禁止使用swap，一旦swap MySQL性能雪崩
vm.swappiness = 1
# 关闭透明巨页THP，会产生大量内存碎片
echo never > /sys/kernel/mm/transparent_hugepage/enabled
# 内存overcommit放开，防止mysqld fork子进程OOM
vm.overcommit_memory = 1
  1. 文件句柄 & 进程数
# my.cnf配套调高，大量连接不报错
ulimit -n 65535
ulimit -u 65535
  1. 网络内核
    调大 tcp_tw_reuse、tcp_syncookies，解决短连接TIME_WAIT堆积；
    调大tcp-backlog，支撑上万并发连接。
  2. 挂载参数
    数据盘挂载增加 barrier=0、noatime 提升IO性能。

1.3. 版本选型规范

  1. 线上稳定版：MySQL 8.0.30+ LTS，长期支持，修复大量锁、并行复制bug；
  2. 禁止5.6及以下老旧版本，无安全补丁、并行复制弱；
  3. 统一集群所有节点小版本，禁止跨大版本混部；
  4. 预评估升级路径，8.0支持instant DDL、writeset并行复制，运维成本大幅降低。

2. my.cnf 核心部署配置重点（分模块）

2.1. 基础全局配置

[mysqld]
port=3306
user=mysql
# 数据、日志分盘路径
datadir=/data/mysql
socket=/tmp/mysql.sock
pid-file=/tmp/mysqld.pid
# 字符集统一utf8mb4，支持emoji
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 时区统一，避免时间错乱
default-time_zone='+8:00'
# 最大连接数，根据业务调整
max_connections=4000
# 关闭DNS反向解析，加速客户端连接
skip-name-resolve
# 禁止本地文件导出导入，安全加固
secure-file-priv=/tmp

2.2. InnoDB 核心（部署重中之重）

# 缓冲池，服务器内存50%-70%
innodb_buffer_pool_size=32G
# 多实例拆分缓冲池，减少锁竞争
innodb_buffer_pool_instances=8
# 独立表空间，每个表单独ibd文件，方便迁移、删表释放空间
innodb_file_per_table=1
# 事务隔离级别 RR
transaction-isolation=REPEATABLE-READ
# redo日志配置，4组，单文件4G，平衡性能与崩溃恢复速度
innodb_log_file_size=4G
innodb_log_files_in_group=4
innodb_log_buffer_size=256M
# 安全策略：每次事务提交刷盘，崩溃最多丢0条数据
innodb_flush_log_at_trx_commit=1
# 脏页刷盘控制，避免集中刷盘IO尖峰
innodb_max_dirty_pages_pct=60
innodb_io_capacity=2000
# 打开惰性删除，drop大表不阻塞业务
innodb_lazy_drop_table=ON
# 死锁检测开启
innodb_deadlock_detect=ON
# 支持在线DDL
innodb_ddl_log=1

2.3. Binlog 主从复制部署标配

# 开启二进制日志
log_bin=/log/binlog/mysql-bin
# 日志过期自动清理，避免磁盘打满
expire_logs_days=7
# 行模式，保证主从数据一致，生产强制row
binlog_format=ROW
# 每次binlog同步刷盘，保障崩溃不丢binlog
sync_binlog=1
# GTID开启，主从切换、重建必备
gtid_mode=ON
enforce_gtid_consistency=ON
# 并行复制（8.0 writeset，大幅降低主从延迟）
binlog_transaction_dependency_tracking=WRITESET
# 半同步复制（至少一台从库接收binlog才返回写入成功，防丢数据）
plugin-load-add=rpl_semi_sync_master.so
plugin-load-add=rpl_semi_sync_slave.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

2.4. 慢查询日志（部署必须开启，故障排查核心）

slow_query_log=ON
slow_query_log_file=/log/slow/slow.log
# 超过100ms判定慢SQL
long_query_time=0.1
# 记录未走索引SQL
log_queries_not_using_indexes=ON
# 每分钟轮写日志，防止单个文件过大
log_slow_admin_statements=ON

2.5. 安全加固配置（部署初始化必加）

  1. 禁用本地文件读写、远程root；
  2. 关闭 local_infile=0；
  3. 开启审计日志（企业安全要求）；
  4. 限制skip-networking内网环境可选，公网实例禁止。

3. 初始化部署流程重点

3.1. 目录权限初始化

所有数据、日志目录归属 mysql:mysql，权限700，禁止其他用户访问；

chown -R mysql:mysql /data/mysql /log/binlog /log/slow
chmod 700 /data/mysql

3.2. 初始化数据库

8.0废弃mysqld_safe —initialize-insecure，分两种模式：

# 生成随机初始密码（生产推荐）
mysqld --initialize --datadir=/data/mysql
# 无密码（仅测试，线上禁用）
mysqld --initialize-insecure

初始化后记录日志内临时密码，立刻修改root密码。

3.3. 安全初始化（部署完成第一时间操作）

  1. 修改root强密码，禁止弱口令；
  2. 删除匿名用户、删除test库；
  3. 限制root仅本地登录，新建业务专用账号；
  4. 账号最小权限：业务库仅SELECT/INSERT/UPDATE/DELETE，禁止DROP/ALTER；
  5. 远程账号限制访问源IP，不允许%全网段访问。
    示例安全脚本：
ALTER USER 'root'@'localhost' IDENTIFIED BY '强密码';
DELETE FROM mysql.user WHERE user='';
DROP DATABASE IF EXISTS test;
CREATE USER biz@'10.0.%' IDENTIFIED BY '业务密码';
GRANT SELECT,INSERT,UPDATE,DELETE ON biz_db.* TO biz@'10.0.%';
FLUSH PRIVILEGES;

4. 主从架构部署核心要点（企业主流）

4.1. 从库my.cnf差异化配置

# 唯一server-id，集群所有节点不能重复
server-id=2
# 只读，业务禁止写入从库
read_only=ON
super_read_only=ON
# 中继日志存储独立盘
relay_log=/log/relay/relay-bin
# 从库崩溃不丢失中继日志
relay_log_recovery=ON
# 不记录从库查询binlog（级联复制才开启log_slave_updates）
log_slave_updates=OFF

4.2. 主从搭建标准流程

  1. 主库加全局读锁，导出全量物理备份（xtrabackup推荐，mysqldump适合小库）；
  2. 将备份拷贝至从库，恢复数据；
  3. 从库配置change master，基于GTID自动同步，无需手动定位binlog位点；
  4. 开启slave，检查 show slave status\G 两个Yes：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
  5. 校验主从数据一致性（pt-table-checksum定期巡检）。

4.3. 主从部署避坑

  1. 主从server-id必须唯一；
  2. 字符集、时区、sql_mode全集群统一，否则数据不一致；
  3. 大库不要用mysqldump线上搭建，锁表阻塞业务；
  4. 从库硬件不能弱于主库，否则回放跟不上产生持续延迟。

5. MGR组复制高可用部署重点（金融/强一致业务）

  1. 集群节点数量3个奇数，一主两从标准架构；
  2. 所有节点开启GTID、binlog、行格式；
  3. 网络互通，放开MGR专用通信端口33061；
  4. 单主模式部署，自动故障切换，配合ProxySQL做读写分离；
  5. 避免大事务，MGR大事务会阻塞集群同步。

6. K8s容器化MySQL部署特殊重点（StatefulSet）

  1. 必须使用StatefulSet，固定网络域名、独立PVC持久存储；
  2. 存储使用高性能CSI块存储，禁止共享存储NAS（IO差）；
  3. 资源严格limit，禁止抢占宿主机内存、CPU；
  4. 容器内彻底关闭swap，内核参数提前注入；
  5. 数据、binlog拆分不同PVC；
  6. 启动脚本自动初始化、密码管理使用Secret，不硬编码配置；
  7. 禁止多实例混部，单Pod单MySQL进程；
  8. 定时离线备份，不能依赖PVC做唯一数据备份。

7. 部署配套运维能力（上线必须同步搭建）

  1. 备份体系
  • 物理全量备份xtrabackup，每日凌晨低峰执行；
  • binlog持续归档，保留7天以上，支持时间点恢复；
  • 备份文件同步至异地存储，每周执行一次恢复演练。
  1. 监控部署
    监控指标：连接数、慢查询、Buffer Pool命中率、磁盘IO、主从Seconds_Behind_Master、锁等待、磁盘剩余空间；
    告警：主从延迟、磁盘使用率、实例宕机、慢SQL突增、备份失败。
  2. 权限审计
    定期清理闲置账号，定期巡检弱密码，记录DDL变更日志。
  3. 定时巡检脚本
    碎片率、主从一致性、undo/redo占用、长事务查杀。

8. 部署上线前校验清单（上线前必查）

  1. 磁盘是否分离data/redo/binlog，挂载参数noatime；
  2. 内核swap、THP已关闭；
  3. my.cnf buffer_pool、redo、binlog、半同步配置齐全；
  4. GTID、行binlog、慢查询日志开启；
  5. 系统ulimit文件句柄调大；
  6. root密码修改，匿名用户、test库清理；
  7. 业务账号最小权限，限制访问IP；
  8. 主从Slave_IO/SQL双Yes，无延迟；
  9. 定时备份脚本可正常执行，备份文件可恢复；
  10. 监控告警接入完成，磁盘、主从、宕机告警打通；
    11 服务器防火墙仅开放3306业务内网IP。

9. 部署常见致命坑汇总

  1. data、redo、binlog共用一块磁盘，写入峰值互相抢占IO；
  2. 未关闭THP，内存碎片暴涨，频繁卡顿；
  3. sync_binlog=0、innodb_flush_log_at_trx_commit=2，宕机大量丢数据；
  4. 主从sql_mode、字符集不一致，同步报错数据错乱；
  5. 未开启半同步，机房断电主库宕机，新从库缺少binlog丢数据；
  6. expire_logs_days未配置，binlog无限膨胀打满磁盘实例锁死；
  7. 容器使用Deployment，Pod重建丢失数据；
  8. 业务账号赋权%任意IP，存在安全风险；
  9. 内存分配过大占用系统内存，触发swap。
返回首页订阅更新