MySQL数据库双机热备配置方法详解

一、双机热备概述

双机热备（High Availability, HA）是保障数据库系统高可用性的重要方案，其核心目标是实现主备服务器之间的实时数据同步与故障自动切换，确保在主库发生故障时，备库能够迅速接管服务，最大限度减少业务中断时间。本文将围绕 基于主从复制（Master-Slave Replication）的热备架构，结合 Keepalived 实现 VIP 漂移 的典型方案，详细讲解配置步骤与关键注意事项。

二、核心原理与架构设计

1. 基础架构组成

典型的双机热备系统由以下组件构成：

主库（Master）：处理所有写操作（INSERT/UPDATE/DELETE）和部分读操作，数据变更通过二进制日志（binlog）同步到备库。
备库（Slave）：实时同步主库的 binlog 数据（通过 I/O 线程接收 binlog，SQL 线程重放事件），默认仅处理读操作（可通过配置分担主库读压力）。当主库故障时，备库需快速提升为主库。
同步机制：基于 MySQL 的 主从复制（Replication），依赖二进制日志（binlog）和中继日志（relay log）实现数据增量同步。
故障切换工具：通过 Keepalived 监控主库状态，当主库不可用时，自动将虚拟 IP（VIP）漂移到备库，使客户端无感知地继续访问服务。

注意：严格来说，”双机热备”通常指 一主一备 的高可用架构（主库对外提供服务，备库实时同步并待命切换），而非严格意义上的 “双主”（双节点互为主从，均处理读写，但可能引发数据冲突）。本文以一主一备的热备方案为主。

三、环境准备（以 Linux + MySQL 5.7/8.0 为例）

1. 服务器规划

角色	IP地址	主机名	说明
主库（Master）	192.168.1.100	mysql-master	处理写操作，同步数据到备库
备库（Slave）	192.168.1.101	mysql-slave	同步主库数据，待命切换
虚拟IP（VIP）	192.168.1.200	–	客户端通过 VIP 访问服务

说明：两台服务器需处于同一局域网，操作系统建议为 CentOS 7+/Ubuntu 18.04+，MySQL 版本建议一致（如均为 5.7 或 8.0）。

2. 软件依赖

MySQL 5.7 或 8.0（需开启二进制日志功能）。
Keepalived（用于 VIP 漂移，Linux 系统自带安装包）。
确保两台服务器网络互通（关闭防火墙或开放 3306、VIP 相关端口）。

四、详细配置步骤

步骤 1：主库（Master）配置

1.1 修改 MySQL 配置文件（my.cnf/my.ini）

编辑主库的 MySQL 配置文件（通常位于 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf），添加或修改以下参数：

[mysqld]
# 唯一标识符（必须唯一，主备库不能重复）
server-id = 1

# 开启二进制日志（必须）
log-bin = mysql-bin

# 可选：指定 binlog 格式（推荐 ROW 格式，避免语句级复制的潜在问题）
binlog-format = ROW

# 可选：限制 binlog 保留时间（单位：天，默认不清理）
expire-logs-days = 7

# 可选：指定需要同步的数据库（若只同步特定库，取消注释并替换 your_db）
# binlog-do-db = your_db

# 可选：排除不需要同步的数据库（如 mysql 系统库）
# binlog-ignore-db = mysql

1.2 重启 MySQL 服务

# CentOS/RHEL
systemctl restart mysqld

# Ubuntu/Debian
systemctl restart mysql

1.3 创建复制专用账号

-- 登录 MySQL（root 用户）
mysql -u root -p

-- 创建复制用户（用户名 repl_user，密码 your_password，允许从 192.168.1.101 访问）
CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'your_password';

-- 授权复制权限（REPLICATION SLAVE 允许读取 binlog）
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

-- 刷新权限
FLUSH PRIVILEGES;

注意：若备库 IP 可能变动，可将 '192.168.1.101' 替换为 '%'（允许所有 IP，但安全性降低）。

1.4 查看主库当前 binlog 状态

记录主库当前的 binlog 文件名（如 mysql-bin.000001）和位置（如 154），后续备库同步时需要此信息：

SHOW MASTER STATUS;

示例输出：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

关键字段：File（当前 binlog 文件名）、Position（当前 binlog 位置），后续配置备库时需用到。

步骤 2：备库（Slave）配置

2.1 修改 MySQL 配置文件

编辑备库的 MySQL 配置文件，添加或修改以下参数：

[mysqld]
# 唯一标识符（与主库不同，例如 2）
server-id = 2

# 开启中继日志（必须，用于存储从主库接收的 binlog 事件）
relay-log = mysql-relay-bin

# 可选：指定需要同步的数据库（与主库的 binlog-do-db 对应）
# replicate-do-db = your_db

# 可选：禁止备库自动写入（避免数据冲突，热备模式下备库通常只读）
read-only = ON

# 可选：忽略备库自身的更新（防止误操作影响同步）
super-read-only = ON

2.2 重启 MySQL 服务

# CentOS/RHEL
systemctl restart mysqld

# Ubuntu/Debian
systemctl restart mysql

2.3 配置主从同步关系

登录备库 MySQL，执行 CHANGE MASTER TO 命令，指定主库的连接信息（IP、端口、账号、密码、binlog 文件名和位置）：

-- 登录 MySQL（root 用户）
mysql -u root -p

-- 配置主从同步（关键参数）
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',      -- 主库 IP
    MASTER_USER='repl_user',          -- 主库创建的复制账号
    MASTER_PASSWORD='your_password',  -- 复制账号密码
    MASTER_PORT=3306,                 -- 主库端口（默认 3306）
    MASTER_LOG_FILE='mysql-bin.000001', -- 主库当前的 binlog 文件名（通过 SHOW MASTER STATUS 获取）
    MASTER_LOG_POS=154;               -- 主库当前的 binlog 位置

-- 启动备库的同步线程（I/O 线程和 SQL 线程）
START SLAVE;

-- 查看同步状态（重点关注 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes）
SHOW SLAVE STATUS\G;

2.4 检查同步状态

执行 SHOW SLAVE STATUS\G 后，重点关注以下字段：

Slave_IO_Running: Yes：表示备库的 I/O 线程正常，正在从主库接收 binlog。
Slave_SQL_Running: Yes：表示备库的 SQL 线程正常，正在重放接收到的 binlog 事件。
Seconds_Behind_Master: 0（或较小数值）：表示备库数据与主库的延迟秒数（0 表示完全同步）。

若出现异常（如 No 或较大延迟），需根据 Last_IO_Error 或 Last_SQL_Error 字段的报错信息排查问题（常见原因包括网络不通、账号权限不足、binlog 文件名/位置错误等）。

步骤 3：验证主从同步

3.1 在主库写入测试数据

在主库执行插入操作，验证备库是否能同步数据：

-- 登录主库 MySQL
mysql -u root -p

-- 创建测试库和表（可选）
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY, name VARCHAR(50));

-- 插入数据
INSERT INTO test_table VALUES (1, 'Master_Data');

3.2 在备库查询数据

登录备库 MySQL，检查是否同步到相同数据：

-- 登录备库 MySQL
mysql -u root -p

-- 使用测试库
USE test_db;

-- 查询数据（应能看到主库插入的记录）
SELECT * FROM test_table;

若备库能查询到主库插入的数据，则说明主从同步配置成功。

步骤 4：配置 Keepalived 实现 VIP 漂移（故障自动切换）

4.1 安装 Keepalived

在主库和备库上均安装 Keepalived（以 CentOS 为例）：

yum install keepalived -y

4.2 配置主库的 Keepalived（/etc/keepalived/keepalived.conf）

主库作为 VIP 的初始持有者，配置如下：

vrrp_instance VI_1 {
    state MASTER          # 当前节点角色（MASTER 表示主节点）
    interface eth0        # 绑定网卡（根据实际网卡名调整，如 ens33）
    virtual_router_id 51  # 虚拟路由 ID（主备库必须相同，范围 1-255）
    priority 100          # 优先级（主库高于备库，例如主库 100，备库 90）
    advert_int 1          # 心跳检测间隔（秒）

    authentication {
        auth_type PASS    # 认证类型
        auth_pass 123456  # 认证密码（主备库必须相同）
    }

    virtual_ipaddress {
        192.168.1.200/24  # 虚拟 IP（VIP），子网掩码根据实际网络调整
    }
}

4.3 配置备库的 Keepalived（/etc/keepalived/keepalived.conf）

备库作为 VIP 的备用持有者，配置如下：

vrrp_instance VI_1 {
    state BACKUP          # 当前节点角色（BACKUP 表示备节点）
    interface eth0        # 绑定网卡（与主库一致）
    virtual_router_id 51  # 虚拟路由 ID（与主库相同）
    priority 90           # 优先级（低于主库，例如 90）
    advert_int 1          # 心跳检测间隔（秒）

    authentication {
        auth_type PASS
        auth_pass 123456  # 与主库相同的密码
    }

    virtual_ipaddress {
        192.168.1.200/24  # 与主库相同的 VIP
    }
}

4.4 启动 Keepalived 服务

在主库和备库上启动 Keepalived 并设置开机自启：

systemctl start keepalived
systemctl enable keepalived

4.5 验证 VIP 漂移

初始状态：通过 ip a 命令查看，VIP（192.168.1.200）应绑定在主库（server-id=1）的网卡上。
模拟主库故障：手动停止主库的 MySQL 或 Keepalived 服务（例如 systemctl stop mysqld 或 systemctl stop keepalived），观察备库是否自动接管 VIP（通过 ip a 检查 VIP 是否出现在备库网卡上）。
恢复主库：重启主库服务后，VIP 会自动漂移回主库（因主库优先级更高）。

注意：Keepalived 默认通过 VRRP 协议检测节点存活状态，若需更精准的 MySQL 服务状态检测，可在 track_script 中自定义脚本（例如检测 MySQL 进程是否存在或能否正常响应）。

五、关键注意事项与优化建议

1. 数据一致性保障

半同步复制（Semi-Synchronous Replication）：默认的异步复制可能导致主库崩溃时部分未同步的数据丢失。可通过启用半同步复制，确保至少一个备库接收并写入 binlog 后，主库才返回成功响应（需安装插件并配置 rpl_semi_sync_master_enabled=ON）。
GTID 复制（推荐）：MySQL 5.6+ 支持全局事务标识符（GTID），简化主从切换时的位置追踪（替代传统的 MASTER_LOG_FILE 和 MASTER_LOG_POS）。配置时需在主从库的 my.cnf 中添加 gtid_mode=ON 和 enforce-gtid-consistency=ON。

2. 故障切换自动化

结合监控工具：通过 Prometheus + Grafana 监控 MySQL 状态（如 QPS、延迟、线程状态），结合 Alertmanager 在异常时触发告警。
脚本化切换：若 Keepalived 未满足需求，可编写自定义脚本（例如检测 MySQL 是否存活，若宕机则手动提升备库为主库并更新 VIP）。

3. 备库延迟处理

监控延迟：通过 SHOW SLAVE STATUS 中的 Seconds_Behind_Master 字段实时监控备库延迟，若延迟过大，需排查网络带宽、备库负载或 SQL 线程阻塞问题。
读写分离：将读请求分发到备库（通过中间件如 MyCat、ProxySQL），减轻主库压力，但需注意备库数据可能略旧于主库。