MySQL 協議深度解析：從 Packet 結構到 mixer 代理實現踩坑記

協議基礎：兩大數據類型

• Integer：Fixed Length (1/2/3/4/8 字節) + Length Encoded Integer (變長)
• String：Fixed Length / Null Terminated / Length Encoded / EOF Terminated

Length Encoded Integer 編碼規則

Packet 封裝格式

+-------------------+-----------------+------------------+
| 3 bytes payload   | 1 byte seq_id   | string[payload]  |
| length (little)   | (0~255 循環)    | payload data     |
+-------------------+-----------------+------------------+
Max payload: 16MB (2^24-1)，超過需拆分多包，seq_id 歸零重新計數

服務端響應包類型

• OK Packet：執行成功，含 affected_rows, insert_id, server_status, warnings
• ERR Packet：錯誤碼、SQLState、錯誤信息
• EOF Packet：結果集結束、舊版分頁標識

認證流程：Handshake V10 詳解

1. Client → Server：TCP 連接建立
2. Server → Client：Initial Handshake Packet
   • Protocol version (10)
   • Server version string
   • Connection ID
   • Auth-plugin-data-part-1 (8 bytes 隨機鹽)
   • Capability flags (lower 2 bytes)
   • Charset (1 byte)
   • Status flags (2 bytes)
   • Capability flags (upper 2 bytes)
   • Auth-plugin-data-len (1 byte)
   • Reserved (10 bytes)
   • Auth-plugin-data-part-2 (12 bytes, 非文檔 13 bytes！)
   • Auth plugin name (如 mysql_native_password)
3. Client → Server：Handshake Response
   • Client capability flags
   • Max packet size
   • Charset
   • Username (null terminated)
   • Auth-response (Length Encoded String，SCRAM-SHA1/雙重 SHA1)
   • Database name (可選)
   • Auth plugin name (可選)
4. Server → Client：OK / ERR Packet

巨坑：Auth-plugin-data-part-2 長度

官方文檔寫 MAX(13, len-8)，實際固定 12 字節，第 13 字節恒為 0。Python/go-sql-driver 等驅動均硬編碼 12。Wireshark 抓包驗證為準，文檔不可全信！

命令體系：COM_QUERY vs COM_STMT

COM_QUERY (文本協議)

• 支援：SELECT/INSERT/UPDATE/DELETE/REPLACE/BEGIN/COMMIT/ROLLBACK/SET NAMES/SET AUTOCOMMIT
• 返回：OK/ERR/Local Infile/Text Resultset
• Text Resultset 結構：
  1. Column Count Packet (Length Encoded Integer)
  2. Column Definition Packets (每列：catalog, schema, table, org_table, name, org_name, charset, length, type, flags, decimals)
  3. EOF Packet
  4. Row Packets (每行：Length Encoded String per column)
  5. EOF/ERR Packet

COM_STMT_* (預處理協議，二進制協議)

• COM_STMT_PREPARE：發送 SQL，返回 statement_id, param_count, column_count, 警告數
• COM_STMT_EXECUTE：綁定參數執行，返回 Binary Resultset
• COM_STMT_CLOSE：釋放 statement_id
• Binary Resultset Row：

  +--------+---------------+------------------+
  | 0x00   | Null Bitmap   | Column Values... |
  | (1 byte)| (col+7)/8 bytes| 按類型編碼       |
  +--------+---------------+------------------+

  Null Bitmap 計算：byte = (pos + 2) / 8, bit = (pos + 2) % 8 (offset=2 跳過 0x00 和保留位)
• 類型編碼：MYSQL_TYPE_LONGLONG=8 字節定長，MYSQL_TYPE_STRING=Length Encoded String，等

為什麼 mixer 只支持 COM_STMT 不支持 COM_QUERY PREPARE？

COM_QUERY PREPARE 需維護會話變量（SET @a=1），代理後端連接池複用時變量狀態難同步，複雜度極高。COM_STMT 狀態在 statement_id 內封裝，天然適合連接池。

實戰工具：Wireshark 解析 MySQL 協議

1. 捕獲過濾器：tcp.port == 3306
2. 右鍵 TCP 流 → Follow → TCP Stream
3. Wireshark 內建 MySQL 協議解析器，自動展示 Packet 結構、命令類型、錯誤碼
4. 對比 Client/Server 交互，快速定位握手失敗、命令格式錯誤、結果集截斷等問題

mixer 專案地址

https://github.com/siddontang/mixer —— 輕量級 MySQL 代理，支持讀寫分離、SQL 審計、熔斷限流，歡迎 Star 與 PR。

總結

MySQL 協議看似簡單，細節決定成敗：Length Encoded Integer、Packet 分片、Handshake V10 鹽值長度、Null Bitmap 計算、二進制協議類型編碼。建議：讀官方源碼 + 抓包驗證 + 寫測試用例，切勿盲目信任文檔。