![捕获所有 mysql 查询](https://linux22.com/image/615209/%E6%8D%95%E8%8E%B7%E6%89%80%E6%9C%89%20mysql%20%E6%9F%A5%E8%AF%A2.png)
我想使用 tcpdump 或 ngrep 捕获其中一个 mysql 服务器上的所有查询,但不启用常规查询日志(因为这会导致高 IOP),然后我想通过 netcat 将这些查询传输到在不同机器上运行的 mysql 实例的端口 3306 并在那里运行它们。这是否可行,因为所有使用用户名和密码的身份验证也必须在目标机器上进行。
答案1
Cole 的脚本可能可以完成您想要做的事情,但听起来您确实想手动完成基于语句的复制-- 我建议使用内置复制来完成此操作会更容易并且性能更好。
答案2
找到这个脚本这里这应该能满足您的要求。但是正如所说,使用此方法可能会导致内存/CPU 性能问题。
#!/bin/bash
# Set some defaults
d=$(date +%F-%T | tr :- _)
self=`basename $0`
REMOTEHOST='some.other.hostname' # CHANGEME
LIMIT=50 # limit # of queries in report
SLEEPTIME=1200 # duration to capture tcpdump data in seconds (1200 = 20 min)
TMP='/tmp'
LOCKFILE="$self.lockfile"
TMPfile="$self.temp"
RESfile="$self.result"
# check lock file
if [ -e "$TMP/$LOCKFILE" ]; then
echo "$self: lock file $LOCKFILE already exists, aborting"
exit 1
fi
# set trap to be sure tcpdump doesn't run for ever
# and clean up the temp file too
trap 'rm -f $LOCKFILE; kill $PID; ssh $REMOTEHOST -- "rm -f $TMP/$TMPfile"; exit' INT TERM EXIT
touch $TMP/$LOCKFILE
# run the tcpdump & write to remote file and sleep for a bit
tcpdump -i eth0 -s 65535 -x -n -q -tttt 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' 2>/dev/null \
| ssh $REMOTEHOST -- "cat - > $TMP/$TMPfile" &
PID=$!
sleep $SLEEPTIME
kill $PID
# set trap to be sure both remote files are removed
trap 'ssh $REMOTEHOST -- "rm -f $TMP/$TMPfile $TMP/$RESfile"; rm -f $LOCKFILE $RESfile; exit' INT TERM EXIT
# digest the result, copy to localhost, then email it
ssh $REMOTEHOST -- "mk-query-digest --type tcpdump --limit $LIMIT < $TMP/$TMPfile 2>&1 > $TMP/$RESfile"
scp -q $REMOTEHOST:$TMP/$RESfile $RESfile
# email $RESfile using your preferred transport
# clean up remote and local files.
ssh $REMOTEHOST -- "rm -f $TMP/$TMPfile $TMP/$RESfile"
rm -f $RESfile $LOCKFILE
trap - INT TERM EXIT
exit 0