一、脚本介绍
语言：bash script，awk，sed
功能：将mysql的binlog日志格式化后，通过load data直接导入数据库，然后通过sql对binlog日志做分析。
优点：通过awk逐行分析，效率还算可以。可以通过分析binlog日志找到系统的各种突发原因，分析系统中表的使用率的DDL的类型，可以做到秒级别的TPS分析。
解压binlog_analyze.tar.gz后获取三个脚本
binlog_analyze.awk:用于将binlog的文本日志转换成可以直接load data到数据库的格式
binlog_analyze.sed:格式化binlog的文本你日志，去掉一些windows换行符和多余的制表符
run.sh：封装了上面两个脚本，用户直接调用的脚本。
二、使用脚本
进入此目录,给脚本赋予执行权限。
指定一个或者多个需要转换的二进制日志，生成的结果会放在目标目录，以.sql结尾，大小和二进制日志差不多。
# ./run.sh ../db03-bin/db03-bin.000042 ../db03-bin/db03-bin.000043
OK: ../db03-bin/db03-bin.000042 is MySQL replication log!
OK: ../db03-bin/db03-bin.000043 is MySQL replication log!
Format binlog ing………………
另外一种使用方法,用*代替目标文件，会一次遍历目标目录，对目标目录的每个文件做处理
# ./run.sh ../db03-bin/*
ll ../db03-bin/*
-rwxr-xr-x 1 andy andy 1073741986 Aug 27 16:15 db03-bin.000033
-rwxr-xr-x 1 root root 1325409484 Aug 27 16:51 db03-bin.000033.txt.awk.sql
-rwxr-xr-x 1 andy andy 1073741881 Aug 27 16:12 db03-bin.000034
-rwxr-xr-x 1 root root 1310773234 Aug 27 17:03 db03-bin.000034.txt.awk.sql
这些生成的文件就可以直接导入数据库了。
三、将文件导入数据库
create database binlog_statis;
use binlog-statis;
DROP TABLE IF EXISTS bin_log;
CREATE TABLE `bin_log` (
`at_pos` INT(11) NOT NULL,
`at_time` TIMESTAMP NOT NULL ,
`server_id` INT(11) NOT NULL,
`end_log_pos` INT(11) NOT NULL,
`thread_type` VARCHAR(500) DEFAULT NULL,
`dml_type` VARCHAR(50) DEFAULT NULL,
`dml_table` VARCHAR(500) DEFAULT NULL,
`dml_sql` VARCHAR(5000) DEFAULT NULL
) ENGINE=myisam DEFAULT CHARSET=utf8;
mysql> LOAD DATA INFILE ‘/home/andy/db03-bin/db03-bin.000033.txt.awk.sql’ INTO TABLE binlog_statis.`bin_log` fields TERMINATED BY “|||||”;
Query OK, 3957099 rows affected, 700 warnings (51.89 sec)
Records: 3957099 Deleted: 0 Skipped: 0 Warnings: 700
mysql> LOAD DATA INFILE ‘/home/andy/db03-bin/db03-bin.000034.txt.awk.sql’ INTO TABLE binlog_statis.`bin_log` fields TERMINATED BY “|||||”;
Query OK, 3299350 rows affected, 1334 warnings (48.79 sec)
Records: 3299350 Deleted: 0 Skipped: 0 Warnings: 1334
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
createdatabasebinlog_statis;
usebinlog-statis;
DROPTABLEIFEXISTSbin_log;
CREATETABLE`bin_log`(
`at_pos`INT(11)NOTNULL,
`at_time`TIMESTAMPNOTNULL,
`server_id`INT(11)NOTNULL,
`end_log_pos`INT(11)NOTNULL,
`thread_type`VARCHAR(500)DEFAULTNULL,
`dml_type`VARCHAR(50)DEFAULTNULL,
`dml_table`VARCHAR(500)DEFAULTNULL,
`dml_sql`VARCHAR(5000)DEFAULTNULL
)ENGINE=myisamDEFAULTCHARSET=utf8;
mysql>LOADDATAINFILE‘/home/andy/db03-bin/db03-bin.000033.txt.awk.sql’INTOTABLEbinlog_statis.`bin_log`fieldsTERMINATEDBY“|||||”;
QueryOK,3957099rowsaffected,700warnings(51.89sec)
Records:3957099Deleted:0Skipped:0Warnings:700
mysql>LOADDATAINFILE‘/home/andy/db03-bin/db03-bin.000034.txt.awk.sql’INTOTABLEbinlog_statis.`bin_log`fieldsTERMINATEDBY“|||||”;
QueryOK,3299350rowsaffected,1334warnings(48.79sec)
Records:3299350Deleted:0Skipped:0Warnings:1334
这里的warnings可以不用管，主要是sql语句过长导致的，一般导入一个周期的数据做分析就可以了，导入以后自己加索引，周期的话可以是一天，一周，一月。
导入完成后就可以开始分析了
#根据其他监控，发现某个时间段的transactions突发很高，可以通过查询指定时间段的DDL，分析什么原因导致的，可以精确到秒。
下面是一些通用分析语句，如果你还能通过这个表挖掘出更有意义的数据，也请告诉我。
#计算某个时间段内每分钟的TPS数量
SELECT
SUBSTR(at_time, 1, 16),
COUNT(*)
FROM
`bin_log`
WHERE dml_table <> ’0′
AND at_time > ’2012-08-18 12:00:00′
AND at_time < '2012-08-20 12:00:00'
GROUP BY SUBSTR(at_time, 1, 16)
ORDER BY SUBSTR(at_time, 1, 16) ;
1
2
3
4
5
6
7
8
9
10
SELECT
SUBSTR(at_time,1,16),
COUNT(*)
FROM
`bin_log`
WHEREdml_table<>’0′
ANDat_time>’2012-08-1812:00:00′
ANDat_time
GROUPBYSUBSTR(at_time,1,16)
ORDERBYSUBSTR(at_time,1,16);
#查看某个时间段delete，insert，update的执行数量，类似global status里面com%的几个输出，不过这里可以限定时间。
SELECT
dml_type,
COUNT(*)
FROM
`bin_log`
WHERE at_time > ’2012-08-18 12:00:00′
AND at_time < '2012-08-20 12:00:00'
GROUP BY dml_type ;
1
2
3
4
5
6
7
8
SELECT
dml_type,
COUNT(*)
FROM
`bin_log`
WHEREat_time>’2012-08-1812:00:00′
ANDat_time
GROUPBYdml_type;
#查看某个时间段，每个表的操作频率
SELECT
dml_table,
COUNT(*)
FROM
`bin_log`
WHERE at_time > ’2012-08-18 12:00:00′
AND at_time < '2012-08-20 12:00:00'
AND dml_table <> ’0′
GROUP BY dml_table ;
1
2
3
4
5
6
7
8
9
SELECT
dml_table,
COUNT(*)
FROM
`bin_log`
WHEREat_time>’2012-08-1812:00:00′
ANDat_time
ANDdml_table<>’0′
GROUPBYdml_table;
#查看某个时间段，某个表的各种操作情况，可以清楚的看见某个表主要做的是那种DDL操作
SELECT
dml_table,
dml_type,
COUNT(*)
FROM
`bin_log`
WHERE at_time > ’2012-08-18 12:00:00′
AND at_time < '2012-08-20 12:00:00'
AND dml_table <> ’0′
GROUP BY dml_table,dml_type ;
1
2
3
4
5
6
7
8
9
10
SELECT
dml_table,
dml_type,
COUNT(*)
FROM
`bin_log`
WHEREat_time>’2012-08-1812:00:00′
ANDat_time
ANDdml_table<>’0′
GROUPBYdml_table,dml_type;
#查看某个表在一个时间段内的访问情况，10分钟为一个间隔
SELECT
dml_table,
SUBSTR(at_time, 1, 15),
COUNT(*)
FROM
`bin_log`
WHERE at_time > ’2012-09-10 12:00:00′
AND at_time < '2012-09-12 12:00:00'
AND dml_table = '`promotionurlreferer`'
GROUP BY dml_table,SUBSTR(at_time, 1, 15);
1
2
3
4
5
6
7
8
9
10
SELECT
dml_table,
SUBSTR(at_time,1,15),
COUNT(*)
FROM
`bin_log`
WHEREat_time>’2012-09-1012:00:00′
ANDat_time
ANDdml_table='`promotionurlreferer`'
GROUPBYdml_table,SUBSTR(at_time,1,15);
四、脚本下载地址
binlog_analyze-0.1.tar
