MySQL local lab quickstart 的核心責任是建立後續 ProxySQL、OSC、replication、backup 與 Vitess sandbox 共用的本地環境。這個 lab 提供可重建 MySQL instance、baseline schema、seed data 與 basic evidence。

本文的驗收標準是：你能啟動 MySQL、套用 schema、跑 sample workload、取得 processlist / InnoDB status / table count，並能 teardown 重建。

Docker Compose

Docker Compose 的核心責任是讓 lab 環境可重建。

 1services:
 2  mysql:
 3    image: mysql:8.4
 4    environment:
 5      MYSQL_ROOT_PASSWORD: root_pw
 6      MYSQL_DATABASE: appdb
 7      MYSQL_USER: app_user
 8      MYSQL_PASSWORD: app_pw
 9    ports:
10      - "33069:3306"
11    command:
12      - "--performance-schema=ON"
13      - "--log-bin=mysql-bin"
14      - "--server-id=1"

啟動：

1docker compose up -d
2export MYSQL_PWD=app_pw
3mysql -h 127.0.0.1 -P 33069 -u app_user appdb -e "SELECT VERSION();"

Baseline Schema

Baseline schema 的核心責任是建立可測 transaction、index、binlog 與 OSC 的模型。

 1mysql -h 127.0.0.1 -P 33069 -u app_user appdb <<'SQL'
 2CREATE TABLE accounts (
 3  id BIGINT PRIMARY KEY AUTO_INCREMENT,
 4  tenant_id CHAR(36) NOT NULL,
 5  owner_name VARCHAR(128) NOT NULL,
 6  status ENUM('active', 'closed') NOT NULL,
 7  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 8  KEY idx_accounts_tenant (tenant_id)
 9) ENGINE=InnoDB;
10
11CREATE TABLE ledger_entries (
12  id BIGINT PRIMARY KEY AUTO_INCREMENT,
13  account_id BIGINT NOT NULL,
14  amount_cents BIGINT NOT NULL,
15  idempotency_key VARCHAR(128) NOT NULL,
16  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
17  UNIQUE KEY uk_ledger_idempotency (idempotency_key),
18  KEY idx_ledger_account_created (account_id, created_at),
19  CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES accounts(id)
20) ENGINE=InnoDB;
21SQL

Seed and Evidence

Seed and evidence 的核心責任是產生可重跑資料與 baseline。

 1mysql -h 127.0.0.1 -P 33069 -u app_user appdb <<'SQL'
 2INSERT INTO accounts(tenant_id, owner_name, status)
 3VALUES ('tenant-a', 'Ada', 'active'), ('tenant-b', 'Lin', 'active');
 4
 5INSERT INTO ledger_entries(account_id, amount_cents, idempotency_key)
 6VALUES (1, 1000, 'seed-ada-1'), (1, -200, 'seed-ada-2'), (2, 500, 'seed-lin-1');
 7
 8SELECT a.owner_name, SUM(l.amount_cents) AS balance_cents
 9FROM accounts a JOIN ledger_entries l ON l.account_id = a.id
10GROUP BY a.owner_name;
11SQL

Basic evidence：

1mysql -h 127.0.0.1 -P 33069 -u app_user appdb -e "SHOW FULL PROCESSLIST;"
2mysql -h 127.0.0.1 -P 33069 -u app_user appdb -e "SHOW TABLE STATUS;"
3mysql -h 127.0.0.1 -P 33069 -u app_user appdb -e "SHOW ENGINE INNODB STATUS\\G"

Teardown

Teardown 的核心責任是讓 lab 可重跑。

1docker compose down -v

完成本篇後，backup 進入 Backup Restore Drill；schema change 進入 Online Schema Change Lab；routing 進入 ProxySQL Routing Lab。