danpros.com
Posted on
• Server

Auto Backup MySQL Database to FTP Server using cURL

Author

RAID is not a backup. RAID arrays can fail just like single drives, RAID is designed for fault tolerance NOT backups.

I wrote this script to backup MySQL databases and than upload it to FTP server using cURL.

Create db-backup.sh and make it executable:

vi /root/db-backup.sh

chmod +x /root/db-backup.sh

Copy paste the following and change it with your credentials:

#!/bin/sh

# Set up all the variables

# 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

Create cron job and execute the script. On this example the script will run everyday @ 2am.

crontab -e

Write the crontab command and save it.

 00 02 * * * /root/db-backup.sh

I have simple backup system for my databases and files but it safe enough.

  1. The FTP server is in my Windows 10 dedicated server and I use it mostly for backup and RDP (HW Raid 1).

  2. Sync the uploaded file automatically to my Google Drive with unlimited storage and to my ownCloud server (also use HW Raid 1).

Related posts