Skip to content

Instantly share code, notes, and snippets.

@frecklebit
Created December 13, 2013 19:04
Show Gist options
  • Save frecklebit/7949431 to your computer and use it in GitHub Desktop.
Save frecklebit/7949431 to your computer and use it in GitHub Desktop.
MySQL database backup via shell script
#!/bin/bash
### MySQL Server Login Info ###
MUSER="ROOT_USER"
MPASS="ROOT_PASSWORD"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
### FTP Server Login Info ###
USEFTP=false
FTPU="FTP_USER"
FTPP="FTP_PASSWORD"
FTPS="FTP_SERVER"
### Tools ###
GZIP="$(which gzip)"
TAR="$(which tar)"
NOW=$(date +"%Y-%m-%d")
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d "$BAK/$NOW" ] && mkdir -p "$BAK/$NOW"
DBS="$($MYSQL -u $MUSER -p$MPASS -Bse 'show databases'|egrep -vi 'information_schema|performance_schema|mysql|test')"
for db in $DBS
do
FILE=$BAK/$NOW/$db.$NOW-$(date +"%T").sql.gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
### Compress Directory ###
cd $BAK
FILE="mysql-backup-$NOW.tar.gz"
$TAR -zcf $FILE $NOW
### Clean up ###
rm -r $BAK/$NOW
if $USEFTP ; then
lftp -u $FTPU,$FTPP -e "mkdir /mysql/$NOW; cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment