What OS is the database server itself running? If it's Linux, you could add a logging rule to the local firewall configuration with minimal impact on the server. Something along the lines of:
iptables -A INPUT -m state --state NEW -p tcp --dport <yourdbport> -j LOG --log-prefix "[database] "
You can add -m limit --limit 4/s
to limit this rule to logging only 4 times/second (etc.), which if the connection rate is high will prevent it from hogging all the IO. This will log lines to syslog that look like:
[database] IN=eth0 OUT= MAC=00:16:3c:21:7c:f1:00:d0:00:6f:8c:00:08:00 SRC=a.b.c.d
DST=w.x.y.z LEN=48 TOS=0x00 PREC=0x00 TTL=114 ID=16448 DF PROTO=TCP SPT=59871
DPT=5900 WINDOW=8192 RES=0x00 SYN URGP=0
The information you want is the SRC=
field.
If you set up a separate Linux (or FreeBSD) system to receive traffic from a mirror port, you can use tcpdump
to record the ip addresses. Something like:
tcpdump -w record_of_connections -i eth0 \
dst port <yourdbport> and 'tcp[tcpflags] & tcp-syn != 0'
This will match all packets to port yourdbport that have the SYN flag set (e.g., they are a new connection attempt). After running this for a file, you can extract IP addresses like this:
tcpdump -r record_of_connections -n
Which will produce lines like this:
15:07:40.808035 IP 10.243.16.190.53967 > 10.243.18.22.http: Flags [S],
seq 1235802021, win 5840, options [mss 1380,sackOK,TS val 3739192250
ecr 0,nop,wscale 7], length 0
Where the fields are:
<time> <protocol> <src_addr>.<src_port> > <dest_addr>.<dest_port>: ...
Which means you can get just the source ip address like this:
tcpdump -r record_of_connections -n |
awk '{print $3}' |
sed 's/\.[^.]*$//' |
sort -u
The sort -u
gives you a unique list of addresses.