Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL backup script on steroids
#!/bin/bash
CMD_MYSQL="/usr/bin/mysql"
CMD_MYSQLDUMP="/usr/bin/mysqldump"
CMD_GZIP="gzip"
CMD_MKDIR="/bin/mkdir"
CMD_TAR="/bin/tar"
CMD_RM="/bin/rm"
CMD_S3='/root/s3cmd/s3cmd --access_key=XXXXXXXXXXXX --secret_key=YYYYYYYYYYYYY put --reduced-redundancy' # s3cmd with some options
STORAGE_FODER="s3://" # the destination bucket
MYSQL_USER="" # mysql user with enough grants to read whatever you want to backup
MYSQL_PASS="" # mysql password
MYSQL_HOST="localhost" # mysql host
BACKUP_SERVER=`hostname`
BACKUP_DIR="/var/backup/database"
BACKUP_CONTENT="/tmp/dumps"
BACKUP_NAME=`LC_ALL=en_US.utf8 /bin/date +%A | tr "[:upper:]" "[:lower:]"`
function _log {
echo -n `date`
echo " - $1"
}
function _dump {
_log "dumping $1.$2"
STORAGE_DIR="$3/$1"
$CMD_MKDIR -p $STORAGE_DIR
$CMD_MYSQLDUMP -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS $1 $2 | $CMD_GZIP > $STORAGE_DIR/$2.sql.gz
}
_log ":: starting backup proccess"
_log "removing old dumps"
$CMD_RM -r $BACKUP_CONTENT/*
_log "starting backup"
MYSQL_DBS=`$CMD_MYSQL -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS -B -N -e "show databases"`
for database in $MYSQL_DBS; do
_log "exporting \"$database\" database"
TABLES=`$CMD_MYSQL -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS -B -N -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$database'"`
for table in $TABLES; do
_dump $database $table $BACKUP_CONTENT
done
done
_log "packaging backup"
$CMD_TAR -zcf $BACKUP_DIR/$BACKUP_SERVER.tar.gz $BACKUP_CONTENT
_log "uploading to S3"
$CMD_S3 put "$BACKUP_DIR/$BACKUP_SERVER.tar.gz" "$STORAGE_FODER/$BACKUP_NAME.tar.gz"
_log ":: backup proccess finished"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment