FTP backup scipt for all the mysql databases

+ First, we would need to add the FTP login details in .netrc file. If this file is not present then you can create new .netrc file. Please note that the permission of this file should be 600.

——————-
machine IPADDDRESS
login username
password password
——————-

+ Create a backup script and put the following content in the file.

————————
!/bin/bash
### MySQL Setup ###
MUSER=”root”
MPASS=”mysqlpassword”
MHOST=”127.0.0.1″
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
BAK=”/backup/mysql”
NOW=$(date +”%F”)
DEL=$(date –date=’4 days ago’ +%F)
### FTP Server Info ###
FTPU=”FTPusername” # ftp user
FTPP=”FTPpassword” # ftp password
FTPS=”IPaddress” # ftp server name/ip address

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

DBS=”$($MYSQL -Bse ‘show databases’)”
for db in $DBS
do
cd $BAK
$MYSQLDUMP $db >$db.sql
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
lftp -u $FTPU,$FTPP -e “cd mysql;rmdir $DEL;quit” $FTPS
echo “Completed”
————————

+ Give the execute permission to the script and then execute the script. Please make sure that there is no error during the execution.

+ Add the script in cron to run on daily basis.