W W W Wie Niet [.nl]

MySQL dump synchronisation script

mysqldump-sync-gzip.sh

Download: mysqldump-sync-gzip.sh
Filename: mysqldump-sync-gzip.sh , Type : Bash source file, Size : 3.6KB
#!/bin/bash
#
# $Id: mysqldump-sync-gzip.sh,v 1.7 2008/08/06 12:25:40 root Exp root $
#
# mysqldump-syncgzip.sh:        Sync a running MySQL server with MySQLdump-ed files.
#                Author:        Diederik Hamelink.
#
# usage:
# - run this script as cronjob or before an (incremental) backup is taken from the filesystem.
#
# requirements:
# - this script needs to be executed with root.
# - root needs full MySQL access without password.
#
# features:
# - update each table in a seperate file.
# - only update changed table. tables without valid Last Changed field are always backuped.
# - timestamps in dumpfiles attributes are set to "last changed" the MySQL table was updated.
# - temporary dumpfiles are used so that there is alwasy a full dump available on the filesystem.
#
# todo:
# - Test how are stored procedures are stored, and howto backup these.
# - Able to run with configurable username [+pasword] [+host]
# - ??

# CONFIG:
#
# SQL_DATA: location where the MySQL dumps are stored.
#
SQL_DATA=/var/backups/mysqldump-sync/

#
# DO NOT EDIT BELOW THIS LINE.
#

abort() {
        echo "$1";
        exit 1;
}




createDumpTable() {
        DATABASE=$1
        TABLE=$2
        DUMPFILE="$SQL_DATA/$DATABASE/$TABLE.sql"
        DUMPFILEGZ="$SQL_DATA/$DATABASE/$TABLE.sql.gz"
        DUMPDIR="$SQL_DATA/$DATABASE/"

        # get UPDATE_TIME, and remove white-spaces
        UPDATE_TIME=$(mysql -e "show table status from $DATABASE where Name=\"$TABLE\"" --vertical | grep Update_time | cut -f2- -d :)
        UPDATE_TIME=$(echo $UPDATE_TIME | tr -d ' :-'| cut -c-12)
        
        mkdir -p $DUMPDIR
        # actual dump of table
        mysqldump $DATABASE $TABLE > $DUMPFILE.temp
        
        # set Filesystem attribute
        if [[ $UPDATE_TIME != "NULL" ]]; then
                touch -t $UPDATE_TIME $DUMPFILE.temp
        fi

        # gzip the sql file.
        gzip $DUMPFILE.temp
        # move the file to it's correct location
        mv $DUMPFILE.temp.gz $DUMPFILEGZ
        
}


#
# Main:
#
if [[ $UID -ne "0" ]]; then
        abort "$(basename $0): needs root permisions.";
fi

echo "Starting $(basename $0) into $SQL_DATA."

#
# get databases
#
DATABASES=$(mysql -e "show databases" | awk -F"\t" '{if(NR!=1) print $1}')

#
# remove database dumps of removed databases
#
if [[ -d $SQL_DATA ]] ; then
        FS_DATABASES=$(find $SQL_DATA -maxdepth 1 -mindepth 1 -type d -printf '%f ')
        for FS_DATABASE in $FS_DATABASES; do 
                echo $DATABASES | tr " " "\n" |  grep "^$FS_DATABASE$" > /dev/null  || (echo $FS_DATABASE removed; rm -rvf $SQL_DATA/$FS_DATABASE)
        done
fi

#
# update/create database dumps
#
for DATABASE in $DATABASES; do 
        echo "db: $DATABASE";
        #
        # get tables
        #
        TABLES=$(mysql -e "show tables" $DATABASE | awk -F"\t" '{if(NR!=1) print $1}')

        #
        # remove table dumps of removed tables
        #
        if [[ -d $SQL_DATA/$DATABASE ]]; then
                FS_TABLES=$(find $SQL_DATA/$DATABASE -maxdepth 1 -mindepth 1 -type f -printf '%f\n' | cut -f1 -d. )
                for FS_TABLE in $FS_TABLES; do 
                        echo $TABLES | tr " " "\n" |  grep "^$FS_TABLE$" > /dev/null  || (echo $FS_TABLE removed; rm -rvf $SQL_DATA/$DATABASE/$FS_TABLE.* )
                done
        fi
        
        for TABLE in $TABLES; do
                echo -n "backup $DATABASE.$TABLE: "
                DUMPFILE="$SQL_DATA/$DATABASE/$TABLE.sql.gz"
                if [[ ! -f $DUMPFILE ]]; then
                        echo -n "create "
                        createDumpTable $DATABASE $TABLE
                        echo "done"
                else 
                        UPDATE_TIME=$(mysql -e "show table status from $DATABASE where Name=\"$TABLE\"" --vertical | grep Update_time | cut -f2- -d :)
                        UPDATE_TIME=$(echo $UPDATE_TIME | tr -d ' :-'| cut -c-12)
                        FILE_LAST_CHANGED=$(stat -c '%y' $DUMPFILE   | cut -f 1 -d .|  tr -d ' :-'| cut -c-12);

                        if [[ "$UPDATE_TIME" > "$FILE_LAST_CHANGED" ]] ; then
                                echo -n "update "
                                createDumpTable $DATABASE $TABLE
                                echo "done"
                        else
                                echo  "uptodate"
                        fi
                fi
                
        done

done