MySQL循环复制集群项目

Word count: 2.7k   |   Reading time≈ 13 min

安装MySQL

Docker安装

在Ubuntu 24.04.3 下安装Docker
1. 卸载旧版本
Docker 的旧版本叫 docker，docker.io 或 docker-engine 。如果已安装，就先卸载

1
$ sudo apt-get remove docker docker-engine docker.io containerd runc

2. 准备安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#添加 Docker 官方 GPG key
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
# 阿里源（推荐使用阿里的gpg KEY）
curl -fsSL https://mirrors.aliyun.com/docker-ce/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg



#添加 apt 源:
#Docker官方源
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
#阿里apt源
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://mirrors.aliyun.com/docker-ce/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null


#更新源
sudo apt update
sudo apt-get update

3. 安装最新版本的 Docker Engine-Community 和 containerd

1
2
3
4
5
6
7
8
9
#安装最新版本的Docker
sudo apt install docker-ce docker-ce-cli containerd.io
#等待安装完成

#查看Docker版本
sudo docker version

#查看Docker运行状态
sudo systemctl status docker

(4. 允许非Root用户执行docker 命令)
可以让当前用户在不切root，或者不加sudo 的情况下正常使用 docker 命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 添加docker用户组
sudo groupadd docker

#将当前用户添加到用户组
sudo usermod -aG docker $USER

#使权限生效
newgrp docker

#测试一下
#查看所有容器
docker ps -a

#最后更新.bashrc文件
#如果没有此行命令，当每次打开新的终端时都必须先执行一次 “newgrp docker” 命令，否则当前用户还是不可以执行docker命令
groupadd -f docker

拉取MySQL镜像

1
docker pull mysql:8.0

使用道客云(https://github.com/DaoCloud/public-image-mirror) 进行加速，使用方法就是增加前缀：docker pull m.daocloud.io/docker.io/library/mysql:8.0

创建三个容器

这一步首先创建一个docker网络，并把三个mysql节点连入其中，此外为每个 MySQL 容器准备 独立的配置文件（my.cnf），使它们之间能正确支持 GTID + 二进制日志 + 唯一 server-id，这是建立循环复制的基础。

创建docker网络

1
docker network create mysql-net

为每个容器准备不同的 MySQL 配置

在宿主机准备三个不同的配置文件（分别包括 GTID、server-id 不同等），并挂载进每个容器里
下面时 3 个节点分别的内容。三个节点的 server-id 和 auto_increment_offset 是不同的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# mysql1.cnf（节点 1）
[mysqld]
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
log_slave_updates=ON
auto_increment_increment=3
auto_increment_offset=1


# mysql2.cnf（节点 2）
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
log_slave_updates=ON
auto_increment_increment=3
auto_increment_offset=2


# mysql3.cnf（节点 3）
[mysqld]
server-id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
log_slave_updates=ON
auto_increment_increment=3
auto_increment_offset=3

可以把这三个文件放在宿主机目录下：

1
2
3
/home/user/mysql1.cnf
/home/user/mysql2.cnf
/home/user/mysql3.cnf

启动三个容器

启动容器要基于同一网络启动，要挂载不同的my.cnf文件，确保server_id不同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# mysql1
docker run -d --name mysql1 --network mysql-net \
  -v /home/user/mysql1.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root123 \
  mysql:8.0

# mysql2
docker run -d --name mysql2 --network mysql-net \
  -v /home/user/mysql2.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root123 \
  mysql:8.0

# mysql3
docker run -d --name mysql3 --network mysql-net \
  -v /home/user/mysql3.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root123 \
  mysql:8.0

启动后确认配置是否生效

进入任意容器：

1
docker exec -it mysql1 mysql -uroot -p

然后检查关键变量：

1
2
3
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'log_bin';

只要每个节点的 server_id 不同且 GTID、binlog 都开启，则配置成功。

建立循环复制关系

mysql1 -> mysql2 -> mysql3 -> mysql1

只在 mysql1 创建复制账户

在mysql1 内执行：

1
2
3
4
5
6
CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass';
#对于mysql8.0等老版本，使用下面mysql_native_passowrd认证来兼容老版本
#CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replpass'; 

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

如果后面报错Last_IO_Errno: 2061 Last_IO_Error: Error connecting to source ‘repl@mysql3:3306’. This was attempt 6/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
这是因为默认 MySQL 8.x 用户使用的是 caching_sha2_password 插件，该插件默认要求安全的连接（例如 SSL/TLS 或公钥加密），当前从库的 I/O 线程尝试建立连接时没有满足这个要求，所以认证失败，无法连接到主库/binlog 源。
解决办法：让 repl 用户使用更兼容的认证插件 mysql_native_password，
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replpass'; FLUSH PRIVILEGES;

在每个节点的配置

在每个节点执行 CHANGE MASTER TO ... 指向另一个容器,然后启动 START SLAVE

1
2
3
4
5
6
7
8
9
#mysql1节点示例：

CHANGE MASTER TO
MASTER_HOST='mysql3',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_AUTO_POSITION=1;

START SLAVE;

查看是否搭建成功：

1. 查看状态

1
SHOW SLAVE STATUS\G;

核心期望输出：
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0 或较小

2. 验证数据同步正确性（功能验证）
测试流程确认写入是否在三个节点复制一致：

1
2
3
4
5
# 进入 mysql1
CREATE DATABASE test_sync;
USE test_sync;
CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(50));
INSERT INTO t1(val) VALUES('replica check');

然后分别到 mysql2、mysql3 上执行：

SELECT * FROM test_sync.t1;

如果所有节点都能看到相同数据，则表示复制真实生效。

注意的点

在配置mysql1->mysql3 的过程中会出现报错：
Last_SQL_Errno: 1396 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b640ad31-0036-11f1-9073-52e71b298e3c:6' at source log mysql-bin.000003, end_log_pos 516...
这是因为在常规 主从复制 中，通常主库创建一次复制账户，它的 binlog 会在从库上回放，从而让从库拥有该账号，这没有问题。
但在 循环复制拓扑（A→B→C→A） 下：

  • 在 A 创建 repl
  • B 会在复制过程中创建 repl（因为回放 A 的 binlog）
  • C 会在复制过程中创建 repl（因为回放 B 的 binlog）
  • 然后当 A 再从 C 复制时就遇到“用户已存在”的冲突

这是因为 CREATE USER 语句 不是幂等操作，也就是说，同样的语句重复执行会失败。

解决办法：手动跳过这个导致复制失败的事务
在出错结果中看到：
Worker 1 failed executing transaction 'b640ad31-0036-11f1-9073-52e71b298e3c:6'
这里：b640ad31-0036-11f1-9073-52e71b298e3c 是 GTID 组 UUID,:6 是事务号,这个就是失败的 GTID。

1
2
3
4
5
6
7
# mysql1内执行：
STOP replica;
# 把 GTID_NEXT 设置成失败事务的 GTID,提交一个空事务让本地“应用”它
SET GTID_NEXT='b640ad31-0036-11f1-9073-52e71b298e3c:6';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

性能压测

使用sysbench对docker环境下的mysql集群进行压力测试，分析吞吐量，延迟和复制延迟。

安装 sysbench

安装流程

使用 Docker 网络内部地址

由于没有没有端口映射，因此需要查询 Docker 网络内部地址，运行命令查看容器 IP：

1
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql1

准备数据库与用户

在一个 MySQL 主节点（或某个节点）执行：

1
2
3
4
CREATE DATABASE sbtest;
CREATE USER 'sbtest'@'%' IDENTIFIED WITH mysql_native_password BY 'sbpass';
GRANT ALL ON sbtest.* TO 'sbtest'@'%';
FLUSH PRIVILEGES;

准备测试数据（Prepare 阶段）

在目标数据库里创建表并插入数据，供压力测试使用。

1
2
3
4
5
6
7
8
9
sysbench --db-driver=mysql \
--mysql-host=172.18.0.2 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=sbpass \
--mysql-db=sbtest \
--tables=10 \
--table-size=100000 \
oltp_read_write prepare

创建 10 个测试表,每表 100 000 条数据,oltp_read_write：混合读写负载模式

运行压力测试（run）

正式压测，下面示例用 50 个并发线程压测 120 秒：

1
2
3
4
5
6
7
8
9
10
11
12
13
sysbench \
  --db-driver=mysql \
  --mysql-host=172.18.0.2 \
  --mysql-port=3306 \
  --mysql-user=sbtest \
  --mysql-password=sbpass \
  --mysql-db=sbtest \
  --tables=10 \
  --table-size=100000 \
  --threads=50 \
  --time=120 \
  --report-interval=10 \
  oltp_read_write run

测试结果与分析

30220 transactions 总数，251.41 transactions/sec (TPS)，这表示数据库每秒能成功提交大约251个事务，这属于中等负载水平表现，可以视为基础性能基线。

queries: 604400 (5028.15 per sec.)表示每秒数据库执行了约 5028 条 SQL 操作。

至于延迟：

1
2
3
4
min: 11.51 ms  
avg: 198.73 ms  
max: 908.61 ms  
95th percentile: 337.94 ms

平均延迟约 199 ms：表示在压测期间，大部分事务平均需要约 0.2 秒处理完毕。
95% 338 ms：说明 95% 的事务响应时间小于 338 毫秒，越低越好。
最大延迟 ~909 ms：有少数较慢的响应，可能由于瞬时压力、锁争用或资源波动。
在 OLTP 场景里，对于简单的读写压力测试来说，平均 <100 ms 是理想，200+ms 表示在当前硬件/配置/负载组合下尚未达到最优，存在优化空间。

结论：系统能够稳定处理 OLTP 负载，数据库在 Docker 环境下可用性良好，
但Docker 容器化部署稍弱于物理或纯 VM，循环复制带来的复制延迟与开销是主要影响因素。

测试结束后清理数据（cleanup）

压测结束后清理 sysbench 在库里创建的表和数据：

1
2
3
4
5
6
7
8
9
10
sysbench \
  --db-driver=mysql \
  --mysql-host=172.18.0.2 \
  --mysql-port=3306 \
  --mysql-user=sbtest \
  --mysql-password=sbpass \
  --mysql-db=sbtest \
  --tables=10 \
  --table-size=100000 \
  oltp_read_write cleanup

集群监控和维护

创建监控脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#!/bin/bash

MYSQL_CONT="$1"                 # 容器名或服务名
MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"

ALERT_LAG=30
ALERT_CONN=80

# 主从延迟
SLAVE_STATUS=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW SLAVE STATUS\G")
LAG=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt $ALERT_LAG ]; then
  echo "[WARN] Replication lag $LAG"
else
  echo "[OK] Replication lag $LAG"
fi

# 复制线程状态
IO=$(echo "$SLAVE_STATUS" | grep "Replica_IO_Running:" | awk '{print $2}')
SQL=$(echo "$SLAVE_STATUS" | grep "Replica_SQL_Running:" | awk '{print $2}')
if [[ "$IO" != "Yes" || "$SQL" != "Yes" ]]; then
  echo "[ERR] Replication threads $IO/$SQL"
else
  echo "[OK] Rep threads $IO/$SQL"
fi

# 当前连接数
CONN=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
MAX=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}')
PCT=$(echo "$CONN * 100 / $MAX" | bc)
echo "[INFO] Conn usage: $CONN/$MAX ($PCT%)"

# QPS & TPS（1秒差值）
Q1=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Queries';" | awk '{print $2}')
C1=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Com_commit';" | awk '{print $2}')
R1=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Com_rollback';" | awk '{print $2}')
sleep 1
Q2=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Queries';" | awk '{print $2}')
C2=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Com_commit';" | awk '{print $2}')
R2=$(docker exec "$MYSQL_CONT" mysql -uroot -p"$MYSQL_PASS" -sN -e "SHOW GLOBAL STATUS LIKE 'Com_rollback';" | awk '{print $2}')
QPS=$((Q2-Q1))
TPS=$(((C2-C1)+(R2-R1)))

echo "[INFO] QPS:$QPS TPS:$TPS"
  • Copyrights © 2023-2026 Hexo

请我喝杯咖啡吧~

支付宝
微信