使用MySQL Sniffer或PacketBeat来实时审计Mysql语句

生产环境中可以使用工具实时审计Mysql流量。

介绍两种方式:

  • MySQL Sniffer
  • PacketBeat

MySQL Sniffer

MySQL Sniffer 是一个基于 MySQL 协议的抓包工具,实时抓取 MySQLServer 端或 Client 端请求,并格式化输出。输出内容包括访问时间、访问用户、来源 IP、访问 Database、命令耗时、返回数据行数、执行语句等。有批量抓取多个端口,后台运行,日志分割等多种使用方式,操作便捷,输出友好。

安装依赖:

yum install glib2-devel libpcap-devel libnet-devel

项目下载地址:

https://github.com/Qihoo360/mysql-sniffer

安装步骤:

cd mysql-sniffer

mkdir proj

cd proj

cmake ../

make

cd bin/

参数如下:

[root@server120 bin]# ./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@server120 bin]# ./mysql-sniffer -i lo -p 3306

2017-08-16 13:56:04        root        127.0.0.1        NULL                0ms                1  select @@version_comment limit 1

2017-08-16 14:01:56        root        127.0.0.1        NULL                0ms                1  SELECT DATABASE()

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms                0  use mysql

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms                5  show databases

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms               23  show tables

2017-08-16 14:02:04        root        127.0.0.1        mysql               0ms                8  select * from user

输出格式为:时间,访问用户,来源 IP,访问 Database,命令耗时,返回数据行数,执行语句。

保存日志可以用filebeat采集:

[root@server120 bin]# ./mysql-sniffer -i eth0 -p 3306 -l /tmp/
[root@server120 tmp]# head -n 5 3306.log 
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             0  SET NAMES utf8
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             2  SHOW VARIABLES LIKE 'lower_case_%'
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             1  SHOW VARIABLES LIKE 'profiling'
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             5  SHOW DATABASES
2017-08-16 14:05:20  root    192.168.190.201     NULL             0ms             0  SET NAMES utf8

-l 指定日志输出路径,日志文件将以 port.log 命名。

需要注意的是:

只能抓取新建的链接,如果是之前创建的链接将获取不到用户名和库名,并有一定几率丢包。

PacketBeat

Packeybeat可以部署在:

  • Mysql服务端
  • 镜像DB服务器上游交换机流量到服务器
yum -y install libpcap
./packetbeat -c packetbeat.yml

packetbeat.yml为配置文件
packetbeat.template.json为mapping文件

测试:

mysql> select host,user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| %         | soc  |
| 127.0.0.1 | root |
| localhost |      |
| localhost | soc  |
+-----------+------+
5 rows in set (0.00 sec)

输出到Elasticsearch内容如下:

  • query:执行的SQL语句
  • num_fields:返回的字段数
  • num_rows:查询结果行数
标签:MySQL 发布于:2019-11-11 18:51:01