tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

トップ > MySQL > MySQLのパケットを読んでいく

MySQLのパケットを読んでいく

MySQL

この記事はMySQL Casual Advent Calendar 2018 10日目の記事です。

最近golangでMySQLのclient/serverプロトコルのでシリアライザを作っていて、この記事ではclient/serverプロトコルを解説しつつ、そのデシリアライザの紹介をしようと思っていました。 ですが、思っている以上に進捗出せなかったので、今回はMySQLのpacketを読む面白さと参考にすると良い資料を紹介しようと思います。

なんでパケットを読むのか

MySQLの通信プロトコルを知ることでMySQLの運用が楽になったり、知らないと困るようなことはまずありません。 とはいえ、プロトコルを解釈できればclient/server間の通信を中継したり、キャプチャすることで分析することができます！！
僕がプロトコルを理解しようと思ったきっかけは、プロダクション環境で実行されているクエリを直接見たいと思ったことでした。

例えばProxySQLではclient/server間のパケットを中継することでルーティングによる負荷分散やクエリの書き換え, クエリキャッシュなどMySQLプロキシとして様々な機能を提供しています。

ProxySQLのような大規模なものでなくてもclient/server間の通信を覗けると、MySQL本体で記録しているpeformance_schemaやinformation_schemaの情報以外の視点からの情報を取って監視したりすることができます。

また、通信プロトコルまでドキュメントになっているミドルウェアはそう多くないと思っていて(当社比)、プロトコルに関してもここまで整備がされているのはMySQLの良さの一つだと思っています。

パケットの読み方

簡単にMySQLのパケットをみる方法を2つ紹介します。 ここではTCPで接続していて、且つSSLでの接続を無効にしている状態を前提としています。(ソケット通信でもある程度おなじなのかな？)
5.7以降ではデフォルトでSSL接続が有効になっているので、--ssl-mode=DISABLEDを指定するとSSL接続を無効化した状態でMySQLに接続することができます。

MySQLのパケットと言っても結局はネットワークパケットなので、コマンドラインから見るのであればtcpdump、GUIで見たければWiresharkでみると簡単です。

tcpdump

ネットワークパケットを見るときにはおなじみのtcpdumpでCUIからすぐにpacketを見られます。

tcpdump -X -i lo port 13306

dockerで立てたMySQLに対して接続しているので、ここではloインタフェースの13306番portのパケットを見ています。 serverからの応答パケットが大きいとやや見づらいかもしれませんが、tcpdumpを実行しているウィンドウとは別のウィンドウでクエリを投げると下のようにクエリが入っていることがわかります。 ここでは昨日の記事で紹介されたsakilaデータベースのactorテーブルから1件のレコードをselectしてきています。

07:24:19.250609 IP localhost.36972 > localhost.13306: Flags [P.], seq 674:706, ack 11072, win 651, options [nop,nop,TS val 3838999213 ecr 3838984172], length 32
    0x0000:  4500 0054 9c01 4000 4006 a0a0 7f00 0001  E..T..@.@.......
    0x0010:  7f00 0001 906c 33fa cb7c eb07 e952 76e8  .....l3..|...Rv.
    0x0020:  8018 028b fe48 0000 0101 080a e4d2 7aad  .....H........z.
    0x0030:  e4d2 3fec 1c00 0000 0373 656c 6563 7420  ..?......select.
    0x0040:  2a20 6672 6f6d 2061 6374 6f72 206c 696d  *.from.actor.lim
  0x0050:  6974 2031                                it.1

tcpdumpはそのまま実行するとネットワークパケットを全て取ろうとして、tcpdumpのバッファを使い切るとバッファからあふれるパケットはロストしてしまいます。 そうすると歯抜けになった出力を見て混乱することになるので、できるだけportやインタフェースを指定して必要なパケットだけに絞り込むことと、名前解決を止めることで負荷を減らしてやる必要があります。 僕はだいたい以下のようなオプションでパケットを絞り込んでいます。

tcpdump -n -nn -s 0 -i eh1 -B 4096 -c 1000 port 3306 -w packet_dump.pcap

-wオプションでファイルに書き出すことで、標準出力するよりも高速にパケットを取得でき、あとでファイルから読み直すこともできます。 さらに通信量の多い場合はファイルの書き込み場所をtmpfsのディレクトリにしたり、バッファサイズを可能な限り大きくする必要があります。

Wireshark

WiresharkにはデフォルトでMySQLのパケットをパースするオプションが付いています。 分析(Analyze) > ...としてデコード(Decode as...) でMySQLのポートを指定して、一番右のセレクタでMySQLを指定します。

f:id:tom__bo:20181210074629p:plain:w500

するとそのポートに対するパケットのTCPレイヤのペイロードに含まれるデータはMySQLプロトコルのパケットとして表示してくれるので、この部分を見ることでMySQLがどんな情報を送っているのか名前付きで見ることができます。

f:id:tom__bo:20181210074642p:plain

デコーダとしてMySQLを指定した後は不要なパケットを省くために以下のようにフィルタを設定すると見やすくなります。

tcp.port == 3306 and !(tcp.flags.ack && tcp.len <= 1)

ここでは解説しませんが、5byte目のコマンドタイプが決まっているのであれば更に次のように5byte目の値を指定することでフィルタすることもできます。

tcp.port == 3306 and !(tcp.flags.ack && tcp.len <= 1) and tcp.payload[4] == 0a

プロトコルに関する資料

なにはともあれ公式のマニュアルを見るのが一番確かです。

とは言っても最新だと英語だったりどこから始めたらよいかわかりずらいという場合は、いろいろな方がプロトコルに関する解説やブログを書いてくださっているのでそれを参考にすると良いと思います。

僕はほとんどマニュアルを読みながらwiresharkでパケット眺めていたので、ブログ等はあまり探せていませんが、他にも良い記事があれば教えてください。

あとはMySQL server, MySQL clientのソースコードや各言語のクライアント実装をよめば良いんじゃないのと思われるかもしれませんが、僕には難しかったです。。。

ソースコードから見るのであればwiresharkのMySQLライブラリを参考にすると良さそうです。 https://github.com/boundary/wireshark/blob/master/epan/dissectors/packet-mysql.c

wiresharkの実装はデコードすることがメインで、完全ではありませんが、character-setの定義(ドキュメントになさそう)があるなど参考にできる部分が多いです。

というわけでMySQLのパケットを読んで見たくなったでしょうか？？w

明日11日目はmeijikさんによる [勝手に対応]MySQL8.0の分析関数の2 です。