前言
在某些特定的问题排查场景下，或者需要复现问题，我们可能需要开启general log 来查看 MySQL 实时运行的 SQL 语句，以此来缩小问题的范围。general log 会记录所有的SQL语句，无论语句是否正确执行或者是由于语法错误而终止执行。同时开启 general log 在实例负载比较大的情况下了能会对服务器性能产生影响，这里推荐一个好用的工具，它基于 MySQL 协议的抓包工具，实时抓取 MySQL Server 端的请求，并格式化输出。
工具安装
安装必要的依赖
yum install gcc gcc-c++ cmake libpcap-devel glib2-devel libnet-devel -y
安装命令
git clone https://github.com/Qihoo360/mysql-sniffer.git
cd mysql-sniffer
(base) [root@ip-10-0-0-22 mysql-sniffer]# ll
total 20
-rw-r--r-- 1 root root 272 Jan 23 08:58 CMakeLists.txt
drwxr-xr-x 2 root root 20 Jan 23 08:58 include
drwxr-xr-x 2 root root 126 Jan 23 08:58 lib
drwxr-xr-x 4 root root 321 Jan 23 08:58 libnids-1.24
-rw-r--r-- 1 root root 1066 Jan 23 08:58 LICENSE
drwxr-xr-x 4 root root 100 Jan 23 09:01 proj
-rw-r--r-- 1 root root 4298 Jan 23 08:58 README_CN.md
-rw-r--r-- 1 root root 2611 Jan 23 08:58 README.md
drwxr-xr-x 2 root root 242 Jan 23 09:01 src
(base) [root@ip-10-0-0-22 mysql-sniffer]# cd src/
(base) [root@ip-10-0-0-22 src]# ls
CMakeLists.txt mysql-dissector.c session.cpp sniff-config.cpp sniff-log.cpp util.c
main.c mysql-dissector.h session.h sniff-config.h sniff-log.h util.h
修改 CMakeLists.txt
ADD_EXECUTABLE(mysql-sniffer ${SRC_LIST})
TARGET_LINK_LIBRARIES(mysql-sniffer optimized
libnidstcpreasm.a
libpthread.so.0 #添加这一行
libnet.a
libpcap.a
libglib-2.0.a
libgthread-2.0.a
librt.so)
TARGET_LINK_LIBRARIES(mysql-sniffer debug
libnidstcpreasm-dbg.a
libpthread.so.0 #添加这一行
libnet.a
libpcap.a
libglib-2.0.a
libgthread-2.0.a
librt.so)
然后执行下面的命令：
mkdir proj
cd proj
cmake ../
make
如何使用
查看帮助
(base) [root@ip-10-0-0-22 bin]# pwd
/root/mysql-sniffer/proj/bin
(base) [root@ip-10-0-0-22 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
实时抓取某端口信息并打印到屏幕
(base) [root@ip-10-0-0-22 bin]# ./mysql-sniffer -i eth0 -p 3306
查看 MySQL 中真实执行的SQL 语句
mysql> shw databases; # 即使错误的语句也会被记录
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shw databases' at line 1
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| dr |
| geek |
| mydumper |
| mysql |
| performance_schema |
| rudonx |
+-----------------------+
7 rows in set (0.04 sec)
mysql> use rudonx
Reading table information for completion of table
……
Database changed
mysql> show tables;
+------------------+
| Tables_in_rudonx |
+------------------+
| gk_37 |
| rudonx |
| t1 |
| t11 |
| test |
+------------------+
5 rows in set (0.04 sec)
查看 sniffer 输出
2022-01-23 17:50:36 rudonx 10.0.0.22 NULL 34ms 1 select @@version_comment limit 1
2022-01-23 17:50:47 rudonx 10.0.0.22 NULL 34ms 0 shw databases
2022-01-23 17:50:51 rudonx 10.0.0.22 NULL 35ms 12 show databases
2022-01-23 17:50:55 rudonx 10.0.0.22 NULL 35ms 1 SELECT DATABASE()
2022-01-23 17:50:55 rudonx 10.0.0.22 rudonx 34ms 0 use rudonx
2022-01-23 17:50:55 rudonx 10.0.0.22 rudonx 35ms 12 show databases
2022-01-23 17:50:55 rudonx 10.0.0.22 rudonx 35ms 5 show tables
2022-01-23 17:51:03 rudonx 10.0.0.22 rudonx 34ms 5 show tables
注 ：输出格式为：时间，访问用户，来源 IP，访问 Database，命令耗时，返回数据行数，执行语句。
抓取某端口信息并打印到文件
运行命令如下：
(base) [root@ip-10-0-0-22 bin]# ./mysql-sniffer -i eth0 -p 3306 -l /tmp
查看文件输出：
(base) [root@ip-10-0-0-22 tmp]# tail -f 3306.log
2022-01-23 18:00:06 rudonx 10.0.0.22 NULL 31ms 1 select @@version_comment limit 1
2022-01-23 18:00:18 rudonx 10.0.0.22 NULL 30ms 1 select 1
2022-01-23 18:00:30 rudonx 10.0.0.22 NULL 38ms 12 show databases
2022-01-23 18:00:48 rudonx 10.0.0.22 NULL 31ms 1 select * from rudonx.rudonx
2022-01-23 18:01:13 rudonx 10.0.0.22 NULL 31ms 61 show tables
注：-l 指定日志输出路径，日志文件将以 port.log 命名
实时抓取多个端口信息并打印到文件
示例命令如下：
(base) [root@ip-10-0-0-22 bin]# ./mysql-sniffer -i eth0 -p 3306,3307,3310 -l /tmp
注：：-l 指定日志输出路径，-p 指定需要抓取的端口列表逗号分割。日志文件将以各自 port.log 命名。
使用注意事项
- 先运行./mysql-sniffer 命令，然后在连接MySQL，才可以抓取的到相关信息。
- 使用该工具有一定的丢包几率。
如果您有其他问题，欢迎您联系火山引擎技术支持服务
