mysql binlog分析脚本

#!/bin/bash

#########################################################

# 只支持mysql5.1以上版本，innodb引擎，mixed binlog格式。#

v_dir=/usr/local/mysql/bin/

usage() {

     echo "for example: binlog_analyse.sh mysql-bin.000001"

     exit 0

}

${v_dir}mysqlbinlog --no-defaults --base64-output=decode-rows -v -v $1 | awk '{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr > /tmp/binlog_analyse.txt

echo "Total: "

echo ""

echo "---------------------------------------------------"

cat /tmp/binlog_analyse.txt

grep -i 'insert' /tmp/binlog_analyse.txt

grep -i 'update' /tmp/binlog_analyse.txt

grep -i 'delete' /tmp/binlog_analyse.txt

if [ "$#" -ne "1" ];then

    usage

fi

#####################################################################

##结果

:<<MULTILINECOMMENT

[root@P1415-BAK-01 logs]# binlog_analyse.sh mysql-bin.027240

Total: 

---------------------------------------------------

UPDATE    IICUPDB.UP_Dynamic                        84046

UPDATE    IICUPDB.UP_ServiceEx                    52520

UPDATE    IICUPDB.UP_User                             52048

UPDATE    IICUPDB.UP_Quota                            28731

UPDATE    IICUPDB.PS_OrderHistory             27759

UPDATE    IICUPDB.PS_BOSSOperation            24755

UPDATE    IICUPDB.UP_SmsCount                     24672

UPDATE    IICUPDB.UP_UserCustomConfig     8380

INSERT    IICUPDB.UP_Buddy_Log                    5708

UPDATE    IICUPDB.UP_Buddy                            4681

INSERT    IICUPDB.UP_OfflineMsg                 4308

INSERT    IICUPDB.UP_OfflineMsg_Log         4308

INSERT    IICUPDB.UP_Buddy                            3474

DELETE    IICUPDB.UP_OfflineMsg                 3441

DELETE    IICUPDB.UP_OfflineMsg_Log         3395

INSERT    IICUPDB.PS_BOSSOperation            2936

INSERT    IICUPDB.UP_BuddyQuota                 2749

INSERT    IICUPDB.UP_AddBuddyEvent            2651

UPDATE    IICUPDB.UP_Password                     1773

DELETE    IICUPDB.UP_Buddy                            1321

UPDATE    IICUPDB.UP_AddBuddyEvent            1173

UPDATE    IICUPDB.UP_BuddyQuota                 1000

DELETE    IICUPDB.UP_AddBuddyEvent            840

INSERT    IICUPDB.UP_UserBirthConfig        506

INSERT    IICUPDB.PS_OrderHistory             457

INSERT    IICUPDB.UP_ChatFriend                 378

DELETE    IICUPDB.UP_OfflineNotify            344

UPDATE    IICUPDB.UP_UserBirthConfig        318

INSERT    IICUPDB.UP_User                             297

INSERT    IICUPDB.UP_BuddyList                    225

DELETE    IICUPDB.UP_ChatFriend                 216

INSERT    IICUPDB.UP_UserDefinedConfig    200

UPDATE    IICUPDB.UP_UserDefinedConfig    143

INSERT    IICUPDB.UP_Quota                            138

INSERT    IICUPDB.UP_SmsCount                     126

INSERT    IICUPDB.UP_Dynamic                        113

INSERT    IICUPDB.UP_Password                     113

INSERT    IICUPDB.UP_OfflineMsgPersist    106

INSERT    IICUPDB.UP_BlackList                    104

DELETE    IICUPDB.UP_OfflineMsgPersist    89

INSERT    IICUPDB.UP_ServiceEx                    84

UPDATE    IICUPDB.UP_UserEx                         70

INSERT    IICUPDB.UP_BuddyBirthConfig     69

INSERT    IICUPDB.UP_OfflineNotify            64

DELETE    IICUPDB.UP_BlackList                    60

INSERT    IICUPDB.UP_ConfigEx                     52

INSERT    IICUPDB.UP_UserEx                         41

INSERT    IICUPDB.UP_UserCustomConfig     34

DELETE    IICUPDB.UP_BuddyList                    22

UPDATE    IICUPDB.UP_BuddyList                    16

UPDATE    IICUPDB.UP_ConfigEx                     15

DELETE    IICUPDB.UP_OfflinePermission    8

INSERT    IICUPDB.UP_BuddyQuotaByUri        8

INSERT    IICUPDB.UP_OfflinePermission    6

UPDATE    IICUPDB.UP_BuddyQuotaByUri        3

############################################################

MULTILINECOMMENT

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/755974