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