mysqldump 意外終止根因深究：net_write_timeout 才是真兇

淘寶長期遭遇 mysqldump 備份隨機失敗，錯誤均為 Lost connection to MySQL server during query。經系統性復現實驗，鎖定核心原因：**mysqldump 下游處理（gzip 壓縮、NFS 寫入）過慢，導致 MySQL 服務端發送數據超時（net_write_timeout）主動斬斷連接**。本文還原排查全過程、給出生產級規避方案。

常見錯誤現象（非根因）

• Lost connection ... at 'reading initial communication packet' → DNS 不穩，開 skip-name-resolve 緩解。
• Lost connection ... at 'reading authorization packet' → 握手階段網絡抖動，增大 connect_timeout 或客戶端重試。
• Lost connection ... during query → **隨機發生，唯有此錯誤無固定誘因，且伴隨 gzip/NFS 場景。**

復現實驗：釐清「慢消費 → 服務端超時」鏈路

環境構建

-- 192.168.0.1:3306 構建大表
CREATE TABLE test (id BIGINT AUTO_INCREMENT PRIMARY KEY,
  b VARCHAR(2000), c VARCHAR(2000), d VARCHAR(2000), e VARCHAR(2000)
) ENGINE=MyISAM CHARSET=utf8;
-- 灌入 10萬行，單行 ~7.6KB，表 ~760MB

客戶端模擬程序（C++，模擬 mysqldump 讀取邏輯）

MYSQL *conn; mysql_init(&conn);
my_bool reconnect = 0; mysql_options(&conn, MYSQL_OPT_RECONNECT, &reconnect);
mysql_real_connect(&conn, "192.168.0.1", "test", "test", "test", 3306, NULL, 0);
mysql_query(&conn, "SELECT SQL_NO_CACHE * FROM test.test");
MYSQL_RES *res = mysql_use_result(&conn);  // 不緩存結果集，流式讀取
while ((row = mysql_fetch_row(res)) != NULL) {
    printf("%s\n", row[0]);
    sleep(100);  // 關鍵：模擬 gzip 壓縮 / NFS 寫入 導致的處理延遲
}
mysql_free_result(res); mysql_close(&conn);

實驗結果

• sleep(100) → **必現** Lost connection during query，約 60 秒後斷開。
• sleep(1) → **完整跑完**，無錯誤。

根因確證

MySQL 服務端發送數據時，若客戶端在 net_write_timeout（預設 60 秒）內未讀取完當前數據包，服務端判定客戶端「讀取過慢」，**主動發送 FIN 包斷開連接**。mysqldump 收到 EOF，報錯 Lost connection during query。

SHOW VARIABLES LIKE 'net_write_timeout';  -- 默認 60 秒

生產級規避方案（按推薦度排序）

1. 增大服務端 net_write_timeout（最直接）

   SET GLOBAL net_write_timeout = 600;  -- 10分鐘
   -- 永久生效寫入 my.cnf [mysqld] 區段

   覆蓋所有慢消費場景（mysqldump、慢應用、異地同步），無需改客戶端代碼。

2. mysqldump 端繞過慢消費：--quick + 直接輸出本地磁碟

   mysqldump --quick --single-transaction --default-character-set=utf8mb4 \
     -h host -u user -p db | gzip > /local/ssd/backup.sql.gz
   # 關鍵：先寫本地 SSD，備份完成再異步上傳 NFS/對象存儲

   --quick 使用 mysql_use_result() 流式讀取，避免客戶端緩存大結果集 OOM；本地 SSD 寫入極快，不會觸發服務端超時。

3. 分表並行備份，壓縮單表耗時

   # 利用 mysqldump --where 分片，或用 mydumper 並行導出
   mydumper -h host -u user -p -d db -o /local/ssd/backup -t 8 -c

4. 調大客戶端讀取緩衝（針對應用層）

   mysql_options(&conn, MYSQL_OPT_READ_TIMEOUT, &timeout);  // 客戶端耐心等待更久
   // 但無法解決服務端主動斷開的問題，治標不治本

延伸思考：為什麼不調 net_read_timeout？

• net_read_timeout：服務端等待**客戶端發送請求**的超時（預設 30 秒）。
• net_write_timeout：服務端等待**客戶端讀取響應**的超時（預設 60 秒）。
• mysqldump 場景是**服務端發送、客戶端讀取**，所以只調 net_write_timeout。

總結

Lost connection during query 在備份場景 90% 以上由 **net_write_timeout 觸發**。一鍵修復：SET GLOBAL net_write_timeout=600 + mysqldump 輸出本地 SSD + 異步上傳。將「網絡抖動、DNS、握手」等干擾項排除後，直擊「生產者快、消費者慢」的流控本質，才是資深運維的分水嶺。