MySQL / 系统课时
搭建可复现的 DDL 实验环境
搭建可复现的 DDL 实验环境
本节搭建一套可重复销毁和重建的 MySQL 8.4 DDL 实验环境:一主一从、百万级测试表、持续读写脚本和复制观测入口。完成后,后续测试可以在相同数据规模和拓扑上比较 DDL 算法、锁等待与复制影响。
学习目标
- 用 Docker Compose 固定 MySQL 8.4 主库、副本和端口配置
- 构造百万级 InnoDB 表,并分别验证复制连接线程与应用线程
- 制造可控的并发读写负载,建立 DDL 前后的观测基线
前置检查
建议为两个 MySQL 容器预留至少 4 GB 可用内存和约 2 GB 磁盘空间,并确保宿主机端口 3307、3308 未被占用。以下命令以 Bash、Docker Engine 和 Compose v2 为例。
docker --version
docker compose version预期输出
Docker version <VALUE>
Docker Compose version v<VALUE>版本号会随环境变化;关键是两条命令都能正常返回。实验使用独立容器和测试密码,只适合本地或隔离测试机,不应照搬到生产环境。
固定实验拓扑
实验包含两个 MySQL 8.4 实例。客户端只向 mysql-primary 发起 DML 和 DDL;主库通过 GTID 异步复制到 mysql-replica。两端都开启 Performance Schema,后续可以把锁、事务和复制状态放在同一条观察链上。

在空目录保存下面的 compose.yaml。主库和副本使用不同 server-id,同时开启 binary log、GTID 和行格式复制;副本启用 super-read-only,避免实验时误把业务写入发到副本。
services:
mysql-primary:
image: mysql:8.4
container_name: mysql-primary
environment:
MYSQL_ROOT_PASSWORD: labpass
ports:
- "3307:3306"
command:
- --server-id=1
- --log-bin=mysql-bin
- --binlog-format=ROW
- --gtid-mode=ON
- --enforce-gtid-consistency=ON
- --performance-schema=ON
volumes:
- primary-data:/var/lib/mysql
mysql-replica:
image: mysql:8.4
container_name: mysql-replica
environment:
MYSQL_ROOT_PASSWORD: labpass
ports:
- "3308:3306"
command:
- --server-id=2
- --log-bin=mysql-bin
- --relay-log=relay-bin
- --log-replica-updates=ON
- --binlog-format=ROW
- --gtid-mode=ON
- --enforce-gtid-consistency=ON
- --read-only=ON
- --super-read-only=ON
- --performance-schema=ON
volumes:
- replica-data:/var/lib/mysql
volumes:
primary-data:
replica-data:这个配置文件本身不会产生终端输出。验证方法是启动后确认两个容器都能通过 mysqladmin ping,再建立复制链路。
docker compose up -d >/dev/null
until docker exec -e MYSQL_PWD=labpass mysql-primary mysqladmin ping -uroot --silent; do sleep 1; done
echo "primary ready"
until docker exec -e MYSQL_PWD=labpass mysql-replica mysqladmin ping -uroot --silent; do sleep 1; done
echo "replica ready"预期输出
primary ready
replica ready初始化复制与大表
先在主库创建仅用于复制的账号,再让副本使用 GTID 自动定位。未配置 TLS 的本地隔离实验中,GET_SOURCE_PUBLIC_KEY=1 可用于向主库请求 RSA 公钥完成认证。
docker exec -e MYSQL_PWD=labpass -i mysql-primary mysql -uroot <<'SQL'
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SQL
docker exec -e MYSQL_PWD=labpass -i mysql-replica mysql -uroot <<'SQL'
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-primary',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='replpass',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
SQL
set -euo pipefail
state=""
for i in $(seq 1 30); do
state=$(docker exec -e MYSQL_PWD=labpass mysql-replica mysql -uroot -Nse "SELECT CONCAT(COALESCE((SELECT SERVICE_STATE FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME=''), 'OFF'), '|', COALESCE((SELECT SERVICE_STATE FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME=''), 'OFF'));" )
if [ "$state" = "ON|ON" ]; then
echo "replication_connection=ON"
echo "replication_applier=ON"
break
fi
if [ "$i" -eq 30 ]; then
echo "replication not ready: $state" >&2
exit 1
fi
sleep 1
done预期输出
replication_connection=ON
replication_applier=ON这里必须同时验证 receiver 和 applier:前者证明副本能够连接并接收源端数据,后者证明已接收的事务能够正常应用。任一状态不是 ON 都不能进入后续数据与 DDL 实验。
接着在主库生成一张 100 万行的 orders 表。payload 列故意扩大行宽,使需要扫描或重建数据的 DDL 更容易在实验机上观察到持续时间和资源变化。
docker exec -e MYSQL_PWD=labpass -i mysql-primary mysql -uroot <<'SQL'
DROP DATABASE IF EXISTS ddl_lab;
CREATE DATABASE ddl_lab;
USE ddl_lab;
CREATE TABLE digits (
n TINYINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE orders (
id BIGINT UNSIGNED PRIMARY KEY,
tenant_id INT UNSIGNED NOT NULL,
status ENUM('NEW','PAID','CANCELLED') NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
payload VARCHAR(200) NOT NULL,
KEY idx_tenant_status_created (tenant_id, status, created_at)
) ENGINE=InnoDB;
INSERT INTO orders (id, tenant_id, status, amount, created_at, payload)
SELECT
x.id,
MOD(x.id, 100) + 1,
CASE MOD(x.id, 3)
WHEN 0 THEN 'NEW'
WHEN 1 THEN 'PAID'
ELSE 'CANCELLED'
END,
MOD(x.id, 10000) / 100,
CAST('2026-01-01 00:00:00' AS DATETIME) + INTERVAL MOD(x.id, 2592000) SECOND,
RPAD(CONCAT('order-', x.id), 180, 'x')
FROM (
SELECT 1 + a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n + 100000*f.n AS id
FROM digits a
CROSS JOIN digits b
CROSS JOIN digits c
CROSS JOIN digits d
CROSS JOIN digits e
CROSS JOIN digits f
) AS x;
DROP TABLE digits;
SELECT COUNT(*) AS rows_on_source FROM orders;
SQL预期输出
rows_on_source
1000000数据由主库写入并通过复制应用到副本。下面等待副本同时满足复制连接状态、applier 状态和行数达到 100 万;任一条件在超时后仍未满足,脚本会明确失败,不继续进行后续 DDL 实验。
set -euo pipefail
state=""
rows=""
ready=false
for i in $(seq 1 60); do
state=$(docker exec -e MYSQL_PWD=labpass mysql-replica mysql -uroot -Nse "SELECT CONCAT(COALESCE((SELECT SERVICE_STATE FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME=''), 'OFF'), '|', COALESCE((SELECT SERVICE_STATE FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME=''), 'OFF'));" )
rows=$(docker exec -e MYSQL_PWD=labpass mysql-replica mysql -uroot -Nse "SELECT COUNT(*) FROM ddl_lab.orders" 2>/dev/null || true)
if [ "$state" = "ON|ON" ] && [ "$rows" = "1000000" ]; then
ready=true
break
fi
sleep 1
done
if [ "$ready" != true ]; then
echo "replica baseline timeout: state=$state rows=$rows" >&2
exit 1
fi
echo "replica baseline ready: state=$state rows=$rows"预期输出
replica baseline ready: state=ON|ON rows=1000000制造并发读写
DDL 风险只有放到活跃业务流量中才容易复现。下面同时启动一个短时写循环和一个读循环:写线程修改不同主键行,读线程持续扫描固定租户;两者约运行数秒,不会无限占用终端。
set -euo pipefail
for i in $(seq 1 60); do
docker exec -e MYSQL_PWD=labpass mysql-primary mysql -uroot -D ddl_lab -e "UPDATE orders SET status=IF(status='PAID','NEW','PAID') WHERE id=$((1000 + i));" >/dev/null
sleep 0.1
done &
writer_pid=$!
for i in $(seq 1 60); do
docker exec -e MYSQL_PWD=labpass mysql-primary mysql -uroot -D ddl_lab -Nse "SELECT COUNT(*) FROM orders WHERE tenant_id=42 AND status='PAID';" >/dev/null
sleep 0.1
done &
reader_pid=$!
wait "$writer_pid" "$reader_pid"
echo "concurrent workload finished"预期输出
concurrent workload finished这里的目标不是制造高吞吐,而是让后续 DDL 测试始终面对可重复的读写竞争。需要更长负载时,增加循环次数即可,不要先扩大并发数,否则 CPU 饱和可能掩盖锁和 DDL 算法本身的差异。
验收实验基线
最后同时确认三件事:主库开启 Performance Schema、副本连接与应用线程正常、并发写入后的关键数据已经追平。只有这些条件同时成立,后续 DDL 实验才有可比较的基线。
docker exec -e MYSQL_PWD=labpass mysql-primary mysql -uroot -Nse "SELECT CONCAT('performance_schema=', @@performance_schema);"
docker exec -e MYSQL_PWD=labpass mysql-replica mysql -uroot -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_IO_Running:|Replica_SQL_Running:|Seconds_Behind_Source:|Auto_Position:'
source_paid=$(docker exec -e MYSQL_PWD=labpass mysql-primary mysql -uroot -Nse "SELECT SUM(status='PAID') FROM ddl_lab.orders")
set -euo pipefail
replica_paid=""
matched=false
for i in $(seq 1 30); do
replica_paid=$(docker exec -e MYSQL_PWD=labpass mysql-replica mysql -uroot -Nse "SELECT SUM(status='PAID') FROM ddl_lab.orders")
if [ "$source_paid" = "$replica_paid" ]; then
matched=true
break
fi
sleep 1
done
if [ "$matched" != true ]; then
echo "final data check failed: source_paid=$source_paid replica_paid=$replica_paid" >&2
exit 1
fi
echo "source_paid=$source_paid"
echo "replica_paid=$replica_paid"预期输出
performance_schema=1
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Auto_Position: 1
source_paid=<VALUE>
replica_paid=<VALUE>最后两行的实际计数取决于并发脚本修改了哪些状态,但两端应相等。若复制线程不是 Yes、applier 未正常运行,或计数在超时后仍不一致,应先修复复制链路再进行 Online DDL 测试;否则 DDL 对副本的影响无法与既有复制故障区分。