MYSQL主从设置

[复制链接]
发表于 昨天 22:20 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
MySQL主从复制(Master-Slave Replication)是保障企业数据库高可用(HA)、读写分离与容灾异地容灾的底层基石。传统基于文件偏移量(Binlog File + Position)的复制配置极其脆弱,在主库宕机切换或跳过错误事务时极易产生脑裂与位点错位。生产环境终极准则:1. 全面淘汰基于文件位点的传统复制,100% 启用基于 GTID(全局事务标识符)的复制拓扑;2. 针对高并发一致性要求,强制启用增强半同步复制(Lossless Semi-Sync),确保至少一个 Slave 确认收到 Relay Log 后 Master 才向客户端返回提交成功(RPO=0);3. 开启基于组提交的多线程并行复制(
  1. LOGICAL_CLOCK
复制代码
)彻底解决从库延迟痛点
。
复制技术类型核心实现机制数据一致性保障能力主从切换与运维复杂度
传统文件位点复制依赖 Master_Log_File + Master_Log_Pos低(崩溃切换时需手工计算位点差,极易丢数据)极高(极难自动化,容易踩坑)
GTID 事务复制基于 UUID:SequenceNumber 全局唯一事务标识高(自动搜索对齐位点,杜绝重复事务)极低(官方首推,原生适配自动化编排)
异步复制 (Asynchronous)Master 写本地 Binlog 后即刻返回提交有丢数据风险(Master 宕机尚未同步数据丢失)简单(但无法满足金融级灾备)
增强半同步复制Master 等待至少一个 Slave 写入 Relay Log 后提交金融级无损(RPO=0,强一致性)中等(需配置半同步插件)


一、GTID 架构下主从搭建工业级实操指南
1. 主库(Master)核心配置:
编辑
  1. /etc/my.cnf
复制代码
:

  1. [mysqld]
  2. server_id = 101
  3. log_bin = /var/log/mysql/mysql-bin
  4. binlog_format = ROW
  5. binlog_row_image = FULL

  6. -- 必须开启 GTID 强一致性参数
  7. gtid_mode = ON
  8. enforce_gtid_consistency = ON

  9. -- 崩溃恢复安全保障
  10. sync_binlog = 1
  11. innodb_flush_log_at_trx_commit = 1
复制代码

重启主库后,创建专用的同步账号:

  1. CREATE USER 'repl'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'ReplSecurePass#2026';
  2. GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';
  3. FLUSH PRIVILEGES;
复制代码


2. 从库(Slave)核心配置与并网启动:
编辑从库
  1. /etc/my.cnf
复制代码
:

  1. [mysqld]
  2. server_id = 102
  3. log_bin = /var/log/mysql/mysql-bin
  4. relay_log = /var/log/mysql/mysql-relay-bin

  5. -- 从库也必须同步开启 GTID
  6. gtid_mode = ON
  7. enforce_gtid_consistency = ON

  8. -- 从库只读保护
  9. read_only = 1
  10. super_read_only = 1

  11. -- 开启多线程并行回放,彻底杜绝主从延迟
  12. slave_parallel_type = LOGICAL_CLOCK
  13. slave_parallel_workers = 8
复制代码

重启从库,使用
  1. CHANGE REPLICATION SOURCE TO
复制代码
(或旧版
  1. CHANGE MASTER TO
复制代码
)连接主库:

  1. -- 配置基于 GTID 的自动化同步拓扑
  2. CHANGE MASTER TO
  3.     MASTER_HOST = '192.168.1.101',
  4.     MASTER_PORT = 3306,
  5.     MASTER_USER = 'repl',
  6.     MASTER_PASSWORD = 'ReplSecurePass#2026',
  7.     MASTER_AUTO_POSITION = 1;

  8. -- 启动主从复制
  9. START SLAVE;
复制代码


二、主从状态核心健康度指标校验
执行状态诊断命令:

  1. SHOW SLAVE STATUS\G;
复制代码

必须重点关注以下黄金字段:
1. Slave_IO_Running: Yes:I/O 线程正常连接主库并持续拉取 Binlog。
2. Slave_SQL_Running: Yes:SQL 线程(或协调线程)正常回放 Relay Log 事件。
3. Seconds_Behind_Master: 0:从库与主库的数据延迟秒数。
4. Retrieved_Gtid_Set 与 Executed_Gtid_Set:确认从库已拉取的 GTID 集合与已执行的 GTID 集合完全追平。

三、主从复制延迟(Replication Lag)的根治秘籍
主库高并发写入时,从库频繁出现
  1. Seconds_Behind_Master > 100
复制代码
,根本原因在于:主库并发有数十个连接多线程写,而从库若采用传统单线程回放,单个 CPU 核心处理速度必然落后于主库集群!
标准优化三板斧:
1. 开启基于组提交的逻辑时钟并行复制:
配置
  1. slave_parallel_type = LOGICAL_CLOCK
复制代码
。在主库同一批组提交落盘的事务,在从库均能由多个工作线程并发重放,回放性能提升 10 倍以上。
2. 从库临时关闭强刷盘:
从库非业务写主库,可配置
  1. innodb_flush_log_at_trx_commit = 2
复制代码
与
  1. sync_binlog = 0
复制代码
,大幅减轻从库物理磁盘 I/O 负担。
3. 监控监控大事务:
严禁在主库执行耗时超过 5 分钟的单条超长事务(如单次更新百万行),必须分批拆分执行,防范从库串行单事务回放阻塞。

常见问题(FAQ)
Q1: 为什么从库的
  1. Slave_IO_Running
复制代码
为 Connecting,一直无法变成 Yes?

A1: 常见诱因包括:1. 主库防火墙或安全组未放行 3306 端口;2. 主库的
  1. bind-address
复制代码
错误配置为 127.0.0.1 导致拒绝外部内网连接;3. 同步账号密码或授权网段错误;4. 两台服务器的
  1. server_id
复制代码
重复(MySQL 要求集群内每个实例的 server_id 绝对唯一)。

Q2: 在 GTID 模式下,如果从库发生主键冲突报错 1062 导致复制中断,如何优雅跳过该事务?
A2: 在 GTID 模式下严禁使用传统的
  1. SET GLOBAL sql_slave_skip_counter = 1
复制代码
!正确的处理方式是“空事务注入法”:在从库查看报错中断的 GTID 编号(例如
  1. 3E11FA47-71CA-11E1-9E33-C80AA9429562:23
复制代码
),在从库执行一个拥有该 GTID 的空事务欺骗系统:

  1. STOP SLAVE;
  2. SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:23';
  3. BEGIN; COMMIT;
  4. SET GTID_NEXT = 'AUTOMATIC';
  5. START SLAVE;
复制代码

系统即可优雅跨过冲突事务恢复同步。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表