#!/bin/bash
### MySQL Setup ###
MUSER="root"
MPASS="PASSWORD"
MHOST="127.0.0.1"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
GZIP="$(which gzip)"
### FTP Server Info ###
FTPU="USER" # ftp user
FTPP="PASSWORD"  # ftp password
FTPS="10.1.12.1" # ftp server name/ip address
NOW=$(date +"%d-%m-%Y")

[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BAK/$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

# make sure ftp server has mysql directory to store database
lftp -u $FTPU,$FTPP -e "mkdir mysql/$NOW;cd mysql/$NOW; mput /backup/mysql/*; quit" $FTPS