Python analysis Mysql-binlog, counts the number of operations for each table in the time period _2016041301

Peng Peng in the classroom: Statistics specify the time period of the database table operation times.
the debut, need the Great God to help optimize.
# # # # #注::: The path in the following script may require you to modify it yourself
650) this.width=650; "alt=" Picture "src=" http://r.photo.store.qq.com/psb?/V11J5LBI30FcCD/ iqtjoxdc.mkbbk2ty47yuiteabtw9yckaiwju4ntaos!/o/dpoaaaaaaaaa&ek=1&kp=1&pt=0&bo=rgsaam0elwidabk! &su=1112068337&sce=0-12-12&rf=2-9 "style=" Margin:0px;padding:0px;border-width:0px;border-style:none ; vertical-align:top;font-family:punctuation, ' Microsoft Jas Black ', tohoma;font-size:14px;line-height:22.4px;white-space:normal ; Width:847px;height:495.544px;background-color:rgb (62,6,75); "/>



Implementation principle:
1. Shell script: Format output of Binlog log file via Mysqlbinlog
Customize the Crontab task by passing the show master status to MySQL to see if the binlog is changing and format the output of the Binlog file that has been switched off if a change occurs
2, Shell script: Custom crontab task, through the comparison of MD5 code, to see if the formatted Binlog file changes, if any change, then call the Python script for statistics


A. Format Binlog Script: script name Custom

#!/bin/bash# initail progran:# :echo 111 > /tmp/md5_code.txt       (need to initialize the program, a little Low) # :touch /tmp/binlog.dump                 (need to initialize the program, a little Low) bin_dir= '/var/lib/mysql ' md5_record= '/ Tmp/md5_code.txt ' bin_dump= '/smbdir/binlog.dump ' md5_code= ' cat ${md5_record} |awk  ' {print $1 } ' 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 [  $md 5_code !=  $md 5_bindump ];then         mysqlbinlog  --base64-output=decode-rows -v  $bin _dir/mysql-bin.${zero_fill}${binlog _seq} >  $bin _dump        echo  ' md5sum  $bin _dump |awk  ' {print $1} '  >  $md 5_record

B, monitor the formatted Binlog script:

#!/bin/bashmd5_record= '/tmp/md5_code.txt ' bin_dump= '/data/smb_sharepath/sup_home/binlog.dump ' md5_code= ' cat ${md5 _record} |awk ' {print $} ' ' local_md5= ' md5sum $bin _dump|awk ' {print '} ' if [$local _md5! = $MD 5_code];then/root/scrip ts/binlog_analyze.py echo $local _md5 > $md 5_RECORDFI

C, Python statistics script
# # # #使用此脚本, premise
1, need to install MYSQLDB module
2. Create a temporary table:

Create table:create Table ' operation_count_tmp_table ' (' ID ' int (one) not NULL auto_increment, ' operation ' varchar (TEN) D Efault NULL, ' database_name ' varchar (max) default NULL, ' table_name ' varchar (+) default NULL, PRIMARY KEY (' ID ')) ENGI Ne=innodb auto_increment=410833 DEFAULT Charset=utf8

3. Create a table of statistical results:

create table: create table  ' Binlog_analyze '   (    ' Record_time '  varchar (+)  COLLATE utf8_bin DEFAULT NULL,   ' db_ Name '  varchar '  CHARACTER SET utf8 DEFAULT NULL,   ' tb_name '   varchar  CHARACTER SET utf8 DEFAULT NULL,   ' operation '  varchar (10)  CHARACTER SET utf8 DEFAULT NULL,   ' COUNT '  bigint ( default)  NULL,   ' start_time '  varchar (+)  COLLATE utf8_bin DEFAULT NULL,    ' End_time '  varchar ( collate utf8_bin default null)  ENGINE=InnoDB  Default charset=utf8 collate=utf8_bin 

#!/usr/bin/env python#-*- coding: utf-8 -*-# analyze mysql binlog . Import mysqldbbin_log_file = open ('/tmp/binlog460.sql1 ') method = [' INSERT ', ' UPDATE ', ' DELETE ']id = 1i = 1# #Connect  to mysql servertry:     Conn = mysqldb.connect (host= ' database address ', user= ' username ', passwd= ' password ', db= ' database ', port= 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:                         &nbSp;    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 += 1conn.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.close () Conn.close ()

Python analysis Mysql-binlog, counts the number of operations for each table in the time period _2016041301