背景
在使用MySQL过程中，可能会因为binlog暴增导致影响数据库业务，此时客户可能会有分析binlog的需求；如哪个表，哪些SQL导致了日志量暴增。
技术预研
这里使用show binlog events 以及load data的方式，将binlog内容加载到MySQL的临时表中，在进一步进行分析。
查看 local_infile 参数是否开启
show global variables like 'local_infile'; #查看local_infile是否开启
set global local_infile =1; #开启local_infile
模拟大事务
flush logs;
use sbtest;
drop table if exists t;
create table t (id int not null primary key auto_increment, name text);
insert into t(name) values(repeat('a',65535));
insert into t(name) values(repeat('a',65535)),(repeat('b',65535));
insert into t(name) values(repeat('a',65535)),(repeat('b',65535)),(repeat('c',65535));
insert into t(name) values(repeat('a',65535)),(repeat('b',65535)),(repeat('c',65535)),(repeat('d',65535));
flush logs;
show binary logs;
建表
drop database sangfor;
create database sangfor;
create table sangfor.binlog_miner(
id bigint not null primary key auto_increment,
log_name varchar(64),
pos bigint,
event_type varchar(32),
server_id varchar(20),
end_log_pos bigint,
diff_pos bigint,
info text
);
create table sangfor.binlog_tmp(
log_name varchar(64),
pos bigint,
event_type varchar(32),
server_id varchar(20),
end_log_pos bigint,
info text
);
将 binlog 导入到文件中
mysql -e "
SHOW BINLOG EVENTS in 'mysql-bin.000026';
" > /root/mysql-bin.000026.log
将文件载入至 MySQL 数据库中的临时表中
load data local
infile '/root/mysql-bin.000026.log'
into table sangfor.binlog_tmp
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines;
加工表
truncate table sangfor.binlog_miner;
insert into sangfor.binlog_miner(log_name,pos,event_type,server_id,end_log_pos,diff_pos,info)
select
log_name,pos,event_type,server_id,end_log_pos,end_log_pos-pos,info
from sangfor.binlog_tmp;
#加索引
alter table sangfor.binlog_miner add index (event_type);
alter table sangfor.binlog_miner add index (diff_pos);
查询产生事务最大的前十条
select log_name,pos,event_type,server_id,end_log_pos,end_log_pos-pos,left(info,100) from sangfor.binlog_miner t
where 1=1
and t.event_type='Rows_query'
order by diff_pos desc
limit 10;
参考文献：
工具 infobin 研究
mysql -e "flush binary logs;"
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-socket=/run/sock/mysql.sock --mysql-user=root --mysql-password=Qwer1234 \
--db-ps-mode=disable --report-interval=1 --forced-shutdown=1 \
--mysql-db=sbtest \
--tables=100 \
--threads=4 \
cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-socket=/run/sock/mysql.sock --mysql-user=root --mysql-password=Qwer1234 \
--db-ps-mode=disable --report-interval=1 --forced-shutdown=1 \
--mysql-db=sbtest \
--tables=3 --table-size=100000 \
--threads=10 \
prepare
mysql -e "delete from sbtest.sbtest1;"
mysql -Bse "begin;
insert into sbtest.t values(1,'zhc');
select sleep(20);
commit;
"
mysql -e "insert into sbtest.t1 values(10);"
mysql -e "flush binary logs;"
3代表3个分片、500000单位是Byte、15单位是秒
./infobin mysql-bin.000025 3 2000000 10 -t > log
cat log
Check is Little_endian
[Author]: gaopeng [QQ]:22389860 [blog]:http://blog.itpub.net/7728585/
Warning: This tool only Little_endian platform!
Little_endian check ok!!!
-------------Now begin--------------
Check Mysql Version is:5.7.30-log
Check Mysql binlog format ver is:V4
Warning:Check This binlog is not closed!
Check This binlog total size:19090365(bytes)
Note:load data infile not check!
-------------Total now--------------
Trx total[counts]:24
Event total[counts]:2477
Max trx event size:8205(bytes) Pos:1483[0X5CB]
Avg binlog size(/sec):46790.109(bytes)[45.693(kb)]
Avg binlog size(/min):2807406.500(bytes)[2741.608(kb)]
--Piece view:
(1)Time:1680580633-1680580681(48(s)) piece:6363455(bytes)[6214.312(kb)]
(2)Time:1680580681-1680580681(0(s)) piece:6363455(bytes)[6214.312(kb)]
(3)Time:1680580681-1680581041(360(s)) piece:6363455(bytes)[6214.312(kb)]
--Large than 500000(bytes) trx:
(1)Trx_size:19081618(bytes)[18634.393(kb)] trx_begin_p:1306[0X51A] trx_end_p:19082924[0X1232EAC]
Total large trx count size(kb):#18634.393(kb)
--Large than 15(secs) trx:
(1)Trx_sec:42(sec) trx_begin_time:[20230404 11:59:48(CST)] trx_end_time:[20230404 12:00:30(CST)] trx_begin_pos:19085450 trx_end_pos:19085703 query_exe_time:0
--Every Table binlog size(bytes) and times:
Note:size unit is bytes
---(1)Current Table:sbtest.t1::
Insert:binlog size(40(Bytes)) times(1)
Update:binlog size(0(Bytes)) times(0)
Delete:binlog size(0(Bytes)) times(0)
Total:binlog size(40(Bytes)) times(1)
---(2)Current Table:sbtest.t::
Insert:binlog size(45(Bytes)) times(1)
Update:binlog size(0(Bytes)) times(0)
Delete:binlog size(0(Bytes)) times(0)
Total:binlog size(45(Bytes)) times(1)
---(3)Current Table:sangfor_inc.dts_3::
Insert:binlog size(945(Bytes)) times(21)
Update:binlog size(0(Bytes)) times(0)
Delete:binlog size(0(Bytes)) times(0)
Total:binlog size(945(Bytes)) times(21)
---(4)Current Table:sbtest.sbtest1::
Insert:binlog size(0(Bytes)) times(0)
Update:binlog size(0(Bytes)) times(0)
Delete:binlog size(19081410(Bytes)) times(2326)
Total:binlog size(19081410(Bytes)) times(2326)
---Total binlog dml event size:19082440(Bytes) times(2349)
查看最大的binlog
ls -lSr mysql-bin.*|tail -n1|awk '{print $NF}'
存储过程
use mysql;
drop procedure if EXISTS P_binlogMiner;
DELIMITER //
CREATE PROCEDURE P_binlogMiner()
begin
-- 定义变量
declare v_begin int;
declare v_end int;
declare v_done int default false;
-- 定义游标
declare cur1 cursor for select id from mysql.binlog_miner where event_type='query' and info = 'begin';
declare cur2 cursor for select id from mysql.binlog_miner where event_type='xid' order by id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;
set group_concat_max_len = 18446744073709551615;
truncate table mysql.binlog_miner_;
-- 开启游标
open cur1;
open cur2;
-- 开始循环
repeat
FETCH cur1 into v_begin;
FETCH cur2 into v_end;
IF NOT v_done THEN
insert into mysql.binlog_miner_ select min(pos),max(end_log_pos),group_concat(info) from binlog_miner where id between v_begin and v_end;
end if;
until v_done end repeat;
-- 关闭游标
close cur1;
close cur2;
end
//
DELIMITER ;
脚本
#!/bin/bash
# 定义参数
miner_path='/db_data/logMiner'
mkdir -p $miner_path
read -p "请输入用户名，推荐使用root：" username
stty -echo
read -p "请输入密码：" passwd
stty echo
echo
export MYSQL_PWD=$passwd
export MYSQL_HOST=127.0.0.1
# 检查用户密码
mysql -e 2>/dev/null "select 1;" &>/dev/null
if [ $? -ne 0 ];then
echo "用户名密码错误，请重新输入"
exit 1
fi
echo
echo "检查 local_infile 是否开启"
is_local_infile=$(mysql -Bse "show global variables like 'local_infile';"|awk '{print $2}')
if [ "${is_local_infile}" = 0 ] || [ "${is_local_infile}" = OFF ];then
mysql -e "set global local_infile =1;"
fi
# 获取binlog列表
ls -l /db_log/mysql/ |grep -Ev 'logfile|index'
echo
read -p "请输入需要分析的 binlog 名称：" binlogname
echo
# 检查输入的binlog内容是否正常
is_null=$(ls -l /db_log/mysql/ |grep -Ev 'logfile|index'|awk 'NR>1 {print $9}'|grep -w $binlogname)
if [ -z $is_null ];then
echo "$binlogname 不在binlog列表中"
exit 1
fi
# 创建日志分析表
echo "开始创建日志分析表"
mysql -e "
drop table if exists mysql.binlog_tmp;
create table mysql.binlog_tmp(
log_name varchar(64),
pos bigint,
event_type varchar(32),
server_id varchar(20),
end_log_pos bigint,
info text
);
drop table if exists mysql.binlog_miner;
create table mysql.binlog_miner(
id bigint not null primary key auto_increment,
log_name varchar(64),
pos bigint,
event_type varchar(32),
server_id varchar(20),
end_log_pos bigint,
diff_pos bigint,
info text
);
drop table if exists mysql.binlog_miner_;
create table mysql.binlog_miner_(start_pos int,end_pos int,info longtext);
"
echo "将binlog导入文件中"
mysql -e "
SHOW BINLOG EVENTS in '${binlogname}';
" > $miner_path/$binlogname
echo "load data"
mysql -e "
load data local
infile '$miner_path/$binlogname'
into table mysql.binlog_tmp
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines;
"
echo "添加索引"
mysql -e "
truncate table mysql.binlog_miner;
insert into mysql.binlog_miner(log_name,pos,event_type,server_id,end_log_pos,diff_pos,info)
select
log_name,pos,event_type,server_id,end_log_pos,end_log_pos-pos,info
from mysql.binlog_tmp;
#加索引
alter table mysql.binlog_miner add index (event_type);
alter table mysql.binlog_miner add index (diff_pos);
"
echo "调用存储过程"
mysql -e "call mysql.P_binlogMiner();"
echo "查询产生事务最大的前十条"
mysql -e "
select start_pos,end_pos,round((end_pos - start_pos)/1024/1024,4) "dif_pos(MB)" ,left(info,100) info from mysql.binlog_miner_ order by end_pos - start_pos desc limit 10;
"
#变量还原
unset MYSQL_PWD
unset MYSQL_HOST
演示
准备数据
export MYSQL_PWD=Qwer1234
export MYSQL_HOST=127.0.0.1
mysql -e "flush binary logs"
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-socket=/run/sock/mysql.sock --mysql-user=root --mysql-password=Qwer1234 \
--db-ps-mode=disable --report-interval=1 --forced-shutdown=1 \
--mysql-db=sbtest \
--tables=100 \
--threads=4 \
cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-socket=/run/sock/mysql.sock --mysql-user=root --mysql-password=Qwer1234 \
--db-ps-mode=disable --report-interval=1 --forced-shutdown=1 \
--mysql-db=sbtest \
--tables=3 --table-size=100000 \
--threads=10 \
prepare
mysql -e "delete from sbtest.sbtest1;"
mysql -e "delete from sbtest.sbtest2 limit 50000;"
"
mysql -e "insert into sbtest.t1 values(10);"
mysql -e "flush binary logs;"
unset MYSQL_PWD
unset MYSQL_HOST
执行脚本
Sangfor:DBVM/host-fefcfe935efb ~ # /root/binlogMiner.sh
请输入用户名，推荐使用root：root
请输入密码：
检查 local_infile 是否开启
total 27174280
-rw-r----- 1 mysql mysql 121307528 Apr 4 11:57 mysql-bin.000024
-rw-r----- 1 mysql mysql 19793285 Apr 4 14:55 mysql-bin.000025
-rw-r----- 1 mysql mysql 134584013 Apr 4 14:57 mysql-bin.000026
-rw-r----- 1 mysql mysql 16179989 Apr 4 15:10 mysql-bin.000027
-rw-r----- 1 mysql mysql 134562974 Apr 4 15:11 mysql-bin.000028
-rw-r----- 1 mysql mysql 9611120 Apr 6 11:34 mysql-bin.000029
-rw-r----- 1 mysql mysql 1075059712 Apr 8 13:46 mysql-bin.000030
-rw-r----- 1 mysql mysql 1075011997 Apr 8 13:48 mysql-bin.000031
-rw-r----- 1 mysql mysql 1075011997 Apr 8 13:50 mysql-bin.000032
-rw-r----- 1 mysql mysql 1073977339 Apr 8 13:53 mysql-bin.000033
-rw-r----- 1 mysql mysql 1075013044 Apr 8 13:55 mysql-bin.000034
-rw-r----- 1 mysql mysql 1073977339 Apr 8 13:57 mysql-bin.000035
-rw-r----- 1 mysql mysql 1075011997 Apr 8 13:59 mysql-bin.000036
-rw-r----- 1 mysql mysql 1073748919 Apr 8 15:18 mysql-bin.000037
-rw-r----- 1 mysql mysql 1080648074 Apr 10 12:03 mysql-bin.000038
-rw-r----- 1 mysql mysql 105372390 Apr 10 15:20 mysql-bin.000039
-rw-r----- 1 mysql mysql 283542090 Apr 10 16:16 mysql-bin.000040
-rw-r----- 1 mysql mysql 144122284 Apr 10 16:16 mysql-bin.000041
-rw-r----- 1 mysql mysql 194 Apr 10 16:16 mysql-bin.000042
请输入需要分析的 binlog 名称：mysql-bin.000041
开始创建日志分析表
将binlog导入文件中
load data
添加索引
调用存储过程
查询产生事务最大的前十条
+-----------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| start_pos | end_pos | dif_pos(MB) | info |
+-----------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| 115499629 | 134581247 | 18.1976 | BEGIN,# delete from sbtest.sbtest1,table_id: 472 (sbtest.sbtest1),table_id: 472,table_id: 472,table_ |
| 134581312 | 144122237 | 9.0989 | BEGIN,# delete from sbtest.sbtest2 limit 50000,table_id: 471 (sbtest.sbtest2),table_id: 471,table_id |
| 21304 | 1061301 | 0.9918 | BEGIN,# INSERT INTO sbtest3(k, c, pad) VALUES(50216, '37995383632-96831892448-13834617111-0328816536 |
| 1061366 | 2101363 | 0.9918 | BEGIN,# INSERT INTO sbtest2(k, c, pad) VALUES(50396, '92419600644-86829681637-42100581414-8029841414 |
| 2101428 | 3141425 | 0.9918 | BEGIN,# INSERT INTO sbtest1(k, c, pad) VALUES(57258, '59630305640-99001561578-28826938779-7489088816 |
| 3141490 | 4181487 | 0.9918 | BEGIN,# INSERT INTO sbtest3(k, c, pad) VALUES(50410, '69903238213-50951479256-02377369014-8906949706 |
| 4181552 | 5221549 | 0.9918 | BEGIN,# INSERT INTO sbtest2(k, c, pad) VALUES(49866, '32293522861-98786855173-94378430223-6354755435 |
| 5221614 | 6261611 | 0.9918 | BEGIN,# INSERT INTO sbtest1(k, c, pad) VALUES(50068, '61316669541-99871727957-41885353171-5236161901 |
| 6261676 | 7301673 | 0.9918 | BEGIN,# INSERT INTO sbtest3(k, c, pad) VALUES(48838, '27377286035-14145611334-95242506732-0397141150 |
| 7301738 | 8341735 | 0.9918 | BEGIN,# INSERT INTO sbtest2(k, c, pad) VALUES(49877, '54490167403-59127631823-28673158528-5244259181 |
+-----------+-----------+-------------+------------------------------------------------------------------------------------------------------+
Sangfor:DBVM/host-fefcfe935efb ~ #
