跳至正文

MySQL互为主从复制+KeepAlived

  • 由

MySQL 5.7 主主同步（Master-Master Replication）配置需要让两台服务器互为主从，即 A 同步到 B，B 也同步到 A。

主主复制

前提条件

假设两台服务器如下：

节点主机名IP地址server-id作用
主库AmysqlA192.168.1.1011主（同步到B）
主库BmysqlB192.168.1.1022主（同步到A）

两边的 MySQL 版本一致（5.7.x），并且时间同步（使用 NTP）。

A 节点（192.168.1.101）的 my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
binlog-format = ROW
sync-binlog = 1
skip-name-resolve
# 避免主主循环更新
replicate-same-server-id = 0
gtid-mode = OFF
enforce-gtid-consistency = OFF

B 节点（192.168.1.102）的 my.cnf

[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
binlog-format = ROW
sync-binlog = 1
skip-name-resolve
replicate-same-server-id = 0
gtid-mode = OFF
enforce-gtid-consistency = OFF

授权复制账户（两边都要执行）

在两台服务器上都执行：

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

设置主从关系

在 A 服务器 上执行（指向 B）：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.102',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;

START SLAVE;

在 B 服务器 上执行（指向 A）：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;

START SLAVE;

⚠️ 这里的 MASTER_LOG_FILE 和 MASTER_LOG_POS 需根据各自主库的 SHOW MASTER STATUS; 输出确定。

检查状态

在两台服务器上执行：

SHOW SLAVE STATUS\G

确认：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

都为 Yes 即表示同步正常。

踩坑

Slave_IO_Running: Yes，但是Slave_SQL_Running: No

这个状态说明：
✅ IO线程（负责从主库拉取binlog）正常；
❌ SQL线程（负责在本地执行这些binlog）出错了。

主主复制里这种情况非常常见，通常是因为某条SQL冲突或重复执行导致的。

在这台出错的服务器上执行以下命令：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

解释：

  • STOP SLAVE; 停止复制。
  • SQL_SLAVE_SKIP_COUNTER=1; 跳过当前出错的那条事务（即那条 CREATE DATABASE）。
  • START SLAVE; 重新启动复制。

然后再执行：

SHOW SLAVE STATUS\G

确认：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果两项都为 Yes，就说明同步恢复了。

有时可能连续几条事务都冲突，可以多跳几次，例如：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3;
START SLAVE;

（3 表示跳过接下来的 3 个事务。）

Last_Errno: 1396

Last_Errno: 1396

Last_Error: Error ‘Operation CREATE USER failed for ‘repl’@’%” on query. Default database: ”. Query: ‘CREATE USER ‘repl’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9”

错误内容：

Error 'Operation CREATE USER failed for 'repl'@'%''

原因是：

  • 其中一台 MySQL 执行了 CREATE USER 'repl'@'%' ...；
  • 另一台主库通过复制又收到了相同的 SQL；
  • 但该用户已经存在，因此报错 1396。

这就是典型的主主同步用户重复创建冲突。

在报错的那台服务器上执行以下命令即可恢复同步：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

然后执行：

SHOW SLAVE STATUS\G

确认：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

因为在 MySQL 5.7 的主主复制中：

  • 所有 SQL（包括 CREATE USER、GRANT、DROP USER 等）都会写入 binlog；
  • 另一边也会重放，从而造成重复用户操作冲突。

因此，可以设置忽略系统库的复制，只操作业务库

KeepAlived

 Keepalived 提供 VIP 漂移机制，保证在主节点故障时，客户端仍能通过虚拟 IP 访问数据库，从而实现业务连续性。需要在A、B机器都安装keepalived

通过 VRRP 协议维持一组主备状态；

检测本地 MySQL 是否存活；

如果检测失败，则自动将 VIP 漂移到另一台主机；

上层应用继续连接 VIP，不感知切换。

编译安装

# 安装依赖
yum install -y gcc gcc-c++ make openssl-devel pcre-devel libnl3-devel
​
# 下载并解压
cd /opt
wget https://www.keepalived.org/software/keepalived-2.2.5.tar.gz
tar zxvf keepalived-2.2.5.tar.gz
cd keepalived-2.2.5
​
# 编译安装
./configure --prefix=/usr/local/keepalived
make && make install
​
# 创建配置目录
mkdir -p /usr/local/etc/keepalived
mkdir -p /etc/keepalived     # 存放检测脚本

配置文件示例

/usr/local/etc/keepalived/keepalived.conf

#MASTER
global_defs {
   router_id LVS_MASTER  #名称标记为master，名字随便取
   vrrp_gna_interval 0
}
vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2      # 每 2 秒检测一次
    rise 1          # 连续 1 次成功才认为 UP
    fall 2          # 连续 2 次失败才认为 DOWN，避免短暂波动
    weight -50
}
​
vrrp_instance VI_1 {
    state MASTER
    interface eth0  # 注意网卡名！！！
    virtual_router_id 99
    priority 200           # MASTER 节点优先级
    advert_int 1
    virtual_ipaddress {
        192.168.1.200   # 漂移的VIP地址
    }
    track_script {
        check_mysql
    }
} 
​
#BACKUP
global_defs {
   router_id LVS_SLAVE  #名称标记为slave，名字随便取
   vrrp_gna_interval 0
}
vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2      # 每 2 秒检测一次
    rise 1          # 连续 1 次成功才认为 UP
    fall 2          # 连续 2 次失败才认为 DOWN，避免短暂波动
}
​
vrrp_instance VI_1 {
    state BACKUP
    interface eth0  # 注意网卡名！！！
    virtual_router_id 99
    priority 199           # BACKUP 节点优先级
    advert_int 1
    virtual_ipaddress {
       192.168.1.200    # 漂移的VIP地址
    }
    track_script {
        check_mysql
    }
}

状态检测

/etc/keepalived/check_mysql.sh

#!/bin/bash

# 检查 MySQL 是否监听 3306
if ss -lnt | grep -q ":3306"; then
    exit 0  # MySQL up
else
    exit 1  # MySQL down
fi

启动

systemctl start keepalived

查看漂移地址：

[root@sql-1 keepalived-2.2.5]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether bc:24:11:bd:06:8c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.101/24 brd 192.168.1.255 scope global noprefixroute dynamic eth0
       valid_lft 10319sec preferred_lft 10319sec
    inet 192.168.1.200/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::a4af:3324:d3b3:cab1/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
    inet6 fe80::4299:7b03:424:dc3a/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
    inet6 fe80::7fd1:b04d:f57:b9b8/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever

# 192.168.1.200 出现了

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注