This article teaches a method for automatically backing up MySQL databases daily and uploading them to an FTP server for offsite backup, applicable to cloud servers running various Linux operating systems.

First, create a backup directory on the cloud server.

mkdir /backups
cd /backups

Then, edit the backup script.

nano backupdb.sh

The content of the backup script is as follows, with the first part's parameters needing adjustments based on actual conditions.

#!/bin/bash

############### Infos - Edit them accordingly  ########################

DATE=`date +%Y-%m-%d_%H%M`
LOCAL_BACKUP_DIR="/backups"
DB_NAME="database_name"
DB_USER="root"
DB_PASSWORD="root_password"

FTP_SERVER="111.111.111.111"
FTP_USERNAME="ftp-user"
FTP_PASSWORD="ftp-pass"
FTP_UPLOAD_DIR="/upload"

############### Local Backup  ########################

mysqldump -u $DB_USER  -p$DB_PASSWORD $DB_NAME | gzip  > $LOCAL_BACKUP_DIR/$DATE-$DB_NAME.sql.gz

############### UPLOAD to FTP Server  ################

ftp -nv $FTP_SERVER << EndFTP
user "$FTP_USERNAME" "$FTP_PASSWORD"
binary
cd $FTP_UPLOAD_DIR
lcd $LOCAL_BACKUP_DIR
put "$DATE-$DB_NAME.sql.gz"
bye
EndFTP

After saving the backup script, grant it executable permissions.

chmod +x backupdb.sh

Run the script to test if the database backup is successful and uploaded to the FTP server.

/backups/backupdb.sh

If the backup script functions correctly, you can then set up a cron job to automate daily backups.

Cron jobs are scheduled with the format of minutes (0-59), hours (0-23), day of the month (1-31), month (1-12), day of the week (0-6), followed by the command line. Enter the following command to edit the cron job.

crontab -e

In the cron job editing interface, add the following line of code.

30 02 * * * /backups/backupdb.sh

The above code means the backup script will execute daily at 2:30 AM, and adjustments can be made as needed. Finally, save the cron job to activate it.