MySQL 双主 vs 一主一从：优势分析 + 完整配置步骤，附错误解决方法

主从备份（含双主模式）梳理

一、核心概念与场景适配

主从备份是数据库高可用方案的基础，核心通过“主库写入+从库同步/读取”实现负载分担与数据冗余。不同模式适配不同业务场景：

模式 核心特点 适配场景 局限性
一主一从 主库写、从库读；单向同步，无数据冲突 读多写少（如博客、电商详情页） 主库故障需手动切换，写入易瓶颈
双主模式 双库均支持写；双向同步，需解决冲突 写压力较大（如订单、日志系统） 架构复杂，需处理ID冲突与数据一致性

二、双主模式 vs 一主一从：优劣势对比

双主模式是一主一从的进阶方案，核心优势围绕“写入可用性”与“负载分散”，但需承担更高复杂度。

1. 优势：双主模式更优的场景

  • 写入高可用性更强
    双主互为主从，任一节点故障时，另一节点可直接接管写入（无需手动提升从库），服务中断时间极短；
    一主一从需通过工具（如MHA）或手动切换主从，存在明显延迟。

  • 写入负载可分散
    可按业务拆分写入（如A业务写主库1，B业务写主库2），避免单一主库的写入瓶颈；
    一主一从所有写入依赖主库，高并发（如秒杀）易导致主库过载。

  • 维护更灵活
    维护其中一个节点（如升级、全量备份）时，可将业务切换到另一节点，无需中断服务；
    一主一从维护主库需先切换主从关系，操作繁琐。

2. 劣势：双主模式的复杂度代价

  • 架构复杂性显著提升
    双向同步可能出现“同一行数据双库同时更新”的冲突，需通过业务设计（如分表隔离）或技术手段（乐观锁、版本号）解决；
    一主一从单向同步，无冲突风险，架构简单。

  • 数据一致性风险更高
    网络延迟、节点宕机可能导致双向同步中断，若未及时处理易出现数据不一致；
    一主一从同步逻辑清晰，只要复制线程正常，从库数据与主库一致（仅微小延迟）。

  • 需额外处理自增ID冲突
    双主若均使用AUTO_INCREMENT，会因ID重复导致同步失败，需手动配置自增规则：

    • 主库1：auto_increment_increment=2，auto_increment_offset=1（ID：1,3,5…）
    • 主库2：auto_increment_increment=2，auto_increment_offset=2（ID：2,4,6…）
      一主一从无需考虑（自增ID仅主库生成）。

  • 资源消耗更高
    双主节点均需承担“写入+同步”压力，CPU、IO、网络开销更大；
    一主一从的从库仅处理读请求与同步，资源消耗低。

三、双主模式实验：Ubuntu 22.04 环境配置

实验准备

  • 两台Ubuntu 22.04服务器，IP分别为：
    • 主库1（互为主从）：192.168.40.195
    • 主库2（互为主从）：192.168.40.196
  • 核心规则：主库1用repl@192.168.40.196连接主库2，主库2用repl@192.168.40.195连接主库1。

步骤1：修改双主节点的MySQL配置文件

MySQL配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf，需重启MySQL生效（systemctl restart mysql）。

主库1（192.168.40.195）配置

1
2
3
4
5
6
7
8
[mysqld]
server-id = 1                  # 节点唯一ID，不可重复
log_bin = /var/lib/mysql/mysql-bin  # 开启二进制日志（同步依赖）
binlog_do_db = blog            # 仅同步blog库（可选，不配置则同步所有库）
auto_increment_offset = 1      # 自增ID起始值
auto_increment_increment = 2   # 自增ID步长
relay_log = /var/lib/mysql/relay-bin  # 开启中继日志（作为从库时需用）
log_slave_updates = 1          # 允许从库将同步的数据写入自身二进制日志（双向同步必需）

主库2（192.168.40.196）配置

1
2
3
4
5
6
7
8
[mysqld]
server-id = 2                  # 唯一ID，与主库1不同
log_bin = /var/lib/mysql/mysql-bin
binlog_do_db = test            # 仅同步test库（按需调整）
auto_increment_offset = 2      # 自增ID起始值（与主库1错开）
auto_increment_increment = 2   # 自增ID步长
relay_log = /var/lib/mysql/relay-bin
log_slave_updates = 1

步骤2：创建双向复制用户

需在两个主库分别创建允许对方连接的复制用户，且指定认证方式为mysql_native_password（MySQL 8.0默认caching_sha2_password，易导致复制连接失败）。

主库1（195）创建用户（允许主库2连接）

1
2
3
4
5
6
7
8
9
-- 创建复制用户repl，仅允许从192.168.40.196连接
CREATE USER 'repl'@'192.168.40.196' 
IDENTIFIED WITH mysql_native_password BY '123456';

-- 授予复制权限（仅需REPLICATION SLAVE权限）
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.40.196';

-- 刷新权限
FLUSH PRIVILEGES;

主库2（196）创建用户（允许主库1连接）

1
2
3
4
5
6
CREATE USER 'repl'@'192.168.40.195' 
IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.40.195';

FLUSH PRIVILEGES;

步骤3：配置双向主从关系

核心是让“主库2作为主库1的从库”+“主库1作为主库2的从库”，需先获取每个主库的二进制日志位置（MASTER_LOG_FILE和MASTER_LOG_POS）。

子步骤3.1：让主库2（196）成为主库1（195）的从库

  1. 获取主库1的二进制日志信息
    在主库1（195）执行以下SQL，记录File（日志文件名）和Position（日志位置）：

    1
    SHOW MASTER STATUS;

    示例结果（需以实际输出为准）：

    File Position Binlog_Do_DB
    mysql-bin.000002 898 blog

  2. 在主库2（196）配置从库关系 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    -- 停止从库进程（若已开启）
    STOP SLAVE;

    -- 配置主库信息（指向主库1）
    CHANGE MASTER TO
      MASTER_HOST = '192.168.40.195',    # 主库1的IP
      MASTER_USER = 'repl',              # 复制用户
      MASTER_PASSWORD = '123456',        # 密码
      MASTER_LOG_FILE = 'mysql-bin.000002',  # 主库1的日志文件名（从SHOW MASTER STATUS获取）
      MASTER_LOG_POS = 898;              # 主库1的日志位置（从SHOW MASTER STATUS获取）

    -- 启动从库进程
    START SLAVE;

    -- 验证从库状态（需确保Slave_IO_Running和Slave_SQL_Running均为Yes）
    SHOW SLAVE STATUS\G;

子步骤3.2：让主库1（195）成为主库2（196）的从库

  1. 获取主库2的二进制日志信息
    在主库2（196）执行：

    1
    SHOW MASTER STATUS;

    示例结果（需以实际输出为准）：

    File Position Binlog_Do_DB
    mysql-bin.000019 897 test

  2. 在主库1（195）配置从库关系 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    STOP SLAVE;

    CHANGE MASTER TO
      MASTER_HOST = '192.168.40.196',    # 主库2的IP
      MASTER_USER = 'repl',
      MASTER_PASSWORD = '123456',
      MASTER_LOG_FILE = 'mysql-bin.000019',  # 主库2的日志文件名
      MASTER_LOG_POS = 897;              # 主库2的日志位置

    START SLAVE;

    -- 验证状态（Slave_IO_Running和Slave_SQL_Running均为Yes则成功）
    SHOW SLAVE STATUS\G;

四、常见错误与解决方案

错误1：复制连接失败（认证插件问题）

  • 错误信息：
    Error connecting to source 'repl@192.168.40.195:3306'... Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
  • 原因：MySQL 8.0默认认证插件为caching_sha2_password，复制连接需安全连接，而我们未配置SSL。
  • 解决方案：
    重新创建复制用户，指定认证方式为mysql_native_password（参考步骤2的SQL）。

错误2：从库同步中断（事务执行失败）

  • 错误信息：
    Coordinator stopped because there were error(s) in the worker(s)... Worker 1 failed executing transaction...
  • 原因：主从数据不一致（如从库已存在主库要插入的数据），导致事务执行失败。
  • 解决方案：跳过当前失败事务（需后续检查数据一致性）：
    1
    2
    3
    4
    STOP SLAVE;
    SET GLOBAL sql_slave_skip_counter = 1;  # 跳过1个事务
    START SLAVE;
    SHOW SLAVE STATUS\G;  # 验证同步是否恢复

错误3：二进制日志位置错误

  • 错误信息：
    Got fatal error 1236 from source when reading data from binary log: 'Could not find first log file name in binary log index file'
  • 原因：配置从库时指定的MASTER_LOG_FILE或MASTER_LOG_POS错误（如主库日志已轮转）。
  • 解决方案：重新在主库执行SHOW MASTER STATUS获取最新日志信息，更新从库配置：
    1
    2
    3
    4
    5
    STOP SLAVE;
    CHANGE MASTER TO
      MASTER_LOG_FILE = '新的日志文件名',  # 如mysql-bin.000003
      MASTER_LOG_POS = 新的日志位置;       # 如2362
    START SLAVE;

五、验证与收尾

配置完成后，需在两个主库分别执行SHOW SLAVE STATUS\G，核心检查以下两项：

  • Slave_IO_Running：Yes（表示从库IO线程正常，能读取主库二进制日志）
  • Slave_SQL_Running：Yes（表示从库SQL线程正常，能执行中继日志中的SQL）

若两项均为Yes，则双主模式配置成功。可通过“在主库1插入数据，查看主库2是否同步”“在主库2更新数据，查看主库1是否同步”进一步验证数据一致性。