We recently had capacity problems where too large a percentage of read queries where going to our master MySQL server instead of the read-only slaves.What I needed to do was capture the queries on the very busy server without consuming more resources to the disk.I started at first using tcpdump to capture the inbound queries.
sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/'
This worked really well but I needed to run this for an hour or so to get a decent sample size and couldn’t use the local disks on because they were already at capacity. What I ended up doing was piping the output from tcpdump through ssh.
ssh $TO_HOST cat - ">" $OUT_FILE
The whole process looks like this.
sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/' | ssh log11 cat - ">" db11m_query_log.02AUG2011_3:30-4:00
This allowed my to capture ~100MB file without consuming more IO resources on the local disk.
dodizzle 