python分析mysql-binlog，统计时间段内各表的操作次数

kuuty

#####注：：：以下脚本中路径可能需要各位自行进行修改




实现原理：
   1、shell脚本：通过mysqlbinlog将binlog日志文件格式化输出
        定制crontab任务，定时通过向mysql传递show master status查看binlog是否变化，若发生变化则格式化输出已经切换掉的binlog文件
   2、shell脚本：定制crontab任务，通过比对md5码，查看格式化后的binlog文件是否发生改变，若发生改变，则调用python脚本进行统计


A、格式化binlog脚本：脚本名自定#!/bin/bash
# initail progran:
# :echo 111 > /tmp/md5_code.txt   #程序初始化
# :touch /tmp/binlog.dump        #程序初始化
bin_dir='/var/lib/mysql'
md5_record='/tmp/md5_code.txt'
bin_dump='/smbdir/binlog.dump'
sqe_code=`cat ${md5_record}`
md5_bindump=`md5sum $bin_dump|awk '{print $1}'`
zero_fill=`mysql  -e 'show master status' | grep mysql | awk '{print $1}' |grep -o '[0-9]\+' |awk -F'[1-9]' '{print $1}'`
binlog_seq=$((`mysql  -e 'show master status' | grep mysql | awk '{print $1}' |awk -F'0' '{print $NF-1}'`))

if [ $sqe_code != $binlog_seq ];then
        mysqlbinlog  --base64-output=decode-rows -v $bin_dir/mysql-bin.${zero_fill}${binlog_seq} > $bin_dump
        echo $binlog_seq > $md5_record
fi

B、监控格式化后的binlog脚本：

1 2 3 4 5 6 7 8 9

#!/bin/bash md5_record='/tmp/md5_code.txt' bin_dump='/data/smb_sharepath/sup_home/binlog.dump' md5_code=`cat ${md5_record} |awk '{print $1}'` local_md5=`md5sum $bin_dump|awk '{print $1}'` if [ $local_md5 != $md5_code ];then     /root/scripts/binlog_analyze.py     echo $local_md5 > $md5_record fi

C、python统计脚本
####使用此脚本，前提
1、需安装MySQLdb模块
2、创建临时表：

1 2 3 4 5 6 7

Create Table: CREATE TABLE `operation_count_tmp_table` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `OPERATION` varchar(10) DEFAULT NULL,   `Database_name` varchar(40) DEFAULT NULL,   `Table_name` varchar(100) DEFAULT NULL,   PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=410833 DEFAULT CHARSET=utf8

3、创建统计结果表：

1 2 3 4 5 6 7 8 9

Create Table: CREATE TABLE `binlog_analyze` (   `RECORD_TIME` varchar(30) COLLATE utf8_bin DEFAULT NULL,   `DB_name` varchar(40) CHARACTER SET utf8 DEFAULT NULL,   `TB_name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,   `OPERATION` varchar(10) CHARACTER SET utf8 DEFAULT NULL,   `COUNT` bigint(20) DEFAULT NULL,   `START_TIME` varchar(30) COLLATE utf8_bin DEFAULT NULL,   `END_TIME` varchar(30) COLLATE utf8_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

#!/usr/bin/env python #-*- coding: utf-8 -*- # Analyze mysql binlog . import MySQLdb bin_log_file = open('/tmp/binlog460.sql1') method = ['INSERT','UPDATE','DELETE'] id = 1 i = 1 ##Connect to mysql server try:     conn = MySQLdb.connect(host='数据库地址',user='用户名',passwd='密码',db='数据库',port=端口)     cur = conn.cursor() except MySQLdb.Error,e:     print "Mysql Error %d: %s" % (e.args[0], e.args[1]) while True:         line = bin_log_file.readline().split(' ')         if line[0] == '':             break         if len(line[0]) == 7 and line[0][1] == '1': #            print line             if i == 1:                 begin_time = line[0][1:3]+'-'+line[0][3:5]+'-'+line[0][5:7]+'/'+line[1]                 i += 1             else:                 end_time = line[0][1:3]+'-'+line[0][3:5]+'-'+line[0][5:7]+'/'+line[1] #       print line,len(line)         if len(line) > 2 and line[1] in method: #               print line                 if line[1] == 'UPDATE':                         db_table = line[2].split('.')                         db_name = db_table[0][1:-1]                         table_name = db_table[1][1:-2]                         try:                             cur.execute('insert into operation_count_tmp_table values (%s,"UP_DATE",%s,%s)', (id,db_name,table_name))                         except MySQLdb.Error,e:                             print "Mysql Error %d: %s" % (e.args[0], e.args[1])                         if id % 1000 == 0:                             conn.commit()                         id += 1                 else:                         db_table = line[3].split('.')                         db_name = db_table[0][1:-1]                         table_name = db_table[1][1:-2]                         operation = line[1]                         try:                             cur.execute('insert into operation_count_tmp_table values (%s,%s,%s,%s)',(id,operation,db_name,table_name))                         except MySQLdb.Error,e:                                 print "Mysql Error %d: %s" % (e.args[0], e.args[1])                         if id % 1000 == 0:                             conn.commit()                         id += 1 conn.commit() try:     cur.execute('INSERT INTO binlog_analyze () SELECT now(),Database_name AS DB_name,Table_name AS TB_name,OPERATION,count(*) AS count,%s,%s FROM operation_count_tmp_table GROUP BY Database_name,Table_name,OPERATION HAVING count(*) > 500',(begin_time,end_time)) except MySQLdb.Error,e:     print "Mysql Error %d: %s" % (e.args[0], e.args[1]) conn.commit() cur.execute('delete from operation_count_tmp_table') conn.commit() cur.close() conn.close()