最近，在開發一個分庫分表中間件，由於功能需求，需要分析MySQL協議，發現網上對於MySQL協議分析的文章大部分都過時了，原因是分析的MySQL版本太低了。怎麼辦呢？於是乎，我便硬著頭皮開始啃MySQL源碼，經過兩個多月的整理，終於總結出這篇MySQL協議。
注：部分來自於互聯網，感謝數據庫大牛前輩們的默默付出！
交互過程
MySQL客戶端與服務器的交互主要分為兩個階段：握手認證階段和命令執行階段。
握手認證階段
握手認證階段為客戶端與服務器建立連接後進行，交互過程如下：
- 服務器 -> 客戶端：握手初始化消息
- 客戶端 -> 服務器：登陸認證消息
- 服務器 -> 客戶端：認證結果消息
命令執行階段
客戶端認證成功後，會進入命令執行階段，交互過程如下：
- 客戶端 -> 服務器：執行命令消息
- 服務器 -> 客戶端：命令執行結果
MySQL客戶端與服務器的完整交互過程如下 ：
基本類型
整型值
MySQL報文中整型值分別有1、2、3、4、8字節長度，使用小字節序傳輸。
字符串（以NULL結尾）（Null-Terminated String）
字符串長度不固定，當遇到'NULL'（0x00）字符時結束。
二進制數據（長度編碼）（Length Coded Binary）
數據長度不固定，長度值由數據前的1-9個字節決定，其中長度值所佔的字節數不定，字節數由第1個字節決定，如下表：
字符串（長度編碼）（Length Coded String）
字符串長度不固定，無'NULL'（0x00）結束符，編碼方式與上面的 Length Coded Binary 相同。
報文結構
報文分為消息頭和消息體兩部分，其中消息頭佔用固定的4個字節，消息體長度由消息頭中的長度字段決定，報文結構如下：
消息頭
報文長度
用於標記當前請求消息的實際數據長度值，以字節為單位，佔用3個字節，最大值為 0xFFFFFF，即接近 16 MB 大小（比16MB少1個字節）。
序號
在一次完整的請求/響應交互過程中，用於保證消息順序的正確，每次客戶端發起請求時，序號值都會從0開始計算。
消息體
消息體用於存放請求的內容及相應的數據，長度由消息頭中的長度值決定。
報文類型
登陸認證交互報文
握手初始化報文（服務器 -> 客戶端）
服務協議版本號：該值由 PROTOCOL_VERSION 宏定義決定（參考MySQL源代碼/include/mysql_version.h頭文件定義）
服務版本信息：該值為字符串，由 MYSQL_SERVER_VERSION 宏定義決定（參考MySQL源代碼/include/mysql_version.h頭文件定義）
服務器線程ID：服務器為當前連接所創建的線程ID。
挑戰隨機數：MySQL數據庫用戶認證採用的是挑戰/應答的方式，服務器生成該挑戰數併發送給客戶端，由客戶端進行處理並返回相應結果，然後服務器檢查是否與預期的結果相同，從而完成用戶認證的過程。
服務器功能標誌：用於與客戶端協商通訊方式，各標誌位含義如下（參考MySQL源代碼/include/mysql_com.h中的宏定義）：
字符編碼：表示服務器所使用的字符集。
服務器狀態：狀態值定義如下（參考MySQL源代碼/include/mysql_com.h中的宏定義）：
登陸認證報文（客戶端 -> 服務器）
MySQL 4.0 及之前的版本
MySQL 4.1 及之後的版本
客戶端權能標誌：用於與客戶端協商通訊方式，標誌位含義與握手初始化報文中的相同。客戶端收到服務器發來的初始化報文後，會對服務器發送的權能標誌進行修改，保留自身所支持的功能，然後將權能標返回給服務器，從而保證服務器與客戶端通訊的兼容性。
最大消息長度：客戶端發送請求報文時所支持的最大消息長度值。
字符編碼：標識通訊過程中使用的字符編碼，與服務器在認證初始化報文中發送的相同。
用戶名：客戶端登陸用戶的用戶名稱。
挑戰認證數據：客戶端用戶密碼使用服務器發送的挑戰隨機數進行加密後，生成挑戰認證數據，然後返回給服務器，用於對用戶身份的認證。
數據庫名稱 ：當客戶端的權能標誌為 CLIENT_CONNECT_WITH_DB 被置位時，該字段必須出現。
客戶端命令請求報文（客戶端 -> 服務器）
命令：用於標識當前請求消息的類型，例如切換數據庫（0x02）、查詢命令（0x03）等。命令值的取值範圍及說明如下表（參考MySQL源代碼/include/mysql_com.h頭文件中的定義）：
參數：內容是用戶在MySQL客戶端輸入的命令（不包括每行命令結尾的";"分號）。另外這個字段的字符串不是以NULL字符結尾，而是通過消息頭中的長度值計算而來。
例如：當我們在MySQL客戶端中執行use hutaow;命令時（切換到hutaow數據庫），發送的請求報文數據會是下面的樣子：
<code>0x02 0x68 0x75 0x74 0x61 0x6f 0x77/<code>
其中，0x02為請求類型值COM_INIT_DB，後面的0x68 0x75 0x74 0x61 0x6f 0x77為ASCII字符hutaow。
COM_QUIT 消息報文
功能：關閉當前連接（客戶端退出），無參數。
COM_INIT_DB 消息報文
功能：切換數據庫，對應的SQL語句為USE。
COM_QUERY 消息報文
功能：最常見的請求消息類型，當用戶執行SQL語句時發送該消息。
COM_FIELD_LIST 消息報文
功能：查詢某表的字段（列）信息，等同於SQL語句SHOW [FULL] FIELDS FROM ...。
COM_CREATE_DB 消息報文
功能：創建數據庫，該消息已過時，而被SQL語句CREATE DATABASE代替。
COM_DROP_DB 消息報文
功能：刪除數據庫，該消息已過時，而被SQL語句DROP DATABASE代替。
COM_REFRESH 消息報文
功能：清除緩存，等同於SQL語句FLUSH，或是執行mysqladmin flush-foo命令時發送該消息。
COM_SHUTDOWN 消息報文
功能：停止MySQL服務。執行mysqladmin shutdown命令時發送該消息。
COM_STATISTICS 消息報文
功能：查看MySQL服務的統計信息（例如運行時間、每秒查詢次數等）。執行mysqladmin status命令時發送該消息，無參數。
COM_PROCESS_INFO 消息報文
功能：獲取當前活動的線程（連接）列表。等同於SQL語句SHOW PROCESSLIST，或是執行mysqladmin processlist命令時發送該消息，無參數。
COM_PROCESS_KILL 消息報文
功能：要求服務器中斷某個連接。等同於SQL語句KILL。
COM_DEBUG 消息報文
功能：要求服務器將調試信息保存下來，保存的信息多少依賴於編譯選項設置（debug=no|yes|full）。執行mysqladmin debug命令時發送該消息，無參數。
COM_PING 消息報文
功能：該消息用來測試連通性，同時會將服務器的無效連接（超時）計數器清零。執行mysqladmin ping命令時發送該消息，無參數。
COM_CHANGE_USER 消息報文
功能：在不斷連接的情況下重新登陸，該操作會銷燬MySQL服務器端的會話上下文（包括臨時表、會話變量等）。有些連接池用這種方法實現清除會話上下文。
COM_BINLOG_DUMP 消息報文
功能：該消息是備份連接時由從服務器向主服務器發送的最後一個請求，主服務器收到後，會響應一系列的報文，每個報文都包含一個二進制日誌事件。如果主服務器出現故障時，會發送一個EOF報文。
COM_TABLE_DUMP 消息報文
功能：將數據表從主服務器複製到從服務器中，執行SQL語句LOAD TABLE ... FROM MASTER時發送該消息。目前該消息已過時，不再使用。
COM_REGISTER_SLAVE 消息報文
功能：在從服務器report_host變量設置的情況下，當備份連接時向主服務器發送的註冊消息。
COM_PREPARE 消息報文
功能：預處理SQL語句，使用帶有"?"佔位符的SQL語句時發送該消息。
COM_EXECUTE 消息報文
功能：執行預處理語句。
COM_LONG_DATA 消息報文
該消息報文有兩種形式，一種用於發送二進制數據，另一種用於發送文本數據。
功能：用於發送二進制（BLOB）類型的數據（調用mysql_stmt_send_long_data函數）。
功能：用於發送超長字符串類型的數據（調用mysql_send_long_data函數）
COM_CLOSE_STMT 消息報文
功能：銷燬預處理語句。
COM_RESET_STMT 消息報文
功能：將預處理語句的參數緩存清空。多數情況和COM_LONG_DATA一起使用。
COM_SET_OPTION 消息報文
功能：設置語句選項，選項值為/include/mysql_com.h頭文件中定義的enum_mysql_set_option枚舉類型：
- MYSQL_OPTION_MULTI_STATEMENTS_ON
- MYSQL_OPTION_MULTI_STATEMENTS_OFF
COM_FETCH_STMT 消息報文
功能：獲取預處理語句的執行結果（一次可以獲取多行數據）。
服務器響應報文（服務器 -> 客戶端）
當客戶端發起認證請求或命令請求後，服務器會返回相應的執行結果給客戶端。客戶端在收到響應報文後，需要首先檢查第1個字節的值，來區分響應報文的類型。
注：響應報文的第1個字節在不同類型中含義不同，比如在OK報文中，該字節並沒有實際意義，值恆為0x00；而在Result Set報文中，該字節又是長度編碼的二進制數據結構（Length Coded Binary）中的第1字節。
響應報文
客戶端的命令執行正確時，服務器會返回OK響應報文。
MySQL 4.0 及之前的版本
MySQL 4.1 及之後的版本
受影響行數：當執行INSERT/UPDATE/DELETE語句時所影響的數據行數。
索引ID值：該值為AUTO_INCREMENT索引字段生成，如果沒有索引字段，則為0x00。注意：當INSERT插入語句為多行數據時，該索引ID值為第一個插入的數據行索引值，而非最後一個。
服務器狀態：客戶端可以通過該值檢查命令是否在事務處理中。
告警計數：告警發生的次數。
服務器消息：服務器返回給客戶端的消息，一般為簡單的描述性字符串，可選字段。
響應報文
MySQL 4.0 及之前的版本
MySQL 4.1 及之後的版本
錯誤編號：錯誤編號值定義在源代碼/include/mysqld_error.h頭文件中。
服務器狀態：服務器將錯誤編號通過mysql_errno_to_sqlstate函數轉換為狀態值，狀態值由5字節的ASCII字符組成，定義在源代碼/include/sql_state.h頭文件中。
服務器消息：錯誤消息字符串到達消息尾時結束，長度可以由消息頭中的長度值計算得出。消息長度為0-512字節。
Result Set 消息
當客戶端發送查詢請求後，在沒有錯誤的情況下，服務器會返回結果集（Result Set）給客戶端。
Result Set 消息分為五部分，結構如下：
Result Set Header 結構
Field結構計數：用於標示Field結構的數量，取值範圍0x00-0xFA。
額外信息：可選字段，一般情況下不應該出現。只有像SHOW COLUMNS這種語句的執行結果才會用到額外信息（標識表格的列數量）。
Field 結構
Field為數據表的列信息，在Result Set中，Field會連續出現多次，次數由Result Set Header結構中的IField結構計數值決定。
MySQL 4.0 及之前的版本
MySQL 4.1 及之後的版本
目錄名稱：在4.1及之後的版本中，該字段值為"def"。
數據庫名稱：數據庫名稱標識。
數據表名稱：數據表的別名（AS之後的名稱）。
數據表原始名稱：數據表的原始名稱（AS之前的名稱）。
列（字段）名稱：列（字段）的別名（AS之後的名稱）。
列（字段）原始名稱：列（字段）的原始名稱（AS之前的名稱）。
字符編碼：列（字段）的字符編碼值。
列（字段）長度：列（字段）的長度值，真實長度可能小於該值，例如VARCHAR(2)類型的字段實際只能存儲1個字符。
列（字段）類型：列（字段）的類型值，取值範圍如下（參考源代碼/include/mysql_com.h頭文件中的enum_field_type枚舉類型定義）：
列（字段）標誌：各標誌位定義如下（參考源代碼/include/mysql_com.h頭文件中的宏定義）：
數值精度：該字段對DECIMAL和NUMERIC類型的數值字段有效，用於標識數值的精度（小數點位置）。
默認值：該字段用在數據表定義中，普通的查詢結果中不會出現。
附：Field結構的相關處理函數：
- 客戶端：
/client/client.c
源文件中的unpack_fields
函數 - 服務器：
/sql/sql_base.cc
源文件中的send_fields
函數
EOF 結構
EOF結構用於標識Field和Row Data的結束，在預處理語句中，EOF也被用來標識參數的結束。
MySQL 4.0 及之前的版本
MySQL 4.1 及之後的版本
告警計數：服務器告警數量，在所有數據都發送給客戶端後該值才有效。
狀態標誌位：包含類似SERVER_MORE_RESULTS_EXISTS這樣的標誌位。
注：由於EOF值與其它Result Set結構共用1字節，所以在收到報文後需要對EOF包的真實性進行校驗，校驗條件為：
- 第1字節值為0xFE
- 包長度小於9字節
附：EOF結構的相關處理函數：
- 服務器：
protocol.cc
源文件中的send_eof
函數
Row Data 結構
在Result Set消息中，會包含多個Row Data結構，每個Row Data結構又包含多個字段值，這些字段值組成一行數據。
字段值：行數據中的字段值，字符串形式。
附：Row Data結構的相關處理函數：
- 客戶端：
/client/client.c
源文件中的read_rows
函數
Row Data 結構（二進制數據）
該結構用於傳輸二進制的字段值，既可以是服務器返回的結果，也可以是由客戶端發送的（當執行預處理語句時，客戶端使用Result Set消息來發送參數及數據）。
空位圖：前2個比特位被保留，值分別為0和1，以保證不會和OK、Error包的首字節衝突。在MySQL 5.0及之後的版本中，這2個比特位的值都為0。
字段值：行數據中的字段值，二進制形式。
PREPARE_OK 響應報文（Prepared Statement）
用於響應客戶端發起的預處理語句報文，組成結構如下：
其中 PREPARD_OK 的結構如下：
Parameter 響應報文（Prepared Statement）
預處理語句的值與參數正確對應後，服務器會返回 Parameter 報文。
類型：與 Field 結構中的字段類型相同。
標誌：與 Field 結構中的字段標誌相同。
數值精度：與 Field 結構中的數值精度相同。
字段長度：與 Field 結構中的字段長度相同。
代碼分析
協議頭
● 數據變成在網絡裡傳輸的數據,需要額外的在頭部添加4 個字節的包頭.
. packet length(3字節), 包體的長度
. packet number(1字節), 從0開始的遞增的
● sql “select 1” 的網絡協議是？
協議頭
● packet length三個字節意味著MySQL packet最大16M大於16M則被分包(net_write_command, my_net_write)
● packet number分包從0開始,依次遞增.每一次執行sql, packet_number清零(sql/net_serv.c:net_clear)
協議類型
● handshake
● auth
● ok|error
● resultset
○ header
○ field
○ eof
○ row
● command packet
連接時的交互
協議說明
● 協議內字段分三種形式
○ 固定長度(include/my_global.h)
■ uint*korr 解包 *
■ int*store 封包
○ length coded binary(sql-common/pack.c)
■ net_field_length 解包
■ net_store_length 封包
○ null-terminated string
● length coded binary
○ 避免binary unsafe string, 字符串的長度保存在字符串的前面
■ length<251 1 byte
■ length <256^2 3 byte(第一個byte是252)
■ length<256^3 4byte(第一個byte是253)
■ else 9byte(第一個byte是254)
handshake packet
● 該協議由服務端發送客戶端
● 括號內為字節數,字節數為n為是null-terminated string;字節數為大寫的N表示length code binary.
● salt就是scramble.分成兩個部分是為了兼容4.1版本
● sql_connect.cc:check_connection
● sql_client.c:mysql_real_connect
auth packet
● 該協議是從客戶端對密碼使用scramble加密後發送到服務端
● 其中databasename是可選的.salt就是加密後的密碼.
● sql_client.c:mysql_real_connect
● sql_connect.c:check_connection
ok packet
● ok包,命令和insert,update,delete的返回結果
● 包體首字節為0.
● insert_id, affect_rows也是一併發過來.
● src/protocol.cc:net_send_ok
error packet
● 錯誤的命令,非法的sql的返回包
● 包體首字節為255.
● error code就是CR_***,include/errmsg.h ● sqlstate marker是#
● sqlstate是錯誤狀態,include/sql_state.h
● message是錯誤的信息
● sql/protocol.cc:net_send_error_packet
resultset packet
● 結果集的數據包,由多個packet組合而成
● 例如查詢一個結構集,順序如下: ○ header ○ field1....fieldN ○ eof ○ row1...rowN ○ eof
● sql/client.c:cli_read_query_result
● 下面是一個sql "select * from d"查詢結果集的例子,結果 集是6行，3個字段 ○ 公式：假設結果集有N行, M個字段.則包的個數為，header(1) + field (M) + eof(1) + row(N) + eof(1) ○ 所以這個例子的MySQL packet的個數是12個
resultset packet - header
● field packet number決定了接下來的field packet的個數.
● 一個返回6行記錄,3個字段的查詢語句
resultset packet - field
● 結果集中一個字段一個field packet.
● tables_alias是sql語句裡表的別名,org_table才是表的真 實名字.
● sql/protocol.cc:Protocol::send_fields
● sql/client.c:cli_read_query_result
resultset packet - eof
● eof包是用於分割field packet和row packet.
● 包體首字節為254
● sql/protocol.cc:net_send_eof
resultset packet - row
● row packet裡才是真正的數據包.一行數據一個packet.
● row裡的每個字段都是length coded binary
● 字段的個數在header packet裡
● sql/client.c:cli_read_rows
command packet
● 命令包,包括我們的sql語句還有一些常見的命令.
● 包體首字母表示命令的類型(include/mysql_com.h),大 部分命令都是COM_QUERY.
網絡協議關鍵函數
● net_write_command(sql/net_serv.cc)所有的sql最終調用這個命令發送出去.
● my_net_write(sql/net_serv.cc)連接階段的socket write操作調用這個函數.
● my_net_read讀取包,會判斷包大小,是否是分包
● my_real_read解析MySQL packet,第一次讀取4字節,根據packet length再讀取餘下來的長度
● cli_safe_read客戶端解包函數,包含了my_net_read
NET緩衝
● 每次socket操作都會先把數據寫,讀到net->buff,這是一 個緩衝區, 減少系統調用調用的次數.
● 當寫入的數據和buff內的數據超過buff大小才會發出一次 write操作,然後再把要寫入的buff裡插入數, 寫入不會 導致buff區區域擴展.(sql/net_serv.cc: net_write_buff).
● net->buff大小初始net->max_packet, 讀取會導致會導致 buff的realloc最大net->max_packet_size
● 一次sql命令的結束都會調用net_flush,把buff裡的數據 都寫到socket裡.
VIO緩衝
● 從my_read_read可以看出每次packet讀取都是按需讀取， 為了減少系統調用,vio層面加了一個read_buffer.
● 每次讀取前先判斷vio->read_buffer所需數據的長度是 否足夠.如果存在則直接copy. 如果不夠,則觸發一次 socket read 讀取2048個字(vio/viosocket.c: vio_read_buff)
MySQL API
● 數據從mysql_send_query處發送給服務端,實際調用的是 net_write_command.
● cli_read_query_result解析header packet, field packet,獲 得field_count的個數
● mysql_store_result解析了row packet,並存儲在result- >data裡
● myql_fetch_row其實遍歷result->data
PACKET NUMBER
在做proxy的時候在這裡迷糊過,翻了幾遍代碼才搞明白，細節如下： 客戶端服務端的net->pkt_nr都從0開始.接受包時比較packet number 和net->pkt_nr是否相等,否則報packet number亂序,連接報錯;相等則pkt_nr自增.發送包時把net->pkt_nr作為packet number發送,然後對net->pkt_nr進行自動保持和對端的同步.
接收包
發送包
我們來幾個具體場景的packet number, net->pkt_nr的變化
連接
開始兩方都為0,服務端發送handshake packet(pkt=0)之後自增為1,然後等待對端發送過來pkt=1的包
查詢
每次查詢,服務客戶端都會對net->pkt_nr進行清零
開始兩方net->pkt_nr皆為0, 命令發送後客戶端端為1,服務端開始發送分包,分包的pkt_nr的依次遞增,客戶端的net->pkt_nr也隨之增加.
解包的細節
my_net_read負責解包，首先讀取4個字節，判斷packet number是否等於net->pkt_nr然後再次讀取packet_number長度的包體。
偽代碼如下：
網絡層優化
從ppt裡可以看到,一個resultset packet由多個包組成,如果每次讀寫包都導致系統調用那肯定是不合理,常規優化方法:寫大包加預讀
NET->BUFF
每個包發送到網絡或者從網絡讀包都會先把數據包保存在net->buff裡,待到net->buff滿了或者一次命令結束才會通過socket發出給對端.net->buff有個初始大小(net->max_packet),會隨讀取數據的增多而擴展.
VIO->READ_BUFFER
每次從網絡讀包,並不是按包的大小讀取,而是會盡量讀取2048個字節,這樣一個resultset包的讀取不會再引起多次的系統調用了.header packet讀取完畢後, 接下來的field,eof, row apcket讀取僅僅需要從vio-read_buffer拷貝指定字節的數據即可.
MYSQL API說明
api和MySQL客戶端都會使用sql/client.c這個文件,解包的過程都是使用sql/client.c:cli_read_query_result.
mysql_store_result來解析row packet,並把數據存儲到res->data裡,此時所有數據都存內存裡了.
mysql_fetch_row僅僅是使用內部的遊標,遍歷result->data裡的數據
mysql_free_result是把result->data指定的行數據釋放掉.
