MySQL 统计binlog大小与个数

一、设计思路

1、获取当前小时内的binlog大小、数量

2、将获取的数据写入表中
3、处理跨时间段的 binlog 文件：如果一个 binlog 文件跨越了多个时间段，需要处理

二、统计脚本

1、表结构

CREATE TABLE `dba_check_binlog_statistics` (
`host_ip` varchar(30) NOT NULL,
`log_nums` int(11) NOT NULL DEFAULT '0',
`log_size` bigint(20) NOT NULL DEFAULT '0',
`file_new` varchar(30) DEFAULT NULL,
`file_old` varchar(30) DEFAULT NULL,
`created_hour` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`host_ip`,`created_hour`),
KEY `idx_host_ip_created_time` (`host_ip`,`created_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

2、脚本

 

cat /opt/tools/bin/binlog_statistics.sh
#!/bin/bash

BINLOG_DIR="/data/mysql/mysql3306/log"
BINLOG_PREFIX="3306-bin.0"

#mysql
basedir=/usr/local/mysql
# config archery database server
# archery server ip
archery_ip="172.xxx.xxx.xxx"
archery_user="dbmonitor_admin"
archery_pwd="dbmonitorxxxxxx"
server_con_archery="$basedir/bin/mysql -u${archery_user} -p${archery_pwd} -h${archery_ip} -P6033 archery"
#log
tools_home="/opt/tools" # Backup log path
log=${tools_home}/logs/binlog_statistics.log

# 获取上个小时整点的时间
current_time=$(date +%s)
one_hour_ago=$((current_time - 3600))
start_time=$(date -d @$one_hour_ago +%Y-%m-%d\ %H:00:00)

# 获取下一个小时整点时间
current_time=$(date +%s)
one_hour_after=$((current_time + 3600))
end_time=$(date -d @$one_hour_after +%Y-%m-%d\ %H:00:00)

# 获取当前整点
NOW_HOUR=$(date +%Y-%m-%d\ %H\:00\:00)

# 获取上一个小时整点的时间
one_hour_ago_date=$start_time

ip_command=`whereis ip | awk '{print $2}'`
local_ip=""

find_ip(){

vip_exist=`${ip_command} a | grep inet | grep eth0 | grep secondary | wc -l`
if [ ${vip_exist} -eq 1 ]
then
local_ip=`${ip_command} a | grep inet | grep eth0 | grep -iv secondary | awk '{print $2}' | cut -d/ -f1`
mysql_host="localhost"
else
local_ip=`${ip_command} a | grep inet | grep eth0| grep brd | grep -v eth0:[0-9] | awk '{print $2}' | cut -d/ -f1`
mysql_host="127.0.0.1"
fi
}

#echo "start_time：$start_time"
#echo "end_time：$end_time"

# 获取当前小时文件大小与个数
totalsize_now_hour=$(find "$BINLOG_DIR" -type f -name "${BINLOG_PREFIX}*" -newermt "$NOW_HOUR" -printf "%s\n" | awk '{total += $1} END {print total}')
totalfiles_now_hour=$(find "$BINLOG_DIR" -type f -name "${BINLOG_PREFIX}*" -newermt "$NOW_HOUR" | wc -l)
# 获取当前小时内最新/最老的文件
FileOld=$(ls "$BINLOG_DIR" -l --full-time |grep 3306-bin.0| grep "$(date +'%Y-%m-%d %H')" | head -n 1 | awk '{print $9}')
FileNew=$(ls "$BINLOG_DIR" -l --full-time |grep 3306-bin.0| grep "$(date +'%Y-%m-%d %H')" | tail -n 1 | awk '{print $9}')

get_data(){
#echo "从一小时前整点到现在文件大小与个数: $total_size bytes、$total_filses"
#echo "当前小时前整点到现在文件大小与个数: $totalsize_now_hour bytes、$totalfiles_now_hour"
#echo "前小时内生成的 Binlog 文件,最新文件为$FileNew"
#echo "前小时内生成的 Binlog 文件,最老文件为$FileOld"
# 获取当前时间
dateStart=$(date +%Y-%m-%d\ %H\:%M\:%S)

echo "$dateStart----info----Start slowlog_nums_size_statistics " >> $log

if [ -z "$totalsize_now_hour" ];then
totalsize_now_hour=0
fi
if [ -z "$totalfiles_now_hour" ];then
totalfiles_now_hour=0
fi

sql1="insert into dbadmin.dba_check_binlog_statistics(host_ip,log_nums,log_size,created_hour,updated_time,file_new,file_old) values (\"$local_ip\",\"$totalfiles_now_hour\",$totalsize_now_hour,\"$NOW_HOUR\",\"$dateStart\",\"$FileNew\",\"$FileOld\") ON DUPLICATE KEY UPDATE log_nums=values(log_nums),log_size=values(log_nums),log_size=values(log_size),updated_time=values(updated_time),file_new=values(file_new),file_old=values(file_old)"

echo "Running sql1" >> $log
$server_con_archery -e "$sql1"

dateEnd=$(date +%Y-%m-%d\ %H\:%M\:%S)
echo "$dateEnd----info----End slowlog_nums_size_statistics " >> $log
echo "" >> $log
}

find_ip
get_data

三、定时执行

*/5 * * * * sh /opt/tools/bin/binlog_statistics.sh