A Cool Script To Backup Your MySQL / MariaDB Databases Using FTP

In this script an automatic FTP backup will pickup up your sql.tar.gz and upload the file to a remote FTP server running CentOS.

So, you may wonder how all this works and how to automate entire procedure to be able to upload file /tmp/backup/sql.tar.gz?

In the most simple scenario you can use a standard FTP client to transfer the backup file on the remote server. 

Here is a quick script to upload file:

#!/bin/sh
USERNAME="your-ftp-user-name"
PASSWORD="your-ftp-password"
SERVER="your-ftp.server.com"
 
# local directory to pickup *.tar.gz file
FILE="/tmp/backup"
 
# remote server directory to upload backup
BACKUPDIR="/pro/backup/sql"
 
# login to remote server
ftp -n -i $SERVER <

Make sure script has executable permissions:

chmod +x /path/to/your/ftp.backup.script.sh

Setup a cron job to run script at 23:59 (24 hr clock time) times:

59 23 * * * /path/to/your.ftp.backup.script.sh

and that’s it.

Also, make sure you have lftp client installed:

# yum install lftp

Here is the shell script that will dump all mysql database and upload them via lftp program:

#!/bin/bash
### MySQL Server Login Info ###
MUSER="root"
MPASS="MYSQL-ROOT-PASSWORD"
# mysql server
MHOST="localhost"
 
### FTP SERVER Login info ###
FTPU="FTP-SERVER-USER-NAME"
FTPP="FTP-SERVER-PASSWORD"
FTPS="FTP-SERVER-IP-ADDRESS"
 
 
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
GZIP="$(which gzip)"
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
 
lftp -u $FTPU,$FTPP -e "mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

Setup a cron job that will run the scrip automatically at midnight:

# crontab -e

and put:

@midnight /path/to/mysql.backup.sh >/dev/null 2>&1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>