python分析mysql-binlog，统计时间段内各表的操作次数
#####注：：：以下脚本中路径可能需要各位自行进行修改实现原理：
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 '\+' |awk -F'' '{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, e.args)
while True:
line = bin_log_file.readline().split(' ')
if line == '':
break
if len(line) == 7 and line == '1':
# print line
if i == 1:
begin_time = line+'-'+line+'-'+line+'/'+line
i += 1
else:
end_time = line+'-'+line+'-'+line+'/'+line
# print line,len(line)
if len(line) > 2 and line in method:
# print line
if line == 'UPDATE':
db_table = line.split('.')
db_name = db_table
table_name = db_table
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, e.args)
if id % 1000 == 0:
conn.commit()
id += 1
else:
db_table = line.split('.')
db_name = db_table
table_name = db_table
operation = line
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, e.args)
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, e.args)
conn.commit()
cur.execute('delete from operation_count_tmp_table')
conn.commit()
cur.close()
conn.close()
页:
[1]