MySQL 8.0.35 GTID 主从复制 + WRITESET 多线程复制搭建

原创于 2026-07-23 22:53:22 发布 · 76 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MYSQL

基于二进制包最小化安装，GTID 主从复制 + WRITESET 多线程复制架构

主库：192.168.195.141
从库：192.168.195.142

1. 环境信息

项目	主库 (Master)	从库 (Slave)
IP	192.168.195.141	192.168.195.142
MySQL版本	8.0.35（二进制包，glibc2.17）	8.0.35（二进制包，glibc2.17）
server-id	1	2
gtid_mode	ON	ON
多线程复制	—	LOGICAL_CLOCK + WRITESET
角色	Master	Slave

2. 安装 MySQL（两台均执行）

2.1 创建用户与组

groupadd mysql
useradd -g mysql mysql

2.2 解压二进制包

cd /usr/local/
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
tar xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
ln -s mysql-8.0.35-linux-glibc2.17-x86_64 mysql

验证：

/usr/local/mysql/bin/mysql --version
# /usr/local/mysql/bin/mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

2.3 创建数据目录

mkdir -p /data/mysql/3306/data
chown mysql.mysql /data/mysql/3306/data/

3. 编辑配置文件

3.1 主库 /etc/my.cnf（141）

[client]
socket = /data/mysql/3306/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
user    = mysql
port    = 3306
socket  = /data/mysql/3306/data/mysql.sock
log_error = /data/mysql/3306/data/mysqld.err
log_timestamps = system
log-bin = mysql-bin
server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format = ROW

参数解释：

GTID 相关：

gtid_mode = ON：开启 GTID 复制。GTID（Global Transaction Identifier）为每个事务分配全局唯一ID，格式为 source_id:transaction_id，其中 source_id 是实例的 server_uuid，transaction_id 是事务序列号。
enforce_gtid_consistency = ON：强制 GTID 一致性，只允许事务安全的语句执行。如果要将 gtid_mode 设置为 ON，则此参数必须为 ON。

WRITESET 多线程复制相关（主库参数）：

binlog_transaction_dependency_tracking = WRITESET_SESSION：设置 binlog 事务依赖追踪方式。取值有：

COMMIT_ORDER：基于组提交顺序（默认值），只有同一组提交内的事务才可以并行回放。
WRITESET：基于行修改的冲突检测，如果两个事务修改的行没有重叠，即使不在同一组提交中，也可以并行回放。相比 COMMIT_ORDER 大幅提升并行度。
WRITESET_SESSION：在 WRITESET 基础上，保证同一会话内的事务按顺序执行（不会并行回放同一会话的事务）。推荐使用此值，兼顾并行度和会话内顺序性。

transaction_write_set_extraction = XXHASH64：设置事务写集合的哈希算法。WRITESET 方案需要对事务修改的每一行计算哈希值，用于冲突检测。XXHASH64 是一种高效的非加密哈希算法。注意：MySQL 8.0.35 中此参数已标记为废弃（deprecated），但仍然需要配置才能启用 WRITESET。

binlog_transaction_dependency_history_size = 25000：WRITESET 冲突检测使用的历史行哈希映射大小。全局维护一个 map，记录行的 hash 值与修改该行的事务的 sequence_number 之间的映射。此参数控制该 map 的大小，默认 25000。值越大，能追踪的行数越多，并行度可能更高，但内存消耗也更大。

binlog_format = ROW：binlog 格式必须为 ROW。WRITESET 方案只在 binlog 格式为 ROW 时才生效，因为只有 ROW 格式的 binlog 才包含了行级别的修改信息，才能进行行级冲突检测。注意：MySQL 8.0.35 中此参数已标记为废弃（deprecated），因为 MySQL 8.0 默认且仅支持 ROW 格式，但显式配置可确保兼容性。

3.2 从库 /etc/my.cnf（142）

[client]
socket = /data/mysql/3306/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
user    = mysql
port    = 3306
socket  = /data/mysql/3306/data/mysql.sock
log_error = /data/mysql/3306/data/mysqld.err
log_timestamps = system
server-id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON

与主库的区别：

server-id = 2：从库的 server-id 必须与主库不同。
从库不需要显式配置 log-bin，MySQL 8.0 默认开启 binlog。
从库不需要配置 WRITESET 主库参数（binlog_transaction_dependency_tracking 等），这些参数只在主库上影响 binlog 中写入的 last_committed 值，从库读取 binlog 后按 last_committed 值决定并行度。

多线程复制相关（从库参数）：

replica_parallel_type = LOGICAL_CLOCK：设置从库并行复制的类型。取值有：

DATABASE：基于库级别的并行复制。不同库的事务可以并行回放，MySQL 8.0.27 之前的默认值。并行度受库数量限制。
LOGICAL_CLOCK：基于组提交的并行复制。同一组提交内（即 last_committed 相同）的事务可以并行回放。配合主库的 WRITESET 方案，可以大幅提升并行度。

replica_parallel_workers = 16：设置 Worker 线程的数量。开启了多线程复制后，原来的 SQL 线程将演变为 1 个 Coordinator（协调）线程和多个 Worker 线程。Coordinator 负责读取 relay log 并将事务分配给 Worker，Worker 负责实际执行事务。建议根据 CPU 核数和负载调整。

replica_preserve_commit_order = ON：事务在从库上的提交顺序是否与主库保持一致。开启后，Worker 线程执行完事务后必须按主库提交顺序依次提交，避免从库数据与主库出现不一致。强烈建议开启。

注意： 上述三个参数调整后需重启复制线程生效（STOP SLAVE; START SLAVE）。

4. 初始化实例（两台均执行）

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize

获取临时密码：

grep "temporary password" /data/mysql/3306/data/mysqld.err

5. 配置 systemd 服务（两台均执行）

5.1 创建 /etc/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/mysql/3306/data/mysqld.pid
TimeoutSec=0
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --pid-file=/data/mysql/3306/data/mysqld.pid --daemonize $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

5.2 创建 /etc/sysconfig/mysql

MYSQLD_OPTS=

6. 启动实例（两台均执行）

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

7. 修改 root 密码（两台均执行）

/usr/local/mysql/bin/mysql -uroot -S /data/mysql/3306/data/mysql.sock -p

alter user user() identified by 'Root@123456';

8. 搭建 GTID 主从复制

8.1 主库创建复制用户

-- 在主库（141）执行
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

8.2 从库建立 GTID 复制

-- 在从库（142）执行
CHANGE MASTER TO
MASTER_HOST='192.168.195.141',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_AUTO_POSITION=1,
GET_MASTER_PUBLIC_KEY=1;

START SLAVE;

参数解释：

MASTER_AUTO_POSITION=1：使用 GTID 自动定位，无需手动指定 MASTER_LOG_FILE 和 MASTER_LOG_POS。从库 IO 线程连接主库时，会自动交换 GTID 信息，从库告诉主库自己已执行了哪些事务，主库只发送缺失的事务。
GET_MASTER_PUBLIC_KEY=1：MySQL 8.0 默认使用 caching_sha2_password 认证插件，非 SSL 连接时需获取主库公钥完成认证。

8.3 验证主从状态

-- 在从库（142）执行
show slave status\G

               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.195.141
                  Master_User: repl
                  Master_Port: 3306
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 11845
               Relay_Log_File: MYSQL142-relay-bin.000002
                Relay_Log_Pos: 12061
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 11845
        Seconds_Behind_Master: 0
             Master_Server_Id: 1
                  Master_UUID: 7bd22bca-869d-11f1-bf74-000c29048d1f
           Retrieved_Gtid_Set: 7bd22bca-869d-11f1-bf74-000c29048d1f:1-42
            Executed_Gtid_Set: 7bd22bca-869d-11f1-bf74-000c29048d1f:1-42,
                               c268d8e3-869e-11f1-8542-000c29a59955:1
                Auto_Position: 1
         Get_master_public_key: 1

重点关注：

Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes，两个均为 Yes 代表主从复制搭建成功。
Auto_Position: 1，表示使用 GTID 自动定位。
Retrieved_Gtid_Set：已从主库获取的 GTID 事务集。
Executed_Gtid_Set：已执行的 GTID 事务集。

9. 验证多线程复制

9.1 从库 show processlist

-- 在从库（142）执行
show processlist;

+----+-----------------+---------------------+------+---------+--------+--------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command | Time   | State                                            | Info             |
+----+-----------------+---------------------+------+---------+--------+--------------------------------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL | Daemon  | 225    | Waiting on empty queue                           | NULL             |
| 11 | system user     | connecting host     | NULL | Connect | 126    | Waiting for source to send event                 | NULL             |
| 12 | system user     |                     | NULL | Query   | 47     | Replica has read all relay log; waiting for more updates | NULL     |
| 13 | system user     |                     | NULL | Query   | 47     | Waiting for an event from Coordinator            | NULL             |
| 14 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 15 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 16 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 17 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 18 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 19 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 20 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 21 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 22 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 23 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 24 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 25 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 26 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 27 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 28 | system user     |                     | NULL | Connect | 126    | Waiting for an event from Coordinator            | NULL             |
| 32 | root            | localhost           | NULL | Query   | 0      | init                                             | show processlist |
+----+-----------------+---------------------+------+---------+--------+--------------------------------------------------+------------------+

线程说明：

11 是 IO 线程（Waiting for source to send event）：负责从主库拉取 binlog 并写入 relay log。
12 是 Coordinator 协调线程（Replica has read all relay log; waiting for more updates）：负责读取 relay log，根据 last_committed 分配事务给 Worker。
13-28 是 16 个 Worker 线程（Waiting for an event from Coordinator）：负责实际执行事务。配置 replica_parallel_workers=16 生效。

9.2 主库 show processlist

-- 在主库（141）执行
show processlist;

+----+-----------------+---------------------+------+-------------+--------+--------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command     | Time   | State                                            | Info             |
+----+-----------------+---------------------+------+-------------+--------+--------------------------------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL | Daemon      | 976    | Waiting on empty queue                           | NULL             |
| 11 | repl            | 192.168.195.142:51690 | NULL | Binlog Dump GTID | 324 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 27 | root            | localhost           | NULL | Query       | 0      | init                                             | show processlist |
+----+-----------------+---------------------+------+-------------+--------+--------------------------------------------------+------------------+

注意：GTID 复制下，主库的 Binlog Dump 线程显示为 Binlog Dump GTID，与位置点复制的 Binlog Dump 不同。

10. WRITESET 并行能力验证

10.1 什么是 last_committed 和 sequence_number

MySQL 5.7 开始，binlog 中每个事务都记录了 last_committed 和 sequence_number，这就是 LOGICAL_CLOCK 的核心：

last_committed：事务提交时上次事务提交的编号。如果多个事务的 last_committed 相同，表示这些事务在同一个"组"内，可以进行并行回放。
sequence_number：顺序增长的序列号，每个事务提交时获得一个唯一的 sequence_number。

10.2 LOGICAL_CLOCK vs WRITESET

LOGICAL_CLOCK 方案：由 Order Commit 实现，只有同一组提交（Group Commit）内的事务才能并行回放。当主库并发度较低时，每次组提交的事务数很少，从库的并行度也随之降低——即使这些事务之间并没有任何冲突。

WRITESET 方案：在 LOGICAL_CLOCK 基础上，通过行级冲突检测进一步优化并行度。原理：

全局维护一个 map，记录行的 hash 值与修改该行的事务的 sequence_number 之间的映射。
事务提交时，遍历该事务修改的行的 hash 值，在全局 map 中查找：
- 如果有冲突（修改了同一行），取最大的 sequence_number 作为 commit parent。
- 如果没有任何冲突，将 commit parent 设为 map 中最小的 sequence_number。
这样，原本因为不在同一组提交而不能并行的事务，经过 WRITESET 处理后，其 last_committed 可能被修改，使得它们可以并行回放。

10.3 验证 WRITESET 并行效果

在主库创建 10 张不同的表，并用 10 个并发连接同时插入数据：

-- 主库创建测试表
CREATE DATABASE test_writeset;
USE test_writeset;
CREATE TABLE t_a (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_b (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_c (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_d (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_e (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_f (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_g (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_h (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_i (id INT PRIMARY KEY, c1 VARCHAR(100));
CREATE TABLE t_j (id INT PRIMARY KEY, c1 VARCHAR(100));

# 10个并发连接同时插入
for i in a b c d e f g h i j; do
  mysql -uroot -S /data/mysql/3306/data/mysql.sock -pRoot@123456 -e "USE test_writeset; INSERT INTO t_${i} VALUES (1,'data_${i}');" &
done
wait

10.4 解析 binlog 查看并行度

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS /data/mysql/3306/data/mysql-bin.000002 | grep "last_committed\|sequence_number"

关键输出（并发插入的10个事务）：

GTID  last_committed=32  sequence_number=33   rbr_only=yes
GTID  last_committed=32  sequence_number=34   rbr_only=yes
GTID  last_committed=32  sequence_number=35   rbr_only=yes
GTID  last_committed=32  sequence_number=36   rbr_only=yes
GTID  last_committed=32  sequence_number=37   rbr_only=yes
GTID  last_committed=32  sequence_number=38   rbr_only=yes
GTID  last_committed=32  sequence_number=39   rbr_only=yes
GTID  last_committed=32  sequence_number=40   rbr_only=yes
GTID  last_committed=32  sequence_number=41   rbr_only=yes
GTID  last_committed=32  sequence_number=42   rbr_only=yes

分析： 10 个事务的 last_committed 全部为 32！这意味着这 10 个事务修改的是不同表的不同行，WRITESET 检测到没有行冲突，将它们的 commit parent 都设为 32。在从库上，这 10 个事务可以完全并行回放。

对比如果使用 COMMIT_ORDER 方案，这些事务可能因为不在同一组提交而无法并行。WRITESET 方案有效提升了从库的并行回放能力。

10.5 如何判断并行能力

根据 PDF 文档，判断并行能力的方法：

解析 binlog 查看 last_committed 和 sequence_number：如果 last_committed 相同的事务数量较多（通常大于 replica_parallel_workers 的值），就需要调大 replica_parallel_workers。
影响并行能力的其他参数：
- binlog_group_commit_sync_delay：事务延迟提交的时间（单位微秒），最大 1000000（1秒）。增加此值可以让更多事务进入同一组提交，增大并行度，但也会增加主库事务延迟。
- binlog_group_commit_sync_no_delay_count：组提交的事务数量凑齐多少就立即提交，无需等待 sync_delay 时间。不会超过 sync_delay 设置。
- 这两个参数都是为了增加主服务器组提交的事务比例，从而增大从库 MTS 的并行度。

11. 数据同步测试

主库写入测试数据：

-- 在主库（141）执行
CREATE DATABASE test_gtid;
USE test_gtid;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t1 VALUES (1,'gtid_data');
SELECT * FROM t1;
-- +----+-----------+
-- | id | name      |
-- +----+-----------+
-- |  1 | gtid_data |
-- +----+-----------+

从库查询验证：

-- 在从库（142）执行
SELECT * FROM test_gtid.t1;
-- +----+-----------+
-- | id | name      |
-- +----+-----------+
-- |  1 | gtid_data |
-- +----+-----------+

SELECT * FROM test_writeset.t_a;
SELECT * FROM test_writeset.t_b;
-- ... 10张表数据均同步

数据一致，GTID 主从复制 + WRITESET 多线程复制工作正常。

12. GTID 相关参数确认

12.1 主库（141）

gtid_mode              = ON
enforce_gtid_consistency = ON
gtid_executed          = 7bd22bca-869d-11f1-bf74-000c29048d1f:1-42
gtid_next              = AUTOMATIC
gtid_purged            =

12.2 从库（142）

gtid_mode              = ON
enforce_gtid_consistency = ON
gtid_executed          = 7bd22bca-869d-11f1-bf74-000c29048d1f:1-42,
                         c268d8e3-869e-11f1-8542-000c29a59955:1
gtid_next              = AUTOMATIC
gtid_purged            =

说明：

从库的 gtid_executed 包含两个 UUID 的事务：主库的 7bd22bca-869d-11f1-bf74-000c29048d1f:1-42（通过复制获取）和从库自身的 c268d8e3-869e-11f1-8542-000c29a59955:1（本地事务）。

13. 多线程复制相关参数确认

13.1 主库（141）— WRITESET 参数

binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction       = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format                          = ROW

13.2 从库（142）— 多线程参数

replica_parallel_type           = LOGICAL_CLOCK
replica_parallel_workers        = 16
replica_preserve_commit_order   = ON

14. 连接信息

# 主库（141）
/usr/local/mysql/bin/mysql -uroot -S /data/mysql/3306/data/mysql.sock -p'Root@123456'

# 从库（142）
/usr/local/mysql/bin/mysql -uroot -S /data/mysql/3306/data/mysql.sock -p'Root@123456'

15. WRITESET 并行复制原理总结

15.1 LOGICAL_CLOCK 原理

LOGICAL_CLOCK 由 MySQL 的 Order Commit（有序提交）实现，核心是 Group Commit（组提交）：

准备阶段：SQL 执行成功，生成 xid 信息及 redo/undo 内存日志，调用 prepare 方法，将事务状态设为 TRX_PREPARED，并将 redo log 刷盘。
提交阶段：
- 记录 Binlog 日志：write() 将 binary log 写入文件系统缓存，fsync() 将缓存永久写入磁盘。
- 告诉引擎做 commit：清除 undo 信息，刷 redo 日志，将事务设为 TRX_NOT_STARTED 状态。

同一组提交内的事务，其 last_committed 相同，可以在从库并行回放。

15.2 WRITESET 原理

WRITESET 在 LOGICAL_CLOCK 基础上进一步优化：

全局维护一个 std::map，记录行 hash 值与修改该行的事务 sequence_number 的映射。
事务提交写入 binlog 时，遍历该事务修改的行的 hash 值，在全局 map 中查找：
- 有冲突（同一行被多个事务修改）→ 取最大的冲突 sequence_number 作为 commit parent。
- 无冲突 → 将 commit parent 设为 map 中最小的 sequence_number。
结果：原本不重叠的 LOGICAL_CLOCK 区间，经过 WRITESET 处理后可能重叠，使并行回放成为可能。

15.3 WRITESET 方案的前提条件

binlog 格式必须为 ROW：只有 ROW 格式才包含行级修改信息，才能计算行 hash。
主库配置：binlog_transaction_dependency_tracking、transaction_write_set_extraction、binlog_transaction_dependency_history_size。
从库配置：replica_parallel_type = LOGICAL_CLOCK、replica_parallel_workers > 0。

标签

#mysql #adb #android