mysql binary log summary

To have an idea on which DML statements are run on which tables I have written this very basic bash script. My need was to know how much activity there is on one table to schedule table maintenance like building indexes. I focus here only on statements that can change table data (insert, update, delete, drop) since I have found sometimes useful to copy the table to work offline with it when there are no changes in the table data. This is a very basic version. The aim is to be able, with this tool, to find the correct possible window with NO-WRITES to minimize table locks while maintenance.

#!/bin/bash

###############################################################################
#                                                                             #
# mysqlbinlogsummary v0.1                           (c)2009 by Claudio Nanni  #
#                                                                             #
#                                                                             #
#                                                                             #
#                                                                             #
#                                                                             #
# Usage: mysqlbinlogsummary [options] (any option that's good for mysqlbinlog)#
#                                                                             #
# Ex: mysqlbinlogsummary mysql-bin.* --start-datetime=20090302184500          #
#                                                                             #
#                                                                             #
#                                                                             #
# It will output a summary of the data changing statements grouped per table. #
# The four statements analyzed here are: update,insert,delete,drop.           #
# It is useful to have an idea on the activity on some table for maintenance. #
#                                                                             #
###############################################################################

//If no arguments are passed then just printout the usage.
if [ "$#" -eq 0 ]
then
    echo -e "\nUsage: mysqlbinlogsummary 'binlog filename' [--start-datetime=yyyymmddhhmmss] [--stop-datetime=yyyymmddhhmmss]\n"
    echo -e "'binlog filename' can contain wildcards 'mysql-bin.000*'\n"
    echo -e "Watch: --start-datetime and --stop-datetime correct parameter format is one unique number without spaces. \n"

    exit 0;
fi


echo -e "\n\nmysqlbinlogsummary: analyzaing binlog $1\n\n"
echo -e "\nchecking for [update] statements"
mysqlbinlog $@ | grep -i -e "^update" | awk '{print "UPDATE " $2}' > UPDATE.LOG
echo -e "\nchecking for [insert] statements"
mysqlbinlog $@ | grep -i -e "^insert" | awk '{print "INSERT " $3}' > INSERT.LOG
echo -e "\nchecking for [delete] statements"
mysqlbinlog $@ | grep -i -e "^delete" | awk '{print "DELETE " $3}' > DELETE.LOG
echo -e "\nchecking for [drop] statements"
mysqlbinlog $@ | grep -i -e "^drop"   | awk '{print "DROP " $2 " "  $3}' > DROP.LOG

echo -e "\n\nSorting statements..."
sort -u UPDATE.LOG >UPDATE.U.LOG
sort -u INSERT.LOG >INSERT.U.LOG
sort -u DELETE.LOG >DELETE.U.LOG
sort -u DROP.LOG >DROP.U.LOG

echo -e "\n\n-----------------------------------------------------------"
echo "UPDATE STATEMENTS"
echo "-----------------------------------------------------------"
while read line 
do
echo -n $line | awk '{printf $2 " "}';grep -c `echo -e "$line" | awk '{print "\t\t\t" $2}'` UPDATE.LOG
done < UPDATE.U.LOG

echo -e "\n\n-----------------------------------------------------------"
echo "INSERT STATEMENTS"
echo "-----------------------------------------------------------"
while read line 
do
echo -n $line | awk '{printf $2 " "}';grep -c `echo -e "$line" | awk '{print "\t\t\t" $2}'` INSERT.LOG
done < INSERT.U.LOG

echo -e "\n\n-----------------------------------------------------------"
echo "DELETE STATEMENTS"
echo "-----------------------------------------------------------"
while read line 
do
echo -n $line | awk '{printf $2 " "}';grep -c `echo -e "$line" | awk '{print "\t\t\t" $2}'` DELETE.LOG
done < DELETE.U.LOG

echo -e "\n\n-----------------------------------------------------------"
echo "DROP STATEMENTS"
echo "-----------------------------------------------------------"
while read line 
do
echo -n $line | awk '{printf $2 " "}';grep -c `echo -e "$line" | awk '{print "\t\t\t" $2}'` DROP.LOG
done < DROP.U.LOG
echo -e "\n\n\nDone....\n\n"

发表回复