Auto Backup MySQL Database to FTP Server using cURL

db-backup.sh

#!/bin/sh

# Set up all the variables
# https://www.danpros.com/2017/06/auto-backup-mysql-database-to-ftp-server-using-curl

# Database name
databases="db1 db2"

# Current date
date=$(date +"%Y-%m-%d")

# Mysql user, password
user=username
pass=password

# FTP user, password, and host (you can specify the port also eg. ftp.example.com:2002)
ftpUser=username
ftpPass=password
ftpHost=ftp.example.com
ftpFolder="backup/"

# Local backup folder
bPath="/var/backups/databases"

# Create the backup dir if doesn't exist
if [ ! -d $bPath ]; then
    mkdir -p $bPath
fi

# Delete backup file older than 3 days (local backup)
find $bPath/*.sql.gz -mtime +3 -exec rm {} \;

# Backup the database
for db in $databases; do
    # Database backup name
    file=$db-$date.sql.gz

    # Do the mysql database backup (mysqldump)
    echo "Starting to dump the $db database as $file"
    mysqldump --user=$user --password=$pass $db | gzip -9 > $bPath/$file

    # Upload it via curl
    echo "Starting to upload the $file to FTP server"
    curl --ftp-create-dirs -T $bPath/$file -u $ftpUser:$ftpPass ftp://$ftpHost/$ftpFolder
done

# Clear the cache (not work on OpenVZ)
free && sync && echo 3 > /proc/sys/vm/drop_caches && echo "" && free