Thread: MySql FTP server backup script

Hello,
i need script which take dump and zip of all mysql databases from one server machine and put it to another server machine at the end of day for backup.
Regards

Is server other end runs ftp server? If so it will be quite easy to do the same.. let me know

Yes ftp server is running

Here is the simple script, you need to install lftp ftp client. Also setup variables in script :

Code:

#!/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