Mysql通信プロトコルの詳細な説明

1.Mysql接続方法

MySQL 通信プロトコルを理解するには、まず MySQL サーバーへの接続に使用される接続方法を知る必要があります。MySQL の主な接続方法には、Unix ソケット、メモリ共有、名前付きパイプ、TCP/IP ソケットなどがあります。

1.1Unixソケット

Linux および Unix 環境では、Unix ソケットを使用して MySQL サーバーに接続できます。Unix ソケットは実際にはネットワークプロトコルではなく、クライアントと MySQL サーバーが同じコンピューター上にある場合にのみ使用できます。使用方法も非常に簡単です。

root@root ~]# mysql -uroot -proot
mysql> 'socket' のような変数を表示します。
+---------------+---------------------------+
| 変数名 | 値 |
+---------------+---------------------------+
| ソケット | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
セット内の 1 行 (0.00 秒)

上記のコマンドは、Unix ソケットファイルの場所を照会します。

1.2 名前付きパイプとメモリ共有

ウィンドウシステムでは、クライアントと MySQL サーバーが同じコンピュータ上に存在し、名前付きパイプと共有メモリを使用できます。

名前付きパイプが有効: –shared-memory=on/off;

共有メモリが有効: –enable-named-pipe=on/off;

1.3 TCP/IP ソケット

この方法は、どのシステムでも使用でき、最も一般的に使用される接続方法でもあります。この記事で紹介する通信プロトコルも、この接続方法に基づいています。以下は、tcpdump による TCP/IP ソケットの予備的な理解です。

サーバー側:

[root@root ~]# tcpdump ポート 3306
tcpdump: 詳細な出力は抑制されています。完全なプロトコル デコードには -v または -vv を使用してください。
venet0 でリッスン、リンク タイプ LINUX_SLL (Linux で調理済み)、キャプチャ サイズ 65535 バイト

サーバーはポート 3306 (つまり、MySQL ポート) をリッスンします。

クライアント：

C:\Users\hui.zhao>mysql -h64.xxx.xxx.xxx -uroot -proot
mysql>終了
さよなら

クライアントはサーバーに接続し、その後切断します。このとき、サーバーの監視結果ログを確認します。

[root@root ~]# tcpdump ポート 3306
tcpdump: 詳細な出力は抑制されています。完全なプロトコル デコードには -v または -vv を使用してください。
venet0 でリッスン、リンク タイプ LINUX_SLL (Linux で調理済み)、キャプチャ サイズ 65535 バイト
02:06:25.442472 IP 153.3.251.202.33876 > root.mysql: フラグ [S]、シーケンス 27289263、win 8192、オプション [mss 1460、nop、wscale 8、nop、nop、sackOK]、長さ 0
02:06:25.442763 IP root.mysql > 153.3.251.202.33876: フラグ [S.]、シーケンス 2014324548、ack 27289264、win 14600、オプション [mss 1460、nop、nop、sackOK、nop、wscale 7]、長さ 0
02:06:25.617449 IP 153.3.251.202.33876 > root.mysql: フラグ [.]、ack 1、win 256、長さ 0
02:06:29.812946 IP root.mysql > 153.3.251.202.33876: フラグ [P.]、シーケンス 1:57、ack 1、win 115、長さ 56
02:06:29.992362 IP 153.3.251.202.33876 > root.mysql: フラグ [P.]、シーケンス 1:63、ack 57、win 256、長さ 62
02:06:29.992411 IP root.mysql > 153.3.251.202.33876: フラグ [.]、ack 63、win 115、長さ 0
02:06:29.992474 IP root.mysql > 153.3.251.202.33876: フラグ [P.]、シーケンス 57:68、ack 63、win 115、長さ 11
02:06:30.166992 IP 153.3.251.202.33876 > root.mysql: フラグ [P.]、シーケンス 63:100、ack 68、win 256、長さ 37
02:06:30.167109 IP root.mysql > 153.3.251.202.33876: フラグ [P.]、シーケンス 68:158、ack 100、win 115、長さ 90
02:06:30.536298 IP 153.3.251.202.33876 > root.mysql: フラグ [.]、ack 158、win 256、長さ 0
02:06:34.568611 IP 153.3.251.202.33876 > root.mysql: フラグ [P.]、シーケンス 100:105、ack 158、win 256、長さ 5
02:06:34.568620 IP 153.3.251.202.33876 > root.mysql: フラグ [F.]、シーケンス 105、ack 158、win 256、長さ 0
02:06:34.568751 IP root.mysql > 153.3.251.202.33876: フラグ [F.]、シーケンス 158、ack 106、win 115、長さ 0
02:06:34.743815 IP 153.3.251.202.33876 > root.mysql: フラグ [.]、ack 159、win 256、長さ 0

[S]:SYN は接続を開始し、[P]:PUSH はデータを送信し、[F]:FIN は接続を閉じ、[.]: は確認パケットを示します。

プロセスは大まかに次のようになります: TCP 接続を確立し、クライアントと Mysql サーバーが接続通信を確立し、TCP 接続を閉じます。

[S][S.][.] これらのパケットはTCP接続の3ウェイハンドシェイクを表します。

[F.][F.][.] これらのパケットはTCP接続の4つのハンドシェイクを表します。

中央の複数の [P.][.] は、実際には接続を確立するためにクライアントと MySQL サーバーによって送信されるプロトコルデータパケットです。

2. プロトコル分析

MySQL プロトコルは、MySQL クライアントと MySQL サーバーが通信するときに使用されます。具体的なシナリオとしては、クライアントとサーバーの接続、MySQL プロキシ、マスター/スレーブバックアップなどがあります。

MySQL クライアントとサーバー間のやり取りは、主に接続フェーズ (または認証フェーズ) とコマンドフェーズの 2 つのフェーズに分かれています。

tcpdump の出力と組み合わせると、クライアントとサーバー間の通信の全体的なプロセスは次のようになります。

1. TCP 接続の 3 ウェイ ハンドシェイクを確立します。
2. MySQL サーバーとの接続を確立します。これは接続フェーズ (接続フェーズまたは認証フェーズ) です。
 s->c: 初期ハンドシェイクパケットを送信する
 c->s: 認証応答を送信する
 s->c: サーバーは認証結果パケット 3 を送信します。認証に合格すると、サーバーはクライアントからのコマンド パケットを受け入れ、対応する応答パケットを送信します。これがコマンド フェーズです。
4. 切断して終了コマンドを要求します。
5. TCP を 4 回振って切断します。

2.1 基本型

プロトコル全体の基本型: 整数と文字列。

2.1.1 整数型

固定長整数型と長さエンコード整数型の 2 種類があります。

固定長整数型:

固定長の符号なし整数は、一連のバイトにその値を格納します。特定の固定バイト数は、1、2、3、4、6、8 のいずれかです。

長さエンコードされた整数型:

保存に必要なバイト数は、以下に示すように値のサイズによって異なります。

1バイト: 0<=X<251;

2バイト: 251<=X<2^16;

3 バイト: 2^16<=X<2^24;

9 バイト: 2^24<=X<2^64;

2.1.2 文字列型

FixedLengthString、NullTerminatedString、VariableLengthString、LengthEncodedString、RestOfPacketString の 5 つのタイプがあります。

FixedLengthString: 固定長文字列には既知のハードコードされた長さがあります。たとえば、常に 5 バイトの長さである ERR_Packet の SQL ステータスです。
NullTerminatedString: Null (バイト 00) で終わる文字列。

VariableLengthString: 長さが別のフィールドによって決定されるか、実行時に計算される可変文字列 (int+value など)。ここで、int は長さ、value は指定された長さのバイト数です。

LengthEncodedString: VariableLengthString で指定された int+value 形式で、文字列の長さを表す長さエンコードされた整数がプレフィックスとして付いた文字列。

RestOfPacketString: 文字列がパケットの最後のコンポーネントである場合、その長さはパケット全体の長さから現在の位置を減算することによって計算できます。

2.2 基本データパケット

MySQL クライアントまたはサーバーがデータを送信する場合、次のようになります。

各データパケットのサイズは 2^24 バイト (16 MB) を超えることはできません。

各データブロックの前にデータパケットヘッダーを追加します。

パッケージの形式は次のとおりです。

int<3>: 特定のパケットコンテンツの長さ。int<3>+int<1>=4バイトを除く。
int<1>: シーケンスIDはパケットごとに増加し、ラップアラウンドする場合があります。 0 から始まり、コマンド フェーズで新しいコマンドが開始されると 0 にリセットされます。
string<var>: 特定のデータ内容、int<3> で指定された長さ。

例えば：

01 00 00 は int に対応し、特定のデータコンテンツの長さが 1 バイトであることを示します。

00 は、sequence_id を表す int に対応します。

01 は文字列の前に指定された 1 バイトのデータに対応します。

2.3 メッセージタイプ

これは、mysql5.1.73 (mysql4.1 以降のバージョン) に基づいて、ログイン認証メッセージ、クライアント要求メッセージ、サーバー返信メッセージの 3 つのカテゴリに分類できます。

2.3.1 ログイン認証メッセージ

主に対話型認証フェーズでは、上記からわかるように、ハンドシェイクパケット、認証応答、結果パケットの 3 つのフェーズに分かれています。ここでは、主に最初の 2 つのパケットを分析します。

2.3.1.1 ハンドシェイクパケット

1バイト: プロトコルバージョン番号 NullTerminatedString: データベースバージョン情報 4バイト: MySQLサーバーに接続して開始されたスレッドID
8 バイト: チャレンジ乱数、データベース認証に使用 1 バイト: 値を埋める (0x00)
2 バイト: クライアントとの通信モードのネゴシエーションに使用 1 バイト: データベース コード 2 バイト: サーバー ステータス 13 バイト: 予約バイト 12 バイト: チャレンジ乱数、データベース認証に使用 1 バイト: パディング値 (0x00)

次のように tcpdump を使用して 16 進ログを監視および出力します。

[root@root ~]# tcpdump ポート 3306 -X
......
03:20:34.299521 IP root.mysql > 153.3.251.202.44658: フラグ [P.]、シーケンス 1:57、ack 1、win 115、長さ 56
 0x0000: 4508 0060 09f1 4000 4006 c666 43da 9190 E..`..@[email protected]...
 0x0010: 9903 fbca 0cea ae72 bb4e 25ba 21e7 27e3 .......rN%.!.'.
 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 バイトで、対応する 16 進数は 34 です。int<3> の 16 進数は 3400 00 で、パケットコンテンツの長さを表します。int<1> の 16 進数は 00 で、sequence_id を表します。後続のコンテンツはパケット本体のコンテンツで、合計 52 バイトです。0a は 10 進数の 10 に対応し、すべてのプロトコルバージョン番号は 10 です。後続のデータベースバージョン情報は 00 で終わり、35 2e31 2e37 33 は現在使用されているデータベースバージョンである 5.1.73 に対応します。4024 0000 は 10 進数の 6436 に対応します。08 はデータベースコードを表します。0200 はサーバーステータスを表します。

2.3.1.2 認証パケット

4 バイト: クライアントとの通信モードのネゴシエーションに使用されます 4 バイト: リクエスト メッセージを送信するときにクライアントがサポートする最大メッセージ長の値 1 バイト: 通信プロセスで使用される文字エンコードを識別します 23 バイト: 予約バイト NullTerminatedString: ユーザー名 LengthEncodedString: 暗号化されたパスワード NullTerminatedString: データベース名 (オプション)

次のように tcpdump を使用して 16 進ログを監視および出力します。

03:20:34.587416 IP 153.3.251.202.44658 > root.mysql: フラグ [P.]、シーケンス 1:63、ack 57、win 256、長さ 62
 0x0000: 4500 0066 29ee 4000 7006 766b 9903 fbca E..f)[email protected]....
 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 ............ルート
 0x0050: 0014 ff58 4bd2 7946 91a0 a233 f2c1 28af ...XK.yF...3..(.
 0x0060: d578 0762 c2e8 .xb.

パケットの合計長さは 62 から int<3>+int<1>4 バイトを引いた 58 バイトで、対応する 16 進数は 3a です。int<3> の 16 進数は 3a00 00 で、パケットコンテンツの長さを表します。int<1> の 16 進数は 01 で、sequence_id を表します。726f 6f74 00 はユーザー名で、デコード後は root になります。以下は暗号化されたパスワードタイプです。

LengthEncodedString、14 の 10 進値は 20 であり、次の 20 バイトは暗号化されたパスワードです。オプションのデータベース名は存在しません。

2.4 クライアント要求メッセージ

int<1>: データベースの切り替えなど、実行するコマンド string<var>: コマンドに対応するパラメータ

コマンドリスト:

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 準備されたステートメントの実行結果を取得します

たとえば、test を使用し、tcpdump を使用して監視すると、16 進数のログ出力は次のようになります。

22:04:29.379165 IP 153.3.251.202.33826 > root.mysql: フラグ [P.]、シーケンス 122:131、ack 222、win 64019、長さ 9
 0x0000: 4500 0031 3f19 4000 7006 6175 9903 fbca [email protected]....
 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 バイトで、対応する 16 進数は 05 です。int<3> の 16 進数は 0500 00 で、パケットコンテンツの長さを表します。int<1> の 16 進数は 00 で、sequence_id を表します。02 は COM_INIT_DB に対応し、その後にテストのバイナリコードが続きます。

2.5 サーバー応答メッセージ

クライアントからサーバーに送信されるほとんどのコマンドに対して、サーバーは OK_Packet、ERR_Packet、EOF_Packet、Result Set のいずれかの応答パケットを返します。

2.5.1OK_パケット

コマンドが正常に完了したことを示します。具体的な形式は次のとおりです。

int<1>: 0x00 または 0xFEOK ヘッダー int<lenenc>: 影響を受ける行数 int<lenenc>: 最後に挿入されたインデックス ID
int<2>: サーバーステータス int<2>: アラームカウント 注: MySQL 4.1以降のバージョンでのみ使用可能 string<lenenc>: サーバーメッセージ (オプション)

use test; サーバーはパケットを返し、tcpdump を使用してそれを監視します。16 進数のログ出力は次のようになります。

22:04:29.379308 IP root.mysql > 153.3.251.202.33826: フラグ [P.]、シーケンス 222:233、ack 131、win 14600、長さ 11
 0x0000: 4508 0033 4a0a 4000 4006 867a 43da 9190 E..3J.@[email protected]...
 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 バイトで、対応する 16 進数は 07 です。int<3> は 16 進数で 0700 です。00 はパケットコンテンツの長さを示します。int<1> は 16 進数で 01 で、sequence_id を示します。00 はパケットヘッダーを示します。00 は影響を受ける行数を示します。00 は最後に挿入されたインデックス ID を示します。0200 はサーバーステータスを示します。

2.5.2ERR_パケット

エラーが発生したことを示します。具体的な形式は次のとおりです。

int<1>: 0xFF ERR ヘッダー int<2>: エラー コード string[1]: SQL ステータス識別子 注: MySQL 4.1 以降のバージョンでのみ使用可能 string[5]: SQL ステータス 注: MySQL 4.1 以降のバージョンでのみ使用可能 string<EOF>: エラー メッセージ

2.5.3EOF_パケット

クエリ実行結果の終了をマークするには:

int<1>: EOF値 (0xFE)
int<2>: アラーム数 注: MySQL 4.1以降のバージョンでのみ使用可能 int<2>: ステータスフラグ 注: MySQL 4.1以降のバージョンでのみ使用可能

2.5.4結果セット

クライアントがクエリ要求を送信すると、エラーがなければ、サーバーは結果セットをクライアントに返します。全部で 5 つの部分があります。

結果セットヘッダー 返されるデータの列数 フィールド 返されるデータの列情報（複数）
EOF 列終了行データ 行データ（複数）
EOF データの終了

2.5.4.1結果セットヘッダー

長さエンコードされた整数 フィールド構造の数 長さエンコードされた整数 追加情報

2.5.4.2フィールド

LengthEncodedString ディレクトリ名LengthEncodedString データベース名LengthEncodedString テーブル名LengthEncodedString テーブル オリジナル名LengthEncodedString 列 (フィールド) 名LengthEncodedString 列 (フィールド) オリジナル名int<1> フィル値int<2> 文字エンコードint<4> 列 (フィールド) 長さint<1> 列 (フィールド) タイプint<2> 列 (フィールド) flagint<1> 整数値 precisionint<2> フィル値 (0x00)
LengthEncodedStringのデフォルト値

2.5.4.3EOF

2.5.3EOF_Packetを参照

2.5.4.4行データ

LengthEncodedString フィールド値... 複数のフィールド値

分析例、表の情報は次のとおりです。

テーブル `btest` を作成します (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `age` int(11) デフォルト NULL,
 `name` varchar(255) デフォルト NULL,
 主キー (`id`)
) エンジン=InnoDB AUTO_INCREMENT=1000 デフォルト文字セット=utf8

select * from btest; サーバーから返されるデータは次のとおりです。

mysql> btest から * を選択します。
+----+------+---------+
| ID | 年齢 | 名前 |
+----+------+---------+
| 1 | 10 | 趙慧 |
| 2 | 11 | 趙慧 |
+----+------+---------+

サーバーから返されるパケットは tcpdump を使用して監視され、16 進数のログ出力は次のようになります。

01:54:21.522660 IP root.mysql > 153.3.251.202.58587: フラグ [P.]、シーケンス 1:196、ack 24、win 115、長さ 195
 0x0000: 4508 00eb 8839 4000 4006 4793 43da 9190 E....9@[email protected]..
 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 ステージ.エイジ.?...
 0x0080: 0003 0000 0000 002c 0000 0403 6465 6604 .......,....def.
 0x0090: 7465 7374 0562 7465 7374 0562 7465 7374 テスト.bテスト.bテスト
 0x00a0: 046e 616d 6504 6e61 6d65 0c21 00fd 0200 .名前.名前.!....
 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は結果セットヘッダーに対応し、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 は 10 進数の 20 に対応し、列 (フィールド) の長さを示します。08 0342 00 は、それぞれ列 (フィールド) タイプ、識別子、および整数値の精度を示します。00002 バイトは塗りつぶし値です。00 はデフォルト値で、空を示します。

後続の年齢と名前のフィールドは上記と同じであり、繰り返されません。

0131 型 LengthEncodedString に対応する文字 1 は id の値です。0231 30 型 LengthEncodedString に対応する文字 10 は age の値です。07 7a68 616f 6875 69 型 LengthEncodedString に対応する文字 zhaohui は name の値です。

上記は、MYSQL 通信プロトコルに関連するすべてのコンテンツをまとめたものです。学習中に質問がある場合は、メッセージを残してください。123WORDPRESS.COM をご愛顧いただきありがとうございます。

<<: ブラウザをJavaScriptで対話させる方法

>>: LinuxにPython 3.6をインストールして落とし穴を避ける