MySQL: Automate your Merge Engines

Posted by Andrew on 16 February 2010 | 0 Comments

Tags:

A while ago, I'd written a bash script to handle the automated control of a table using the MERGE engine.

The table originally held the log data from a mobile messaging service, and very quickly became far  too big to be easily manageable.  Since the application was provided by a third party, and I had no way of changing how it logged it's data, I devised this bash script to automatically reorganise a merge table to ensure the data was in manageable chunks. This predates partitioning within MySQL, which would have been an ideal solution at the time.  However, there are still practical uses for the merge engine, so I've dug out the script for your enjoyment.  I'm sure it worked, as is, back in 2005, but I can't recall if this was the totally polished and final version, and I've also sanitised it to remove references to the company and the third party app. So, if it doesn't work perfectly, don't be surprised, but it is certainly a good starting point if you want to do something similar. This script also handles the creation of the new tables, so you don't need to predefine the tables X years in advance.

#/bin/bash

 

####################################################

# Merge engine rotate and clean script. 

# What do I do?                              

# Take latest week live  entries entries (till time on day script was run)     

# and copy them into a new table.

# Modifies MASTER table (using “merge” storage engine) so it  

# displays data for only last x weeks. All other weekly tables remain 

# individually available.                                                      

#                                                                              

# Executes as user: [whatever]                                        

# Execution frequency: [stick it in your crontab. Weekly would be good].       

#                                                                              

# Changelog:                                                               

# Initial state                                                               

# Version 1.0                                                               

#####################################################

 

#####################################################

#Okay, before I begin, I need to know some stuff….

# How do I get into the database, and where do I go when I get there?

user=”xxxx”

passwd=”xxxx”

echo “setup login”

 

database=”dbname”  #the database i’m working with

tableprefix=”tablename” #the table that the live data is in

mergetable=”toptable” #the “merge” engine table

keycolumn=”id”  #the unique identifier in the live log table

logrotateday=”Monday” #the day I should rotate the logs

logretain=2 #how many weeks do we want under the merge table?

 

#Who do I send the “oh dear I’m not working” email to?

alert_email=”email@null.com”

echo “setup vars”

 

# Where do you want me to put the file with the table listing?

listpath=”/path/to/writeable/tempfile/” #path to mergetable listing file

listfile=”merge_tables.txt” #filename of mergetable listing

listfqp=”${listpath}/${listfile}” #file storing the merge tables

mysql=”/path/to/mysql/bin/mysql -u$user -p$passwd $database” # execute MySQL

 

# Where do you want me tell you about all the bad shit?

qrylogdate=`date +%Y-%m-%d` #date for use in queries.

errlog=”/path/tp/error_log_”$qrylogdate”.err”

echo “setup paths and files”

# If bad stuff happens, I’m going to do this…

function report_error(){

echo “Log rotation script exited” >> $errlog

tail -n 10 $errlog | mail -s “log rotation script has failed” “$alert_email”

 

}

echo “running….”

####################################################

# Now I start doing stuff…

 

daytoday=`date +%A` #what day of the week is it?

#before any new tables are added, get the latest one.

lasttable=`grep “^$tableprefix” $listfqp | tail -n 1 | sed ’s/’${tableprefix}’_//g’`

newtable=$lasttable

oldtable=$lasttable

echo “${lasttable} , ${newtable},${oldtable}”

if [[ $logrotateday == $daytoday ]]

then

echo “It’s ${daytoday}! Lets create a new table.”

 

#Create new table. with _<var> suffix.

newtable=`expr $lasttable + 1`

echo “$newtable”

$mysql -e”CREATE TABLE ${tableprefix}_${newtable} like ${tableprefix}_${oldtable}” 2>> $errlog

if [ "$?" != 0 ]

then

echo “failed at table creation” >> $errlog

report_error

exit

fi

 

# get a list of all the archive log tables, and insert into the

# merge_log_tables.txt file

echo “Writing merge table listing to file”

$mysql -e”show tables like ‘${tableprefix}_%’” > $listfqp 2>> $errlog

# parse file to take 6 most recent tables and create CSV list.

mergelist=`grep “^${tableprefix}_” $listfqp | tail -n 6 | awk ‘{ ORS=”,” ; print $0 }’|sed -e ’s#,$#n#g’`

echo $mergelist

# modify the master log archive table to use only the latest 6 log tables.

echo “altering $mergetable with new union list”

$mysql -e”alter table $mergetable union($mergelist)” 2>> $errlog

if [ "$?" != 0 ]

then

echo “failed at master archive update” >> $errlog

report_error

exit

fi

fi

# use a “select into” to copy all records inserted between the last entry in the

# archive, and midnight last night.

echo “copying data into ${tableprefix}_${newtable}”

$mysql -e”insert into ${tableprefix}_${newtable} select * from $tableprefix where id < (select max(${keycolumn}) from ${tableprefix})”  2>> $errlog

if [ "$?" != 0 ]

then

echo “failed at ’select * into…..’” >> $errlog

report_error

exit

fi

 

# and delete this, and all preceding records from the live log.

echo “deleting copied data from $tableprefix”

$mysql -e”delete from $tableprefix where $keycolumn<=(select max($keycolumn) from ${tableprefix}_${newtable})” 2>> $errlog

if [ "$?" != 0 ]

then

echo “failed at ‘deletion from live logs…..’” >> $errlog

report_error

exit

fi

echo “All done”

# All Done…  Bye bye

############################################