VMWare CentOS7 Docker MySQL Keepalive

/etc/sysconfig/network-scripts/ifcfg-ens33

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
UUID=35ba4419-15a9-4757-92d9-3cf2365bd8db
DEVICE=ens33
ONBOOT=yes

IPADDR=192.168.30.10
GATEWAY=192.168.30.2
NETMASK=255.255.255.0
DNS1=8.8.8.8
DNS2=4.4.4.4

/etc/yum.repos.d/CentOS-Base.repo

# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
baseurl=http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7

#released updates
[updates]
name=CentOS-$releasever - Updates
baseurl=http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
baseurl=http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus
baseurl=http://mirrors.aliyun.com/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7

CentOS 7 安装 Docker

sudo docker pull mysql:5.7.44

docker run -d \
  --name mysql \
  -p 3306:3306 \        # MySQL经典协议端口（命令行/SQL客户端）
  -p 33060:33060 \      # MySQL X Protocol端口（文档存储/NoSQL接口）
  -e MYSQL_ROOT_PASSWORD=Hf@20251207 \
  -v /data/modules/mysql/data:/var/lib/mysql \
  -v /data/modules/mysql/conf:/etc/mysql/conf.d \
  mysql:5.7.44

docker run -d
–name mysql
-p 3306:3306
-p 33060:33060
-e MYSQL_ROOT_PASSWORD=Hf@20251207
-v /data/modules/mysql/data:/var/lib/mysql
-v /data/modules/mysql/conf:/etc/mysql/conf.d
mysql:5.7.44

GRANT REPLICATION SLAVE ON . TO ‘repl’@’%’ IDENTIFIED BY ‘Hf@20251207’;

CHANGE MASTER TO MASTER_HOST=’192.168.30.10’, MASTER_USER=’repl’, MASTER_PASSWORD=’Hf@20251207’, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000001’, MASTER_LOG_POS=589;

sudo docker exec mysql mysqladmin ping -h localhost -uroot -pHf@20251207 –silent 2>/dev/null

主库 my.cnf

[mysqld]
server-id = 10                      # 全局唯一（建议用IP后两位）
log_bin = /var/lib/mysql/mysql-bin  # 开启binlog，指定路径
binlog_format = ROW                 # 行级格式，避免跨库同步问题
binlog_row_image = FULL             # 记录完整行数据（便于恢复）
sync_binlog = 1                     # 事务提交强制刷盘，确保binlog不丢失

gtid_mode = ON
enforce_gtid_consistency = ON

expire_logs_days = 7                # binlog保留7天，避免磁盘占满
max_binlog_size = 1G

从库 my.cnf

[mysqld]
server-id = 11                                # 全局唯一（建议用IP后两位）
relay_log = /var/lib/mysql/mysql-relay-bin    # 开启中继日志（必须）
log_slave_updates = 1                         # 一主一从无需开启（级联复制设为1）
# read_only = 1                                 # 普通用户只读
# super_read_only = 1

slave_sql_verify_checksum = 1  # 验证中继日志校验和

gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW

expire_logs_days = 7
max_binlog_size = 1G

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

check_mysql.sh

#!/bin/bash

ss -tnl | grep 3306 >/dev/null 2>&1

if [ $? -eq 0 ]; then
  exit 0
else
  exit 1
fi

notify_master.sh

#!/bin/bash

TYPE=$1
VIP=$2

MASTER_IP="192.168.30.11"
CONTAINER_NAME="mysql"
USER="root"
PWD="Hf@20251207"
SLAVE_USER="repl"
SLAVE_USER_PWD="Hf@20251207"

case $TYPE in
  "master")
    echo "$(date) Becoming MASTER for VIP $VIP" >> /var/log/keepalived_mysql.log
    # 1. 停止复制，清除从库信息，设置为可读写
    docker exec $CONTAINER_NAME mysql -u$USER -p$PWD -e "
    STOP SLAVE;
    RESET SLAVE ALL;
    SET GLOBAL read_only = OFF;
    SET GLOBAL super_read_only = OFF;
    " 2>> /var/log/keepalived_mysql.log
    # 2. 可选：重启MySQL使某些参数完全生效，但非必须
    # docker restart $CONTAINER_NAME
    ;;
  "backup" | "fault")
    echo "$(date) Becoming $TYPE for VIP $VIP" >> /var/log/keepalived_mysql.log
    # 如果知道当前主库IP，可以配置为从库
    # 注意：在故障切换场景下，原主库恢复后需要以从库身份重新加入
    if [ "$TYPE" = "backup" ] && [ -n "$MASTER_IP" ]; then
      docker exec $CONTAINER_NAME mysql -u$USER -p$PWD -e "
      CHANGE MASTER TO
      MASTER_HOST='$MASTER_IP',
      MASTER_USER='$SLAVE_USER',
      MASTER_PASSWORD='$SLAVE_USER_PWD',
      MASTER_PORT=3306,
      MASTER_AUTO_POSITION=1;
      START SLAVE;
      SET GLOBAL read_only = ON;
      " 2>> /var/log/keepalived_mysql.log
    fi
    ;;
esac
exit 0

keepalived.conf 主库

! Configuration File for keepalived

global_defs {
   router_id centos7-node-01
}

vrrp_script chk_mysql {
    script "/etc/keepalived/scripts/check_mysql.sh"     # 健康检查脚本
    interval 2                                          # 检查间隔
    weight -100                                           # 检查失败则优先级降低5
    fall 2                                              # 连续2次失败才判定为失败
    rise 1                                              # 1次成功就认为恢复
}

vrrp_instance VI_1 {
    state BACKUP            # 初始状态为MASTER
    interface ens33         # 绑定VIP的网卡，请用 `ip a` 命令确认你的网卡名（可能是ens33等）
    virtual_router_id 51    # 虚拟路由ID，主备必须相同
    priority 150            # 初始优先级，主节点要高于备节点
    advert_int 1            # 心跳间隔

    authentication {
        auth_type PASS
        auth_pass 1111     # 主备密码必须一致
    }

    # 本机真实IP，有助于减少脑裂[citation:1]
    unicast_src_ip 192.168.30.10
    # 对端（备机）IP，使用单播模式[citation:6]
    unicast_peer {
        192.168.30.11
    }

    virtual_ipaddress {
        192.168.30.100/24   # 虚拟IP (VIP)，你的应用将连接这个地址
    }

    track_script {
        chk_mysql          # 关联健康检查脚本
    }

    notify_master "/etc/keepalived/scripts/notify_master.sh master"
    notify_backup "/etc/keepalived/scripts/notify_master.sh backup"
}

从库

! Configuration File for keepalived

global_defs {
    router_id centos7-node-02
    script_user root
}

vrrp_script chk_mysql {
    script "/etc/keepalived/scripts/check_mysql.sh"     # 健康检查脚本
    interval 2                                          # 检查间隔
    weight -5                                           # 检查失败则优先级降低5
    fall 2                                              # 连续2次失败才判定为失败
    rise 1                                              # 1次成功就认为恢复
}

vrrp_instance VI_1 {
    state BACKUP            # 初始状态为MASTER
    interface ens33         # 绑定VIP的网卡，请用 `ip a` 命令确认你的网卡名（可能是ens33等）
    virtual_router_id 51    # 虚拟路由ID，主备必须相同
    priority 100            # 初始优先级，主节点要高于备节点
    advert_int 1            # 心跳间隔

    authentication {
        auth_type PASS
        auth_pass 1111     # 主备密码必须一致
    }

    # 本机真实IP，有助于减少脑裂[citation:1]
    unicast_src_ip 192.168.30.11
    # 对端（备机）IP，使用单播模式[citation:6]
    unicast_peer {
        192.168.30.10
    }

    virtual_ipaddress {
        192.168.30.100/24   # 虚拟IP (VIP)，你的应用将连接这个地址
    }

    track_script {
        chk_mysql          # 关联健康检查脚本
    }

    notify_master "/etc/keepalived/scripts/notify_master.sh master"
    notify_backup "/etc/keepalived/scripts/notify_master.sh backup"
}