IT博客汇 | MySQL Sniffer：Install and Used

MySQL Sniffer：Install and Used

Adamhuan发表于 2017-03-03 08:01:06

今天，在找MySQL的分析与挖掘工具的时候，发现了如题所示的工具：MySQL Sniffer。

该工具是奇虎360，内部团队HULK中的大牛们开发的，基于MySQL协议，实时抓取MySQL的数据包，来获得相关的诸如访问DB，命令耗时，用户，… 等信息。

该工具的官方代码托管页面为：
https://github.com/Qihoo360/mysql-sniffer

这里，我参考官方的帮助文档，在自己的环境里玩玩：
https://github.com/Qihoo360/mysql-sniffer/blob/master/README_CN.md

一、安装依赖包：
yum install -y glib2-devel libpcap-devel libnet-devel

二、上传编译好的二进制文件到服务器：

[root@mha1 c++]# pwd
/script/c++
[root@mha1 c++]# ls
mysql-sniffer
[root@mha1 c++]# 
[root@mha1 c++]# ls -ltr
total 196
-rwxr-xr-x 1 root root 199583 Mar  3 14:42 mysql-sniffer
[root@mha1 c++]#

三、查看帮助信息，并使用：

帮助信息：

[root@mha1 c++]# ./mysql-sniffer -h
Usage ./mysql-sniffer [-d] -i eth0 -p 3306,3307,3308 -l /var/log/mysql-sniffer/ -e stderr
         [-d] -i eth0 -r 3000-4000
         -d daemon mode.
         -s how often to split the log file(minute, eg. 1440). if less than 0, split log everyday
         -i interface. Default to eth0
         -p port, default to 3306. Multiple ports should be splited by ','. eg. 3306,3307
            this option has no effect when -f is set.
         -r port range, Don't use -r and -p at the same time
         -l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout.
         -e error log FILENAME or 'stderr'. if set to /dev/null, runtime error will not be recorded
         -f filename. use pcap file instead capturing the network interface
         -w white list. dont capture the port. Multiple ports should be splited by ','.
         -t truncation length. truncate long query if it's longer than specified length. Less than 0 means no truncation
         -n keeping tcp stream count, if not set, default is 65536. if active tcp count is larger than the specified count, mysql-sniffer will remove the oldest one
[root@mha1 c++]#

当前正在运行的MySQL：

[root@mha1 c++]# ps -ef | grep mysql
root       584   512  0 14:23 pts/0    00:00:00 mysql -u root -px xxxxxx
root      1632     1  0 Feb13 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     1918  1632  0 Feb13 ?        00:23:51 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      2569   543  0 15:52 pts/1    00:00:00 grep mysql
[root@mha1 c++]# 
[root@mha1 c++]# netstat -tupln | grep mysql
tcp        0      0 :::3306                     :::*                        LISTEN      1918/mysqld         
[root@mha1 c++]# 

[root@mha1 c++]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:50:56:84:12:D2  
          inet addr:10.158.1.94  Bcast:10.158.1.255  Mask:255.255.254.0
          inet6 addr: fe80::250:56ff:fe84:12d2/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:27273916 errors:0 dropped:0 overruns:0 frame:0
          TX packets:26064451 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:5754000753 (5.3 GiB)  TX bytes:3518558716 (3.2 GiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:1783 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1783 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:397277 (387.9 KiB)  TX bytes:397277 (387.9 KiB)

[root@mha1 c++]#

开始监控：

[root@mha1 c++]# ./mysql-sniffer -i eth0 -p 3306
2017-03-03 15:58:03      root    192.168.253.39  NULL            50ms             0      SET NAMES utf8
2017-03-03 15:58:09      root    192.168.253.39  NULL             8ms             0      SET NAMES utf8
2017-03-03 15:58:09      root    192.168.253.39  NULL           213ms             2      SHOW VARIABLES LIKE 'lower_case_%'
2017-03-03 15:58:09      root    192.168.253.39  NULL            12ms             1      SHOW VARIABLES LIKE 'profiling'
2017-03-03 15:58:09      root    192.168.253.39  NULL           164ms             4      SHOW DATABASES


2017-03-03 15:59:09      root    192.168.253.39  NULL            10ms             0      SET NAMES utf8


2017-03-03 15:59:23      root    192.168.253.39  NULL           586ms            31      SHOW FULL TABLES FROM `mysql` WHERE Table_type != 'VIEW'
2017-03-03 15:59:24      root    192.168.253.39  NULL             4ms             0      SHOW FULL TABLES FROM `mysql` WHERE Table_type = 'VIEW'
2017-03-03 15:59:24      root    192.168.253.39  NULL           418ms             0      SHOW PROCEDURE STATUS WHERE Db='mysql'
2017-03-03 15:59:24      root    192.168.253.39  NULL            56ms             0      SHOW FUNCTION STATUS WHERE Db='mysql'


2017-03-03 15:59:39      root    192.168.253.39  NULL            11ms             0      SET NAMES utf8
2017-03-03 15:59:39      root    192.168.253.39  NULL            17ms             0      SET PROFILING=1;

发现：
1. 通过Navicat这样的工具访问MySQL数据库的操作可以被追踪到，但是如果【mysql -u -h】的方式的连接的，似乎找不到？

————————————————————————
Done。