MySQL Forums
Forum List  »  Merge Storage Engine

Re: Automatic Merge table
Posted by: Jin No Kim
Date: March 20, 2007 07:06AM

On *ix, use something similar to this AFTER you've created the initial merge specification:

[start make_merge.sh]
#!/bin/sh

#
# use your DB Directory
#

cd /var/lib/mysql/DBName

#
# assuming your tables are named something like:
# my_monthly_log_name_02 for February
#
# Assumes merge table is named merge_table
#
# *adjust as necessary*
#
# assumes you have GNU awk, but others may work as well
#

ls my_monthly_log_name_??.frm | awk -F '.' '{print $1}' > merge_table.MRG

#
# Assumes mysql runs as user mysql and there is a valid mysql group
#
# like everything else, adjust as necessary.
#

chown mysql:mysql merge_table.MRG
#
# Alternatively,
#
#chown mysql merge_table.MRG
#chgrp mysql merge_table.MRG

#
# The flush tables isn't strictly necessary, but makes the whole OS
# modification of the Merge spec much more reliable.
#
# you can use any user that has permission to execute the command,
# replace "your_pass" with the user's pass
#

mysql -h localhost -u root -pyour_pass -e "flush tables;"

[end make_merge.sh]


Call your script from cron on the first or second day of the month and it will
include all of the tables listed by the ls command. If you are picky about your
ls spec, you can get what you are asking for.


To do the same on Windows XP or later is a bit trickier, but I'll wing it.
This *MAY* work (for values of MAY that include probably won't):

***Assumes you have the "cut.exe" command from the zip file at the link some
place in your search path, or the MySQL DB directory***

http://sourceforge.net/project/showfiles.php?group_id=9328&package_id=9393

[Start rollup.cmd]
@echo off
rem: replace with your DB Drive and directory

D:
cd \mysql\DB_Name

rem: assumes your log files are named log_file_2007_02
rem: for the table created for February 2007 and that
rem: your merge table is "merge_table"
rem:
rem: Also assumes you have the "cut.exe" command from the
rem: sourceforge link above - the package is UnxUtils.zip
rem: in the directory \usr\local\wbin should be a cut.exe
rem: This *MUST* be in your search path or the mysql db dir
rem:
rem: No promises - just worked for this little test on XP

dir /b log_file_2007_*.frm | cut -s -d. -f 1 > merge_table.MRG

rem: A windows mysql user should add a flush tables line below,
rem: but I'll leave it out and say "execute a flush tables from
rem: your favorite query browser before using the new merge spec"
rem: ;)

[end rollup.cmd]

I guess theoretically, you could schedule this with "at", or some other Windows
scheduler, but I wouldn't want to hazard a guess as to how...

Someone may want to check what's included above, I'm a couple of pints into the
evening and my windows scripting is especially rusty.

P.S. - when did they drop cut from Windows?


-JNK

Options: ReplyQuote


Subject
Views
Written By
Posted
7660
November 08, 2006 06:03AM
5683
November 20, 2006 08:48AM
Re: Automatic Merge table
5545
March 20, 2007 07:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.