MySQL 主从复制与读写分离

type
status
date
slug
summary
tags
category
icon
password

一、MySQL 主从复制详解

1.1 主从复制核心概念

工作原理示意图：
优势与价值：
  • 高可用性：主库故障时可快速切换到从库
  • 负载均衡：将读操作分散到多个从库
  • 数据备份：实时备份数据，减少数据丢失风险
  • 数据分析：在从库执行报表查询，不影响主库性能

1.2 复制延迟分析与优化

延迟原因：
  • 网络带宽不足
  • 从库硬件性能较差
  • 大事务执行时间长
  • 从库负载过高
优化策略：

1.3 常见架构

  • 一主一从
  • 一主多从
  • 双主复制

二、主从复制实战配置

2.1 环境准备

系统要求：
  • 主从服务器时间同步
  • 关闭 SELinux 或配置适当策略
  • 防火墙开放 3306 端口

2.2 主库配置详解

2.3 创建复制账户

2.4 数据备份与同步

2.5 从库配置

2.6 启动复制进程

三、复制状态监控与维护

3.1 关键监控指标

3.2 常用监控命令

3.3 故障处理手册

1. IO 线程故障处理：
2. SQL 线程故障处理：

四、读写分离

1、读写分离介绍

  • 加快数据库工作效率
  • 在主从复制环境 ，主库执行写操作，从库执行读操作

2、实现方案

  • 开发在业务代码中固定写数据库地址
  • 数据库中间件 mysql-proxy MySQL官方 aomerba 阿里，开源 MyCAT 国内，开源 Atlas 360

五、读写分离实现（MyCAT）

5.1 应用层实现

5.2 使用 MyCAT 中间件

安装与配置：
schema.xml 配置：
server.xml 配置：

六、GTID 复制模式

6.1 GTID 优势

  • 自动识别复制位置，无需手动指定 binlog 文件和位置
  • 简化故障转移和主从切换
  • 保证数据一致性

6.2 启用 GTID 复制

6.3 配置 GTID 复制

七、双主复制架构

7.1 双主配置

  • 在旧主服务器建立允许远程连接的用户
双主复制都允许执行写操作，同时会复制给对方，为了避免由于主键冲突导致故障，可以调整自动增长的步长

7.2 双主复制实现

7.3 双主注意事项

  • 避免同时写入相同数据
  • 使用 keepalived 或 VIP 实现自动故障转移
  • 定期检查数据一致性

八、常见问题与解决方案

8.1 主从数据不一致检查

8.2 主从切换流程

8.2.1 将从服务器提升为新主
在原主库故障后，选择延迟最小的从库提升为主库。
此时，从库 192.168.140.11 被提升为新主库。
8.2.2 将业务的数据库连接切换到新主服务器
修改应用配置文件（例如 WordPress 的 wp-config.php），让业务直接连接新的主库。
在新主库上创建应用账号并授权：
应用此时已经可以连到新的主库继续提供服务。
8.2.3 将故障的主机修复完毕后，作为从库接入新主
  1. 修复旧主库
      • 确认数据库能正常启动。
      • 检查数据是否还在，建议先清理掉旧的 binlog 信息。
  1. 清理旧的复制信息
      2. 在旧主库上执行：
  1. 把旧主设置为只读
      2. （避免误写，确保它只作为从库同步）
  1. 获取新主的位点信息
      2. 在新主库执行：
      记录下：
      • File（binlog 文件名）
      • Position（binlog 位置）
  1. 在旧主配置复制信息
      2. 在旧主（现在要当从库）执行：
  1. 启动复制
    1. 检查复制状态
        2. 确认以下两项都为 Yes：
        • Slave_IO_Running: Yes
        • Slave_SQL_Running: Yes

    九、性能优化建议

    9.1 硬件优化

    • 使用 SSD 硬盘提升 IO 性能
    • 确保足够的内存容量
    • 千兆或万兆网络连接

    9.2 参数优化

    十、监控告警配置

    10.1 关键监控项

    • 复制延迟时间
    • 复制线程状态
    • 网络连接状态
    • 磁盘空间使用率

    10.2 告警规则示例

    Loading...