Wednesday, May 12, 2010

backing up a mysql DB incrementally using mysql dump

This script will backup your mysql DB using mysqldump, but because mysql dump locks the entire DB, this script only backs up one table at a time, thus only locking one table at a time. 
 
 
#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# Modified by dfish to use rsync
# ---------------------------------------------------------------------

echo `date`

#########################
######TO BE MODIFIED#####

### System Setup ###
BACKUP=/backups/

### MySQL Setup ###
MUSER="foo"
MPASS="bar"
MHOST="localhost"

### REMOTE SETUP ###
RUSER="root"
RHOST="192.168.0.70"
RPATH="/backups/db/."

### MYSQL PATHS ### 
MYSQL="/home/mysql/mysql-standard-4.1.13-pc-linux-gnu-i686/bin/mysql"
MYSQLDUMP="/home/mysql/mysql-standard-4.1.13-pc-linux-gnu-i686/bin/mysqldump"
 
 
######SHOULD NOT NEED TO MAKE MODIFICATION BELOW#####
#########################################

### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
FTP="$(which ftp)"


### Today + hour in 24h format ###
NOW=$(date +"%A")

### Create hourly dir ###
rm -rf $BACKUP/$NOW
mkdir $BACKUP/$NOW

### Get all databases name ###
DBS="ihp"
#DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do

### Create dir for each databases, backup tables in individual files ###
  mkdir $BACKUP/$NOW/$db

  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
  do
#    FILE=$BACKUP/$NOW/$db/$i.sql.gz
#    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
    FILE=$BACKUP/$NOW/$db/$i.sql
    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i > $FILE
  done
done

### Compress all tables in one nice file to upload ###

ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW

$TAR -cvzf $ARCHIVE $ARCHIVED

### Dump backup using FTP ###
#cd $BACKUP
#DUMPFILE=$NOW.tar.gz
#$FTP -n $FTPS <
#quote USER $FTPU
#quote PASS $FTPP
#cd $FTPD
#mput $DUMPFILE
#quit
#END_SCRIPT

### Delete the backup dir and keep archive ###

rm -rf $ARCHIVED

### rsync the directory with the backup machine
### This preassumes that you have made it so no password is needed to 
### login to backupmachine
rsync -Cavz $BACKUP $RUSER@$RHOST:$RPATH