List:General Discussion« Previous MessageNext Message »
From:Rob Munsch Date:April 11 2006 6:43pm
Subject:Backup / rotate an ever-growing DB
View as plain text  
Hello,

    I'm using what looks to be a fairly popular cron.daily method of 
backing up a (4.1, MyISAM) database here:

-----

    |#!/bin/bash|
    |#|
    |# a rudimentary stab at MySQL backups to a remote location.|
    |||||# Note:|
    |# The target username receiving the backup is using scponly:|
    |# http://www.sublimation.org/scponly/|
    |# for a shell.|
    |# rmunsch {at} solutionsforprogress.com
    #
    ||FILENAME=`date +%FT%H%M%S`_logs.sql.gz|
    |cd /tmp/logs|
    |mysqldump syslog | gzip > $FILENAME|
    |chmod 0400 $FILENAME|
    |`scp -p ./"$FILENAME" "someone@somehost:$FILENAME"` > /dev/null 2>&1

    |

(the excessive granularity of the timestamp is due to my messing around 
with the thing while setting it all up, of course).
The catch is that it's a centralized logserver.  The size of the log, 
therefore, is ever-growing.
I've poked around the lists, the docs, and google, and see a number of 
methods for purging, deleting, row-pruning, dropping, and otherwise 
spring cleaning, etc.

What i *don't* have is a clear idea on how to walk the line between 
overlap and data loss, without falling off it.  How can i best sync up 
some kind of rotation in the live database itself?  I've been assuming 
that something that'll kill all rows with a timestamp < the current date 
of backup would be best; there will be some duplication between the end 
of X and the start of X+1, but I can live with that.

If there's a more efficient way of going about this that i've missed, 
though, i'd love to hear about it before i hack my way through this 
solution...||

||

-- 
Rob Munsch
Solutions For Progress IT

Thread
Backup / rotate an ever-growing DBRob Munsch11 Apr