#!/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 "---------------------------------------------------"
echo ""
cat /tmp/binlog_analyse.txt
echo ""
echo "---------------------------------------------------"
echo ""
grep -i 'insert' /tmp/binlog_analyse.txt
echo ""
echo "---------------------------------------------------"
echo ""
grep -i 'update' /tmp/binlog_analyse.txt
echo ""
echo "---------------------------------------------------"
echo ""
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

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

INSERT    IICUPDB.UP_Buddy_Log                    5708
INSERT    IICUPDB.UP_OfflineMsg                 4308
INSERT    IICUPDB.UP_OfflineMsg_Log         4308
INSERT    IICUPDB.UP_Buddy                            3474
INSERT    IICUPDB.PS_BOSSOperation            2936
INSERT    IICUPDB.UP_BuddyQuota                 2749
INSERT    IICUPDB.UP_AddBuddyEvent            2651
INSERT    IICUPDB.UP_UserBirthConfig        506
INSERT    IICUPDB.PS_OrderHistory             457
INSERT    IICUPDB.UP_ChatFriend                 378
INSERT    IICUPDB.UP_User                             297
INSERT    IICUPDB.UP_BuddyList                    225
INSERT    IICUPDB.UP_UserDefinedConfig    200
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
INSERT    IICUPDB.UP_ServiceEx                    84
INSERT    IICUPDB.UP_BuddyBirthConfig     69
INSERT    IICUPDB.UP_OfflineNotify            64
INSERT    IICUPDB.UP_ConfigEx                     52
INSERT    IICUPDB.UP_UserEx                         41
INSERT    IICUPDB.UP_UserCustomConfig     34
INSERT    IICUPDB.UP_BuddyQuotaByUri        8
INSERT    IICUPDB.UP_OfflinePermission    6

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

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
UPDATE    IICUPDB.UP_Buddy                            4681
UPDATE    IICUPDB.UP_Password                     1773
UPDATE    IICUPDB.UP_AddBuddyEvent            1173
UPDATE    IICUPDB.UP_BuddyQuota                 1000
UPDATE    IICUPDB.UP_UserBirthConfig        318
UPDATE    IICUPDB.UP_UserDefinedConfig    143
UPDATE    IICUPDB.UP_UserEx                         70
UPDATE    IICUPDB.UP_BuddyList                    16
UPDATE    IICUPDB.UP_ConfigEx                     15
UPDATE    IICUPDB.UP_BuddyQuotaByUri        3

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

DELETE    IICUPDB.UP_OfflineMsg                 3441
DELETE    IICUPDB.UP_OfflineMsg_Log         3395
DELETE    IICUPDB.UP_Buddy                            1321
DELETE    IICUPDB.UP_AddBuddyEvent            840
DELETE    IICUPDB.UP_OfflineNotify            344
DELETE    IICUPDB.UP_ChatFriend                 216
DELETE    IICUPDB.UP_OfflineMsgPersist    89
DELETE    IICUPDB.UP_BlackList                    60
DELETE    IICUPDB.UP_BuddyList                    22
DELETE    IICUPDB.UP_OfflinePermission    8
############################################################
MULTILINECOMMENT