mysqlbinlog分析

zhangshengdong

使用和不使用--base64-output=decode-rows的區(qū)別：

[root@mysql-n1 binlog]# mysqlbinlog--base64-output=decode-rows ./mysql
-bin.000003 >base_rows.log
[root@mysql-n1 binlog]# mysqlbinlog./mysql-bin.000003 >nobase_rows.log

用對(duì)比工具分析兩個(gè)log的結(jié)果：



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

因此：

[root@mysql-n1 binlog]# mysqlbinlog --base64-output=decode-rows -v ./mysql-bin.000003

可以注釋掉row-format的binlog，出現(xiàn)可識(shí)別的sql（不過也是注釋的）

zhangshengdong

再繼續(xù)通過分析binlog

[root@mysql-n1 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）都可以統(tǒng)計(jì)出來。

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

zhangshengdong

學(xué)習(xí)awk指令：

awk例子：
[root@mysql-n1 binlog]# vi test
wo zhang sheng dong

[root@mysql-n1 binlog]# cat ./test |awk -F ' ' '{print $1,$2,$3}'
wo zhang sheng

這里awk的意思是：以空格為分界符，輸出前三個(gè)域。

zhangshengdong

學(xué)習(xí)sed指令：基本用法

[root@mysql-n1 binlog]# vi test2
###zhang
###liu
###hu

[root@mysql-n1 binlog]# cat test2 |sed 's/^##*//g'
zhang
liu
hu

sed指令有著替換的功能：類似的例子：s/pig/cow/g (意思：把pig變成cow)

zhangshengdong

基于awk和sed命令的使用，我在繼續(xù)分析mysql日志，可以得到：

[root@mysql-n1 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"|  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

使用tr '[A-Z]' '[a-z]' (把所有的大寫字母變成小寫)

[root@mysql-n1 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"|  awk -F ' ' ' { print $1,$2,$3,$4}' |  sed 's/^##*//g'|tr '[A-Z
]' '[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

學(xué)習(xí)sed命令，基礎(chǔ)第二階：

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/(.*$//; （把(.*----.*為撿破爛模式，通吃所有的字符串.）

例子：
[root@mysql-n1 binlog]# vi test3
`zhangshengdong(..???\\zkdkjsdji%##

zhangshengdong

[root@mysql-n1 binlog]# cat ./test3 | sed -e "s/\t//g;s/\`//g;s/(.*$//;"
zhangshengdong

zhangshengdong

然后，再繼續(xù)分析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 '[A-Z]' '[a-z]' \
|  awk -F ' ' ' { if($1=="update") {$3=""} print $1,$2,$3}' \
|  sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/[a-zA-Z0-9\_\-]*\.//;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

sort命令：

sort -nr

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

例子：
[root@mysql-n1 binlog]# 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
[root@mysql-n1 binlog]# 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
[root@mysql-n1 binlog]# cat finally.log |sort |uniq -c
      1 delete from user
      6 insert into user
      1 update user
[root@mysql-n1 binlog]# cat finally.log |sort |uniq -c |sort -nr
      6 insert into user
      1 update user
      1 delete from user