1.Mysql的連接方式
要了解Mysql的通訊協議，首先需要知道是以哪種連接方式去連接Mysql服務器的；Mysql的主要連接方式包括：Unix套接字，內存共享，命名管道，TCP/IP套接字等。
1.1Unix套接字
在Linux和Unix環境下，可以使用Unix套接字進行Mysql服務器的連接；Unix套接字其實不是一個網絡協議，只能在客戶端和Mysql服務器在同一臺電腦上才可以使用，使用方式也很簡單：
|
1
2
3
4
5
6
7
8
|
root@root ~]# mysql -uroot -prootmysql> show variables like 'socket';+---------------+---------------------------+| Variable_name | Value |+---------------+---------------------------+| socket | /var/lib/mysql/mysql.sock |+---------------+---------------------------+1 row in set (0.00 sec) |
以上命令查詢Unix套接字文件的位置；
1.2命名管道和內存共享
在window系統中客戶端和Mysql服務器在同一臺電腦上，可以使用命名管道和共享內存的方式，
命名管道開啟：–shared-memory=on/off；
共享內存開啟：–enable-named-pipe=on/off;
1.3TCP/IP套接字
在任何系統下都可以使用的方式，也是使用最多的連接方式，本文要介紹的通訊協議也是基于此連接方式的，下面通過tcpdump對TCP/IP套接字有一個初步的了解：
服務器端：
|
1
2
3
|
[root@root ~]# tcpdump port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on venet0, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes |
服務器端監聽3306端口(也就是Mysql的端口)；
客戶端：
|
1
2
3
|
C:\Users\hui.zhao>mysql -h64.xxx.xxx.xxx -uroot -prootmysql> exitBye |
客戶端連接服務器，然后斷開連接，這時候觀察服務器的監聽結果日志：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[root@root ~]# tcpdump port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on venet0, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes02:06:25.442472 IP 153.3.251.202.33876 > root.mysql: Flags [S], seq 27289263, win 8192, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 002:06:25.442763 IP root.mysql > 153.3.251.202.33876: Flags [S.], seq 2014324548, ack 27289264, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 002:06:25.617449 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 1, win 256, length 002:06:29.812946 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 1:57, ack 1, win 115, length 5602:06:29.992362 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 1:63, ack 57, win 256, length 6202:06:29.992411 IP root.mysql > 153.3.251.202.33876: Flags [.], ack 63, win 115, length 002:06:29.992474 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 57:68, ack 63, win 115, length 1102:06:30.166992 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 63:100, ack 68, win 256, length 3702:06:30.167109 IP root.mysql > 153.3.251.202.33876: Flags [P.], seq 68:158, ack 100, win 115, length 9002:06:30.536298 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 158, win 256, length 002:06:34.568611 IP 153.3.251.202.33876 > root.mysql: Flags [P.], seq 100:105, ack 158, win 256, length 502:06:34.568620 IP 153.3.251.202.33876 > root.mysql: Flags [F.], seq 105, ack 158, win 256, length 002:06:34.568751 IP root.mysql > 153.3.251.202.33876: Flags [F.], seq 158, ack 106, win 115, length 002:06:34.743815 IP 153.3.251.202.33876 > root.mysql: Flags [.], ack 159, win 256, length 0 |
[S]:SYN發起連接標志,[P]:PUSH傳送數據標志,[F]:FIN關閉連接標志,[.]:表示確認包;
可以大致看出流程：建立tcp連接，客戶端和Mysql服務器建立連接通訊，關閉tcp連接；
[S][S.][.]這幾個數據包表示tcp連接的三次握手；
[F.][F.][.]這幾個數據包表示tcp連接的四次揮手；
中間的多個[P.][.]其實就是客戶端和Mysql服務器建立連接發送的協議數據包。
2.協議分析
Mysql協議被用在Mysql Clients和Mysql Server通訊的時候，具體有以下幾個場景：客戶端和服務器進行連接，Mysql代理以及主從備份；
MySQL客戶端與服務器的交互主要分為兩個階段：Connection Phase（連接階段或者叫認證階段）和Command Phase（命令階段）；
結合tcpdump的輸出，客戶端和服務器端通訊的整個流程大致如下：
|
1
2
3
4
5
6
7
8
|
1.建立tcp連接三次握手；2.與Mysql服務器建立連接，即Connection Phase（連接階段或者叫認證階段）； s->c:發送握手初始化包（a Initial Handshake Packet） c->s:發送驗證包(authentication response) s->c:服務器發送認證結果包3.認證通過之后，服務器端接受客戶端的命令包，發送相應的響應包，即Command Phase（命令階段）；4.斷開連接請求exit命令；5.四次揮手tcp斷開連接； |
2.1基本類型
在整個協議中的基本類型：整數型和字符串型；
2.1.1整數型
分為兩種類型Fixed-Length Integer Types和Length-Encoded Integer Type；
Fixed-Length Integer Types：
一個固定長度的無符號整數將其值存儲在一系列字節中，具體固定字節數可以是：1，2，3，4，6，8；
Length-Encoded Integer Type：
存儲需要的字節數取決于數值的大小，具體可參照如下：
1個字節：0<=X<251；
2個字節：251<=X<2^16；
3個字節：2^16<=X<2^24；
9個字節：2^24<=X<2^64；
2.1.2字符串型
分為5種類型包括，FixedLengthString，NullTerminatedString，VariableLengthString，LengthEncodedString和RestOfPacketString；
FixedLengthString：固定長度的字符串具有已知的硬編碼長度，一個例子是ERR_Packet的SQL狀態，它總是5個字節長；
NullTerminatedString：以遇到Null(字節為00)結束的字符串；
VariableLengthString：可變字符串，字符串的長度由另一個字段決定或在運行時計算，比如int+value，int為長度，value為指定長度的字節數；
LengthEncodedString：以描述字符串長度的長度編碼的整數作為前綴的字符串，是VariableLengthString指定的int+value方式；
RestOfPacketString：如果一個字符串是數據包的最后一個組件，它的長度可以從整個數據包長度減去當前位置來計算；
2.2基本數據包
如果MySQL客戶端或服務器想要發送數據，則：
每個數據包大小不能超過2^24字節(16MB);
在每個數據塊前面加上一個數據包頭；
包格式如下：
|
1
2
3
|
int<3>：具體包內容的長度；除去int<3>+int<1>=4字節長度；int<1>：sequence_id隨每個數據包遞增，并可能環繞。 它從0開始，在命令階段開始一個新的命令時重置為0；string<var>：具體數據內容，也是int<3>指定的長度； |
例如：
01 00 00對應int表示具體數據內容的長度為1個字節；
00對應int表示sequence_id；
01對應string前面指定的數據內容為1個字節。
2.3報文類型
可以分成三個大類：登錄認證報文，客戶端請求報文以及服務器端返回報，基于mysql5.1.73(mysql4.1以后的版本)
2.3.1登錄認證報文
主要在交互的認證階段，由上文中可以知道一共分為三個階段：Handshake Packet，authentication response以及結果包，這里主要分析前兩個包；
2.3.1.1 Handshake Packet
|
1
2
3
4
5
6
7
8
9
10
11
|
1字節：協議版本號NullTerminatedString：數據庫版本信息4字節：連接MySQL Server啟動的線程ID8字節：挑戰隨機數，用于數據庫認證1字節：填充值(0x00)2字節：用于與客戶端協商通訊方式1字節：數據庫的編碼2字節：服務器狀態13字節：預留字節12字節：挑戰隨機數，用于數據庫認證1字節：填充值(0x00) |
使用tcpdump進行監聽，輸出十六進制日志如下：
|
1
2
3
4
5
6
7
8
9
|
[root@root ~]# tcpdump port 3306 -X......03:20:34.299521 IP root.mysql > 153.3.251.202.44658: Flags [P.], seq 1:57, ack 1, win 115, length 56 0x0000: 4508 0060 09f1 4000 4006 c666 43da 9190 E..`..@.@..fC... 0x0010: 9903 fbca 0cea ae72 bb4e 25ba 21e7 27e3 .......r.N%.!.'. 0x0020: 5018 0073 b1e0 0000 3400 0000 0a35 2e31 P..s....4....5.1 0x0030: 2e37 3300 4024 0000 5157 4222 252f 5f6f .73.@$..QWB"%/_o 0x0040: 00ff f708 0200 0000 0000 0000 0000 0000 ................ 0x0050: 0000 0032 4a5d 7553 7e45 784f 627e 7400 ...2J]uS~ExOb~t. |
包的總長度是56，減去int<3>+int<1>4字節=52字節，對應的十六進制就是34；int<3>十六進制為3400 00表示包內容長度，int<1>十六進制為00表示sequence_id；后續的內容就是包體內容共52字節，0a對應的十進制是10，所有協議號版本是10；后續的數據庫版本信息遇到00結束，35 2e31 2e37 33對應的5.1.73，正是當前使用的數據庫版本；4024 0000對應十進制是6436；08表示數據庫的編碼；0200表示服務器狀態；后續的13對00為預留字節；最后的13個字節是挑戰隨機數和填充值。
2.3.1.2 Authentication Packet
|
1
2
3
4
5
6
7
|
4字節：用于與客戶端協商通訊方式4字節：客戶端發送請求報文時所支持的最大消息長度值1字節：標識通訊過程中使用的字符編碼23字節：保留字節NullTerminatedString：用戶名LengthEncodedString：加密后的密碼NullTerminatedString：數據庫名稱（可選） |
使用tcpdump進行監聽，輸出十六進制日志如下：
|
1
2
3
4
5
6
7
8
|
03:20:34.587416 IP 153.3.251.202.44658 > root.mysql: Flags [P.], seq 1:63, ack 57, win 256, length 62 0x0000: 4500 0066 29ee 4000 7006 766b 9903 fbca E..f).@.p.vk.... 0x0010: 43da 9190 ae72 0cea 21e7 27e3 bb4e 25f2 C....r..!.'..N%. 0x0020: 5018 0100 d8d2 0000 3a00 0001 85a6 0f00 P.......:....... 0x0030: 0000 0001 2100 0000 0000 0000 0000 0000 ....!........... 0x0040: 0000 0000 0000 0000 0000 0000 726f 6f74 ............root 0x0050: 0014 ff58 4bd2 7946 91a0 a233 f2c1 28af ...XK.yF...3..(. 0x0060: d578 0762 c2e8 .x.b.. |
包的總長度是62，減去int<3>+int<1>4字節=58字節，對應的十六進制就是3a；int<3>十六進制為3a00 00表示包內容長度；int<1>十六進制為01表示sequence_id；726f 6f74 00是用戶名解碼后是root；后面是加密后的密碼類型是
LengthEncodedString，14對應的十進制是20，后面20個字節就是加密后的密碼；可選的數據庫名稱不存在。
2.4客戶端請求報文
|
1
2
|
int<1>：執行的命令，比如切換數據庫string<var>：命令相應的參數 |
命令列表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
0x00 COM_SLEEP （內部線程狀態)0x01 COM_QUIT 關閉連接0x02 COM_INIT_DB 切換數據庫0x03 COM_QUERY SQL查詢請求0x04 COM_FIELD_LIST 獲取數據表字段信息0x05 COM_CREATE_DB 創建數據庫0x06 COM_DROP_DB 刪除數據庫0x07 COM_REFRESH 清除緩存0x08 COM_SHUTDOWN 停止服務器0x09 COM_STATISTICS 獲取服務器統計信息0x0A COM_PROCESS_INFO 獲取當前連接的列表0x0B COM_CONNECT （內部線程狀態)0x0C COM_PROCESS_KILL 中斷某個連接0x0D COM_DEBUG 保存服務器調試信息0x0E COM_PING 測試連通性0x0F COM_TIME （內部線程狀態）0x10 COM_DELAYED_INSERT （內部線程狀態）0x11 COM_CHANGE_USER 重新登陸（不斷連接）0x12 COM_BINLOG_DUMP 獲取二進制日志信息0x13 COM_TABLE_DUMP 獲取數據表結構信息0x14 COM_CONNECT_OUT （內部線程狀態)0x15 COM_REGISTER_SLAVE 從服務器向主服務器進行注冊0x16 COM_STMT_PREPARE 預處理SQL語句0x17 COM_STMT_EXECUTE 執行預處理語句0x18 COM_STMT_SEND_LONG_DATA 發送BLOB類型的數據0x19 COM_STMT_CLOSE 銷毀預處理語句0x1A COM_STMT_RESET 清除預處理語句參數緩存0x1B COM_SET_OPTION 設置語句選項0x1C COM_STMT_FETCH 獲取預處理語句的執行結果 |
比如：use test;使用tcpdump進行監聽，輸出十六進制日志如下：
|
1
2
3
4
5
|
22:04:29.379165 IP 153.3.251.202.33826 > root.mysql: Flags [P.], seq 122:131, ack 222, win 64019, length 9 0x0000: 4500 0031 3f19 4000 7006 6175 9903 fbca E..1?.@.p.au.... 0x0010: 43da 9190 8422 0cea 42e2 524b 7e18 25c1 C...."..B.RK~.%. 0x0020: 5018 fa13 a07b 0000 0500 0000 0274 6573 P....{.......tes 0x0030: 74 |
包的總長度是9，減去int<3>+int<1>4字節=5字節，對應的十六進制就是05；int<3>十六進制為0500 00表示包內容長度；int<1>十六進制為00表示sequence_id；02對應COM_INIT_DB，后面是test的二進制編碼；
2.5服務器響應報文
對于客戶端發送給服務器的大多數命令，服務器返回其中一個響應的數據包：OK_Packet，ERR_Packet和EOF_Packet，Result Set；
2.5.1OK_Packet
表示成功完成一個命令，具體格式如下：
|
1
2
3
4
5
6
|
int<1>：0x00或0xFEOK包頭int<lenenc>：受影響行數int<lenenc>：最后插入的索引IDint<2>：服務器狀態int<2>：告警計數 注：MySQL 4.1 及之后的版本才有string<lenenc>：服務器消息(可選) |
use test;服務器返回的包，使用tcpdump進行監聽，輸出十六進制日志如下：
|
1
2
3
4
5
|
22:04:29.379308 IP root.mysql > 153.3.251.202.33826: Flags [P.], seq 222:233, ack 131, win 14600, length 11 0x0000: 4508 0033 4a0a 4000 4006 867a 43da 9190 E..3J.@.@..zC... 0x0010: 9903 fbca 0cea 8422 7e18 25c1 42e2 5254 ......."~.%.B.RT 0x0020: 5018 3908 3b61 0000 0700 0001 0000 0002 P.9.;a.......... 0x0030: 0000 00 |
包的總長度是11，減去int<3>+int<1>4字節=7字節，對應的十六進制就是07；int<3>十六進制為0700 00表示包內容長度；int<1>十六進制為01表示sequence_id；00表示包頭；00表示受影響行數；00表示最后插入的索引ID；0200表示服務器狀態；
2.5.2ERR_Packet
表示發生了錯誤，具體格式如下：
|
1
2
3
4
5
|
int<1>：0xFF ERR包頭int<2>：錯誤碼string[1]：Sql狀態標識 注：MySQL 4.1 及之后的版本才有string[5]：Sql狀態 注：MySQL 4.1 及之后的版本才有string<EOF>：錯誤消息 |
2.5.3EOF_Packet
以標記查詢執行結果的結束：
|
1
2
3
|
int<1>：EOF值（0xFE）int<2>：告警計數 注：MySQL 4.1 及之后的版本才有int<2>：狀態標志位 注：MySQL 4.1 及之后的版本才有 |
2.5.4Result Set
當客戶端發送查詢請求后，在沒有錯誤的情況下，服務器會返回結果集（Result Set）給客戶端，一共有5個部分：
|
1
2
3
4
5
|
Result Set Header 返回數據的列數量Field 返回數據的列信息（多個）EOF 列結束Row Data 行數據（多個）EOF 數據結束 |
2.5.4.1Result Set Header
|
1
2
|
Length-Encoded Integer Field結構的數量Length-Encoded Integer 額外信息 |
2.5.4.2Field
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
LengthEncodedString 目錄名稱LengthEncodedString 數據庫名稱LengthEncodedString 數據表名稱LengthEncodedString 數據表原始名稱LengthEncodedString 列（字段）名稱LengthEncodedString 列（字段）原始名稱int<1> 填充值int<2> 字符編碼int<4> 列（字段）長度int<1> 列（字段）類型int<2> 列（字段）標志int<1> 整型值精度int<2> 填充值（0x00）LengthEncodedString 默認值 |
2.5.4.3EOF
參考2.5.3EOF_Packet
2.5.4.4Row Data
|
1
2
|
LengthEncodedString 字段值...... 多個字段值 |
實例分析，表信息如下：
|
1
2
3
4
5
6
|
CREATE TABLE `btest` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 |
select * from btest;服務器返回的數據如下：
|
1
2
3
4
5
6
7
|
mysql> select * from btest;+----+------+---------+| id | age | name |+----+------+---------+| 1 | 10 | zhaohui || 2 | 11 | zhaohui |+----+------+---------+ |
服務器返回的包，使用tcpdump進行監聽，輸出十六進制日志如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
01:54:21.522660 IP root.mysql > 153.3.251.202.58587: Flags [P.], seq 1:196, ack 24, win 115, length 195 0x0000: 4508 00eb 8839 4000 4006 4793 43da 9190 E....9@.@.G.C... 0x0010: 9903 fbca 0cea e4db 9dd8 0216 eda6 f730 ...............0 0x0020: 5018 0073 ca34 0000 0100 0001 0328 0000 P..s.4.......(.. 0x0030: 0203 6465 6604 7465 7374 0562 7465 7374 ..def.test.btest 0x0040: 0562 7465 7374 0269 6402 6964 0c3f 0014 .btest.id.id.?.. 0x0050: 0000 0008 0342 0000 002a 0000 0303 6465 .....B...*....de 0x0060: 6604 7465 7374 0562 7465 7374 0562 7465 f.test.btest.bte 0x0070: 7374 0361 6765 0361 6765 0c3f 000b 0000 st.age.age.?.... 0x0080: 0003 0000 0000 002c 0000 0403 6465 6604 .......,....def. 0x0090: 7465 7374 0562 7465 7374 0562 7465 7374 test.btest.btest 0x00a0: 046e 616d 6504 6e61 6d65 0c21 00fd 0200 .name.name.!.... 0x00b0: 00fd 0000 0000 0005 0000 05fe 0000 2200 ..............". 0x00c0: 0d00 0006 0131 0231 3007 7a68 616f 6875 .....1.10.zhaohu 0x00d0: 690d 0000 0701 3202 3131 077a 6861 6f68 i.....2.11.zhaoh 0x00e0: 7569 0500 0008 fe00 0022 00 ui.......". |
0328 0000 02對應的是Result Set Header，03表示3個字段；03 6465 66對應的是目錄名稱的默認值def，03表示后面的字節數為3；04 7465 7374
對應的是數據庫名稱test；0562 7465 7374對應的是數據表名稱btest；0562 7465 7374對應的是數據表原始名稱btest；0269 64對應字段名稱id；02 6964對應列（字段）原始名稱id；0c3f 00對應的是填充值和字符編碼；14 0000 00對應的十進制是20表示列（字段）長度；08 0342 00分別表示列（字段）類型，標識，整型值精度；00002個字節為填充值；00為默認值表示空的；
后續的age和name字段同上，不在重復；
0131類型LengthEncodedString對應的字符1就是id的值；0231 30類型LengthEncodedString對應的字符10就是age的值；07 7a68 616f 6875 69類型LengthEncodedString對應的字符zhaohui就是name的值；
以上就是我們給大家整理的關于MYSQL通訊協議相關的全部內容，大家在學習的時候如果還有任何不明白的地方可以留言給我們，感謝你對服務器之家的支持。
原文鏈接：https://my.oschina.net/OutOfMemory/blog/1595684
