使用tcpdump抓取MySQL执行语句并记录

由于MySQL自带的Binlog日志只记录操作语句，而General Log也是多记录个操作用户，并无记录执行的机器IP地址，故写了此脚本来进行抓包。

从抓取的数据包中截取想要得到的信息并进行日志记录:

#/bin/bash
# Author:Chris            __by 2017/08/29
# ==============Description==============
# This is for Capture MySQL Data Traffic & Log it.
# =======================================

# Defined Color
Red='\033[31m\033[1m'	# \e[1;31m
Green='\033[32m\033[1m'	# \e[1;32m
Blue='\033[94m'		# \e[0;94m
Null='\033[0m'		# \e[0m

# <----------------------------Configure Start--------------------------->
# <---TCPDUMP Settings--->
Interface="ens160"
My_Port=3306
Protocol="mysql"

# <---Filter Settings--->
Filter="CREATE|DROP|INSERT|DELETE|UPDATE|ALTER|SHOW"

# <---Log Settings--->
Date=$(date +%Y-%m-%d)
Log_Dir="/data/mysql/logs"
mkdir -p ${Log_Dir}
Log_File="${Log_Dir}/tcpdump.${Date}.log"
# <----------------------------Configure  End---------------------------->

pid=$(ps -ef | awk -v Port="${My_Port}" '/^tcpdump/ && $0 ~ $Port {print $2}')
until [ -z "${pid}" ]
do
	echo -e "${Red}MySQL Capture is Running(${pid})...${Null}"
	if [ "$1" == "1" ];then
		echo -e "Killing The Process!"
		kill ${pid}
		break
	fi
	exit 1
done

echo -e "Capture MySQL Data at port ${Red}${My_Port}${Null} on ${Green}${Interface}${Null}"
/usr/sbin/tcpdump -i ${Interface} -qs0 -ttttnn -A dst port ${My_Port} and ${Protocol} and "tcp[13] & 8 != 0" and greater 75 | sed 's/\r//g' | awk 'BEGIN{ORS=""} /^[0-9]{4,}/ {if (FNR!=1) a="\n"; time=a$1" "$2" "$4" "$5" "$6"\n"} /^[^0-9]/ {gsub(/^\s*/,"");sql=$0} {if (time !~ /^[\s| ]*$/) {print time;time=""} else if (sql ~ /\S/ && sql !~ /^\s*.{2}\..*\.$/) {print sql;sql=""}}' | sed -e 's/^\s*.*\.\{4,\}\([a-zA-Z\/]\)/\1/g' -e 's/\/\*.*\*\///g' | awk 'BEGIN{ORS=""} /^[0-9]{4,}/{t="\n"$0} /^[^0-9]/{s=$0} {print t,s;t="";s=""}' | sed -e '/.*:\s*$/d' | awk -v recode="${Filter}" 'BEGIN{IGNORECASE=1} {if ($6 ~ recode) print $0}' >> ${Log_File} &
if [ "$?" -eq "0" ];then
	echo -e "${Green}Capture Complate,You can check log in file ${Log_File}${Null}"
else
	echo -e "${Red}Capture Faild,Please Check Configure!${Null}"
fi

• 指定抓取的接口和MySQL监听的端口和协议，然后就可以抓取所有发到服务器的所有MySQL协议的数据包
• -s 0抓取所有大小的数据包；-q快速输出模式，打印短协议信息
• -tttt指定时间显示为 YYYY:mm:dd HH:MM:SS 格式；-nn显示端口号而不是名称
• -A 以ASCII编码打印每个数据包
• tcp[13] & 8 != 0指定TCP包flag中PSH位不为0，即表示包内含有数据；tcp[13]即为tcpflag
• greater 75表示只打印超过75字节的数据包；因为有的包是空数据
• 之后的一系列sed和awk使用都是为了去除数据包中的多余杂项，最后提取只留下时间，源、目的地址，和匹配执行的SQL语句;具体诸位可以自行一层一层去掉查验

最后效果如下:

将脚本加入计划任务，每天0点自动重启，生成新的dump日志文件:

[root@fm2 Scripts]# crontab -l
0 0 * * * /root/Scripts/tcpdump_mysql.sh 1