Migration MySQL 8.4 — giảm downtime, giảm rủi ro, giữ backward-compatible

Bài này là bản song song của Migration PostgreSQL — giảm downtime…: cùng mục tiêu (đổi schema trên hệ thống đang chạy mà downtime (thời gian gián đoạn) gần bằng 0, giữ backward-compatible (tương thích ngược — code bản cũ vẫn chạy với schema mới) và rollback (quay lại bản cũ) được), cùng thứ tự Phần 0 → Phần 8, để đọc từng phần và so ngay MySQL khác PostgreSQL ở đâu. Có thêm hai phần riêng của MySQL:

• Phần 2b — khi Online DDL native không đủ: gh-ost và pt-online-schema-change (pt-osc), đo thật trên bảng 3 triệu dòng có tải ghi.
• Phần 5b — áp vào hệ thống thật trong CV: LMS Uni (Moodle + student portal NestJS dùng chung một database MySQL primary/replica, blue-green deploy). Phần này để bảo vệ trung thực câu CV "quy trình deploy zero-downtime … migration theo expand–contract" — kể cả giới hạn của nó: đổi schema bảng Moodle không zero-downtime.

Không lặp lại nội dung đã có ở bài khác: chiến lược deploy (rolling, blue-green) ở chien-luoc-deployment; migration chạy ở stage nào, lock khi chạy migrate, có nên rollback ở migration-ci-cd.

Môi trường lab (mọi output trong bài lấy từ đây): MySQL 8.4.13 (image mysql:8.4.13, InnoDB, binlog_format=ROW, binlog_row_image=FULL, GTID bật), 1 primary + 1 replica (GTID auto-position, replica_parallel_workers=4), mỗi container 2 CPU / 1,5 GB RAM, buffer pool 384 MB; gh-ost 1.1.11, percona-toolkit 3.7.2 (pt-online-schema-change). Bảng minh hoạ kiểu LMS submissions 3.000.000 dòng (~466 MB data + 178 MB index). Host thật đang chạy service khác nên số tuyệt đối dao động — đọc tỉ lệ và hình dạng, đừng thuộc con số. Script và log ở labs/database/mysql-migration/, bảng tra ở Phụ lục.

Phần 0. Hai thứ phải hiểu trước

Bài PostgreSQL nói hai thứ: lock queue và table rewrite. MySQL có đúng hai thứ tương đương, tên khác và chi tiết khác: metadata lock queue và table rebuild (dựng lại bảng). Thêm một thứ thứ ba mà PostgreSQL gần như không có: replica chỉ bắt đầu chạy DDL sau khi primary chạy xong — nói ở 0.3.

0.1. Metadata lock (MDL) queue — lý do downtime thật sự xảy ra

Mỗi câu lệnh chạm vào một bảng — kể cả SELECT — đều xin một metadata lock (khoá metadata — khoá trên định nghĩa bảng, không phải trên dòng) ở mức SHARED_READ (đọc) hoặc SHARED_WRITE (ghi), và giữ tới hết transaction, không phải tới hết câu lệnh. DDL thì cần EXCLUSIVE ít nhất trong một khoảnh khắc. Cơ chế xếp hàng giống hệt PostgreSQL: khi DDL đang chờ EXCLUSIVE, mọi request MDL đến sau nó — kể cả SELECT vốn không xung đột với transaction đang giữ lock — phải xếp sau DDL.

sequenceDiagram
  participant A as Session A (BEGIN + SELECT, rồi im lặng)
  participant T as MDL trên bảng submissions
  participant B as ALTER ... ALGORITHM=INSTANT
  participant C as 5 câu SELECT mới
  A->>T: SHARED_READ (GRANTED, giữ tới COMMIT)
  B->>T: SHARED_UPGRADABLE (GRANTED)
  B->>T: xin EXCLUSIVE → PENDING, chờ A
  C->>T: SHARED_READ → PENDING, xếp sau B
  Note over C: bị chặn dù chỉ đọc
  A->>T: COMMIT sau 25 giây
  T-->>B: EXCLUSIVE granted, ALTER chạy ~0 ms
  T-->>C: SHARED_READ granted

Lab tái hiện (03-mdl-queue.sh, phần A). Session A mở transaction, đọc 1 dòng rồi im lặng 25 giây — đúng hình dạng của một tab DBeaver quên commit, hoặc một request ORM mở transaction rồi đi gọi API ngoài. Hai giây sau chạy một ALTER … ADD COLUMN …, ALGORITHM=INSTANT (thao tác "chỉ sửa metadata"), rồi bắn 5 câu SELECT. Trước hết, giá trị mặc định:

SELECT @@lock_wait_timeout AS lock_wait_timeout_mac_dinh;
-- | lock_wait_timeout_mac_dinh |
-- |                   31536000 |      ← 31.536.000 giây = 365 ngày

Cách nói khi phỏng vấn: "Biến quyết định một câu DDL được phép đứng chờ khoá metadata bao lâu là lock_wait_timeout, và mặc định của MySQL là một năm. Nghĩa là nếu không cấu hình gì, một câu ALTER gặp transaction treo sẽ đứng chờ gần như vô hạn, và trong lúc đó chặn mọi truy vấn đến sau trên bảng ấy. Đó là khác biệt đầu tiên em kiểm tra khi chuyển từ PostgreSQL sang MySQL."

Ảnh chụp lúc 6 giây sau khi ALTER bắt đầu chờ — ba góc nhìn, nên thuộc cả ba vì đây là thứ cần gõ lúc 3h sáng:

-- 1) processlist: ai đang chờ cái gì
SELECT ID, USER, COMMAND, TIME, STATE, LEFT(INFO, 70) AS INFO
FROM information_schema.PROCESSLIST WHERE DB='lms' AND ID <> CONNECTION_ID() ORDER BY ID;
-- | 538 | root | Sleep   | 7 |                                 | NULL                                         |
-- | 539 | root | Query   | 5 | Waiting for table metadata lock | ALTER TABLE submissions ADD COLUMN note ...  |
-- | 540 | root | Query   | 3 | Waiting for table metadata lock | SELECT COUNT(*) FROM submissions WHERE id <= 10 |
-- | 541 … 544: giống 540

-- 2) performance_schema.metadata_locks: lock nào GRANTED, lock nào PENDING
SELECT ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_DURATION, ml.LOCK_STATUS, t.PROCESSLIST_ID AS conn_id
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_SCHEMA='lms' AND ml.OBJECT_NAME='submissions';
-- | submissions | SHARED_READ       | TRANSACTION | GRANTED |  538 |   ← session A, đang Sleep
-- | submissions | SHARED_UPGRADABLE | TRANSACTION | GRANTED |  539 |   ← ALTER đã có phần "đọc định nghĩa"
-- | submissions | EXCLUSIVE         | TRANSACTION | PENDING |  539 |   ← ALTER chờ độc quyền
-- | submissions | SHARED_READ       | TRANSACTION | PENDING |  540 |   ← SELECT xếp hàng sau ALTER
-- | … 541, 542, 543, 544 giống 540

-- 3) sys.schema_table_lock_waits: ai chặn ai, kèm luôn lệnh KILL gợi ý
SELECT object_name, waiting_pid, waiting_lock_type, blocking_pid, blocking_lock_type, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits WHERE object_schema='lms';
-- | submissions | 539 | EXCLUSIVE   | 538 | SHARED_READ       | KILL 538 |
-- | submissions | 540 | SHARED_READ | 538 | SHARED_READ       | KILL 538 |
-- | submissions | 540 | SHARED_READ | 539 | SHARED_UPGRADABLE | KILL 539 |
-- | … (12 dòng)

Cách nói khi phỏng vấn: "Khi site treo nghi do migration, em xem ba chỗ. Danh sách tiến trình cho biết ai đang ở trạng thái chờ khoá metadata. Bảng metadata_locks trong performance_schema cho biết khoá nào đã được cấp, khoá nào đang chờ, và của kết nối nào. View schema_table_lock_waits trong sys ghép hai thứ đó thành 'ai chặn ai' và gợi ý luôn lệnh KILL — ở đây thủ phạm gốc là kết nối 538 đang ngủ trong transaction, không phải câu ALTER."

Kết quả khi session A commit (sau 25 giây):

10:26:04.063 session A COMMIT
SELECT-C1  bắt đầu 10:25:42  chờ+chạy  21614 ms  10
ALTER-B    bắt đầu 10:25:40  chờ+chạy  23653 ms  altered
SELECT-C2  bắt đầu 10:25:43  chờ+chạy  21344 ms  10
SELECT-C3  bắt đầu 10:25:43  chờ+chạy  21068 ms  10
SELECT-C5  bắt đầu 10:25:44  chờ+chạy  20411 ms  10
SELECT-C4  bắt đầu 10:25:43  chờ+chạy  20756 ms  10

Một ALTER "INSTANT" làm 5 câu SELECT 10 dòng mất 20–22 giây. Ở tải thật của LMS Uni lúc cao điểm, 20 giây là hàng trăm request PHP-FPM đứng chờ, hết worker, Nginx trả 502/504 — tức là sập site, dù câu DDL không hề "nặng".

Cách chống — giống ý tưởng lock_timeout của PostgreSQL: đặt lock_wait_timeout ngắn cho riêng session chạy migration rồi retry.

SET SESSION lock_wait_timeout = 3;   -- chờ MDL tối đa 3 giây rồi bỏ
ALTER TABLE submissions ADD COLUMN note VARCHAR(50) NULL, ALGORITHM=INSTANT;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   (sau 3246 ms)

Cách nói khi phỏng vấn: "Session chạy migration em đặt thời gian chờ khoá metadata khoảng ba giây. Nếu có transaction dài đang giữ bảng, câu ALTER tự bỏ cuộc sau ba giây thay vì đứng chặn cả hàng đợi, các truy vấn của người dùng được đi tiếp, và job migration thử lại sau vài giây. Thà migration chậm vài lần thử còn hơn site đứng."

Đo cùng tình huống (phần B): các SELECT chỉ phải chờ 1,6–2,2 giây — đúng phần còn lại của 3 giây ALTER đứng trong hàng — rồi chạy được ngay, dù session A vẫn chưa commit. Phần C là vòng retry (lock_wait_timeout=3, nghỉ 2 giây):

10:26:29.353 lần 1: ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
10:26:34.679 lần 2: ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
10:26:37.252 session A COMMIT
10:26:37.324 lần 3: OK sau 639 ms

Hai lưu ý chỉ MySQL có:

1. Hai biến timeout, hai loại khoá khác nhau. lock_wait_timeout (mặc định 1 năm) áp cho metadata lock; innodb_lock_wait_timeout (mặc định 50 giây, lab đo ở Phần 3) áp cho khoá dòng của InnoDB. Đặt nhầm biến thì không có tác dụng gì với hàng đợi MDL. Mã lỗi giống nhau (1205), chỉ khác loại khoá.

2. Không có transactional DDL. PostgreSQL bọc được RENAME + CREATE VIEW trong một transaction. MySQL thì mỗi DDL tự commit ngầm transaction đang mở. Lab (05-instant-limit.sh, phần cuối):

   BEGIN;
   INSERT INTO t_tx VALUES (1);
   ALTER TABLE t_tx ADD COLUMN a INT NULL, ALGORITHM=INSTANT;   -- commit ngầm dòng 1
   INSERT INTO t_tx (id) VALUES (2);                            -- phiên đã về autocommit → tự commit
   ROLLBACK;                                                    -- không còn gì để huỷ
   SELECT * FROM t_tx;
   -- | id | a    |
   -- |  1 | NULL |
   -- |  2 | NULL |
   

   Cách nói khi phỏng vấn: "Trong MySQL, hễ gặp một câu đổi cấu trúc là transaction đang mở bị commit luôn, và từ đó phiên quay về chế độ tự commit. Nên lệnh rollback phía sau không cứu được gì — cả hai dòng đều đã nằm trong bảng. Hệ quả cho migration: một file migration có ba câu ALTER mà câu thứ hai lỗi thì câu thứ nhất đã áp dụng xong, không có chuyện rollback cả file như PostgreSQL. Vì thế em giữ mỗi migration một câu DDL."

   Cùng chuỗi lệnh trên PostgreSQL 18.4 (12-pg-compare.sh): sau ROLLBACK, bảng chỉ còn id = 1 có từ trước, cột a cũng biến mất — DDL nằm trong transaction và bị huỷ cùng.

   MySQL 8.0+ có atomic DDL (một câu DDL hoặc xong hẳn hoặc không gì cả, kể cả khi server chết giữa chừng) — nhưng đó là atomic từng câu, không phải transaction nhiều câu. (Tính chất crash-safe này lấy từ tài liệu MySQL, lab chưa kill server giữa DDL — chưa verify.)

0.2. INSTANT / INPLACE / COPY — lý do lock kéo dài

PostgreSQL chia hai loại: sửa catalog (mili-giây) và rewrite bảng. InnoDB chia ba thuật toán, chọn bằng mệnh đề ALGORITHM=, cộng mệnh đề LOCK= quy định cho phép ghi đồng thời hay không:

"Online" trong Online DDL của MySQL nghĩa là cho phép DML trong lúc chạy, không có nghĩa là không khoá. Tài liệu MySQL 8.4 (mục Online DDL Performance and Concurrency) chia một câu online DDL làm ba pha; lab ở dưới chứng minh pha 3 là chỗ hay gây sự cố:

flowchart LR
  P1["Pha 1 — Initialization<br/>MDL SHARED_UPGRADABLE<br/>(chặn DDL khác, cho DML)"]
  P2["Pha 2 — Execution<br/>MDL EXCLUSIVE thoáng qua lúc chuẩn bị<br/>rồi build index / rebuild bảng<br/>DML vẫn chạy, ghi vào online alter log"]
  P3["Pha 3 — Commit table definition<br/>nâng lên MDL EXCLUSIVE<br/>phải chờ MỌI transaction đang giữ bảng"]
  P1 --> P2 --> P3
  P3 -.->|"transaction dài mở giữa chừng"| Q["Hàng đợi MDL như 0.1<br/>mọi query mới bị chặn"]

Lab (03-mdl-queue.sh, phần D): ADD INDEX … ALGORITHM=INPLACE, LOCK=NONE trên 3 triệu dòng. Hai giây sau khi ALTER bắt đầu, một session mở transaction đọc 1 dòng rồi giữ 40 giây. Một probe chạy SELECT 1 dòng mỗi nửa giây.

--- đối chứng: cùng ALTER ADD INDEX nhưng không có transaction nào chặn
ALTER-IDX0 bắt đầu 10:28:21  chờ+chạy  25408 ms  altered

--- có transaction mở SAU khi ALTER bắt đầu:
--- processlist (lúc 10:27:01.325)
| 559 | root | Query | 24 | Waiting for table metadata lock | ALTER TABLE submissions ADD INDEX idx_status_created ... |
| 561 | root | Sleep | 22 |                                 | NULL                                                     |
| 599 | root | Query |  2 | Waiting for table metadata lock | SELECT id FROM submissions WHERE id=3                    |
--- performance_schema.metadata_locks
| submissions | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 559 |
| submissions | SHARED_READ       | TRANSACTION | GRANTED | 561 |
| submissions | EXCLUSIVE         | TRANSACTION | PENDING | 559 |
| submissions | SHARED_READ       | TRANSACTION | PENDING | 599 |
10:27:19.657 session A2 COMMIT
ALTER-IDX  bắt đầu 10:26:37  chờ+chạy  42542 ms  altered
10:27:20.186 probe SELECT mất 21437 ms

Đọc số: bình thường build index mất ~25 giây và không ai bị chặn. Lần này ALTER mất 42,5 giây, và một SELECT bị chặn 21,4 giây — dù câu lệnh khai báo LOCK=NONE. Transaction gây chuyện được mở sau khi migration đã chạy, nên kiểm tra "không có transaction dài" lúc bắt đầu là chưa đủ: lock_wait_timeout phải đặt cho cả câu, vì nó cũng áp cho lần xin EXCLUSIVE ở pha 3. Cái giá: nếu timeout ở pha 3, công build 25 giây bị bỏ, phải chạy lại từ đầu.

Cách nói khi phỏng vấn: "LOCK bằng NONE chỉ có nghĩa là trong lúc build, người dùng vẫn đọc ghi được. Ở đầu và cuối, MySQL vẫn cần khoá độc quyền trên định nghĩa bảng trong chốc lát. Em đã đo: một transaction mở giữa chừng làm câu thêm index phải chờ ở bước cuối, và trong lúc chờ nó chặn một truy vấn đọc hơn hai mươi giây. Nên em đặt thời gian chờ khoá ngắn cho cả câu lệnh, chấp nhận chạy lại, và theo dõi transaction dài trong suốt quá trình chứ không chỉ lúc bắt đầu."

Nguyên tắc vàng của PostgreSQL vẫn đúng nhưng phải viết lại cho MySQL: trên bảng lớn, không chạy thao tác COPY trong giờ làm việc; thao tác INPLACE có rebuild thì chạy được nhưng phải tính replica lag (0.3); và luôn ghi rõ ALGORITHM, LOCK để MySQL từ chối thay vì lặng lẽ chọn thuật toán nặng hơn (lý do ở 2.11).

0.3. Replica chạy DDL sau primary — thứ PostgreSQL không có

Physical replication của PostgreSQL stream WAL (nhật ký ghi trước) ngay trong lúc primary đang rewrite, nên replica replay gần như song song (bài này không đo phía PG). MySQL replication (binlog) thì gửi câu DDL sau khi nó commit trên primary. Replica bắt đầu chạy câu đó từ đầu, mất ngần ấy thời gian lần nữa, và tài liệu MySQL 8.4 ghi rõ: DML đến sau trên primary chỉ được apply trên replica sau khi replica chạy xong DDL. Hệ quả: ALTER 10 phút trên primary = replica trễ ~10 phút ngay sau đó, kể cả khi primary "không khoá gì".

sequenceDiagram
  participant P as Primary
  participant BL as Binlog
  participant R as Replica (đọc tách sang đây)
  participant U as User đọc qua replica
  P->>P: ALTER ... INPLACE rebuild (N giây), DML vẫn chạy
  P->>BL: ghi câu ALTER khi COMMIT xong
  BL->>R: ALTER
  R->>R: chạy lại ALTER từ đầu (N giây nữa)
  P->>BL: DML mới (nộp bài, chấm điểm)
  Note over R: DML mới đứng sau ALTER
  U->>R: đọc → dữ liệu cũ N giây

Số đo thật ở Phần 2 (cột "replica thêm") và Phần 2b. Với hệ thống tách đọc sang replica như LMS Uni, đây là lý do số 1 để dùng gh-ost/pt-osc cho bảng lớn kể cả khi native INPLACE chạy được — chi tiết ở Phần 5b.

Phần 1. Expand–Migrate–Contract — khung sườn cho mọi thay đổi

Khung sườn y hệt bài PostgreSQL (Phần 1 bên đó): không đổi tại chỗ; tách một thay đổi phá vỡ thành nhiều release nhỏ, release nào cũng tương thích ngược với code bản trước. Bốn quy tắc rút gọn (thêm trước dùng sau; ngừng dùng trước xoá sau; đổi tên/đổi kiểu = thêm mới + copy + xoá cũ; migration và deploy code là hai bước tách rời) giữ nguyên. Cái MySQL thay đổi là giá của từng bước:

flowchart LR
  E["EXPAND<br/>ADD COLUMN NULL / DEFAULT hằng số<br/>→ INSTANT, ~mili-giây"]
  M1["MIGRATE<br/>code dual-write<br/>backfill batch (Phần 3)<br/>→ lo row lock + replica lag"]
  M2["Chuyển đọc sang cột mới<br/>index mới: INPLACE LOCK=NONE<br/>hoặc gh-ost nếu bảng lớn"]
  C["CONTRACT<br/>DROP COLUMN → INSTANT<br/>(release sau vài ngày)"]
  E --> M1 --> M2 --> C

Ba điểm MySQL khác khi áp khung này:

1. Expand và Contract đều rẻ trên MySQL 8.0.29+ vì ADD COLUMN (mọi vị trí) và DROP COLUMN là INSTANT — đo ở 2.1, 2.5. Nhưng INSTANT có hạn mức 64 lần mỗi bảng (2.11); team làm expand–contract đều đặn trên một bảng nóng sẽ chạm hạn mức này nhanh hơn tưởng tượng.

2. Bước siết ràng buộc đắt hơn PG. MySQL không có NOT VALID rồi VALIDATE, nên bật NOT NULL hay thêm CHECK trên bảng lớn là rebuild (INPLACE) hoặc COPY (2.2, 2.8). Thường phải dùng gh-ost/pt-osc cho bước này, hoặc chấp nhận để ràng buộc ở tầng ứng dụng.

3. Một migration = một câu DDL, vì không có transactional DDL (0.1). File migration nhiều câu fail giữa chừng sẽ để schema ở trạng thái nửa vời, và bảng migrations của framework không ghi nhận là đã chạy — lần chạy lại vấp lỗi "Duplicate column".

Phần 2. Bảng tra cứu nhanh theo từng thao tác

Khác bài PostgreSQL (cột "Lock"), ở MySQL câu hỏi đúng là: thuật toán mạnh nhất MySQL chấp nhận là gì? Script 04-algorithms.sh thử từng thao tác lần lượt với ALGORITHM=INSTANT → INPLACE, LOCK=NONE → INPLACE, LOCK=SHARED → COPY, dừng ở cái đầu tiên MySQL chấp nhận, và ghi: có rebuild không (TABLE_ID trong information_schema.INNODB_TABLES đổi = bảng được dựng lại), primary mất bao lâu, replica cần thêm bao lâu để khớp GTID. Bảng t_alg 916.975 dòng (file .ibd 221 MB), primary không có tải khác. Cột replica có ~0,4–0,6 giây là chi phí của chính công cụ đo (gọi docker exec + WAIT_FOR_EXECUTED_GTID_SET), nên chỉ đọc khi nó lên hàng chục giây.

Ba dòng làm người quen PostgreSQL bất ngờ nhất: nới lỏng NOT NULL vẫn rebuild cả bảng (PG chỉ sửa catalog); nới VARCHAR qua mốc 255 byte là COPY (với utf8mb4 mỗi ký tự tính 4 byte, nên mốc là VARCHAR(63)); và không có cách thêm CHECK/FK có kiểm tra mà không khoá ghi. Nguồn đối chiếu: bảng "Online DDL Support for … Operations" trong tài liệu MySQL 8.4 (mục 17.12.1) — lab khớp với bảng đó ở mọi dòng đã thử.

-- Mẫu chạy thật của script cho một dòng (INT -> BIGINT):
ALTER TABLE t_alg MODIFY user_id BIGINT NOT NULL, ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
ALTER TABLE t_alg MODIFY user_id BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
ALTER TABLE t_alg MODIFY user_id BIGINT NOT NULL, ALGORITHM=COPY;
-- Query OK, 916975 rows affected (24.88 sec)        rebuild=YES (TABLE_ID 1084 -> 1085), replica đuổi kịp thêm 33399 ms

Cách nói khi phỏng vấn: "Em không đoán MySQL sẽ dùng thuật toán nào. Em ghi rõ thuật toán mong muốn vào câu lệnh: nếu MySQL không làm được theo cách nhẹ đó thì nó báo lỗi ngay, kèm lý do, thay vì lặng lẽ chuyển sang cách copy cả bảng và khoá ghi. Đổi kiểu từ int sang bigint là ví dụ: nhanh nhất cũng chỉ có cách copy, gần một triệu dòng mất hai mươi lăm giây khoá ghi, rồi replica trễ thêm ba mươi giây — lúc đó em chuyển sang gh-ost."

2.1. Thêm cột mới (case đơn giản nhất)

SET SESSION lock_wait_timeout = 3;
ALTER TABLE submissions ADD COLUMN channel VARCHAR(20) NULL, ALGORITHM=INSTANT;

Cách nói khi phỏng vấn: "Thêm cột cho phép rỗng là thao tác chỉ sửa từ điển dữ liệu, mất vài trăm mili-giây trên bảng gần một triệu dòng. Rủi ro không nằm ở thời gian chạy mà ở hàng đợi khoá metadata, nên em vẫn đặt thời gian chờ khoá ba giây và ghi rõ thuật toán INSTANT."

Vì sao backward-compatible: giống PG — code cũ không biết cột, INSERT không liệt kê cột thì MySQL điền NULL. Bẫy SELECT * map theo vị trí và INSERT … VALUES (…) không nêu tên cột cũng giống PG — và ở MySQL còn nặng hơn vì AFTER col chèn cột vào giữa được, làm lệch vị trí mọi cột sau nó.

Hai khác biệt so với PG 2.1:

1. Default là biểu thức thì bị từ chối hẳn, không phải rewrite. Có binlog bật (mọi hệ thống có replica), MySQL không cho dùng hàm cho ra giá trị khác nhau trên replica:

   ALTER TABLE t_alg ADD COLUMN token CHAR(36) NULL DEFAULT (UUID()), ALGORITHM=INSTANT;
   -- ERROR 1674 (HY000): Statement is unsafe because it uses a system function that may return a different value on the replica.
   -- (INPLACE, LOCK=NONE / INPLACE, LOCK=SHARED / COPY: cùng lỗi 1674)
   

   Cách nói khi phỏng vấn: "Muốn thêm cột có giá trị mặc định là một UUID sinh ngẫu nhiên thì MySQL có binlog từ chối luôn, vì primary và replica sẽ sinh ra hai giá trị khác nhau. Cách làm là thêm cột cho phép rỗng, sinh giá trị ở tầng ứng dụng cho dòng mới, và backfill dòng cũ theo lô."

2. INSTANT có hạn mức — mỗi lần ADD/DROP COLUMN instant tốn một "row version", tối đa 64 — xem 2.11.

2.2. Thêm cột NOT NULL

Ở PostgreSQL, ADD COLUMN … NOT NULL không default trên bảng có dữ liệu là lỗi ngay ở DB (chạy trên PostgreSQL 18.4 container test, 12-pg-compare.sh: ERROR: column "attempt" of relation "tmp_mym_nn" contains null values). MySQL thì chấp nhận và lặng lẽ điền lặng lẽ điền "giá trị ngầm định" của kiểu (0 cho số, chuỗi rỗng cho chuỗi) vàoquot;giá trị ngầm địnhlặng lẽ điền "giá trị ngầm định" của kiểu (0 cho số, chuỗi rỗng cho chuỗi) vàoquot; của kiểu (với INT là 0) vào dòng cũ — nên migration chạy xanh, rồi code cũ chết (04b-extra.sh):

ALTER TABLE t_x ADD COLUMN attempt INT NOT NULL, ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.03 sec)
SELECT id, attempt FROM t_x ORDER BY id LIMIT 2;
-- |  1 | 0 |
-- |  2 | 0 |
-- code cũ (không biết cột attempt), sql_mode có STRICT_TRANS_TABLES (mặc định 8.4):
INSERT INTO t_x (user_id, assignment_id, status, content, created_at, updated_at) VALUES (1, 1, 'new', 'old-code', NOW(), NOW());
-- ERROR 1364 (HY000): Field 'attempt' doesn't have a default value
ALTER TABLE t_x ALTER COLUMN attempt SET DEFAULT 0, ALGORITHM=INSTANT;
-- chạy lại INSERT của code cũ: OK

Cách nói khi phỏng vấn: "MySQL cho thêm cột bắt buộc mà không có giá trị mặc định, tự điền số không vào dữ liệu cũ, nên migration trông như thành công. Nhưng bản code cũ vẫn đang chạy trong lúc blue-green chưa chuyển xong không biết cột này, và mọi lệnh thêm dòng của nó bị từ chối. Quy tắc của em: cột mới hoặc cho phép rỗng, hoặc có giá trị mặc định, ở đúng release thêm nó."

Cách đúng, theo thứ tự (so với 4 bước bên PG):

1. Release 1: ADD COLUMN channel VARCHAR(20) NOT NULL DEFAULT 'web', ALGORITHM=INSTANT — được luôn NOT NULL mà không rebuild, vì default hằng số. Đây là đường tắt PG không có.
2. Nếu giá trị đúng cho dòng cũ khác default: thêm cột NULL, backfill batch (Phần 3), rồi mới siết.
3. Siết MODIFY channel VARCHAR(20) NOT NULL trên cột đã có: INPLACE rebuild (đo 16,5 s cho 917k dòng, replica +16 s). Dòng nào còn NULL thì lỗi ERROR 1138 Invalid use of NULL value sau khi đã quét. Bảng vài chục triệu dòng → gh-ost, hoặc giữ ràng buộc ở tầng ứng dụng.

MySQL không có cách "mượn CHECK NOT VALID" như PG 12+. CHECK enforced trong MySQL là COPY (2.8), nên không dùng CHECK để né rebuild được.

2.3. Đổi tên cột

Giống hệt PG: RENAME COLUMN là INSTANT (471 ms) nhưng phá code cũ ngay lập tức → làm expand–contract (thêm cột mới, dual-write, backfill, chuyển đọc, xoá cột cũ) — sơ đồ ở bài PG 2.3. Riêng MySQL: đổi tên cột đang được FK bảng khác trỏ tới thì INSTANT bị từ chối, INPLACE thì được, và FK bên bảng con tự cập nhật:

ALTER TABLE courses RENAME COLUMN id TO cid, ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ALTER TABLE courses RENAME COLUMN id TO cid, ALGORITHM=INPLACE, LOCK=NONE;
-- Query OK (152 ms, không rebuild)
SHOW CREATE TABLE enrolments\G
--   CONSTRAINT `fk_enrol_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`cid`) ON DELETE CASCADE

Cách nói khi phỏng vấn: "Đổi tên cột về phía database thì rẻ, kể cả khi cột đó đang được khoá ngoại trỏ tới — MySQL tự sửa định nghĩa khoá ngoại bên bảng con. Cái đắt là code: bản cũ vẫn gọi tên cũ. Nên với hệ thống chạy hai bản song song, em coi đổi tên là thêm cột mới rồi bỏ cột cũ qua nhiều release."

2.4. Đổi tên bảng — và FK từ các bảng khác trỏ vào thì sao?

Kết luận giống PG: FK không vỡ. PG trỏ theo OID; MySQL thì data dictionary sửa luôn định nghĩa FK bên bảng con sang tên mới, và FK vẫn chặn đúng (04-algorithms.sh, dòng 32):

ALTER TABLE courses RENAME TO courses_v2, ALGORITHM=INSTANT;      -- 611 ms, TABLE_ID không đổi
SHOW CREATE TABLE enrolments\G
--   CONSTRAINT `fk_enrol_course` FOREIGN KEY (`course_id`) REFERENCES `courses_v2` (`id`) ON DELETE CASCADE
INSERT INTO enrolments (course_id, user_id) VALUES (999999, 1);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (... REFERENCES `courses_v2` (`id`) ON DELETE CASCADE)

Cách nói khi phỏng vấn: "Đổi tên một bảng đang có bảng con trỏ khoá ngoại vào không làm vỡ khoá ngoại ở MySQL: định nghĩa bên bảng con được sửa theo tên mới và vẫn chặn dữ liệu sai. Vấn đề thật là code bản cũ gọi tên cũ, nên đi kèm một view mang tên cũ trong vài ngày."

Nhánh A bản MySQL — RENAME + view mang tên cũ

MySQL có view ghi được (updatable view) cho trường hợp SELECT từ đúng một bảng, nên mẹo của PG dùng được, trừ một điểm: không bọc được hai lệnh trong một transaction (0.1). Giữa RENAME TABLE và CREATE VIEW có một khoảnh khắc tên cũ không tồn tại; request rơi vào khe đó nhận ERROR 1146 Table doesn't exist. Gửi hai lệnh liền nhau trong một kết nối thì khe đó rất ngắn — nhưng không bằng 0 (lab không đo độ dài khe — chưa verify). Lab 04c-rename-view.sh:

RENAME TABLE users_v TO accounts_v; CREATE VIEW users_v AS SELECT * FROM accounts_v;
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS WHERE TABLE_SCHEMA='lms';
-- | users_v | YES |
INSERT INTO users_v (name) VALUES ('d'); SELECT LAST_INSERT_ID();          -- 4  (Laravel insertGetId dựa vào cái này)
UPDATE users_v SET name='bb' WHERE id=2;                                      -- OK
INSERT INTO users_v (id, name) VALUES (2, 'dup') ON DUPLICATE KEY UPDATE name='upsert';   -- OK
DELETE FROM users_v WHERE id=4;                                               -- OK
-- FK của bảng con orders_v tự trỏ sang accounts_v (như dòng 32 ở trên)

Cách nói khi phỏng vấn: "Ở MySQL em vẫn dùng được mẹo đổi tên bảng rồi dựng view mang tên cũ: view lấy từ đúng một bảng thì thêm, sửa, xoá, upsert qua view đều chạy và lấy được id vừa sinh. Khác PostgreSQL là hai lệnh không gói chung một transaction được, nên có một khe rất ngắn tên cũ không tồn tại — em chạy lúc thấp điểm và để code cũ có retry."

Bốn cái bẫy của PG xuất hiện đủ cả ở MySQL, đo thật:

Nhánh B (bảng mới thật — đổi kiểu PK, chuyển partition) thì như PG: chuyển từng FK một; khác ở chỗ bước thêm FK không có NOT VALID — dùng cách ở 2.8.

2.5. Xoá cột

Về phía DB, MySQL 8.0.29+ DROP COLUMN là INSTANT (269 ms, không rebuild): cột chỉ bị đánh dấu trong data dictionary, dữ liệu cũ vẫn nằm trong các dòng tới lần rebuild sau. Tài liệu MySQL ghi "Rebuilds Table: Yes" cho DROP COLUMN — đó là cho đường INPLACE; với ALGORITHM=INSTANT lab đo được TABLE_ID không đổi. Hai rủi ro phía code giữ nguyên như PG 2.5: code cũ còn đọc cột → lỗi; và không rollback được dữ liệu. Thứ tự an toàn y hệt: release N ngừng dùng → theo dõi 2–7 ngày → release N+1 mới DROP.

Thêm một lý do MySQL để không vội xoá: mỗi lần DROP COLUMN instant tốn một row version (2.11). Gom nhiều cột cần xoá vào một câu ALTER TABLE … DROP COLUMN a, DROP COLUMN b, ALGORITHM=INSTANT chỉ tốn một version.

2.6. Đổi kiểu dữ liệu (vd INT → BIGINT)

Đây là chỗ MySQL tệ hơn PG một bậc ở bảng có FK. Hai số đo:

• INT → BIGINT trên bảng không FK: chỉ COPY, 25,2 s cho 917k dòng, khoá ghi suốt thời gian đó (LOCK=NONE bị từ chối: ERROR 1846 … COPY algorithm requires a lock. Try LOCK=SHARED.), replica thêm 33,4 s. Trên 3 triệu dòng có tải, đo ở Phần 2b.
• Cột có FK: bị từ chối hẳn với mọi thuật toán, vì hai đầu FK phải cùng kiểu:

ALTER TABLE enrolments MODIFY course_id BIGINT NOT NULL, ALGORITHM=COPY;
-- ERROR 3780 (HY000): Referencing column 'course_id' and referenced column 'id' in foreign key constraint 'fk_enrol_course' are incompatible.

Cách nói khi phỏng vấn: "Đổi kiểu một cột trong MySQL gần như luôn là copy cả bảng và khoá ghi. Nếu cột đó nằm trong khoá ngoại thì còn không đổi được một phía, vì MySQL bắt hai đầu khoá ngoại cùng kiểu. Với bảng lớn em có hai đường: gh-ost hoặc pt-osc để copy nền không khoá, hoặc expand–contract bằng cột mới kiểu mới, ghi song song, backfill, rồi chuyển khoá ngoại sang cột mới."

Chọn đường nào: bảng không có FK và đổi kiểu không làm code phải đổi (INT→BIGINT thì driver/ORM đọc vẫn ra số) → gh-ost, một lần chạy, không cần dual-write. Bảng có FK → gh-ost từ chối (2b.4) → pt-osc với --alter-foreign-keys-method, hoặc expand–contract như PG 2.6. Khoá chính (PK) INT→BIGINT trên bảng cha có nhiều bảng con trỏ vào là ca khó nhất — phải lên kế hoạch riêng, như PG.

2.7. Tạo / sửa / xoá index

MySQL không có từ khoá CONCURRENTLY — ADD INDEX mặc định đã là INPLACE, cho ghi đồng thời, không rebuild bảng (3,9 s trên 917k dòng; 30,7 s trên 3 triệu dòng có tải — 2b). Ghi rõ để MySQL từ chối nếu không làm được online:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE submissions ADD INDEX idx_status_created (status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

Cách nói khi phỏng vấn: "Thêm index trong MySQL 8 đã là thao tác trực tuyến, người dùng vẫn ghi được trong lúc build. Em vẫn ghi rõ thuật toán và mức khoá để nếu vì lý do nào đó không làm trực tuyến được thì câu lệnh báo lỗi thay vì khoá bảng. Hai thứ còn phải canh là khoá metadata ở bước cuối và độ trễ replica bằng đúng thời gian build."

Những điều cần nhớ, so với danh sách bên PG:

• Không có "index INVALID". Build fail (ví dụ UNIQUE gặp dữ liệu trùng) thì MySQL huỷ sạch, không để lại rác — khác PG. Nhưng lỗi chỉ hiện sau khi đã quét cả bảng:

SELECT content, COUNT(*) AS n FROM t_x GROUP BY content HAVING n > 1 ORDER BY n DESC LIMIT 3;
-- | 7a8969a2…ed8ae9 | 5 |   (dữ liệu sinh bằng REPEAT(MD5(RAND()),3) vẫn trùng)
ALTER TABLE t_x ADD UNIQUE INDEX uq_content (content), ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1062 (23000): Duplicate entry '974d907d…' for key 't_x.uq_content'

Cách nói khi phỏng vấn: "Trước khi thêm ràng buộc duy nhất, em chạy câu đếm nhóm trùng trước. Nếu không, câu thêm index sẽ quét hết bảng rồi mới báo trùng — tốn công, tốn replica lag, mà không được gì. Khác PostgreSQL ở chỗ MySQL không để lại index hỏng, nên không phải dọn."

• Không chạy được trong transaction không phải vấn đề — MySQL vốn không có transactional DDL; Laravel không bọc migration MySQL trong transaction.
• Replica lag = thời gian build (0.3): ADD INDEX 3 triệu dòng → replica trễ ~31 s (2b).
• "Sửa index" = tạo mới rồi xoá cũ, giống PG. MySQL có thêm invisible index (index ẩn — vẫn được cập nhật khi ghi nhưng optimizer không dùng): ẩn index cũ trước khi xoá, theo dõi vài ngày, cần thì hiện lại ngay — "rename-park" cho index. Lab (04c-rename-view.sh, bảng 3 triệu dòng):

EXPLAIN SELECT id FROM submissions WHERE assignment_id = 42 AND status = 'graded';
-- possible_keys: idx_assignment,idx_assignment_inv | key: idx_assignment_inv | rows: 205 | Extra: Using index
ALTER TABLE submissions ALTER INDEX idx_assignment_inv INVISIBLE;      -- 844 ms
EXPLAIN SELECT id FROM submissions WHERE assignment_id = 42 AND status = 'graded';
-- possible_keys: idx_assignment | key: idx_assignment | rows: 544 | Extra: Using where
ALTER TABLE submissions ALTER INDEX idx_assignment_inv VISIBLE;        -- 348 ms

Cách nói khi phỏng vấn: "Trước khi xoá một index, em ẩn nó: index vẫn được cập nhật nhưng bộ tối ưu không chọn nữa. Nếu có truy vấn nào chậm hẳn đi thì hiện lại trong chưa tới một giây, không phải build lại từ đầu. Yên ổn vài ngày mới xoá thật."

2.8. Thêm constraint (CHECK, FOREIGN KEY)

Khác biệt lớn nhất với PG: MySQL không có NOT VALID + VALIDATE. Đo được:

• ADD CHECK (enforced) → chỉ COPY: 43,7 s, khoá ghi, replica +30 s.
• ADD CHECK … NOT ENFORCED → INSTANT, nhưng ràng buộc không kiểm tra gì cả, kể cả dòng mới (13-quick-checks.sh: chèn score = -5 vẫn OK). Bật lên khi đã có dòng vi phạm thì ERROR 3819 Check constraint 'ck_pos' is violated. Bật lên bằng ALTER CHECK … ENFORCED → lại COPY (31,7 s).
• ADD FOREIGN KEY với foreign_key_checks=1 → COPY. Với foreign_key_checks=0 trong session → INPLACE, LOCK=NONE, 294 ms, không rebuild — nhưng MySQL không kiểm tra dữ liệu cũ (dòng mới thì có).

-- FK an toàn kiểu MySQL: tự làm phần "VALIDATE" bằng tay TRƯỚC
SELECT COUNT(*) FROM enrolments e LEFT JOIN courses c ON c.id = e.course_id WHERE c.id IS NULL;  -- phải = 0
SET SESSION foreign_key_checks = 0;      -- chỉ session migration
SET SESSION lock_wait_timeout = 3;
ALTER TABLE enrolments ADD CONSTRAINT fk_enrol_course FOREIGN KEY (course_id) REFERENCES courses(id)
  ON DELETE CASCADE, ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;

Cách nói khi phỏng vấn: "MySQL không có kiểu thêm ràng buộc trước rồi kiểm tra dữ liệu cũ sau mà không khoá như PostgreSQL. Với khoá ngoại, em tự đếm dòng mồ côi bằng một phép nối trái, phải ra không, rồi thêm khoá ngoại với kiểm tra tắt trong đúng phiên migration — MySQL làm trực tuyến và chỉ sửa metadata. Rủi ro là khoảng giữa lúc đếm và lúc thêm có thể sinh dòng mồ côi mới, nên câu đếm chạy lại một lần sau khi thêm."

CHECK trên bảng lớn: không có đường tắt online native. Ba lựa chọn, xếp theo mức hay dùng: (1) để ràng buộc ở tầng ứng dụng/validation; (2) gh-ost/pt-osc chạy ADD CHECK trên bảng bóng (copy nền) — cut-over sẽ fail nếu dữ liệu vi phạm, nên soi trước bằng SELECT COUNT(*) … WHERE NOT (điều kiện); (3) chấp nhận COPY trong cửa sổ bảo trì. Backward-compatibility của constraint giống PG: siết chặt hơn thì sửa code trước, nới lỏng thì luôn an toàn với code cũ.

Một dòng tài liệu chưa khớp lab: tài liệu MySQL 8.4 (Online DDL Limitations) ghi LOCK=NONE không được phép khi bảng có FK ON … CASCADE/ON … SET NULL. Trên 8.4.13, lab chạy được ALTER TABLE enrolments FORCE, ALGORITHM=INPLACE, LOCK=NONE và ADD INDEX … LOCK=NONE trên chính bảng con có ON DELETE CASCADE (04b-extra.sh). Chưa tìm ra thao tác nào bị giới hạn này chặn — chưa verify; cứ ghi LOCK=NONE để MySQL tự báo nếu gặp.

2.9. Đổi constraint đã có

Như PG: không có "ALTER CONSTRAINT" cho phần điều kiện → thêm cái mới trước, xoá cái cũ sau. Ở MySQL bước "thêm cái mới" là COPY với CHECK, INPLACE (+ foreign_key_checks=0) với FK, còn DROP CHECK là INSTANT (390 ms) và DROP FOREIGN KEY là INPLACE không rebuild (526 ms). Đổi CHECK trên bảng lớn vì vậy thường đi đường gh-ost/pt-osc: một --alter "DROP CHECK ck_old, ADD CONSTRAINT ck_new CHECK (…)" chạy trên bảng bóng.

2.10. Xoá bảng

DROP TABLE một bảng 221 MB đo được 455 ms. Rủi ro vẫn là "không quay lại được", nên dùng mẹo rename-park y như PG 2.10:

RENAME TABLE legacy_logs TO zz_legacy_logs_deprecated_20261026;   -- 160 ms trong lab (bảng 917k dòng)
-- 1–2 tuần yên tĩnh, không ai kêu:
DROP TABLE zz_legacy_logs_deprecated_20261026;

Cách nói khi phỏng vấn: "Em không xoá bảng ngay. Em đổi tên nó thành tên có hạn dùng, chờ một hai tuần; nếu có báo cáo hay job nào còn gọi tới thì đổi tên ngược lại trong một giây. Không ai kêu thì mới xoá. Đổi tên trong MySQL chỉ sửa từ điển dữ liệu nên gần như miễn phí."

Kiểm tra còn ai dùng trước khi xoá, bản MySQL:

-- FK trỏ tới bảng này?
SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME = 'legacy_logs';
-- bảng còn được đọc/ghi không? (số đếm tăng giữa hai lần chạy = còn người dùng)
SELECT COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = DATABASE() AND OBJECT_NAME = 'legacy_logs';

Cách nói khi phỏng vấn: "Trước khi xoá em xem hai thứ: còn khoá ngoại nào trỏ tới bảng không, và bộ đếm đọc ghi theo bảng trong performance_schema có còn tăng giữa hai lần xem không. Bộ đếm reset khi restart server, nên phải so hai thời điểm chứ không nhìn một con số." (Lab 04c-rename-view.sh: một câu SELECT đọc 98 dòng của courses làm COUNT_READ đi từ 1 lên 99. Bảng vừa bị pt-osc dựng lại thì chưa có dòng thống kê nào — một lý do nữa để so hai thời điểm.)

2.11. Giới hạn của INSTANT — 64 row version, và cú rơi lặng lẽ sang rebuild

Mỗi câu ADD/DROP COLUMN … ALGORITHM=INSTANT tạo một row version mới trong metadata của bảng (cột TOTAL_ROW_VERSIONS trong information_schema.INNODB_TABLES). MySQL 8.4 cho tối đa 64 (9.1+ là 255 — theo tài liệu, chưa verify). Lab 05-instant-limit.sh: 32 cặp ADD + DROP rồi thêm cột thứ 33:

TOTAL_ROW_VERSIONS ban đầu: 0
sau 8 lần ADD+DROP: TOTAL_ROW_VERSIONS = 16
sau 32 lần ADD+DROP: TOTAL_ROW_VERSIONS = 64
vòng 33, 'ADD COLUMN c33 INT NULL' -> ERROR 4092 (HY000) at line 1: Maximum row versions reached for table lms/t_ver. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
-- không chỉ định ALGORITHM (để MySQL tự chọn) thì sao?
Query OK, 0 rows affected (0.20 sec)
TOTAL_ROW_VERSIONS: 0                       ← MySQL đã lặng lẽ REBUILD cả bảng
-- reset bằng rebuild: ALTER TABLE ... FORCE (INPLACE)
TOTAL_ROW_VERSIONS sau FORCE: 0

Dòng đáng sợ là dòng giữa: không ghi ALGORITHM thì MySQL không báo lỗi mà tự chuyển sang rebuild. Bảng 2 dòng mất 0,2 giây; bảng submissions 3 triệu dòng thì là một lần rebuild ~30–60 giây kèm replica lag tương đương (đo ở 2b), xảy ra vào đúng một lần deploy "chỉ thêm một cột nullable" mà không ai ngờ. Cùng cơ chế: ép ALGORITHM=INPLACE cho ADD COLUMN (thói quen từ MySQL 5.7) cũng rebuild — đo 12,1 s thay vì 0,3 s (04b-extra.sh).

-- Đưa vào checklist: bảng nào sắp hết row version?
SELECT NAME, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES
WHERE TOTAL_ROW_VERSIONS > 50 ORDER BY TOTAL_ROW_VERSIONS DESC;

Cách nói khi phỏng vấn: "Thêm hay bỏ cột tức thì trong MySQL 8 có hạn mức sáu mươi tư lần cho mỗi bảng; hết hạn mức thì phải dựng lại bảng mới reset. Nguy hiểm là nếu câu lệnh không ghi rõ thuật toán, MySQL tự dựng lại bảng mà không báo, nên một migration tưởng mất một giây có thể thành một phút khoá kèm replica trễ. Em ghi rõ thuật toán tức thì trong mọi migration thêm cột để nó báo lỗi, và theo dõi số version còn lại; khi gần chạm thì chủ động dựng lại bảng bằng gh-ost vào giờ thấp điểm."

Phần 2b. Khi native không đủ: gh-ost vs pt-online-schema-change vs Online DDL

PostgreSQL ít khi cần công cụ ngoài (có CONCURRENTLY, NOT VALID; pg_repack cho rebuild). MySQL thì hệ sinh thái online schema change (OSC — đổi schema bằng cách copy sang bảng bóng rồi tráo) là công cụ hằng ngày, vì ba lý do đo được ở trên: thao tác COPY khoá ghi, rebuild INPLACE làm replica trễ bằng thời gian chạy và không giảm tốc được, và pha cuối của online DDL vẫn có khoảnh khắc độc quyền.

2b.1. Mốc so sánh: native Online DDL trên 3 triệu dòng có tải ghi

06-native-under-load.sh: bảng sub_native 3.000.199 dòng; tải nền 4 writer (INSERT + UPDATE theo PK, mục tiêu 50 thao tác/s mỗi writer) + 1 reader SELECT theo PK trên primary; một luồng ghi heartbeat NOW(6) lên primary mỗi 100 ms và một luồng đọc nó trên replica mỗi 250 ms → lag thật. Mỗi case: tải chạy 10 s, chạy ALTER, chờ replica khớp GTID, thêm 8 s.

Cách đọc từng dòng:

1. b — index: primary không đứng ghi giây nào; cái giá nằm ở replica: lag lên 31 s, đúng bằng thời gian build (0.3). Seconds_Behind_Source báo 61 — gấp đôi lag thật, vì nó tính từ lúc câu ALTER bắt đầu trên primary (31 s chạy trên primary + 30 s chạy trên replica).

2. c — rebuild "LOCK=NONE": trong 110 s đa phần ghi vẫn chạy, nhưng có 10 giây liền (10:51:13 → 10:51:22) không một câu ghi nào và không một câu đọc nào trên bảng hoàn thành, rồi cả loạt xong cùng lúc ở 10:51:23 với độ trễ 10,5 s. Đó là pha cuối: áp nốt online alter log tích luỹ trong lúc rebuild và đổi định nghĩa bảng dưới khoá độc quyền. Trích out/06-c-force-rebuild-load.log:

   time      w_ok w_err  w_p50  w_p99    w_max | r_ok   r_max | lag_max_ms
   10:51:12   121     0   13.5   52.9     53.3 |   29    12.5 |      251
   10:51:13     0     0    0.0    0.0      0.0 |    0     0.0 |      124
   …  (10:51:14 → 10:51:21: cùng 0 / 0)
   10:51:22     0     0    0.0    0.0      0.0 |    0     0.0 |      126
   10:51:23    32     0   24.8 10523.2  10523.2 |    9 10485.3 |      472
   10:51:24   141     0   12.1  113.2    122.5 |   35    51.0 |     1491
   

3. d — COPY: 350 giây không ghi được (đọc vẫn được — LOCK=SHARED), một câu ghi chờ 348,7 s; rồi replica chạy lại câu COPY thêm 369 s, lag 374 s. Với LMS Uni lúc cao điểm, đây là gần 6 phút không ai nộp được bài rồi 6 phút trang "bài đã nộp" đọc từ replica hiển thị dữ liệu cũ.

Cách nói khi phỏng vấn: "Em đo ba thao tác native trên bảng ba triệu dòng có tải ghi. Thêm index: primary không đứng giây nào nhưng replica trễ ba mươi mốt giây. Dựng lại bảng với khoá NONE: vẫn có mười giây cuối mọi đọc ghi trên bảng đứng hẳn. Đổi kiểu cột bằng copy: gần sáu phút không ghi được, rồi replica trễ thêm sáu phút. Ba con số đó là lý do em dùng gh-ost cho mọi thay đổi rebuild trên bảng lớn có tải."

Lab chạy trên host dùng chung, replica chỉ 2 CPU — có lúc replica còn đang đuổi backlog của case trước (lag nền ở đầu case c/d). Số tuyệt đối sẽ khác trên máy thật; hình dạng (đứng ghi ở cuối, lag ≈ thời gian DDL) là thứ cần nhớ.

2b.2. pt-online-schema-change — bảng bóng + trigger + copy theo chunk + RENAME nguyên tử

Cơ chế (đọc từ output --print thật của lab, khớp với source bin/pt-online-schema-change qua Context7):

flowchart TD
  A["1. CREATE TABLE _t_new (định nghĩa gốc)<br/>ALTER TABLE _t_new ... (bảng rỗng → nhanh)"]
  B["2. Tạo 3 trigger AFTER INSERT / UPDATE / DELETE trên t<br/>→ REPLACE INTO / DELETE IGNORE sang _t_new<br/>(chạy TRONG transaction của ứng dụng)"]
  C["3. Copy theo chunk:<br/>INSERT LOW_PRIORITY IGNORE INTO _t_new SELECT ... WHERE id BETWEEN ? AND ?<br/>LOCK IN SHARE MODE — chunk tự co giãn theo --chunk-time 0.5s"]
  D{"Sau mỗi chunk:<br/>replica lag > --max-lag?<br/>Threads_running > --max-load?"}
  E["Chờ (trigger VẪN chạy)"]
  F["4. RENAME TABLE t TO _t_old, _t_new TO t<br/>(một lệnh, nguyên tử)"]
  G["5. DROP _t_old, DROP 3 trigger"]
  A --> B --> C --> D
  D -- "có" --> E --> D
  D -- "không, còn chunk" --> C
  D -- "hết chunk" --> F --> G

Trigger thật pt-osc tạo trên bảng (lấy từ information_schema.TRIGGERS giữa lúc chạy):

-- pt_osc_lms_sub_ptosc_ins  AFTER INSERT:
BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `lms`.`_sub_ptosc_new` (...) VALUES (NEW....); END
-- pt_osc_lms_sub_ptosc_upd  AFTER UPDATE:
BEGIN ...; DELETE IGNORE FROM `lms`.`_sub_ptosc_new` WHERE !(OLD.`id` <=> NEW.`id`) AND ...; REPLACE INTO ...; END
-- pt_osc_lms_sub_ptosc_del  AFTER DELETE:
BEGIN ...; DELETE IGNORE FROM `lms`.`_sub_ptosc_new` WHERE `lms`.`_sub_ptosc_new`.`id` <=> OLD.`id`; END
-- chunk copy:
INSERT LOW_PRIORITY IGNORE INTO `lms`.`_sub_ptosc_new` (...) SELECT ... FROM `lms`.`sub_ptosc` FORCE INDEX(`PRIMARY`)
  WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE
-- swap:
RENAME TABLE `lms`.`sub_ptosc` TO `lms`.`_sub_ptosc_old`, `lms`.`_sub_ptosc_new` TO `lms`.`sub_ptosc`

Cách nói khi phỏng vấn: "pt-osc tạo một bảng mới đã đổi cấu trúc, gắn ba trigger lên bảng cũ để mọi thêm, sửa, xoá của ứng dụng được chép sang bảng mới ngay trong cùng transaction, rồi copy dữ liệu cũ sang theo từng lô nhỏ, lô nào cũng tự canh độ trễ replica và tải. Copy xong thì đổi tên hai bảng bằng một lệnh đổi tên nguyên tử. Điểm yếu nằm ở trigger: chúng chạy trong transaction của người dùng nên làm mỗi lần ghi nặng hơn, và không tắt được khi muốn tạm dừng."

Ba điều lab chỉ ra mà README không nói to:

1. Cần quyền tạo trigger khi binlog bật. Lần chạy đầu với user không có SUPER fail ngay bước tạo trigger (out/07-ptosc-attempt1-no-super.log):

   Error creating triggers: ... You do not have the SUPER privilege and binary logging is enabled
   (you *might* want to use the less safe log_bin_trust_function_creators variable)
   ...
   `lms`.`sub_ptosc` was not altered.
   

   Lab bật SET GLOBAL log_bin_trust_function_creators = ON rồi chạy lại; MySQL 8.4 trả cảnh báo 1287 '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release. Trên RDS/Cloud SQL thì phải đổi qua parameter group. Đây là một quyết định bảo mật phải ghi vào runbook, không phải chi tiết vặt. gh-ost không tạo trigger nên không cần.

2. Tool fail để lại rác. Cùng log: Not dropping the new table `lms`.`_sub_ptosc_new` because the tool was interrupted — lần chạy sau phải dọn tay (DROP TABLE IF EXISTS `lms`.`_sub_ptosc_new`).

3. Trigger vẫn chạy khi tool đang chờ lag (source: after_nibble chỉ chờ, không gỡ trigger — Context7). Nghĩa là "tạm dừng" của pt-osc chỉ dừng phần copy; phần ghi kép vẫn đè lên primary.

2b.3. gh-ost — không trigger, đọc binlog như một replica, cut-over có kiểm soát

Theo tài liệu thiết kế của gh-ost (doc/triggerless-design.md, doc/cut-over.md trên GitHub; Context7 chưa có gh-ost): gh-ost tự đăng ký như một replica, đọc binlog dạng ROW, lọc sự kiện của bảng cần đổi và tự áp sang bảng bóng _t_gho bằng một kết nối duy nhất, xen kẽ với việc copy theo chunk. Không có trigger, nên khi throttle thì tải ghi thêm trên primary về gần 0 thật.

flowchart TD
  A["1. Tạo _t_ghc (changelog + heartbeat) và _t_gho (bảng bóng)<br/>ALTER _t_gho"]
  B["2. Kết nối như replica (Binlog Dump GTID)<br/>đọc sự kiện ROW của bảng t"]
  C["3. Một luồng ghi duy nhất xen kẽ:<br/>copy chunk (--chunk-size 1000) + áp sự kiện binlog (--dml-batch-size 10)"]
  T{"Throttle?<br/>replica lag > --max-lag-millis<br/>flag file, --max-load, lệnh qua socket"}
  P{"Copy xong — có postpone flag file?"}
  W["Hoãn cut-over: vẫn áp binlog,<br/>bảng gốc vẫn phục vụ"]
  X["4. Cut-over nguyên tử:<br/>LOCK t + bảng sentry _t_del → chờ áp hết binlog tới lock<br/>→ RENAME t TO _t_del, _t_gho TO t (bị chặn chờ)<br/>→ DROP sentry, UNLOCK → RENAME chạy"]
  A --> B --> C --> T
  T -- "có" --> C
  T -- "không" --> P
  P -- "có" --> W --> P
  P -- "không" --> X

Trích log cut-over thật (out/08-ghost-tool.log, sau khi lab xoá postpone flag lúc 11:35:47.527):

11:35:48.391 INFO Locking `lms`.`sub_ghost`, `lms`.`_sub_ghost_del`
11:35:48.415 INFO Tables locked
11:35:48.430 INFO Writing changelog state: AllEventsUpToLockProcessed:1793010948405794238
11:35:48.728 INFO Done waiting for events up to lock; duration=237.570133ms
11:35:48.748 INFO Issuing and expecting this to block: rename /* gh-ost */ table `lms`.`sub_ghost` to `lms`.`_sub_ghost_del`, `lms`.`_sub_ghost_gho` to `lms`.`sub_ghost`
11:35:48.751 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
11:35:48.796 INFO Tables unlocked
11:35:48.826 INFO Tables renamed
11:35:48.832 INFO Lock & rename duration: 445.933394ms. During mgtr time, queries on `sub_ghost` were blocked

Cách nói khi phỏng vấn: "gh-ost không dùng trigger. Nó giả làm một replica, đọc binlog dạng từng dòng, và tự chép thay đổi sang bảng bóng bằng một kết nối, xen kẽ với việc copy dữ liệu cũ. Vì mọi lần ghi vào bảng bóng đều do nó làm, nó có thể dừng thật khi replica trễ hay khi mình bấm dừng. Lúc đổi bảng, nó khoá bảng gốc, đợi áp hết binlog tới thời điểm khoá, rồi đổi tên nguyên tử — trong lab, khoảng truy vấn bị chặn là bốn trăm năm mươi mili-giây."

Hai chế độ điều khiển đã thử thật:

• Throttle bằng flag file (8 giây): log chuyển sang State: throttled, flag-file, số dòng copy đứng ở 47000 trong khi Backlog binlog tăng dần (21 → 299 sự kiện) — tức là gh-ost ngừng ghi hẳn, gom sự kiện lại để áp sau.
• Throttle tự động theo replica lag: State: throttled, tmp-mym-replica:3306 replica-lag=2.352667s mỗi khi replica (yếu, host chung) trễ quá --max-lag-millis=2000.
• Hoãn cut-over: copy xong lúc 11:35:37, state postponing cut-over trong 10 giây — gh-ost vẫn áp binlog (Applied 6200 → 6603), bảng gốc phục vụ bình thường, tới khi mình cho phép. Trong thực tế: copy chạy cả ngày, cut-over đặt vào 2h sáng.

2b.4. Đo cùng điều kiện: native COPY vs pt-osc vs gh-ost

Cùng thao tác MODIFY user_id BIGINT NOT NULL (chỉ chạy được COPY), cùng bảng 916.975 dòng (~221 MB), cùng tải nền 2 writer × 25 thao tác/s + reader + heartbeat (06b-native-copy-small.sh, 07-ptosc.sh, 08-ghost.sh). Ngưỡng lag của hai công cụ đều 2 giây.

(*) gh-ost: có 1 giây không ghi được lúc 11:32:05 và lần ghi 1,84 s lúc 11:32:06 — trước khi gh-ost khởi động (11:32:11), trùng lúc host đang tải nặng; không tính cho gh-ost.

Đọc bảng như một tech lead:

1. Công cụ đổi thời gian lấy an toàn. Native nhanh gấp 3–10 lần nhưng 21 giây không ai ghi được và replica trễ 29 giây. Hai công cụ không làm đứng ghi giây nào, replica gần như không trễ — đổi lại chạy lâu hơn nhiều.

2. Công cụ đẩy chi phí sang binlog và đĩa. Native ghi một câu DDL vào binlog (replica tự làm lại); công cụ ghi cả bảng dưới dạng sự kiện ROW (~122–127 MB cho bảng 221 MB file). Bảng 200 GB nghĩa là ~100+ GB binlog thêm và đĩa cần chỗ cho bản sao thứ hai — phải tính trước khi chạy.

3. gh-ost chậm nhất ở đây vì nó nhường nhất: một luồng ghi, throttle mỗi khi replica trễ quá 2 s. Lần thử đầu trên bảng 3 triệu dòng với tải 4×50 ops/s (out/08-ghost-attempt1-3M-aborted-tool.log) chỉ copy được 8,2% sau 7 phút: replica 2 CPU trên host đang load average ~11 trễ liên tục, gh-ost throttle liên tục (State: throttled, tmp-mym-replica:3306 replica-lag=1.924666s), ETA 1h16m — lab dừng tay vì host là production. Đó đúng là hành vi thiết kế: replica yếu thì migration chạy lâu, chứ không đè chết replica.

Cách nói khi phỏng vấn: "Em đo cùng một thao tác đổi kiểu cột trên gần một triệu dòng có tải ghi. Cách native nhanh nhất, hai mươi hai giây, nhưng suốt hai mươi mốt giây không ai ghi được và replica trễ gần ba mươi giây. pt-osc mất bảy mươi giây, gh-ost gần bốn phút, nhưng không làm đứng ghi giây nào và replica trễ chỉ vài giây. Cái giá của công cụ là thời gian, gấp đôi dung lượng bảng trên đĩa, và lượng binlog xấp xỉ kích thước bảng."

2b.5. Foreign key: gh-ost từ chối, pt-osc phải viết lại FK của bảng con

09-fk-tools.sh trên courses (cha, 2.000 dòng) ← enrolments (con, 200k dòng, ON DELETE CASCADE):

=== gh-ost trên bảng CON:
ERROR found 1 child-side foreign keys on `lms`.`enrolments`. Child-side foreign keys are not supported. Bailing out
=== gh-ost trên bảng CHA:
ERROR found 1 parent-side foreign keys on `lms`.`courses`. Parent-side foreign keys are not supported. Bailing out
=== pt-osc trên bảng CHA, không chỉ định --alter-foreign-keys-method:
Child tables:
  `lms`.`enrolments` (approx. 195172 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.
=== pt-osc trên bảng CHA, --alter-foreign-keys-method=rebuild_constraints:
ALTER TABLE `lms`.`enrolments` DROP FOREIGN KEY `fk_enrol_course`, ADD CONSTRAINT `_fk_enrol_course` FOREIGN KEY (`course_id`) REFERENCES `lms`.`_courses_new` (`id`) ON DELETE CASCADE
RENAME TABLE `lms`.`courses` TO `lms`.`_courses_old`, `lms`.`_courses_new` TO `lms`.`courses`
-- sau đó: FK tên mới `_fk_enrol_course` trỏ `courses`, vẫn chặn (ERROR 1452), ON DELETE CASCADE vẫn chạy (100 → 0 dòng)
=== pt-osc trên bảng CON: FK của chính nó đổi tên thành `__fk_enrol_course` (thêm 2 dấu gạch dưới)

Ba hệ quả cho người vận hành:

1. rebuild_constraints chạy một câu ALTER TABLE trên bảng con. Bảng con 200k dòng thì nhẹ; bảng con 50 triệu dòng thì chính câu đó là một ALTER nặng (đổi FK với foreign_key_checks=0 là INPLACE không rebuild theo 2.8 — pt-osc tự set foreign_key_checks=0 khi rebuild constraint theo source; thời gian trên bảng con lớn — chưa verify).

2. Tên FK bị đổi (_fk…, __fk…, xoay vòng mỗi lần chạy) — migration sau của framework mà DROP FOREIGN KEY fk_enrol_course theo tên cũ sẽ fail. Ghi lại tên mới hoặc đổi tên lại.

3. drop_swap (xoá bảng gốc rồi đổi tên — có khoảnh khắc bảng không tồn tại): release notes 3.0.11 nói dùng không được trên MySQL 8.0+ do bug đổi tên làm mất FK; bản 3.7.2 vẫn nhận tham số ở chế độ --dry-run. Không dùng.

Và một cái bẫy của gh-ost: --skip-foreign-key-checks làm nó bỏ qua kiểm tra chứ không hỗ trợ FK (09b-ghost-skip-fk.sh):

-- trước: | fk_child_parent | fk_child | fk_parent |
gh-ost ... --skip-foreign-key-checks --execute     → chạy xong
-- sau:  REFERENTIAL_CONSTRAINTS của fk_child: (rỗng)
INSERT INTO fk_child (parent_id) VALUES (999999);   → OK        ← FK đã mất, không một lời cảnh báo

Cách nói khi phỏng vấn: "gh-ost không hỗ trợ khoá ngoại, cả phía bảng con lẫn bảng cha, và nó từ chối chạy. Có một cờ bỏ qua kiểm tra, nhưng em đã thử: chạy xong thì khoá ngoại biến mất khỏi bảng và dữ liệu sai được chèn vào bình thường. Bảng có khoá ngoại thì em dùng pt-osc với chế độ dựng lại ràng buộc bên bảng con, và ghi lại tên ràng buộc mới vì công cụ đổi tên nó."

2b.6. Ma trận quyết định

Tránh khi nào: đừng dùng gh-ost/pt-osc cho thứ native INSTANT làm được (tốn hàng phút copy và hàng trăm MB binlog cho việc mili-giây); đừng dùng khi replica lag vốn đã cao (công cụ throttle mãi không xong, như lần thử 3 triệu dòng); đừng chạy hai công cụ trên cùng một bảng; và đừng đưa vào pipeline tự động không người canh (Phần 4).

Phần 3. Backfill dữ liệu lớn — chia batch, không chạy 1 phát

Lý do chia batch giống PG Phần 3, nhưng hai cơ chế gây đau ở MySQL khác PG: không phải bloat/dead tuple (InnoDB update tại chỗ, bản cũ nằm trong undo log — xem dead-tuple-index-bloat), mà là khoá dòng (và khoá khoảng) giữ suốt transaction và replica phải áp lại cả khối. Lab 10-backfill.sh: bảng 916.975 dòng, cột mới channel (INSTANT), tải nền 2 writer × 25 thao tác/s đang INSERT + UPDATE theo PK đúng bảng đó.

Ba điều đọc ra:

1. Một câu UPDATE lớn khoá ghi gần như cả bảng. WHERE channel IS NULL không có index nên InnoDB quét cả bảng và (ở REPEATABLE READ mặc định) đặt next-key lock (khoá dòng + khoảng trống trước nó) trên mọi dòng đã quét cho tới khi commit — UPDATE của ứng dụng trên bất kỳ dòng nào đều phải chờ, và INSERT cũng đứng (lab đo 23 giây không một câu ghi nào xong). Soi trực tiếp trên bảng 1.000 dòng (13-quick-checks.sh): trong lúc UPDATE chưa commit, performance_schema.data_locks có RECORD | X | 1003 (chế độ X không kèm REC_NOT_GAP = next-key), và một INSERT id tự tăng ở cuối bảng chờ hết 3 giây rồi ERROR 1205. Chạy lâu hơn 50 giây (innodb_lock_wait_timeout mặc định) thì ứng dụng bắt đầu nhận lỗi 1205. Đọc thì không sao — MVCC đọc bản chụp.

2. Replica áp lại cả khối sau khi primary commit, giống DDL ở 0.3: +40,5 s.

3. Batch không giảm lượng binlog (~240 vs ~249 MB, vì binlog_row_image=FULL ghi cả ảnh trước lẫn sau của từng dòng) — nó chỉ rải ra theo thời gian để replica và ứng dụng thở được. Bảng 50 triệu dòng thì tính trước dung lượng binlog và băng thông sang replica.

Job backfill dùng trong lab (files/backfill.py, rút gọn):

while last < hi:
    n = cur.execute(f"UPDATE {table} SET {col}=%s WHERE id > %s AND id <= %s AND {col} IS NULL",
                    (value, last, last + batch))          # mỗi batch 1 transaction (autocommit)
    last += batch
    lag = replica_heartbeat_lag_ms()                      # đọc heartbeat trên replica
    while lag > max_lag_ms:                               # replica trễ -> chờ
        time.sleep(0.5); lag = replica_heartbeat_lag_ms()
    time.sleep(sleep_ms / 1000)

Cách nói khi phỏng vấn: "Backfill em chạy như một job nền, đi theo khoảng khoá chính, mỗi lô năm nghìn dòng là một transaction riêng, có điều kiện 'còn rỗng' để chạy lại không sao. Sau mỗi lô em đọc độ trễ replica qua bảng heartbeat; trễ quá một giây thì dừng chờ. Trong lab, một câu update cả bảng làm ứng dụng không ghi được hai mươi ba giây và replica trễ bốn mươi giây; cách chia lô chậm gấp bốn lần nhưng không có giây nào đứng ghi."

Khác bản PG: không có FOR UPDATE SKIP LOCKED trong câu UPDATE theo khoảng (MySQL 8 có SKIP LOCKED cho SELECT … FOR UPDATE, nhưng đi theo khoảng PK đã đủ ngắn và idempotent); luôn đi theo khoảng PK, đừng UPDATE … LIMIT 5000 không có ORDER BY trên bảng có replica — MySQL đánh dấu câu đó là không an toàn cho replication dạng statement, và với điều kiện không index nó vẫn quét lại từ đầu mỗi lần (theo tài liệu — chưa verify trong lab). Theo dõi lag bằng heartbeat, vì Seconds_Behind_Source báo 64 khi lag thật 42 s.

Phần 4. Chạy migration trong pipeline CI/CD

Thứ tự trong một lần deploy và các quy tắc chung (backup trước DDL, mỗi migration một thay đổi, down() không phải cách rollback dữ liệu, migration là job riêng chạy từ một chỗ) giống hệt bài PG Phần 4 và được phân tích kỹ ở migration-ci-cd — không lặp lại ở đây. Phần MySQL phải đổi:

sequenceDiagram
    participant CI as GitLab CI (job migrate, resource_group)
    participant DB as MySQL primary
    participant RP as Replica
    participant App as Blue / Green
    CI->>DB: 1. kiểm tra: transaction dài? TOTAL_ROW_VERSIONS? replica lag hiện tại?
    CI->>DB: 2. SET SESSION lock_wait_timeout=3, ALTER ... ALGORITHM=INSTANT
    Note over CI,DB: lỗi 1205 → retry có backoff, lỗi 1845/1846 → DỪNG, chuyển sang gh-ost
    DB-->>RP: 3. ALTER sang replica (INSTANT ≈ 0 lag)
    CI->>RP: 4. chờ replica khớp GTID trước khi đi tiếp
    CI->>App: 5. switch blue → green
    Note over CI: thay đổi nặng (COPY / rebuild bảng lớn) KHÔNG nằm trong pipeline:<br/>chạy gh-ost như một job tay, có người canh

Cụ thể:

• Session migration đặt timeout và ghi rõ thuật toán. Laravel (MySQL):

  public function up(): void
  {
      DB::statement('SET SESSION lock_wait_timeout = 3');
      DB::statement("ALTER TABLE submissions ADD COLUMN channel VARCHAR(20) NULL, ALGORITHM=INSTANT");
  }
  

  Cách nói khi phỏng vấn: "Với MySQL em không dùng Schema Builder trơn cho bảng lớn, vì nó không cho ghi thuật toán. Em viết câu ALTER thô, có thuật toán INSTANT hoặc INPLACE với khoá NONE, và đặt thời gian chờ khoá ba giây cho phiên đó. Nếu MySQL không làm được theo cách nhẹ, migration fail ngay ở CI chứ không âm thầm khoá bảng production."

  NestJS/TypeORM tương tự: await queryRunner.query('SET SESSION lock_wait_timeout = 3') rồi queryRunner.query('ALTER TABLE … , ALGORITHM=INSTANT'). TypeORM không có lock chống chạy migrate hai lần (xem migration-ci-cd 1.6); bản MySQL của advisory lock là GET_LOCK('portal_migrate', 0) — lab (13-quick-checks.sh): session 1 nhận 1, session 2 gọi cùng lúc nhận 0 ngay (timeout 0), sau khi session 1 nhả thì session 3 nhận 1. Khoá gắn với kết nối, nên migration phải giữ nguyên một kết nối suốt quá trình. Cách đơn giản hơn vẫn là một job CI duy nhất với resource_group.

• Retry phân biệt hai loại lỗi. ERROR 1205 (chờ khoá quá hạn) = bảng đang bận → retry 3–5 lần có backoff. ERROR 1845/1846 (thuật toán không hỗ trợ) = migration viết sai chỗ → không retry, fail pipeline, người viết chuyển sang gh-ost hoặc expand–contract.

• Chờ replica sau migration. Hệ thống tách đọc sang replica phải chờ replica khớp trước khi switch, nếu không code mới đọc từ replica chưa có cột mới → lỗi Unknown column. Cách đo chắc chắn (dùng trong lab): lấy @@gtid_executed trên primary rồi gọi trên replica SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid>', 60) — trả 0 là xong, 1 là timeout.

• Không tin Seconds_Behind_Source khi có DDL. Lab 2b đo được replica thật sự trễ ~31 s (heartbeat) nhưng Seconds_Behind_Source báo tới 61 s — nó tính từ lúc câu DDL bắt đầu trên primary. Dùng bảng heartbeat (như pt-heartbeat hay heartbeat của gh-ost) nếu cần con số đúng.

• Thao tác nặng không nằm trong pipeline tự động. gh-ost chạy hàng chục phút, cần người canh (throttle, hoãn cut-over) — đưa vào pipeline thì job timeout giữa chừng. Pipeline chỉ chạy được migration INSTANT/INPLACE nhỏ; gh-ost là một bước vận hành có runbook, chạy trước release cần cột/kiểu mới.

Phần 5. Checklist trước khi merge một migration (MySQL)

Danh sách gốc bên PG Phần 5 vẫn đúng ở ý tưởng; bản MySQL đổi các dòng về CONCURRENTLY/NOT VALID thành câu hỏi về thuật toán, và thêm câu hỏi về replica:

[ ] Migration chỉ THÊM, hay có XOÁ / ĐỔI TÊN / ĐỔI KIỂU? (xoá/đổi tên/đổi kiểu → tách 2+ release)
[ ] Code bản cũ (v1) chạy được với schema mới? (cột mới NULL hoặc có DEFAULT — MySQL cho NOT NULL không default
    và code cũ INSERT sẽ lỗi 1364)
[ ] Code bản mới (v2) chạy được với schema CŨ? (để rollback code / blue-green switch ngược)
[ ] Mỗi câu ALTER có ghi ALGORITHM=INSTANT hoặc ALGORITHM=INPLACE, LOCK=NONE chưa?
[ ] Đã chạy thử trên staging xem MySQL CHẤP NHẬN thuật toán đó chưa? (lỗi 1845/1846 = phải đổi cách làm)
[ ] Có thao tác chỉ chạy được COPY không? (đổi kiểu, nới VARCHAR qua 255 byte, ADD CHECK, ADD FK khi
    foreign_key_checks=1, chèn giá trị ENUM vào giữa) → gh-ost / pt-osc, không chạy trong pipeline
[ ] Có INPLACE rebuild trên bảng lớn không? (NULL↔NOT NULL, ADD COLUMN ép INPLACE, FORCE) → ước replica lag
[ ] Bảng còn bao nhiêu row version INSTANT? (TOTAL_ROW_VERSIONS gần 64 → gom ADD/DROP vào 1 câu, hoặc rebuild chủ động)
[ ] Mỗi file migration chỉ MỘT câu DDL? (MySQL không có transactional DDL)
[ ] Session migration có SET lock_wait_timeout (không phải innodb_lock_wait_timeout) chưa?
[ ] UNIQUE mới: đã đếm dữ liệu trùng chưa? FK mới: đã đếm dòng mồ côi chưa?
[ ] Backfill tách khỏi migration, chia batch, có giảm tốc theo replica lag chưa? (Phần 3)
[ ] Đã ước replica lag = thời gian ALTER trên primary và chọn giờ phù hợp với read-split chưa?
[ ] Đã backup / snapshot chưa?

5.1. Merge request template (dùng cho LMS Uni)

Đây là bản mẫu để chứng minh câu CV "checklist và merge request template bắt buộc trước khi merge". Đặt ở .gitlab/merge_request_templates/Default.md của repo portal và repo plugin Moodle; reviewer không approve nếu mục nào bỏ trống. (Bản mẫu soạn cho bài này dựa trên các lab ở trên — phần hình thức là minh hoạ, không phải file lấy từ repo thật.)

## Thay đổi database
- [ ] Không có thay đổi schema  → bỏ qua mục này
- Bảng bị đụng: `____`   Chủ sở hữu: [ ] Moodle core/plugin (mdl_*)  [ ] Portal NestJS (portal_*)
- Ước số dòng production: `____`   Bảng có FK vào/ra không? `____`

### Nếu là bảng Moodle (mdl_*) — thay đổi đi qua upgrade của Moodle
- [ ] Đã bump version.php của plugin, có db/install.xml + db/upgrade.php tương ứng
- [ ] Kết quả `php admin/cli/upgrade.php --is-maintenance-required` trên staging: exit `__` (2 = khoá site khi upgrade, 3 = chạy được --maintenance=false)
- [ ] Thời gian upgrade đo trên staging có dữ liệu thật: `__` giây (= thời gian site trả "Site is being upgraded")
- [ ] Bước upgrade.php chỉ dùng thao tác INSTANT/INPLACE nhỏ? Nếu có đổi kiểu/CHECK/NOT NULL trên bảng lớn → cần cửa sổ bảo trì
- [ ] Ngày deploy KHÔNG trùng hạn nộp bài/quiz (đính kèm kết quả query lịch hạn — mục 5b.6)

### Nếu là bảng portal (portal_*) — expand–contract
- [ ] Release này là bước: [ ] expand  [ ] migrate/backfill  [ ] contract   (contract chỉ sau khi expand chạy ổn ≥ 3 ngày)
- [ ] Câu ALTER ghi rõ `ALGORITHM=INSTANT` hoặc `ALGORITHM=INPLACE, LOCK=NONE`; session có `SET lock_wait_timeout = 3`
- [ ] Đã chạy migration trên staging: MySQL chấp nhận thuật toán (dán output)
- [ ] Code v1 chạy với schema mới, code v2 chạy với schema cũ (đã test cả 2 chiều)
- [ ] Thao tác COPY/rebuild bảng lớn → tách ra runbook gh-ost, không nằm trong migration
- [ ] Ước replica lag: `__` giây — có ảnh hưởng trang đọc từ replica không?

## Tương thích ngược ngoài database
- [ ] Payload cache (Redis) đổi cấu trúc? Key có version (`v2:`) hoặc code đọc được cả format cũ lẫn mới
- [ ] Dữ liệu job của worker đổi cấu trúc? Worker bản mới đọc được job bản cũ còn trong queue và ngược lại
- [ ] Rollback = chỉ switch blue-green ngược, KHÔNG cần đụng DB (nếu không đúng → ghi rõ kế hoạch rollback)

Cách nói khi phỏng vấn: "Template bắt người viết trả lời ba câu trước khi merge: bảng này của Moodle hay của portal; thao tác này MySQL làm tức thì, trực tuyến hay phải copy cả bảng; và bản code cũ với bản mới có chạy được với cả hai dạng schema, cache và job không. Bảng của Moodle thì phải đi qua cơ chế upgrade của Moodle và đo thời gian khoá site trên staging. Bảng của portal thì làm expand–contract. Thao tác copy cả bảng thì tách ra thành việc vận hành riêng với gh-ost."

Phần 5b. Áp vào LMS Uni — Moodle + NestJS dùng chung một MySQL

Bối cảnh từ CV (không nhắc lại chi tiết deploy — xem chien-luoc-deployment): Moodle (PHP) và student portal (NestJS API + Next.js) dùng chung một database MySQL; MySQL primary/replica, truy vấn đọc (báo cáo, danh sách khoá học) đẩy sang replica; Nginx load balancer trước 3 app node; blue-green deploy; Redis tách instance cho session/cache/lock; worker trên nhiều VPS; ~100.000 sinh viên, cao điểm ~8.000 người dùng đồng thời dồn vào những ngày cuối hạn nộp bài.

Câu CV cần bảo vệ: "Xây quy trình deploy zero-downtime cho đội 7 developer bằng checklist và merge request template bắt buộc trước khi merge: migration database theo expand–contract; thay đổi cấu trúc payload cache và dữ liệu job của worker phải tương thích ngược…". Phần này chỉ ra câu đó đúng ở đâu và có giới hạn ở đâu, để không bị hỏi vặn mà lúng túng.

5b.1. Ai sở hữu bảng nào — quyết định mọi thứ phía sau

Quy tắc rút ra: portal không bao giờ ALTER bảng mdl_* (sẽ lệch với install.xml, upgrade Moodle sau đó vấp lỗi), và mọi cột mdl_* mà portal đọc là một hợp đồng giữa hai codebase — Moodle upgrade đổi cột đó thì portal phải ra bản đọc được cả hai dạng trước (expand–contract xuyên team).

5b.2. Moodle upgrade: ba pha, và vì sao đổi schema Moodle không zero-downtime

Nguồn: đọc source Moodle nhánh main ngày 26/10/2026 (admin/cli/upgrade.php, public/lib/setup.php, public/lib/setuplib.php, public/lib/upgradelib.php, public/lib/moodlelib.php, public/lib/classes/component.php, public/lib/classes/cron.php, public/index.php, public/my/index.php) bằng 11-moodle-source.sh, so với MOODLE_401_STABLE / MOODLE_402_STABLE. Đọc source, chưa chạy thật — không dựng Moodle trong lab này.

Trong Moodle, schema chỉ đổi qua upgrade (bump version.php + db/install.xml / db/upgrade.php) — cho cả core lẫn plugin tự viết. Nên "đổi một bảng Moodle" luôn là "một lần upgrade". Một lần deploy có upgrade đi qua ba pha:

flowchart TD
  D["Deploy code mới (green) — version.php đã bump"]
  P1["PHA 1 — code mới, CHƯA upgrade<br/>moodle_needs_upgrading() = true (hash version.php code ≠ DB)<br/>• chỉ user có moodle/site:config bị redirect sang admin/index.php<br/>• user thường vẫn dùng bình thường — nhưng trên CODE MỚI + SCHEMA CŨ<br/>• cron: 'Moodle upgrade pending, cron execution suspended.' → scheduled + adhoc task dừng"]
  Q{"Có thư mục /db/ nào đổi?<br/>(upgrade.php --is-maintenance-required)"}
  P2a["PHA 2 (khoá) — upgrade web hoặc CLI mặc định --maintenance=true<br/>upgrade_started(): config upgraderunning = now + 300 (gia hạn liên tục)<br/>setup.php: MỌI request khác → 'Site is being upgraded, please retry later.'<br/>kéo dài đúng bằng thời gian upgrade (gồm các câu ALTER)"]
  P2b["PHA 2 (không khoá) — CLI --maintenance=false (Moodle 4.2+)<br/>chỉ được khi hash mọi /db/ không đổi (exit 3)<br/>đặt outagelessupgrade, KHÔNG đặt upgraderunning"]
  P3["PHA 3 — upgrade_finished()<br/>unset upgraderunning + cache_helper::purge_all(true)<br/>→ cache MUC lạnh, DB chịu tải dồn ngay sau đó"]
  D --> P1 --> Q
  Q -- "exit 2: có đổi" --> P2a --> P3
  Q -- "exit 3: không đổi" --> P2b --> P3

Từng pha, kèm dòng source (log thật của 11-moodle-source.sh):

1. Pha 1 — code mới, chưa chạy upgrade. moodle_needs_upgrading() so hash của mọi version.php trong code (core_component::get_all_versions_hash()) với $CFG->allversionshash trong DB. Lệch là true. Hệ quả:

   ---- index.main.php
   65:$hassiteconfig = has_capability('moodle/site:config', context_system::instance());
   67:if ($hassiteconfig && moodle_needs_upgrading()) {
   ---- my-index.main.php
   49:if ($hassiteconfig && moodle_needs_upgrading()) {
   ---- cron.main.php
   69:        if (moodle_needs_upgrading()) {
   70:            echo "Moodle upgrade pending, cron execution suspended.\n";
   

   Tức là Moodle không bắt người dùng thường vào chế độ bảo trì chỉ vì version đã bump: chỉ admin bị đẩy sang trang upgrade; sinh viên vẫn làm bài — nhưng trên code mới chạy với schema cũ (code mới truy vấn cột chưa tồn tại thì lỗi), còn cron dừng hẳn (chấm điểm tự động, gửi thông báo, các adhoc task đứng). Ngoại lệ chỉ ở core: is_major_upgrade_required() (DB core version < 2024010400.00, đang cài đặt, hoặc adminsetuppending) thì redirect_if_major_upgrade_required() huỷ session hiện tại và 303 mọi người sang trang admin.

2. Pha 2 — upgrade đang chạy. Bấm upgrade trên web, hoặc chạy CLI với mặc định --maintenance=true: upgrade_started() ghi config upgraderunning = time() + 300, upgrade_set_timeout() gia hạn liên tục. Trong lib/setup.php:

   // Verify upgrade is not running unless we are in a script that needs to execute in any case.
   if (!defined('NO_UPGRADE_CHECK') and isset($CFG->upgraderunning)) {
       if ($CFG->upgraderunning < time()) {
           unset_config('upgraderunning');
       } else {
           throw new \moodle_exception('upgraderunning');   // 'Site is being upgraded, please retry later.'
       }
   }
   

   Cách nói khi phỏng vấn: "Khi Moodle chạy upgrade ở chế độ thường, nó ghi một mốc thời gian 'đang nâng cấp' vào bảng cấu hình, và mọi request khác của mọi người dùng đều bị trả về thông báo site đang nâng cấp cho tới khi upgrade xong. Chữ 'maintenance' trong script upgrade của Moodle nghĩa đúng là cái khoá này, không phải trang bảo trì tĩnh. Upgrade một plugin nhỏ thì vài giây, khó ai để ý; nhưng nếu bước upgrade có một câu ALTER copy cả bảng lớn trên MySQL thì cả site bị chặn đúng bằng thời gian câu ALTER đó."

   Quy đổi bằng số đo của bài này: nếu upgrade.php của một plugin đổi kiểu cột (INT→BIGINT, COPY) trên bảng cỡ 3 triệu dòng thì site trả "Site is being upgraded" khoảng ngần ấy thời gian copy (đo ở 2b), cộng thêm replica trễ tương đương sau đó. Thêm cột nullable (INSTANT) thì chỉ vài trăm mili-giây.

3. --maintenance=false — upgrade không khoá (Moodle 4.2+).

   =========== 1. Có bản nào có --is-maintenance-required / outagelessupgrade?
   upgrade.401.php: is-maintenance-required=0 outagelessupgrade=0
   upgrade.402.php: is-maintenance-required=5 outagelessupgrade=4
   upgrade.main.php: is-maintenance-required=5 outagelessupgrade=4
   ---- upgrade.main.php
    89:--is-maintenance-required   Returns exit code 2 if the upgrade requires maintenance mode.
    90:                            Returns exit code 3 if no maintenance is required for the upgrade.
   149:    if ($allhash !== $CFG->allcomponenthash) {
   151:        cli_error(get_string('cliupgrademaintenancerequired', 'core_admin'), 2);
   159:    set_config('outagelessupgrade', true);
   

   Cổng quyết định là core_component::get_all_component_hash(): sha1 của mọi file trong mọi thư mục /db/ của core và plugin (install.xml, upgrade.php, access.php, events.php, tasks.php, caches.php, services.php…), so với $CFG->allcomponenthash. Nghĩa là không chỉ đổi schema: thêm một capability, một scheduled task, một cache definition cũng buộc đi đường khoá. Ngược lại, release chỉ bump version.php mà không đụng /db/ (sửa PHP, JS, template) thì --is-maintenance-required trả 3 và chạy được --maintenance=false: upgrade_started() bỏ qua bước đặt upgraderunning khi CLI_UPGRADE_RUNNING, nên không ai bị chặn. Moodle 4.1 trở xuống không có đường này — MOODLE_401_STABLE không có cả cờ lẫn config outagelessupgrade.

4. Pha 3 — xong upgrade. upgrade_finished() xoá upgraderunning rồi cache_helper::purge_all(true): toàn bộ cache MUC (trên Redis) bị xoá, các request đầu tiên sau upgrade dồn xuống DB. Lúc cao điểm, đây là một cú tăng tải đáng kể — thêm lý do tránh giờ cao điểm.

Kết luận trung thực cho câu CV:

• Release không đụng /db/ (đa số release Moodle của team) và mọi release chỉ đổi bảng portal → zero-downtime thật: blue-green switch, upgrade --maintenance=false (4.2+), rollback bằng switch ngược. Lưu ý rollback: sau khi upgrade đã ghi version mới vào DB, code cũ có version.php thấp hơn → hash lệch → cron trên code cũ lại dừng và admin bị redirect. Chạy upgrade trên code cũ thì core ném downgradedcore (admin/cli/upgrade.php dòng 132, public/admin/index.php dòng 552), plugin ném downgrade_exception — "Cannot downgrade {plugin} from {oldversion} to {newversion}." (upgradelib.php khi $installedversion > $plugin->version). Tức là rollback code qua một lần bump version không sạch kể cả khi không đổi schema — đọc source, chưa chạy thật.
• Release đổi schema Moodle (hoặc bất kỳ file nào trong /db/) → không zero-downtime: một khoảng khoá toàn site bằng thời gian upgrade, một cửa sổ code-mới/schema-cũ ở pha 1, cron dừng, cache lạnh sau đó. Cái làm được là thu nhỏ nó, không xoá nó.

Cách nói khi phỏng vấn: "Câu zero-downtime trong CV của em đúng cho hai loại thay đổi: bảng của portal NestJS, làm expand–contract; và release Moodle không đụng thư mục db, chạy upgrade ở chế độ không khoá từ Moodle 4.2. Còn thay đổi schema của Moodle thì bắt buộc đi qua upgrade, và upgrade khoá toàn site trong lúc chạy. Ở đó em không hứa zero-downtime; em hứa thời gian khoá ngắn và có kế hoạch: chỉ dùng thao tác tức thì trên bảng lớn, chạy upgrade ngay trong pipeline sau khi chuyển code, và không deploy vào ngày hạn nộp bài."

5b.3. Thu nhỏ khoảng khoá khi buộc phải đổi schema Moodle

sequenceDiagram
  participant CI as GitLab CI
  participant DB as MySQL primary
  participant G as Green (code mới)
  participant B as Blue (code cũ)
  participant U as Sinh viên
  CI->>G: 1. deploy code mới, chưa nhận traffic
  CI->>G: 2. upgrade.php --is-maintenance-required
  alt exit 3 (không đổi /db/)
    CI->>DB: 3a. upgrade.php --non-interactive --maintenance=false
    CI->>G: switch traffic, U không bị chặn
  else exit 2 (có đổi /db/)
    CI->>DB: 3b. backup, kiểm tra transaction dài, replica lag
    CI->>G: switch traffic (pha 1 bắt đầu)
    CI->>DB: 4. NGAY LẬP TỨC upgrade.php --non-interactive (pha 2: khoá)
    U->>G: 'Site is being upgraded' trong N giây
    DB-->>CI: 5. xong, cache purge (pha 3)
  end
  CI->>B: giữ blue để rollback code — nhưng rollback sau pha 2 = restore DB

1. Giữ ALTER trong upgrade.php của plugin tự viết ở mức INSTANT / nhỏ. Plugin của team có bảng lớn (log nộp bài, kết quả chấm) thì thiết kế bước upgrade theo Phần 2: thêm cột nullable/default hằng số (INSTANT), tránh đổi kiểu, tránh siết NOT NULL, tránh CHECK, tránh index trên bảng hàng chục triệu dòng trong cùng bước. Đo thời gian upgrade trên staging có dữ liệu thật và ghi vào MR (5.1). Lưu ý: xmldb của Moodle tự sinh câu ALTER, không có chỗ ghi ALGORITHM=INSTANT — MySQL sẽ tự chọn, nên phải đo, và nhớ 2.11 (hết row version thì tự rebuild).

2. Rút pha 1 về gần 0: chạy CLI upgrade ngay trong job deploy, liền sau khi switch, không để admin bấm tay trên web lúc nào rảnh. Cron chỉ chạy trên một node đã cùng version với DB.

3. Chọn giờ không trùng hạn nộp bài (5b.6) — vì pha 2 chặn cả site và pha 3 làm cache lạnh.

4. Thay đổi rất nặng trên bảng Moodle (đổi kiểu cột trên bảng hàng chục triệu dòng): có thể chạy gh-ost trước lên đúng cấu trúc mới, để bước upgrade.php (thường viết dạng if (!$dbman->field_exists(…))) thành no-op. Rủi ro: schema lệch với install.xml nếu làm sai, và phải xử lý FK (gh-ost không hỗ trợ) — chưa verify, chỉ dùng khi đã thử trên staging; mặc định vẫn là cửa sổ bảo trì có báo trước.

5b.4. Migration của NestJS trên database mà Moodle cũng đọc

• Bảng của portal (vd tiền tố portal_): áp nguyên expand–contract + Phần 2/2b. Không có FK sang mdl_* thì gh-ost dùng được cho bảng portal lớn.

• Transaction dài của portal chặn upgrade Moodle. Báo cáo nặng của portal đọc mdl_grade_grades trong một transaction dài giữ MDL SHARED_READ — câu ALTER trong upgrade Moodle phải chờ (0.1, 0.2), và vì pha 2 đã khoá site, site bị khoá lâu hơn trong lúc chờ. Trước khi chạy upgrade: soi sys.schema_table_lock_waits / transaction dài, tạm dừng worker xuất báo cáo.

• Hợp đồng đọc mdl_*. Trước một upgrade Moodle đổi cột mà portal đọc, portal phải ra bản đọc được cả hai dạng (vd COALESCE cột mới/cũ) — thứ tự release: portal expand → Moodle upgrade → portal contract.

• Hai pipeline, một database. Migration portal và upgrade Moodle không được chạy chồng nhau (cùng tranh MDL, cùng làm replica trễ). Dùng chung một resource_group CI cho mọi job đụng schema.

5b.5. Replica lag và read-split

Truy vấn đọc (báo cáo, danh sách khoá học, nhiều trang portal) đi replica. Theo 0.3, mọi DDL rebuild/COPY trên primary làm replica trễ thêm đúng bằng thời gian chạy. Số đo trên bảng 3 triệu dòng có tải ghi (2b): ADD INDEX INPLACE → replica khớp GTID chậm thêm ~31 s; FORCE rebuild INPLACE → chậm thêm ~74 s. Trong khoảng đó sinh viên nộp bài xong, trang "bài đã nộp" đọc từ replica chưa thấy → bấm nộp lại, tạo ticket.

• Bảng lớn mà portal/Moodle đọc từ replica: dùng gh-ost/pt-osc có --max-lag thay vì native rebuild, kể cả khi native chạy được — đánh đổi thời gian chạy lấy lag dưới 1–2 giây (2b).
• Tạm chuyển đọc về primary trong lúc ALTER chỉ làm ngoài giờ cao điểm: theo CV, tách đọc sang replica đã kéo CPU primary lúc cao điểm từ ~85% xuống ~45%; dồn đọc về primary lúc cao điểm là quay lại mức 85%.
• Dùng heartbeat, không dùng Seconds_Behind_Source, cho quyết định "replica đã ổn chưa" (Phần 4).

5b.6. Chọn cửa sổ không trùng hạn nộp bài

-- Hạn nộp assignment và hạn đóng quiz trong 14 ngày tới, gom theo giờ
SELECT FROM_UNIXTIME(t, '%Y-%m-%d %H:00') AS gio, COUNT(*) AS so_han
FROM (
  SELECT duedate AS t FROM mdl_assign WHERE duedate BETWEEN UNIX_TIMESTAMP() AND UNIX_TIMESTAMP() + 14*86400
  UNION ALL
  SELECT timeclose FROM mdl_quiz WHERE timeclose BETWEEN UNIX_TIMESTAMP() AND UNIX_TIMESTAMP() + 14*86400
) d
GROUP BY gio ORDER BY so_han DESC;

Cách nói khi phỏng vấn: "Tải của LMS dồn vào trước hạn nộp bài, nên em lấy lịch hạn từ chính database: hạn nộp của assignment và giờ đóng của quiz trong hai tuần tới, gom theo giờ. Ca deploy có upgrade Moodle hoặc thao tác copy bảng lớn phải rơi vào khoảng xa mọi mốc hạn, thường là sáng sớm của ngày không có hạn nào."

Tên bảng/cột (mdl_assign.duedate, mdl_quiz.timeclose, Unix timestamp) theo schema Moodle; câu này chưa chạy trên database Moodle thật — chưa verify. Hạn gia hạn riêng từng sinh viên (override) không nằm trong hai cột này.

5b.7. Những gì CV nói được, và chỗ interviewer sẽ đào

Phần 6. Case thực tế có thể gặp và cách xử lý

Các case chung (migration chạy 2 lần, DROP COLUMN xong mới biết BI còn đọc, rollback code làm insert fail) xử lý giống bài PG Phần 6. Dưới đây là các case có cách xử lý riêng của MySQL.

Case 1 — ALTER "INSTANT" lúc 10h sáng, site treo, PHP-FPM hết worker. Hàng đợi MDL (0.1): một transaction quên commit giữ SHARED_READ, ALTER chờ EXCLUSIVE, mọi query sau xếp hàng. Xử lý ngay: SELECT * FROM sys.schema_table_lock_waits → cột sql_kill_blocking_connection chỉ thẳng thủ phạm. Thứ tự ưu tiên: KILL <id câu ALTER> trước (hàng đợi tan ngay, không mất dữ liệu người dùng), rồi mới tính tới kill transaction treo. Ngăn lần sau: SET SESSION lock_wait_timeout = 3 trong mọi migration (mặc định là 1 năm).

Case 2 — ADD INDEX … LOCK=NONE chạy ngon 25 giây, rồi site đứng ở giây cuối. Pha commit cần MDL EXCLUSIVE (0.2): transaction mở giữa chừng làm ALTER chờ ở cuối, lab đo một SELECT bị chặn 21,4 s. Xử lý: như Case 1 — kill câu ALTER (mất công build, nhưng site sống). Ngăn: lock_wait_timeout ngắn cho cả câu; với job/report dài, đặt max_execution_time hoặc tạm dừng chúng trong lúc chạy migration. max_execution_time chỉ cắt SELECT: lab (13-quick-checks.sh) với 1000 ms thì SELECT SLEEP(3) bị cắt sau ~1,4 s, còn UPDATE … SET v = SLEEP(3) vẫn chạy đủ 3,2 s.

Case 3 — "LOCK=NONE" mà ghi vẫn đứng 10 giây. Rebuild INPLACE (FORCE, đổi NULL/NOT NULL) trên bảng có tải ghi: lab 2b đo 10 giây không có ghi nào thành công ở cuối một lần rebuild 110 giây — phần online alter log tích luỹ trong lúc rebuild được áp nốt dưới khoá độc quyền. Tải ghi càng lớn, khoảng đứng cuối càng dài. Ngăn: rebuild bảng lớn có tải ghi → gh-ost (lab đo khoảng khoá lúc cut-over 0,45 s) thay vì native.

Case 4 — ALTER xong trên primary, 1 phút sau user kêu "nộp bài rồi mà không thấy". Replica apply DDL sau primary (0.3) rồi mới tới DML phía sau; trang đọc từ replica thấy dữ liệu cũ. Xử lý ngay: chờ replica khớp, hoặc tạm đưa đọc của luồng "vừa ghi xong" về primary. Ngăn: bảng lớn dùng gh-ost/pt-osc có --max-lag; theo dõi bằng heartbeat, không dùng Seconds_Behind_Source (báo 61 s khi lag thật 31 s).

Case 5 — Migration "thêm một cột nullable" khoá bảng 1 phút. Bảng đã dùng hết 64 row version INSTANT; câu ALTER không ghi ALGORITHM → MySQL lặng lẽ rebuild (2.11). Ngăn: luôn ghi ALGORITHM=INSTANT (để nó lỗi 4092 thay vì rebuild); theo dõi TOTAL_ROW_VERSIONS; gom ADD/DROP nhiều cột vào một câu.

Case 6 — Deploy xong, code cũ (blue vẫn đang nhận traffic) INSERT lỗi hàng loạt. Migration thêm cột NOT NULL không default — MySQL nhận, điền 0 cho dòng cũ, code cũ thiếu cột → ERROR 1364. Xử lý ngay: ALTER … ALTER COLUMN x SET DEFAULT …, ALGORITHM=INSTANT (mili-giây). Ngăn: checklist Phần 5.

Case 7 — Migration 3 câu ALTER fail ở câu thứ 2, chạy lại thì lỗi "Duplicate column name". Không có transactional DDL: câu 1 đã áp dụng, bảng migrations chưa ghi. Xử lý: sửa tay trạng thái (xoá cột câu 1 hoặc đánh dấu migration đã chạy), không chạy down() mù. Ngăn: một câu DDL một file; DDL viết idempotent khi có thể (kiểm tra information_schema.COLUMNS trước).

Case 8 — ADD UNIQUE chạy 20 phút rồi lỗi Duplicate entry. MySQL quét xong mới biết trùng. Không để lại rác như PG nhưng tốn công và tốn replica lag (replica cũng chạy lại 20 phút). Ngăn: GROUP BY … HAVING COUNT(*) > 1 trước; dọn trùng; chặn trùng ở tầng ứng dụng trong lúc build.

Case 9 — gh-ost cut-over thử mãi không xong. Cut-over cần khoá bảng gốc trong tối đa --cut-over-lock-timeout-seconds (mặc định 3 s); transaction dài trên bảng gốc làm mỗi lần thử timeout rồi rollback về trạng thái trước cut-over (an toàn, bảng gốc vẫn phục vụ). Xử lý: tìm transaction dài như Case 1; dùng --postpone-cut-over-flag-file để tự chọn lúc cut-over (lab 2b).

Case 10 — Upgrade plugin Moodle giữa tuần thi, site báo "Site is being upgraded" 2 phút. upgrade.php của plugin có một bước đổi kiểu cột trên bảng log lớn — COPY trên MySQL, và trong pha 2 Moodle chặn mọi request (5b.2). Ngăn: MR template có dòng "thời gian upgrade đo trên staging" + lịch hạn nộp (5b.6); bước upgrade chỉ dùng thao tác INSTANT/nhỏ.

Phần 7. Câu hỏi phụ interviewer hay hỏi tiếp

"MySQL 8 có INSTANT DDL rồi thì còn cần gh-ost làm gì?" INSTANT chỉ phủ thêm/bỏ cột, đổi default, đổi tên, nối ENUM. Đổi kiểu, nới VARCHAR qua 255 byte, thêm CHECK, thêm FK có kiểm tra vẫn là COPY — khoá ghi. Rebuild INPLACE thì cho ghi, nhưng không throttle được, làm replica trễ bằng thời gian chạy, và lab đo 10 s đứng ghi ở cuối. gh-ost giải ba thứ đó: không khoá, tự giảm tốc theo replica lag, tự chọn lúc cut-over.

"LOCK=NONE nghĩa là không khoá gì?" Không. Nó nghĩa là cho DML trong lúc chạy. Đầu và cuối vẫn cần metadata lock độc quyền; lab đo một SELECT bị chặn 21,4 s vì một transaction mở giữa lúc build index.

"lock_wait_timeout khác innodb_lock_wait_timeout thế nào?" Cái đầu cho metadata lock (mặc định 1 năm) — thứ DDL chờ. Cái sau cho khoá dòng InnoDB (mặc định 50 s) — thứ UPDATE chờ nhau. Migration cần đặt cái đầu; backfill thì lo cái sau.

"gh-ost hay pt-online-schema-change?" Mặc định gh-ost: không trigger nên tải trên primary thấp và dừng được thật sự, cut-over kiểm soát được. pt-osc khi bảng có FK (gh-ost từ chối), khi không có binlog ROW, hoặc khi không mở được kết nối replication. Không dùng cái nào cho thao tác INSTANT. Chi tiết và số đo ở 2b.

"Bảng có foreign key thì đổi schema online thế nào?" Native: thêm FK dùng foreign_key_checks=0 + tự đếm dòng mồ côi; đổi kiểu cột trong FK bị từ chối (3780). Công cụ: gh-ost không hỗ trợ FK (cả bảng con lẫn bảng cha); pt-osc dùng --alter-foreign-keys-method=rebuild_constraints, đổi tên FK (thêm dấu _) và ALTER bảng con — bảng con lớn thì chính bước đó là một ALTER nặng.

"Rollback migration MySQL thế nào?" Không có transactional DDL, nên không có rollback "cả file". Cách đúng giống PG: thiết kế để không cần rollback schema (expand trước, contract sau), rollback chỉ là rollback code. gh-ost/pt-osc có thêm một lưới: bảng cũ được giữ lại (_t_del / _t_old) nếu không bật xoá — đổi tên ngược được, nhưng dữ liệu ghi sau cut-over sẽ thiếu.

"Moodle upgrade có zero-downtime không?" Từ 4.2: release không đổi thư mục /db/ nào thì có (--maintenance=false). Có đổi /db/ (schema, capability, scheduled task…) thì không: Moodle chặn mọi request bằng upgraderunning trong lúc upgrade. Chi tiết 5b.2 — đọc source, chưa chạy thật.

"Làm sao biết trước ALTER sẽ khoá bao lâu?" Chạy đúng câu đó với ALGORITHM/LOCK ghi rõ trên staging có kích thước gần production: MySQL trả lời ngay là chấp nhận hay không, và thời gian đo được là thời gian replica sẽ trễ. Bảng 1.000 dòng thì số nào cũng vô nghĩa.

"PostgreSQL và MySQL, cái nào dễ migration online hơn?" Thêm/bỏ cột thì MySQL 8 ngang PG (INSTANT, chèn được cột giữa bảng). Index thì ngang nhau. Ràng buộc thì PG hơn hẳn (NOT VALID + VALIDATE, NOT NULL qua CHECK). Đổi kiểu thì cả hai đều phải copy/rewrite, nhưng MySQL có hệ sinh thái gh-ost/pt-osc trưởng thành hơn. Replica: MySQL trễ bằng thời gian DDL, phải tính vào kế hoạch.

Phần 8. Câu trả lời ngắn cho phỏng vấn

"Nguyên tắc vẫn là expand–contract như PostgreSQL: thêm trước dùng sau, ngừng dùng trước xoá sau, đổi tên đổi kiểu thì làm cột mới. Riêng MySQL em canh ba thứ. Một là hàng đợi metadata lock: mặc định câu ALTER chờ khoá tới một năm và chặn mọi truy vấn sau nó, nên migration nào cũng đặt thời gian chờ khoảng ba giây và thử lại. Hai là thuật toán: em ghi rõ INSTANT hoặc INPLACE với khoá NONE để MySQL từ chối thay vì lặng lẽ copy cả bảng; thao tác chỉ copy được như đổi kiểu, thêm CHECK thì chạy bằng gh-ost, bảng có khoá ngoại thì pt-osc. Ba là replica: MySQL chạy DDL trên replica sau khi primary xong, nên một câu ALTER một phút là replica trễ một phút — với hệ thống tách đọc như LMS Uni, đó là lý do dùng gh-ost có giảm tốc theo độ trễ.

Ở LMS Uni em nói rõ giới hạn: bảng của portal NestJS làm zero-downtime được; release Moodle không đụng thư mục db cũng vậy từ bản 4.2. Còn đổi schema của Moodle thì bắt buộc qua upgrade, và upgrade khoá toàn site trong lúc chạy — em thu nhỏ khoảng đó bằng thao tác tức thì, chạy upgrade ngay trong pipeline, và tránh ngày hạn nộp bài."

Phụ lục: lab đã chạy

Thư mục: labs/database/mysql-migration/. Chạy lại theo thứ tự số; mỗi script ghi out/<tên script>.log. Container/network/volume đều tên tmp-mym-*, network riêng tmp-mym-net, không publish port. Mật khẩu sinh ngẫu nhiên vào .env (gitignored, không có trong repo); file run/migrator.cnf cho công cụ cũng gitignored.

• MySQL 8.4.13 (mysql:8.4.13), primary + replica GTID, replica_parallel_workers=4, buffer pool 384 MB, 2 CPU / 1,5 GB mỗi container. Bật --mysql-native-password=ON và tạo user app/migrator bằng mysql_native_password cho tiện kết nối từ công cụ — thoả hiệp của lab, production nên giữ caching_sha2_password + TLS.
• gh-ost 1.1.11 (binary chính thức từ GitHub release, kiểm SHA256, chạy trong container tạm); percona-toolkit 3.7.2 (percona/percona-toolkit:3.7.2); Python 3.12 + PyMySQL 1.1.1 cho tải giả lập.
• Host dùng chung với service production (4 CPU, load average lúc chạy 4–12) — số tuyệt đối nhiễu; so sánh trong cùng một bảng đo mới có nghĩa.

Chưa verify (nguồn đang dựa vào)

• Atomic DDL crash-safe khi server chết giữa DDL — tài liệu MySQL 8.0+ (chưa kill server trong lab).
• LOCK=NONE bị cấm khi có FK ON … CASCADE — tài liệu MySQL 8.4 nói vậy, lab 8.4.13 không tái hiện được với FORCE/ADD INDEX/ADD COLUMN INPLACE.
• Giới hạn 255 row version từ MySQL 9.1 — tài liệu MySQL.
• Khe thời gian giữa RENAME TABLE và CREATE VIEW dài bao nhiêu — chưa đo.
• UPDATE … LIMIT không ORDER BY bị đánh dấu unsafe và quét lại — tài liệu MySQL.
• Thời gian rebuild_constraints của pt-osc trên bảng con rất lớn; xử lý trigger có sẵn của hai công cụ — tài liệu công cụ.
• Toàn bộ hành vi Moodle ở 5b — đọc source nhánh main (và so 401/402), chưa chạy Moodle thật; chạy gh-ost trước rồi để upgrade.php thành no-op; câu SQL lịch hạn trên mdl_assign/mdl_quiz chưa chạy trên DB Moodle thật.
• Replica của PostgreSQL replay song song khi primary rewrite — không đo ở bài này.

cd labs/database/mysql-migration
./00-env.sh && ./01-up.sh && ./02-seed.sh          # ~6 phút seed 3 triệu dòng
./03-mdl-queue.sh                                      # ~4 phút
./04-algorithms.sh && ./04b-extra.sh && ./04c-rename-view.sh && ./05-instant-limit.sh
./06-native-under-load.sh                              # ~25 phút (case COPY 3 triệu dòng rất lâu)
./06b-native-copy-small.sh && ./07-ptosc.sh && ./08-ghost.sh && ./09-fk-tools.sh && ./09b-ghost-skip-fk.sh
./10-backfill.sh && ./11-moodle-source.sh && ./12-pg-compare.sh && ./13-quick-checks.sh
./99-cleanup.sh

Cách nói khi phỏng vấn: "Mọi con số em nói về MySQL đều đo trên 8.4.13 với một primary và một replica dựng tạm, có tải ghi giả lập và heartbeat để đo độ trễ replica thật. Phần Moodle thì em đọc source chứ chưa chạy thật, và em nói rõ điều đó."