MySQL主从配置详细指南

发布于：2025-06-13 ⋅ 阅读:(30) ⋅ 点赞:(0)

好的，MySQL 主从复制配置详解如下。这是一种非常核心的高可用性、读写分离和灾难恢复方案。

核心原理：

  1. 主库 (Master)：处理所有写操作（INSERT, UPDATE, DELETE, DDL）。任何数据变更都会被记录到二进制日志 (Binary Log, binlog) 中。
  2. 从库 (Slave)：启动后，连接到主库请求 binlog。从库有两个核心线程：
    • I/O Thread (Slave IO Thread)：负责连接到主库，请求 binlog 内容，并将其下载保存到本地的中继日志 (Relay Log) 中。
    • SQL Thread (Slave SQL Thread)：负责读取本地的 Relay Log，解析并执行其中记录的 SQL 语句（或基于行的变更），从而将主库的数据变更应用到从库上。
  3. 异步复制 (Asynchronous Replication)：默认模式。主库提交事务后立即响应客户端，而不等待从库确认是否已接收和应用该事务。这意味着从库数据可能存在短暂延迟。

配置步骤详解：

第一部分：准备工作

  1. 环境要求：
    • 至少两台 MySQL 服务器（物理机、虚拟机或云实例），可以是不同版本但建议主从版本一致或从库版本不低于主库。
    • 网络互通：确保主从服务器之间 TCP/IP 网络畅通，且 MySQL 端口（默认 3306）可访问。
    • 主库数据一致性：如果主库已有数据，需要确保从库在开始复制前拥有与主库一致的数据快照。通常使用 mysqldump 或 xtrabackup 进行全量备份并在从库恢复。
    • 时间同步：强烈建议使用 NTP 同步主从服务器时间，避免因时间差导致的问题。
  2. 规划：
    • 确定主库和从库的 IP 地址或主机名。
    • 为每个服务器分配唯一的 server-id（正整数）。主从必须不同！建议：主库用 1，从库用 2, 3, …。

第二部分：主库 (Master) 配置

  1. 修改主库配置文件 (my.cnf 或 my.ini):
    [mysqld]
# 启用二进制日志 (必需)
log-bin = mysql-bin   # 日志文件前缀名，可自定义
# 设置唯一的服务器ID (必需)
server-id = 1         # 主库ID，确保唯一性
# 指定需要复制的数据库 (可选，默认复制所有库。多个数据库重复此行)
# binlog-do-db = database_name1
# binlog-do-db = database_name2
# 指定不需要复制的数据库 (可选。与 binlog-do-db 冲突时后者优先)
# binlog-ignore-db = database_name_to_ignore
# 推荐：设置 binlog 格式为 ROW (数据安全，兼容性好)
binlog_format = ROW   # 可选：STATEMENT, MIXED。ROW 是推荐的生产环境设置
# 推荐：启用 GTID (Global Transaction Identifier) 简化故障转移和位置跟踪 (MySQL 5.6+)
gtid_mode = ON
enforce_gtid_consistency = ON
# 其他可选优化参数
expire_logs_days = 7  # 自动清理过期 binlog 的天数
max_binlog_size = 100M # 单个 binlog 文件大小限制
    • 保存配置文件并重启 MySQL 服务使配置生效。
  2. 在主库创建复制专用用户：
    登录主库 MySQL：
    mysql> CREATE USER 'repl'@'slave_server_ip_or_hostname' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_server_ip_or_hostname';
mysql> FLUSH PRIVILEGES;
    • 将 slave_server_ip_or_hostname 替换为从库的实际 IP 地址或主机名（确保主库能解析）。如果从库 IP 不固定或需要多个从库，可以用 %（不推荐生产）或创建多个用户。
    • 将 strong_password 替换为强密码。
  3. 获取主库当前 binlog 状态 (如果主库已有数据且未使用 GTID)：
    mysql> FLUSH TABLES WITH READ LOCK;  -- 锁定所有表，阻止写操作，确保数据一致性
mysql> SHOW MASTER STATUS;
    记录下输出结果中的 File (如 mysql-bin.000001) 和 Position (如 107) 的值。非常重要！ 稍后在从库配置时需要用到。
    mysql> UNLOCK TABLES;  -- 释放锁
    • 重要： 如果主库有数据，在 FLUSH TABLES WITH READ LOCK; 后，需要立即使用 mysqldump 或其他工具备份数据，并在从库恢复数据。恢复完成后才能执行 UNLOCK TABLES; 和后续的从库配置。如果主库是全新的，无数据，可以跳过锁定和备份步骤，SHOW MASTER STATUS; 得到的初始位置（通常是 4）即可。

第三部分：从库 (Slave) 配置

  1. 修改从库配置文件 (my.cnf 或 my.ini):
    [mysqld]
# 设置唯一的服务器ID (必需，且不同于主库和其他从库)
server-id = 2
# 启用中继日志 (可选，默认开启。自定义路径或前缀时可设置)
# relay-log = mysql-relay-bin
# 启用从库的 binlog (可选。如果此从库可能成为其他从库的主库，则必需)
# log-bin = mysql-bin
# 推荐：启用 GTID (如果主库启用了)
gtid_mode = ON
enforce_gtid_consistency = ON
# 只读模式 (强烈推荐，防止在从库误写导致数据不一致)
read_only = ON
# 可选：指定需要复制的数据库 (如果主库未过滤且从库不需要所有库)
# replicate-do-db = database_name1
# replicate-do-db = database_name2
# 可选：指定不需要复制的数据库
# replicate-ignore-db = database_name_to_ignore
# 可选：忽略复制某些表
# replicate-ignore-table = db_name.table_name
    • 保存配置文件并重启 MySQL 服务使配置生效。
  2. 恢复主库数据 (如果主库有数据)：
    • 将在主库 FLUSH TABLES WITH READ LOCK; 后备份的数据（mysqldump 文件或 xtrabackup 备份）传输到从库服务器。
    • 在从库上恢复数据：
      mysql -u root -p < master_data_dump.sql
      • 确保恢复的数据是主库在锁定那一刻的完整快照。
  3. 配置从库连接到主库并启动复制：
    登录从库 MySQL：
    • 方法一：使用 binlog File 和 Position (传统方法，如果主库未启用 GTID 或明确使用此方法)：
      CHANGE MASTER TO
  MASTER_HOST = 'master_server_ip_or_hostname',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'strong_password',
  MASTER_PORT = 3306, -- 主库MySQL端口
  MASTER_LOG_FILE = 'mysql-bin.000001', -- 替换为 SHOW MASTER STATUS 记录的文件名
  MASTER_LOG_POS = 107;                 -- 替换为 SHOW MASTER STATUS 记录的位置
    • 方法二：使用 GTID (推荐，MySQL 5.6+，主库需启用)：
      CHANGE MASTER TO
  MASTER_HOST = 'master_server_ip_or_hostname',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'strong_password',
  MASTER_PORT = 3306,
  MASTER_AUTO_POSITION = 1; -- 关键！启用基于GTID的自动定位
    • 启动复制进程：
      START SLAVE;  -- 或 START REPLICA; (MySQL 8.0.22+)
    • 检查从库复制状态：
      SHOW SLAVE STATUS\G  -- 或 SHOW REPLICA STATUS\G (MySQL 8.0.22+)
      • 关键指标解读：
        • Slave_IO_State: I/O 线程当前状态（Waiting for master to send event 表示正常连接）。
        • Slave_IO_Running: I/O 线程是否运行 (Yes 表示正常)。
        • Slave_SQL_Running: SQL 线程是否运行 (Yes 表示正常)。
        • Last_IO_Error, Last_SQL_Error: 显示最近的错误信息（正常为空）。
        • Seconds_Behind_Master: 从库落后主库的秒数（0 表示完全同步，非实时业务允许短暂延迟）。
        • (使用 GTID 时) Retrieved_Gtid_Set: 已接收的 GTID 集合。
        • (使用 GTID 时) Executed_Gtid_Set: 已执行的 GTID 集合。应与主库 SHOW MASTER STATUS; 中的 Executed_Gtid_Set 一致（或为其子集）。

第四部分：验证与监控

  1. 基本验证：
    • 在主库插入、更新或删除一条测试数据。
    • 在从库查询，检查数据是否已同步。
  2. 状态监控：
    • 定期运行 SHOW SLAVE STATUS\G 检查 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, 以及是否有错误信息。
    • 监控主库 binlog 文件和位置的增长 (SHOW MASTER STATUS;)。
    • 监控从库 Relay Log 的接收和应用情况。
  3. 工具监控：
    • MySQL Workbench 复制监控面板。
    • 第三方监控系统：Prometheus + Grafana (使用 mysqld_exporter), Zabbix, Nagios 等。
    • Percona Toolkit 中的 pt-heartbeat：精确测量复制延迟。
  4. 日志监控： 检查 MySQL 错误日志 (/var/log/mysql/error.log 或类似路径) 是否有复制相关的错误信息。

第五部分：常见问题与维护

  1. 复制中断：
    • 原因： 网络中断、主键冲突、从库写入导致数据不一致、DDL 语句不兼容、主库 binlog 被清除、版本兼容性问题等。
    • 排查： 查看 SHOW SLAVE STATUS\G 中的 Last_IO_Error 和 Last_SQL_Error。查看 MySQL 错误日志。
    • 修复 (举例)：
      • SQL 线程错误 (如主键冲突)： 手动跳过错误 (谨慎！仅用于可忽略的错误)：
        STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1; -- 跳过1个事件
START SLAVE;
        或 (如果启用了 GTID，更安全)：
        STOP SLAVE;
SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; -- 指定出错事务的GTID
BEGIN; COMMIT;                     -- 模拟一个空事务
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
      • 主库 binlog 被清除 (I/O 线程报错 Could not find first log file): 需要根据从库当前的位置重新做主库快照并重新配置复制 (或使用备份重建从库)。
      • 网络问题： 解决网络连接问题后，复制通常会自动重连恢复。
  2. 从库延迟 (Seconds_Behind_Master 持续较大)：
    • 原因： 从库硬件性能差、主库写压力过大、网络带宽不足、大事务、慢查询在从库执行慢、单线程复制瓶颈 (MySQL 5.6 前)。
    • 优化：
      • 提升从库硬件 (CPU、内存、IOPS)。
      • 优化主库慢查询。
      • 避免在主库执行超大事务。
      • 升级到 MySQL 5.7+ 使用多线程复制 (MTS: Multi-Threaded Slave)，设置 slave_parallel_workers > 1。
      • 确保从库 read_only=ON 且无其他写负载。
      • 使用 pt-query-digest 分析从库 Relay Log 找出慢查询。
  3. 日常维护：
    • 监控： 持续监控复制状态和延迟。
    • 备份： 定期备份主库和从库。
    • 版本升级： 升级前仔细阅读发行说明，测试复制兼容性。
    • 添加新从库： 过程类似上述步骤，确保新从库有唯一的 server-id，并从主库或现有从库 (级联复制) 获取一致的数据快照。
    • 故障切换 (Failover)： 主库宕机时，需要将一个从库提升为新的主库，并重新配置其他从库指向新主库。使用 GTID 可以大大简化这个过程。建议使用 MHA, Orchestrator, RDS/Aurora 高可用机制等工具自动化故障转移。

重要提示：

  • 测试！测试！测试！ 在正式环境操作前，务必在测试环境演练整个配置过程。
  • 备份！ 在修改配置文件和执行关键操作（如 CHANGE MASTER TO, RESET SLAVE）前，备份数据库和配置文件。
  • read_only： 强烈建议在从库设置 read_only=ON 防止意外写入导致复制中断和数据不一致。即使设置了 read_only，具有 SUPER 权限的用户 (如 root) 仍然可以写。如果需要完全阻止写入（包括 root），考虑使用 FLUSH TABLES WITH READ LOCK（临时）或文件系统权限（更彻底）。
  • GTID： 对于新部署，强烈推荐启用 GTID。它极大地简化了复制管理、故障转移和添加新从库的过程。
  • 安全： 保护复制用户 (repl) 的密码，限制该用户只能从特定的从库 IP 连接 (@'slave_ip')。使用 SSL 加密主从连接更安全（配置 MASTER_SSL=1 等参数）。

通过以上详细步骤和说明，你应该能够成功配置和管理 MySQL 主从复制。记住，理解原理和仔细操作是关键。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布