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

小鹏鹏装逼课堂之：统计指定时间段数据库表的操作次数。×××作，需要大神帮忙优化。

bug修复：

1、小时位为个位数时，时间显示异常 ----- 已修复

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

实现原理：   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}' |grep -o '[0-9]\+' |  grep -o '[1-9]\+0*$'`-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脚本：#!/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、创建临时表：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、创建统计结果表：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

#!/usr/local/python27/bin/python

#-*- coding: utf-8 -*-

# Analyze mysql binlog .

import MySQLdb

bin_log_file = open('/data/smb_sharepath/sup_home/binlog.dump')

method = ['INSERT','UPDATE','DELETE']

id = 1

i = 1

##Connect to mysql server

try:

conn = MySQLdb.connect(host='192.168.0.105',user='miner',passwd='Miner~!',db='logminer',port=4406)

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:

if line[1] == '':

begin_time = line[0][1:3]+'-'+line[0][3:5]+'-'+line[0][5:7]+'/'+line[2]

else:

begin_time = line[0][1:3]+'-'+line[0][3:5]+'-'+line[0][5:7]+'/'+line[1]

i += 1

else:

if line[1] == '':

end_time = line[0][1:3]+'-'+line[0][3:5]+'-'+line[0][5:7]+'/'+line[2]

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()