読者です 読者をやめる 読者になる 読者になる

MySQLのプロトコルを学ぶ

今年ももうそろそろ終わりですね。
沢山パケットキャプチャした方も、そうでない方もいらっしゃると思いますが、今年残り僅かの日々も大事にパケットキャプチャしていければな思います。


じつはWiresharkではMySQLプロトコルに対応しているのは知っていたのですが、試したことなかったのでせっかくの機会ですので試してみることにしました。特にレプリケーション周りは個人的にも気になっていました(試してないですがGTID使うパターンも気になる)


個人的には実際にパケットキャプチャをしながら資料を読むのが、結構理解につながるなと思っています。
今回は以下のページなども参考にしました。
MySQL Internals Manual
MySQLのプロトコル解説
MySQLユーザーのためのMySQLプロトコル入門


環境

mysql client - mysql server

まず最初にmysql clientからサーバにクエリを発行するパターンをキャプチャしました。
(参考 http://dev.mysql.com/doc/internals/en/connection-phase.html)


以上の流れに沿って詳しく見て行きたいと思います

  • Server Greeting(Initial Handshake)
  • Login Request(Handshake Response)
  • Response Ok
  • Request Query (COM_QUERY)
  • Response (COM_QUERY Response)
  • Request quit(COM_QUIT)
Server Greeting(Initial Handshake)

http://dev.mysql.com/doc/internals/en/plain-handshake.html
mysqlのドキュメント中で、Initial Handshakeと呼ばれているメッセージになります。
ClientがServerに接続すると、サーバ側から送信されます。
SSL/TLSを利用することも出来るようですが、今回は平文です。

MySQL Protocol
  Packet Length: 91
  Packet Number: 0
  Server Greeting
    Protocol: 10
    Version: 5.5.40-0ubuntu0.14.04.1
    Thread ID: 3
    Salt: `QU8T!F}
    Server Capabilities: 0xf7ff
    Server Language: latin1 COLLATE latin1_swedish_ci (8)
    Server Status: 0x0002
    Unused: 0f801500000000000000000000
    Salt: eb=I4i7r\Use
    Authentication Plugin: mysql_native_password


Wiresharkでパラメータを見ることで、プロトコルのバージョン、サーバの情報、各種フラグ、ソルトなどが送られていることが確認できます。
Server Capabilitiesには例えば以下の様なフラグがあります

  • Long Password
  • Speaks 4.1 protocol
  • Switch to SSL after handshake
Login Request(Handshake Response)

http://dev.mysql.com/doc/internals/en/connection-phase-packets.html
上記Initial Handshakeメッセージに対する応答としてクライアントからサーバに送信されます。

MySQL Protocol
  Packet Length: 86
  Packet Number: 1
  Login Request
    Client Capabilities: 0xa20d
    Extended Client Capabilities: 0x000f
    MAX Packet: 16777216
    Charset: utf8 COLLATE utf8_general_ci (33)
    Username: root
    Password: a3b39d74b2ccf10d765f4296d540029f83a4d8fc
    Schema: mysql
    Client Auth Plugin: mysql_native_password


ユーザ名及びパスワード(Initial Handshakeで受け取ったソルトを用いて計算されたハッシュ値)をサーバに送信します。
また、Client Capabilitiesとしてクライアント側の各種フラグも合わせて送信します。
今回は接続時にスキーマ名も指定しています。

Response Ok

http://dev.mysql.com/doc/internals/en/packet-OK_Packet.html
ユーザのユーザ名とパスワードを受け取ったサーバは、検証し正しいユーザであればResponse Okのメッセージを返します。

MySQL Protocol
  Packet Length: 7
  Packet Number: 2
  Affected Rows: 0
  Server Status: 0x0002
  Warnings: 0


ただしエラーが発生した場合は、Response Errorとして以下の様なメッセージが返答されることになります。
Error Codeとは別にError messageも返却されています。

MySQL Protocol
  Packet Length: 76
  Packet Number: 2
  Error Code: 1045
  SQL state: 28000
  Error message: Access denied for user 'root'@'192.168.0.161' (using password: YES)
Request Query (COM_QUERY)

http://dev.mysql.com/doc/internals/en/com-query.html
認証が終わると、クライアントからコマンドとしてqueryを送信できます。
至ってシンプルなフォーマットです

MySQL Protocol
  Packet Length: 17
  Packet Number: 0
  Request Command Query
    Command: Query (3)
    Statement: SELECT version()


mysql clientでは最初に以下の様なクエリを発行するようです。

Statement: select @@version_comment limit 1
Response (COM_QUERY Response)

http://dev.mysql.com/doc/internals/en/com-query-response.html
レスポンスは長く、データベースやテーブル・カラムの定義情報が最初にレスポンスされ、行が続いて(今回は5.5.40-0ubuntu0.14.04.1)結果として返ってきます(今回は一行)。


この部分についての構造は如何分かりやすそうです。
http://dev.mysql.com/doc/internals/en/com-query-response.html#packet-ProtocolText::Resultset

MySQL Protocol
  Packet Length: 1
  Packet Number: 1
  Number of fields: 1
MySQL Protocol
  Packet Length: 31
  Packet Number: 2
  Catalog: def
  Database: 
  Table: 
  Original table: 
  Name: version()
  Original name: 
  Charset number: utf8 COLLATE utf8_general_ci (33)
  Length: 69
  Type: FIELD_TYPE_VAR_STRING (253)
  Flags: 0x0001
  Decimals: 31
MySQL Protocol
  Packet Length: 5
  Packet Number: 3
  EOF marker: 254
  Warnings: 0
  Server Status: 0x0002
MySQL Protocol
  Packet Length: 24
  Packet Number: 4
  text: 5.5.40-0ubuntu0.14.04.1
MySQL Protocol
  Packet Length: 5
  Packet Number: 5
  EOF marker: 254
  Warnings: 0
  Server Status: 0x0002
Request quit(COM_QUIT)

http://dev.mysql.com/doc/internals/en/com-quit.html
接続を終了する旨サーバに通知します。

MySQL Protocol
  Packet Length: 1
  Packet Number: 0
  Request Command Quit
    Command: Quit (1)

おわりに

パケットキャプチャしてみて、細かいパラメータの意味を理解するところまでは至ってませんが、ざっとした流れは追うことが出来ました。
結構楽しかったです。

つらつら書いてたら長くなってしまったのでレプリケーション周りの所は後日にしようと思います。