Build Log2026-10-0517 min read

每週把正式站資料庫複製到測試機，然後測試機的硬碟被 binlog 塞爆了

每週把正式站資料庫用 mysqldump pipe 複製到測試機，先灌暫存、驗證過才換位。跑了一個月後測試機寫入全部卡死：binlog 把 1TB 資料碟塞到 100%，連 PURGE 都清不掉。記錄同步機制、踩過的坑，以及怎麼打破這個死結。

背景

我們的測試環境（DEV、QA、SIT 幾個站）共用一台 MySQL 測試機，裡面放的是一份長年累積的測試資料。問題是測試資料量太小：正式站一張表幾百萬筆，測試機只有幾千筆，很多效能問題（報表逾時、少了索引的全表掃描）在測試環境根本重現不出來。

所以做了一套機制：每週一凌晨，把正式站整個資料庫複製一份到測試機的另一個 schema，讓其中一個測試站（SIT）接這份「有正式站資料量」的快照，其他測試站照舊用原本的測試資料，互不干擾。

這篇記錄這套機制怎麼做，以及它跑了一個月之後，某個週一早上整台測試機的寫入全部卡死的事故。

一、複製機制

整體流程

每週同步流程：正式站 app_db 用 mysqldump pipe 到測試機的 _staging，驗證筆數後，舊的 _prodsync 退到 _prev，_staging 換上 _prodsync

由 GitLab 的 Pipeline schedule 每週觸發一次，job 固定跑在正式站 DB 那台機器的 runner 上。

1. dump 不落地，直接 pipe 進測試機

mysqldump \
  --single-transaction \
  --quick \
  --lock-tables=false \
  --no-tablespaces \
  --column-statistics=0 \
  --set-gtid-purged=OFF \
  -h 127.0.0.1 -u "$DB_USER" -p"$DB_PASS" app_db \
| mysql -h <測試機內網 IP> -u "$DB_USER" -p"$DB_PASS" app_db_prodsync_staging

--single-transaction + --lock-tables=false：InnoDB 的一致性快照，不鎖正式站的表，dump 期間正式站照常寫入。
--quick：一筆一筆吐，不把大表整個載進記憶體。
整段是 pipe，沒有任何中間檔案落地。這點很重要，因為正式站那台的系統碟只剩 41GB，資料庫有 54GB， dump 檔寫下來就直接撐爆正式站。
走 GCP 內網 IP，不經過外網，也不經過自己的筆電。

2. 先灌到 staging，驗證過才換位

直接灌進 SIT 正在用的 schema，灌到一半 SIT 就會看到殘缺的資料；灌失敗更慘，SIT 直接變成空庫。所以先灌進 _staging，檢查幾張核心表的筆數合不合理：

if [ "$SCHOOLS_COUNT" -lt 100 ] || [ "$USERS_COUNT" -lt 10000 ]; then
  echo "驗證失敗，中止換位，保留舊的快照"
  exit 1
fi

通過了才用 RENAME TABLE 把舊的搬去 _prev、新的搬上正式位置。RENAME TABLE 只改中繼資料，幾十 GB 的表也是瞬間完成，SIT 幾乎感覺不到切換。

二、建這套機制時踩的坑

坑一：帳號沒有 PROCESS 權限，新版 mysqldump 直接拒絕執行

用來 dump 的帳號只有那個資料庫的權限，沒有 PROCESS。新版 mysqldump 預設會去讀 tablespace 資訊和欄位統計，沒有權限就報錯停下。

修法：加 --no-tablespaces 和 --column-statistics=0。這兩個資訊在 restore 時本來就用不到。

坑二：view 不能跨 schema 搬

RENAME TABLE a.t TO b.t 對一般的表沒問題，但對 view 會失敗。而且如果只搬表、把 view 留在原地，那個 view 會變成一個指向「已經被搬空的舊位置」的殘影，下一步在新位置建同名 view 時還會撞名。

修法：換位前先 SHOW CREATE VIEW 把定義存下來、sed 把 schema 名稱換掉、DROP VIEW，等表都搬完再到新位置重建。

坑三：GitLab job 預設 1 小時就被砍

dump + restore 實測要 1～2 小時。job 跑到剛好 1h0m0s 就被殺掉，log 看起來像腳本壞了，其實是 GitLab 專案預設的 job timeout。

修法：在 job 上設 timeout: 3h，留餘裕給之後資料量繼續長大。

坑四：避免有人手動觸發

這個 job 會改測試機的資料庫，不能讓人在跑其他部署時不小心觸發。所以 rule 寫兩道：

rules:
  - if: '$TARGET == "SYNC_PROD_DB" && $CI_PIPELINE_SOURCE == "schedule"'

只有「排程觸發」而且「帶對變數」才會跑，手動按 Run pipeline 帶錯變數也不會動到。

還沒解的：個資沒有遮罩

正式站的資料裡有真實使用者的姓名、email，現在是原封不動搬到多人可連的測試機。比較嚴謹的做法是同步完再跑一支假名化的腳本，這個目前還沒做，先記在這裡。

三、事故：一個月後，測試機的寫入全部卡死

症狀

某個週一早上，我在測試機上跑一個很普通的 migration（幫一張只有二十幾筆的 log 表加一個欄位），結果卡住不動。去查連線狀態，又得到一個很奇怪的錯誤：

The table '/tmp/#sql32c_804e_0' is full

能撈到的連線清單裡，一堆寫入卡在同一個狀態，最久的已經卡了 18 分鐘，其中還有別的團隊登入時寫 token 的 UPDATE：

waiting for handler commit

查原因

連上機器看磁碟：

/dev/root   243G  125G  118G  52% /
/dev/sdb   1007G  961G     0 100% /var/lib/mysql

MySQL 的資料碟 100% 滿。再看是誰佔的：

資料碟用量：binlog 572G、原本的測試資料 101G、快照 _prodsync 91G、上一份 _prev 89G、同步一半的 _staging 87G、更早的舊快照 16G

真正的元兇是 binlog，佔了整顆碟的六成。

binlog 是什麼、為什麼會長這麼大

binlog 是 MySQL 把每一筆新增、修改、刪除照時間順序記下來的流水帳，主要有三個用途：

主從同步：別台 DB 照著流水帳重做一遍，保持一致。
時間點還原：先還原備份，再重播 binlog 到出事前一刻。
事後追查：查某筆資料是什麼時候被改的。

這台機器的設定是：

log_bin                    = ON
binlog_format              = ROW
binlog_expire_logs_seconds = 2592000   # 30 天

保留 30 天是 MySQL 8 的預設值，看起來很合理。問題出在每週的同步本身：把 85GB 的資料灌進來，這 85GB 的每一筆 INSERT 也全部被記進 binlog。binlog_format=ROW 記的是每一列的完整內容，所以每同步一次，binlog 就多出一份跟整個資料庫差不多大的量。

30 天 × 每週一次 × 85GB，再加上平常的寫入，一顆 1TB 的碟撐不了多久。這週同步灌到一半時碟滿了， _staging 就這樣卡在那裡，同時所有其他寫入也跟著卡住。

而這台機器完全用不到 binlog：沒有任何 DB 在跟它做主從同步，資料每週從正式站重灌，也不需要時間點還原。它只是預設開著。

坑五：錯誤訊息說 /tmp 滿了，但 /tmp 根本沒滿

The table '/tmp/#sql...' is full 會讓人第一時間去看 /tmp，但 /tmp 所在的系統碟還有 118GB。我的理解是：MySQL 8 的內部暫存表超出記憶體上限後，會改寫到資料目錄裡的 InnoDB 暫存空間，真正滿的是資料碟，錯誤訊息裡的 /tmp/#sql... 只是那張暫存表的名字。看到這個錯誤，要檢查的是 MySQL 的資料目錄，不只是 /tmp。

坑六：PURGE BINARY LOGS 也一起卡住

直覺的修法是清掉舊的 binlog：

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

結果等了 10 分鐘一個檔都沒刪。原因是碟滿的當下，有一筆交易正卡在「寫 binlog」這一步，手上握著 binlog 的鎖不放；PURGE 也需要同一把鎖，只能排隊。要清 binlog，得先讓卡住的寫入能動，但寫入要能動，又得先有空間，繞成一個圈。

死結循環：資料碟滿、交易卡在寫 binlog、binlog 的鎖不放、PURGE 只能排隊、空間出不來；解法是先刪掉 _staging 騰出空間

順便想把保留期改短：

SET PERSIST binlog_expire_logs_seconds = 259200;

一樣失敗，Variables cannot be persisted，因為 SET PERSIST 要寫一個設定檔到資料目錄，碟滿寫不進去。

解法：先騰出一點空間，再清 binlog

要打破這個圈，只要先騰出一點空間，不必是 binlog。這次剛好有現成的目標：同步到一半失敗的 _staging，87GB，留著也沒用（下次同步會重建）。刪除時要注意，DROP DATABASE 本身也會寫 binlog，所以在同一個連線裡先關掉 binlog 再刪：

SET SESSION sql_log_bin = 0;   -- 只影響這一個連線
DROP DATABASE IF EXISTS app_db_prodsync_staging;

碟馬上從 100% 降到 92%。空間一出來，卡住的寫入陸續完成，鎖放開了，再跑 PURGE 就順利結束：

/dev/sdb   1007G  365G  596G  38% /var/lib/mysql

binlog 從 572GB 降到 63GB，最後把保留期改成 3 天，之後就會自動清：

SET PERSIST binlog_expire_logs_seconds = 259200;   -- 3 天

資料碟用量變化：碟滿當下 100%，刪掉 _staging 後 92%，PURGE binlog 後 38%

如果手邊沒有可以刪的東西，另一個不刪任何資料的辦法是 ext4 預設保留給 root 的 5% 空間（1TB 的碟大約 50GB）。MySQL 不是用 root 執行，用不到這塊，所以 df 才會顯示剩 0。用 tune2fs -m 1 /dev/sdb 把保留比例降到 1%，可以馬上多出約 40GB，事後再改回 5%。這次沒用到，但先記下來。

為什麼不乾脆關掉 binlog

完全關掉要改設定檔、重啟 MySQL，所有共用這台的測試站都會斷線。保留 3 天在空間上已經完全夠用，又留了一點事後追查的能力，所以選擇不關。

四、順便踩的坑：卡住的 migration，清出空間後自己跑完了

事故發生時，我手上正在幫一個新功能（管理者操作紀錄）跑 migration。當時為了驗證可以重複執行，下的是一串 migrate → undo → migrate。第一個 migrate 卡住，我把本機的行程砍掉，以為就結束了。

結果兩件事出乎意料：

砍掉本機的行程，不會停掉已經送到 server 的 SQL。 server 上的 ALTER TABLE 還在排隊。
我砍的只是那一串指令裡的第一個行程，shell 接著執行了 undo，送出一個 DROP TABLE，也進了排隊。

碟空出來之後，這兩條排隊的語句都跑完了，結果 migration 的執行紀錄跟資料庫的實際結構對不上：

項目	實際狀態	migration 紀錄表
歸檔表	被 undo 刪掉了	還記著「已建立」
新欄位	已經加上	沒有紀錄

這種「紀錄說有、實際沒有」的狀態，下次部署跑 migration 就會撞牆：以為建過的表不存在，以為沒加過的欄位一加就 Duplicate column。

教訓：

migration 要寫成可重複執行。建表前先查 information_schema 確認不存在、加欄位前先查欄位在不在。這次就是靠這點，補救時只要刪掉紀錄表裡那一筆，再正常跑一次 migration，就會自己補齊，不用手動下 DDL。
要中止一個卡住的 migration，先停整串指令，再到 DB 端看 processlist、決定要不要 KILL。 只砍本機行程，等於什麼都沒停。
DB 已經卡住的時候，不要再往上疊 DDL。 先找出卡住的原因。

總結

「把正式站複製到測試機」本身不難：mysqldump 用 pipe 不落地、先灌暫存再換位、寫好驗證，就很穩。
難的是它的副作用：每次大量寫入，都會在 binlog 留下一份同樣大的紀錄。用不到 binlog 的機器，保留期至少要調短，或乾脆關掉。
碟滿時的錯誤訊息會誤導人（說 /tmp 滿），清理的指令也會跟著卡住（要先騰空間才能清）。先準備好一個「不刪資料也能騰出空間」的退路，例如 ext4 的保留空間。
開新的定期任務時，順手算一下它每次會讓什麼東西長多大，以及那些東西多久才會被清掉。

背景