The MySQL binary log is a set of log files that contain information about data modifications made to a MySQL server instance. At times we need to understand the disk usage by binary logs to know how much data is being written and probably growth estimation.
How many binary logs do you generate per hour?
Following command (shell script) will provide you with the hourly binlogs generated in MB. Note that the binary log name needs to be updated, I have used mysql-bin.
Binary log growth per hour
find . -name "mysql-bin.*" -exec ls -lt --time-style=+%F {} \; | awk 'BEGIN{FS=" "; print "Date\t\t Files\t Total \t\t PerHour"} NR!=1 {a[$6]++;sum[$6]=sum[$6]+$5}END{for (i in a) printf("%s %10.0f %10.2f MB %10.2f MB\n", i, a[i], sum[i]/1048576, sum[i]/24/1048576)} 'Assuming you have to collect the binary log growth from multiple servers we can use following script to fetch the data from all of them together. That said, note that we have assumed passwordless authentication for shipping the script to all the servers.
Binary log growth estimate automated script
for srvr in server1 server2; do
# get binlog dir
BINLOGDIR=$(dirname $(db_connect $srvr --silent -N --raw -e "select @@log_bin_basename"));
# get binlog name
BINLOGNAME=$(basename $(db_connect $srvr --silent -N --raw -e "select @@log_bin_basename"));
echo $srvr; echo "-------------------";
echo "Binlog Dir: $BINLOGDIR - Binlog Basename: $BINLOGNAME";
echo "-------------------";
# ship the script to respective server
scp -q binlog_size.sh $srvr:/tmp/;
# execute the script
echo "$srvr: sh /tmp/binlog_size.sh $BINLOGDIR/ $BINLOGNAME";
ssh -q -o "StrictHostKeyChecking no" $srvr "sh /tmp/binlog_size.sh $BINLOGDIR/ $BINLOGNAME";
echo "-------------------"; echo;
done
