论坛 › MySQL › mysqlbinlog分析

zhangshengdong 发表于 2012-08-10 10:44

mysqlbinlog分析

使用和不使用--base64-output=decode-rows的区别：

# mysqlbinlog--base64-output=decode-rows ./mysql
-bin.000003 >base_rows.log
# mysqlbinlog./mysql-bin.000003 >nobase_rows.log

用对比工具分析两个log的结果：



可以知道row-format的binlog消失了。

因此：

# mysqlbinlog --base64-output=decode-rows -v ./mysql-bin.000003

可以注释掉row-format的binlog，出现可识别的sql（不过也是注释的）

zhangshengdong 发表于 2012-08-10 10:48

再继续通过分析binlog

# mysqlbinlog --base64-output=decode-rows -v ./my
sql-bin.000003|grep -e "^##* UPDATE" -e "^##* INSERT" -e "^##* DELE
TE" -e "^##* REPLACE" -i -e "^update" -e "^insert" -e "^delete" -e "^re
place"
### INSERT INTO zsd.user
### INSERT INTO zsd.user
### INSERT INTO zsd.user
### INSERT INTO zsd.user
### INSERT INTO zsd.user
### INSERT INTO zsd.user

可以把每条的记录（update,insert,delete,replace）都可以统计出来。

其中：
grep指令：
-e, --regexp=PATTERN      use PATTERN as a regular expression
-i, --ignore-case         ignore case distinctions （忽视大小写差别）

zhangshengdong 发表于 2012-08-10 11:12

学习awk指令：

awk例子：
# vi test
wo zhang sheng dong

# cat ./test |awk -F ' ' '{print $1,$2,$3}'
wo zhang sheng

这里awk的意思是：以空格为分界符，输出前三个域。

zhangshengdong 发表于 2012-08-10 11:30

学习sed指令：基本用法

# vi test2
###zhang
###liu
###hu

# cat test2 |sed 's/^##*//g'
zhang
liu
hu

sed指令有着替换的功能：类似的例子：s/pig/cow/g (意思：把pig变成cow)

zhangshengdong 发表于 2012-08-10 11:32

基于awk和sed命令的使用，我在继续分析mysql日志，可以得到：

# mysqlbinlog --base64-output=decode-rows -v ./my
sql-bin.000003|grep -e "^##* UPDATE" -e "^##* INSERT" -e "^##* DELE
TE" -e "^##* REPLACE" -i -e "^update" -e "^insert" -e "^delete" -e "^re
place"|awk -F ' ' ' { print $1,$2,$3,$4}' |sed 's/^##*//g'
INSERT INTO zsd.user
INSERT INTO zsd.user
INSERT INTO zsd.user
INSERT INTO zsd.user
INSERT INTO zsd.user
INSERT INTO zsd.user
UPDATE zsd.user
DELETE FROM zsd.user

替换掉前面所用的'###'

zhangshengdong 发表于 2012-08-10 11:36

使用tr '' '' (把所有的大写字母变成小写)

# mysqlbinlog --base64-output=decode-rows -v ./my
sql-bin.000003|grep -e "^##* UPDATE" -e "^##* INSERT" -e "^##* DELE
TE" -e "^##* REPLACE" -i -e "^update" -e "^insert" -e "^delete" -e "^re
place"|awk -F ' ' ' { print $1,$2,$3,$4}' |sed 's/^##*//g'|tr '[A-Z
]' ''
insert into zsd.user
insert into zsd.user
insert into zsd.user
insert into zsd.user
insert into zsd.user
insert into zsd.user
update zsd.user
delete from zsd.user

zhangshengdong 发表于 2012-08-10 11:59

学习sed命令，基础第二阶：

sed -e "s/\t/ /g;s/\`//g;s/(.*$//;"

其中-e：
-e script, --expression=script
               add the script to the commands to be executed



上面的命令："s/\t/ /g;s/\`//g;s/(.*$//;" 得拆分看：

s/\t/ /g; （把制表符换成空格）
s/\`//g;（把`换成空）
s/(.*$//; （把(.*----.*为捡破烂模式，通吃所有的字符串.）

例子：
# vi test3
`zhangshengdong(..???\\zkdkjsdji%##

zhangshengdong 发表于 2012-08-10 12:00

# cat ./test3 | sed -e "s/\t//g;s/\`//g;s/(.*$//;"
zhangshengdong

zhangshengdong 发表于 2012-08-10 12:05

然后，再继续分析binlog日志：

mysqlbinlog --base64-output=decode-rows -v ./mysql-bin.000003\
|grep -e "^##* UPDATE" -e "^##* INSERT" -e "^##* DELETE" -e "^##* \
REPLACE" -i -e "^update" -e "^insert" -e "^delete" -e "^replace"|\
awk -F ' ' ' { print $1,$2,$3,$4}' |sed 's/^##*//g' | tr '' '' \
|awk -F ' ' ' { if($1=="update") {$3=""} print $1,$2,$3}' \
|sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/*\.//;s/ set .*$//;s/ as .*$//" \

insert into user
insert into user
insert into user
insert into user
insert into user
insert into user
update user
delete from user

（把前面的zsd.都祛除了）

zhangshengdong 发表于 2012-08-10 12:12

sort命令：

sort -nr

-n, --numeric-sort          compare according to string numerical value
-r, --reverse               reverse the result of comparisons

例子：
# cat finally.log
insert into user
insert into user
insert into user
insert into user
insert into user
insert into user
update user
delete from user
# cat finally.log |sort
delete from user
insert into user
insert into user
insert into user
insert into user
insert into user
insert into user
update user
# cat finally.log |sort |uniq -c
      1 delete from user
      6 insert into user
      1 update user
# cat finally.log |sort |uniq -c |sort -nr
      6 insert into user
      1 update user
      1 delete from user

查看完整版本: mysqlbinlog分析