将mysql binlog导入到数据库

一、脚本介绍
语言：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

这里的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) ;

#查看某个时间段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 ;

#查看某个时间段，每个表的操作频率

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 ;

#查看某个时间段，某个表的各种操作情况，可以清楚的看见某个表主要做的是那种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 ;

#查看某个表在一个时间段内的访问情况，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);

四、脚本下载地址
binlog_analyze-0.1.tar
转自：http://isadba.com/?p=354

版权说明

文章标题:  将mysql binlog导入到数据库
本文地址:  http://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/