MySQL_TCPDUMP.pdf
5墨值下载
How to Configure MySQL TCPDUMP
1.Percona-Tookit (pt-query-digest will be used to analyze network packages)
2.I wrote a bash shell script to analyze all network packages.
#cat /usr/local/dbadmin/monitor/tcpdump.sh
#!/bin/sh
#****************************************************************#
# ScriptName: test1.sh
# Create Date: 2014-11-03 15:13
# Modify Date: 2014-11-03 15:13
#***************************************************************#
source="/usr/bin/mysql -h 10.128.6.21 -uxxxxxx -pxxxxxxxx -P 3310";
host=`cat /etc/sysconfig/network-scripts/ifcfg-bond0 |grep "IPADDR"|awk -F"=" '{print
$2}'`;
hostid=`$source -e "select id from dbinfo.hosts where ip='$host'"|sed -n -e '2p'`;
for port in `ls -ltr /etc/my33*cnf | awk -F/etc {'print $2'} | awk -F"/my" {'print $2'} | awk
-F".cnf" {'print $1'}`;
do
vip=`$source -e "select vip from dbinfo.rip2vip where hostid=$hostid and port=$port"|sed
-n -e '2p'`
/usr/sbin/tcpdump -s 65535 -x -nn -q -tttt -i any -c 10000 port $port | pt-query-digest --type
tcpdump --watch-server "$vip:$port" --run-time 20s --user=dbmon -P 3310
--password=xxxxx --review h=10.128.6.228,D=dbmon,t=tcp_dump_review --history
h=10.128.6.228,D=dbmon,t=tcp_dump_review_history --no-report --limit=100%
--filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{dbport}=$port and
\$event->{hostid}=$hostid and \$event->{Bytes} = length(\$event->{arg}) and
\$event->{hostname}=\"$HOSTNAME\""
rm -rf /tmp/pt-query-digest-errors.*
done
We use DBMON database to store all data.Two tables in DBMON database:
tcp_dump_review
tcp_dump_review_history
CREATE TABLE `tcp_dump_review` (
`checksum` BIGINT (20) UNSIGNED NOT NULL,
`fingerprint` TEXT NOT NULL,
`sample` TEXT NOT NULL,
`first_seen` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_seen` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`reviewed_by` VARCHAR (20) DEFAULT NULL,
`reviewed_on` DATETIME DEFAULT NULL,
`comments` TEXT,
PRIMARY KEY (`checksum`, `first_seen`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 /*!50100 PARTITION BY RANGE
( UNIX_TIMESTAMP(first_seen))
(PARTITION p201401 VALUES LESS THAN (1391184000) ENGINE = InnoDB,
PARTITION p201402 VALUES LESS THAN (1393603200) ENGINE = InnoDB,
PARTITION p201403 VALUES LESS THAN (1396281600) ENGINE = InnoDB,
PARTITION p201404 VALUES LESS THAN (1398873600) ENGINE = InnoDB,
PARTITION p201405 VALUES LESS THAN (1401552000) ENGINE = InnoDB,
PARTITION p201406 VALUES LESS THAN (1404144000) ENGINE = InnoDB,
PARTITION p201407 VALUES LESS THAN (1406822400) ENGINE = InnoDB,
PARTITION p201408 VALUES LESS THAN (1409500800) ENGINE = InnoDB,
PARTITION p201409 VALUES LESS THAN (1412092800) ENGINE = InnoDB,
PARTITION p201410 VALUES LESS THAN (1414771200) ENGINE = InnoDB,
PARTITION p201411 VALUES LESS THAN (1417363200) ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN (1420041600) ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN (1422720000) ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN (1425139200) ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN (1433088000) ENGINE = InnoDB,
PARTITION p201506 VALUES LESS THAN (1435680000) ENGINE = InnoDB,
PARTITION p201507 VALUES LESS THAN (1438358400) ENGINE = InnoDB,
PARTITION p201508 VALUES LESS THAN (1441036800) ENGINE = InnoDB,
PARTITION p201509 VALUES LESS THAN (1443628800) ENGINE = InnoDB,
PARTITION p201510 VALUES LESS THAN (1446307200) ENGINE = InnoDB,
PARTITION p201511 VALUES LESS THAN (1448899200) ENGINE = InnoDB,
PARTITION p201512 VALUES LESS THAN (1451577600) ENGINE = InnoDB,
PARTITION p201601 VALUES LESS THAN (1454256000) ENGINE = InnoDB,
PARTITION p201602 VALUES LESS THAN (1456761600) ENGINE = InnoDB,
PARTITION p201603 VALUES LESS THAN (1459440000) ENGINE = InnoDB,
PARTITION p201604 VALUES LESS THAN (1462032000) ENGINE = InnoDB,
PARTITION p201605 VALUES LESS THAN (1464710400) ENGINE = InnoDB,
PARTITION p201606 VALUES LESS THAN (1467302400) ENGINE = InnoDB,
PARTITION p201607 VALUES LESS THAN (1469980800) ENGINE = InnoDB,
PARTITION p201608 VALUES LESS THAN (1472659200) ENGINE = InnoDB,
of 11
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论