CODE EXAMPLE: mysql binary log summary

Program Language bash

Code:

#!/bin/bash

###############################################################################
# #
# mysqlbinlogsummary v0.1
# #
# #
# #
# #
# #
# Usage: mysqlbinlogsummary [binlogfile] (only one binary log per time,sorry!)#
# #
# Ex: mysqlbinlogsummary mysql-bin.001 (d be nice to use wildcards for file)#
# #
# #
# #
# 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. #
# #
###############################################################################



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

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.

uh?

sorry - using the journal as a code-drop. :)

I figured :D I just wish I understood it!

the key to this is Grep - works like a super "find" in text.

http://en.wikipedia.org/wiki/Grep

-- as per Roland Boumann's suggestions, a new version
-- TODO: add triggers, procedures

CREATE DATABASE IF NOT EXISTS dba;
USE dba;

DROP VIEW IF EXISTS `dba`.`dba_objects`;
CREATE VIEW `dba`.`dba_objects` AS
SELECT SCHEMA_NAME AS `OBJECT_NAME`, 'system' AS `SUPER_OBJECT`, 'schema' AS `OBJECT_TYPE`, 'system' AS `SUPER_OBJECT_TYPE`, SCHEMA_NAME as `SCHEMA_NAME` FROM information_schema.schemata
UNION
SELECT TABLE_NAME, TABLE_SCHEMA, 'table', 'schema', TABLE_SCHEMA AS s1 FROM information_schema.tables
UNION
SELECT TABLE_NAME, TABLE_SCHEMA, 'view', 'schema', TABLE_SCHEMA AS s1 FROM information_schema.VIEWS
UNION
SELECT COLUMN_NAME, TABLE_NAME, 'column', 'table', TABLE_SCHEMA FROM information_schema.COLUMNS
UNION
SELECT CONSTRAINT_NAME, TABLE_NAME, 'index', 'table', TABLE_SCHEMA FROM information_schema.KEY_COLUMN_USAGE;

SELECT * FROM dba.dba_objects;

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. Well, we do have information like that on MySQL 5.0 (and up) thru the information_schema database, but it’s scattered thru several different tables. This 'view' allow us to quickly locate any objects within a MySQL database. It's actually a stored procedure, but after the table gets populated, you can query it as any other table.

Saving on j:/uiia/qmysql/ (or locally on - http://scottobear.com/sql/uiia/queries/)