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 为例。

bash
docker --version
docker compose version

预期输出

text
Docker version <VALUE>
Docker Compose version v<VALUE>

版本号会随环境变化;关键是两条命令都能正常返回。实验使用独立容器和测试密码,只适合本地或隔离测试机,不应照搬到生产环境。

固定实验拓扑

实验包含两个 MySQL 8.4 实例。客户端只向 mysql-primary 发起 DML 和 DDL;主库通过 GTID 异步复制到 mysql-replica。两端都开启 Performance Schema,后续可以把锁、事务和复制状态放在同一条观察链上。

MySQL Online DDL 双节点实验拓扑

在空目录保存下面的 compose.yaml。主库和副本使用不同 server-id,同时开启 binary log、GTID 和行格式复制;副本启用 super-read-only,避免实验时误把业务写入发到副本。

yaml
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,再建立复制链路。

bash
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"

预期输出

text
primary ready
replica ready

初始化复制与大表

先在主库创建仅用于复制的账号,再让副本使用 GTID 自动定位。未配置 TLS 的本地隔离实验中,GET_SOURCE_PUBLIC_KEY=1 可用于向主库请求 RSA 公钥完成认证。

bash
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

预期输出

text
replication_connection=ON
replication_applier=ON

这里必须同时验证 receiver 和 applier:前者证明副本能够连接并接收源端数据,后者证明已接收的事务能够正常应用。任一状态不是 ON 都不能进入后续数据与 DDL 实验。

接着在主库生成一张 100 万行的 orders 表。payload 列故意扩大行宽,使需要扫描或重建数据的 DDL 更容易在实验机上观察到持续时间和资源变化。

bash
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

预期输出

text
rows_on_source
1000000

数据由主库写入并通过复制应用到副本。下面等待副本同时满足复制连接状态、applier 状态和行数达到 100 万;任一条件在超时后仍未满足,脚本会明确失败,不继续进行后续 DDL 实验。

bash
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"

预期输出

text
replica baseline ready: state=ON|ON rows=1000000

制造并发读写

DDL 风险只有放到活跃业务流量中才容易复现。下面同时启动一个短时写循环和一个读循环:写线程修改不同主键行,读线程持续扫描固定租户;两者约运行数秒,不会无限占用终端。

bash
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"

预期输出

text
concurrent workload finished

这里的目标不是制造高吞吐,而是让后续 DDL 测试始终面对可重复的读写竞争。需要更长负载时,增加循环次数即可,不要先扩大并发数,否则 CPU 饱和可能掩盖锁和 DDL 算法本身的差异。

验收实验基线

最后同时确认三件事:主库开启 Performance Schema、副本连接与应用线程正常、并发写入后的关键数据已经追平。只有这些条件同时成立,后续 DDL 实验才有可比较的基线。

bash
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"

预期输出

text
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 对副本的影响无法与既有复制故障区分。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

搭建可复现的 DDL 实验环境

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通