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