将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………………文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

另外一种使用方法,用*代替目标文件，会一次遍历目标目录，对目标目录的每个文件做处理
# ./run.sh ../db03-bin/*文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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
这些生成的文件就可以直接导入数据库了。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

三、将文件导入数据库文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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

文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

这里的warnings可以不用管，主要是sql语句过长导致的，一般导入一个周期的数据做分析就可以了，导入以后自己加索引，周期的话可以是一天，一周，一月。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

导入完成后就可以开始分析了
#根据其他监控，发现某个时间段的transactions突发很高，可以通过查询指定时间段的DDL，分析什么原因导致的，可以精确到秒。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

下面是一些通用分析语句，如果你还能通过这个表挖掘出更有意义的数据，也请告诉我。
#计算某个时间段内每分钟的TPS数量文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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%的几个输出，不过这里可以限定时间。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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 ;

#查看某个时间段，每个表的操作频率文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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操作文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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分钟为一个间隔文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/

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文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-binlog-importe-to-database/