MySQLユーザーのためのMySQLプロトコル入門

さいきんMySQLユーザーのためのほげほげ、みたいなのが巷で流行しているようなので暇つぶしがてらに読んでいるMySQLプロトコルについて書いてみようかと思います。

いやまぁ、こういうプロトコルが読めるからといってすごく役立つということは全くないんですが、お酒の席のネタにできたり、高速、簡単、無料で試せるRDS MySQLからRedshiftへのデータ同期に出てくるようなreplicationをいじったツールとかのメンテが容易にできるかもしれなかったり、俺mysqldだぜ、みたいな事ができたり、なんかよくわからないけどちょっとハッピーになれそうですね!

今日は手始めにMySQLとmysql clientがどういう通信をしているのか見ていき、実際にInitial Handshake Packetをparseしてみるところまでをやってみます。

Max OSXでのセットアップ

普段homebrewを使っているのでmysqlとngrepをインストールしてみます。ngrepはお手軽なtcpdumpだと思っていただければOKです。

brew install ngrep
brew install mysql

それではmysqldを起動してngrepを使ってみましょう

mysql.server start
sudo ngrep -x -q -d lo0 '' 'port 3306'

# 別ターミナルでmysqlコマンドを実行する
mysql -u root -h 127.0.0.1 -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20 Homebrew

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Ctrl-C -- exit!
Aborted

するとngrepをしていたターミナルで下記のような結果が得られると思います。

interface: lo0 (127.0.0.0/255.0.0.0)
filter: (ip or ip6) and ( port 3306 )

T 127.0.0.1:3306 ->; 127.0.0.1:49482 [AP]
  4a 00 00 00 0a 35 2e 36    2e 32 30 00 01 00 00 00    J....5.6.20.....
  21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80    !K:~0oax...!....
  15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64    ...........X0&jd
  3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61    :43E@S^.mysql_na
  74 69 76 65 5f 70 61 73    73 77 6f 72 64 00          tive_password.  

T 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]
  a4 00 00 01 85 a6 7f 00    00 00 00 01 08 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 72 6f 6f 74    00 00 6d 79 73 71 6c 5f    ....root..mysql_
  6e 61 74 69 76 65 5f 70    61 73 73 77 6f 72 64 00    native_password.
  67 03 5f 6f 73 07 6f 73    78 31 30 2e 39 0c 5f 63    g._os.osx10.9._c
  6c 69 65 6e 74 5f 6e 61    6d 65 08 6c 69 62 6d 79    lient_name.libmy
  73 71 6c 04 5f 70 69 64    04 36 36 36 37 0f 5f 63    sql._pid.6667._c
  6c 69 65 6e 74 5f 76 65    72 73 69 6f 6e 06 35 2e    lient_version.5.
  36 2e 32 30 09 5f 70 6c    61 74 66 6f 72 6d 06 78    6.20._platform.x
  38 36 5f 36 34 0c 70 72    6f 67 72 61 6d 5f 6e 61    86_64.program_na
  6d 65 05 6d 79 73 71 6c                               me.mysql        

T 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]
  07 00 00 02 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]
  21 00 00 00 03 73 65 6c    65 63 74 20 40 40 76 65    !....select @@ve
  72 73 69 6f 6e 5f 63 6f    6d 6d 65 6e 74 20 6c 69    rsion_comment li
  6d 69 74 20 31                                        mit 1           

T 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]
  01 00 00 01 01 27 00 00    02 03 64 65 66 00 00 00    .....'....def...
  11 40 40 76 65 72 73 69    6f 6e 5f 63 6f 6d 6d 65    .@@version_comme
  6e 74 00 0c 08 00 08 00    00 00 fd 00 00 1f 00 00    nt..............
  05 00 00 03 fe 00 00 02    00 09 00 00 04 08 48 6f    ..............Ho
  6d 65 62 72 65 77 05 00    00 05 fe 00 00 02 00       mebrew......... 

T 127.0.0.1:49482 -> 127.0.0.1:3306 [AP]
  01 00 00 00 01                                        .....           

mysqldがlistenしているportに接続するとServerからInitial Handshake Packetが送られてきます。この情報を元にClientはHandshakeResponse41のPacketを生成して返送し、Server側で認証がとおればTextProtocolのやり取りに進むことが出来ます。

通信メッセージの単位 – MySQL Packet

通信を行う際はMySQL Packetというメッセージの単位でやり取りをすることになります。MySQL Packetは4byteのheader + payloadという構成になっており、dev.mysql.comの資料から引用すると:

size name description
int<3> payload_length Length of the payload. The number of bytes in the packet beyond the initial 4 bytes that make up the packet header.
int<1> sequence_id Sequence ID
string payload [len=payload_length] payload of the packet

4byteしかないのでさっくりparseできそうですね。
それでは先ほどngrepで表示されていたpacketをMySQL Packetとして手でparseしてみましょう

T 127.0.0.1:3306 -> 127.0.0.1:49482 [AP]
  4a 00 00 00 0a 35 2e 36    2e 32 30 00 01 00 00 00    J....5.6.20.....
  21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80    !K:~0oax...!....
  15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64    ...........X0&jd
  3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61    :43E@S^.mysql_na
  74 69 76 65 5f 70 61 73    73 77 6f 72 64 00          tive_password.

MySQL Packetは3byteのpayload length, 1byteのsequence id、payload length – 4がpayloadとなるので

payload_length: 4a 00 00
sequence_id: 00
payload: 0a 35 2e 36    2e 32 30 00 01 00 00 00	5.6.20.....

となるわけですね。それではpayloadを更にparseしてみましょう

Connection Phase Packet

接続した際に最初にServerから送られてくるMySQL PacketがConnection Phase Packetとなります。再びdev.mysql.comの資料から引用します:

1              [0a] protocol version
string[NUL]    server version
4              connection id
string[8]      auth-plugin-data-part-1
1              [00] filler
2              capability flags (lower 2 bytes)
  if more data in the packet:
1              character set
2              status flags
2              capability flags (upper 2 bytes)
  if capabilities & CLIENT_PLUGIN_AUTH {
1              length of auth-plugin-data
  } else {
1              [00]
  }
string[10]     reserved (all [00])
  if capabilities & CLIENT_SECURE_CONNECTION {
string[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
  if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL]    auth-plugin name
  }

Connection Phaseのデータ構造は可変長のデータがあまり出てこないので簡単ですね。ngrepで見ていた時はこんな感じのpayloadだったので

            0a 35 2e 36    2e 32 30 00 01 00 00 00
21 4b 3a 7e 30 6f 61 78    00 ff f7 21 02 00 7f 80
15 00 00 00 00 00 00 00    00 00 00 58 30 26 6a 64
3a 34 33 45 40 53 5e 00    6d 79 73 71 6c 5f 6e 61
74 69 76 65 5f 70 61 73    73 77 6f 72 64 00

手で分割していくとこのようになります。

protocol version: 0a
server version: 35 2e 36    2e 32 30 00
connection id: 01 00 00 00
auth-plugin-data-part-1: 21 4b 3a 7e 30 6f 61 78
filter: 00
capability flags: ff f7
character set: 21
status flag: 02 00
capability flags: 7f 80
auth-plugin-data: 15
reserved: 00 00 00 00 00 00 00    00 00 00
auth-plugin-data-part-2: 58 30 26 6a 64 3a 34 33 45 40 53 5e 00
auth-plugin-name: 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73    73 77 6f 72 64 00

なんとなくそれっぽいデータになりましたね。とはいえ手で分割してても時間かかりますし、面白く無いのでInitial PacketをGoでparseしてみるプログラムを書いてみました。
これを元にnet.DialでつなげてみたりするとInitial Packetだけはparseできたり、無駄に頑張ればオレオレMySQL Clientが作れるので参考に遊んでみてもらえれば、と思います。

http://play.golang.org/p/Gvb4fxZETE

もし間違っていたら突っ込みいただけるとありがたいです。

そのほか

  • homebrewのmysqlはmysql.server stopで止められます

http://dev.mysql.com/doc/internals/en/client-server-protocol.html

  • Character Set

http://dev.mysql.com/doc/internals/en/character-set.html#packet-Protocol::CharacterSet

Author: chobi_e

PHP, Golang, Cあたりがメインだったんですが気づいたらUnityやってました。なんでもやさん