mysqlsnifferでMySQLのプロトコルをキャプチャリング

MySQLで接続関係の問題、具体的に言うとAborted_clientsやAborted_connectionsというステータス変数が増え続けてしまうような現象に遭遇することがある。MySQLは、そういったネットワーク関係の問題をあまりたくさんログに記録しないようになっている。DoS攻撃などでログが溢れかえってしまわないようにするためだ。そんなとき、試して頂きたいのがmysqlsniffer。

mysqlsnifferとは。

http://hackmysql.com/mysqlsniffer

mysqlsnifferは、tcpdumpのようにネットワークインターフェイスからパケットをキャプチャするプログラムだが、tcpdumpとはMySQLのプロトコルを理解するという点で異なる。というかMySQLのプロトコル専門のキャプチャプログラムだ。OSのroot権限さえあれば、MySQLサーバーに対してどのようなリクエストが発行され、また、どのような応答が返っているかということを、如実に知ることが出来る。MySQLにおいてネットワーク系の問題が起きた時の強い味方なのだ！（もちろんSSLで暗号化していれば通信は見えない。）

インストール

というわけで、まずはインストール方法を紹介しよう。

mysqlsnifferは、libpcapというライブラリを用いて作られている。なのでmysqlsnifferを使う場合は、libpcapを事前にインストールしておこう。Ubuntuの場合のインストール方法は次の通り。

shell> sudo apt-get install libpcap-dev

また、mysqlsnifferはソースコードのみで配布されているため、コンパイルに必要なツール一式が必要だ。Ubuntuの場合、build-essentialパッケージも同様にインストールしておこう。

準備が出来たら上記のページからmysqlsniffer.tgzをダウンロードする。展開すると、ファイルがカレントディレクトリ直下に作成されてしまうため、事前に作業用ディレクトリを作成しておくといい。

shell> mkdir mysqlsniffer
shell> tar xf mysqlsniffer.tgz -C mysqlsniffer
shell> cd mysqlsniffer

そしてコンパイル

shell> gcc -m64 -g -O3 -lpcap -o mysqlsniffer *.c

使い方

mysqlsnifferの実行にはroot権限が必要だ。次のように、パケットをキャプチャするインターフェイス名を指定しよう。

shell> sudo ./mysqlsniffer eth0

以下はMySQL Serverにログインしたときのパケットをキャプチャしたものだ。その後、STATUSコマンドでMySQL Serverのステータスを確認している。

mysqlsniffer listening for MySQL on interface eth0 port 3306
server > 192.168.1.15.50190: ID 1 len 7 Waiting for server to finish response... ::DUMP:: 00 01 00 02 00 00 00 ::DUMP::
server > 192.168.1.15.50183: ::FRAGMENT END::
server > 192.168.1.15.50177: ::FRAGMENT END::
server > 192.168.1.15.50184: ::FRAGMENT END::
server > 192.168.1.15.50182: ::FRAGMENT END::
server > 192.168.1.15.50193: ID 0 len 67 Handshake  
192.168.1.15.50193 > server: ID 1 len 60 Handshake (new auth)  
server > 192.168.1.15.50193: ID 2 len 7 OK  
192.168.1.15.50193 > server: ID 0 len 33 COM_QUERY: select @@version_comment limit 1
server > 192.168.1.15.50193: ID 1 len 1 1 Fields
 ID 2 len 39 Field: ..@@version_comment 
 ID 3 len 5 End  
 ID 4 len 9 || (Ubuntu) ||
 ID 5 len 5 End  
192.168.1.15.50193 > server: ID 0 len 14 COM_QUERY: select USER()
server > 192.168.1.15.50193: ID 1 len 1 1 Fields
 ID 2 len 28 Field: ..USER() 
 ID 3 len 5 End  
 ID 4 len 26 || mikiya@open-machine.local ||
 ID 5 len 5 End  
192.168.1.15.50196 > server: ID 0 len 34 COM_QUERY: select DATABASE(), USER() limit 1
server > 192.168.1.15.50196: ID 1 len 1 2 Fields
 ID 2 len 32 Field: ..DATABASE() 
 ID 3 len 28 Field: ..USER() 
 ID 4 len 5 End  
 ID 5 len 27 || NULL | mikiya@open-machine.local ||
 ID 6 len 5 End  
192.168.1.15.50196 > server: ID 0 len 116 COM_QUERY: select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1
server > 192.168.1.15.50196: ID 1 len 1 4 Fields
 ID 2 len 44 Field: ..@@character_set_client 
 ID 3 len 48 Field: ..@@character_set_connection 
 ID 4 len 44 Field: ..@@character_set_server 
 ID 5 len 46 Field: ..@@character_set_database 
 ID 6 len 5 End  
 ID 7 len 24 || utf8 | utf8 | latin1 | latin1 ||
 ID 8 len 5 End  
192.168.1.15.50196 > server: ID 0 len 1 COM_STATISTICS
server > 192.168.1.15.50196: ID 1 len 140 Uptime: 3222  Threads: 1  Questions: 14567  Slow queries: 3828  Opens: 1255  Flush tables: 1  Open tables: 63  Queries per second avg: 4.521

このように、誰がログインして、どのようなクエリを実行し、そしてどのような結果が返っているかということが如実に分かるのである！

使い方のコツ

全てが分かるとは言え、全てのパケットを解析していては日が暮れてしまう。ここは当然、興味のある情報を絞り込むべきである。つまり、grepが大活躍するわけだ。

例えば、クライアントから送られたクエリだけをフィルタするには次のようにコマンドを実行しよう。

shell> sudo ./mysqlsniffer eth0 | grep COM_QUERY

MySQL Serverで発生しているエラーを追いかけたいならこうだ。

shell> sudo ./mysqlsniffer eth0 | grep Error

ログインに失敗すると次のようなログが出力される。

server > 192.168.1.15.50207: ID 0 len 67 Handshake  
192.168.1.15.50207 > server: ID 1 len 60 Handshake (new auth)  
server > 192.168.1.15.50207: ID 2 len 83 Error 1045 (#28000): Access denied for user 'mikiya'@'open-machine.local' (using password: YES)

なので、ログインの失敗（つまりAborted_connectsが増加する）問題を追跡したいなら、さらに次のように結果を絞り込めばいい。

shell> sudo ./mysqlsniffer eth0 | grep Error | grep 1045

ただし、mysqlsnifferにはひとつ問題がある。あまり安定していないのだ。筆者が手元のシステムで試したところ、しょっちゅうバッファオーバーフローでクラッシュしてしまう。面倒なので原因は追跡していないが、恐らくパケットの解析ロジックに誤りがあるのだろう。なので、長期間監視を行いたいときは、次のように自動的に何度でも再起動するようにしておくといいだろう。

shell> while [ 1 ]; do sudo ./mysqlsniffer eth0 | grep Error | grep 1045 > /var/tmp/mysql-login.err; done