嘻嘻哈哈的部落格(blog)

在mysql的官方网站上找到几个用tcpdump来抓在mysql server上跑的sql语句
# — (1.1) To capture all traffic on the interface eth0, run:
time tcpdump -i eth0 -s 1500 -w 20060427-db-traffic-01.dmp

# — (1.2) To capture traffic on the interface eth0 coming from a specific IP address, run:
time tcpdump -i eth0 -s 1500 src host 192.168.2.10 -w 20060427-db-traffic-01.dmp

# — Press Ctrl+C — do not leave tcpdump running infinitely on high traffic interfaces

# — (2) To process the results, run:
strings 20060427-db-traffic-01.dmp | grep -i ’select’ | awk ‘{printf(”%s %s %s %s\n”, $1,$2,$3, $4);}’| sort| uniq -c | awk ‘{printf(”%06ld %s %s %s %s\n”, $1,$2,$3,$4,$5);}’|sort

还有一句诗：
tcpdump -l -i eth0 -w - src or dst port 3306 | strings

Tags: Tags: Linux, Mysql, sql, tcpdump 作者 哈哈
No Comments »

mysql里，从4.0开始支持UNION语句
用来把不同的结果集合成一个
今天需要写个SQL语句
用UNION会非常简单，但是数据库是3.23的
于是就想3.x的mysql怎样替代union呢
于是去网上搜，还真找到了
看Writing UNION statements in MySQL 3.x
这里，有点拿不准的就是表_dummy
直接用会发现说_dummy这个表找不到的错误
最后手工建了个表_dummy
还添加了2条记录：“0”、“1”
然后就搞定了

SELECT DISTINCT
IFNULL(d1.t1.ip, d2.t2.tsip) AS ip,
IFNULL(d1.t1.port, d2.t2.tsport) AS port,
IFNULL(d1.t1.cguid, d2.t2.channelid) AS cguid
FROM _dummy AS d
LEFT JOIN  d1.t1
ON (d.num = 0
AND d1.t1.ip is not null
AND d1.t1.port > 0
AND d1.t1.tport > 0)
LEFT JOIN d2.t2
ON (d.num = 1
AND d2.t2.tsport > 0
AND d2.t2.tsip != ”
AND d2.t2.tsip is not null
AND d2.t2.channelid != ”
AND d2.t2.channelid is not null)
WHERE d.num < 2
ORDER BY ip, port

Tags: Tags: 3.x, 4.x, join, Mysql, select, sql, union 作者 哈哈
No Comments »