max_allowed_packet引起MySQL遷移丟失資料的問題

Mr-houzi發表於2021-06-18

起因

最近遷移了伺服器,運維同學給導了資料庫,資料庫也跟著一起遷移到了新機房。今天,有使用者發現自己建立的一條記錄不見了,我去新庫查詢確實沒有,去舊庫查詢到了這條記錄,確定了是資料丟失。

排查過程

起初,懷疑遷移到新庫已經有幾天了,是否是這幾天使用者手動刪除了這條記錄?還好對使用者的改動做了記錄,經過查詢發現,使用者並沒有刪除動作。

所以,大概率是資料庫遷移引起的資料庫丟失,具體是哪個環節出錯,還要繼續排查。

運維同學給出了當時從老庫裡匯出的sql,發現是存在丟失的這條記錄的,這證明了從老庫匯出沒有問題,問題出現在往新庫匯入的時候

找到了這條丟失記錄的匯入sql,由於有一個欄位用了 longtext 型別,這個欄位記憶體入了大量的json資料,將這條sql儲存成txt後,竟然有1.7M大小。把這條語句通過命令列單獨匯入,報錯 MySQL server has gone away

問題

通過上面的排查,基本上可以確定是由於sql語句過大,導致插入失敗,引起遷移時資料丟失的問題。

解決

MySQL 中有一個 max_allowed_packet 引數,用來控制一次插入語句的大小,像Blob、longtext型別的欄位很容易導致sql語句過長,而達到 max_allowed_packet 的限制。

max_allowed_packet 當前大小是 1048576 (1024 X 1024 X 1),也就是 1M 大小,而我那條語句竟然達到了1.7M ,顯然超過了上限。


mysql> show global variables like 'max_allowed_packet';

+--------------------+---------+

| Variable_name      | Value   |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.01 sec)

調大 max_allowed_packet


mysql> set global max_allowed_packet=1024*1024*16;

Query OK, 0 rows affected (0.00 sec)

對比新老兩庫後,發現了總共丟失了兩條記錄,在調大 max_allowed_packet 值後,分別重新插入,恢復了丟失的資料。

反思

經歷了這次事件後的幾個小反思:

1. Text 欄位是個坑。

Text對資料庫效能就已經有明顯影響了。更何況了是LongText。LongText 最大能存 (2^32 -1)個位元組,即 4GiB。使用LongText欄位便是給自己和運維同學留下了一個坑。由於這個欄位儲存的是大文字 json ,日後可以考慮將此欄位放入 MongoDB 。

  • TEXT | 65,535(2 16 -1)個位元組= 64個KiB

  • MEDIUMTEXT | 16,777,215(2 24 -1)位元組= 16 MiB

  • LONGTEXT | 4,294,967,295(2 32 -1)個位元組= 4個GiB

2. 資料遷移失敗日誌。

運維同學說使用 mysqldump 導得資料,如果資料遷移時能有一個失敗日誌,那麼就能及時發現這個問題。另外,遷移庫後,如果我立即比較一下表的記錄數,或許也能早點發現這個問題。

[參考]

MySQL server has gone away 問題的解決方法

本作品採用《CC 協議》,轉載必須註明作者和本文連結

相關文章