The first thing a DBA should do after deploying a database is to ensure that backups are created regularly.

But storing a backup file on the same server where the MySQL server is located would be a big mistake. Therefore, you should create a backup and put it out of the server on which MySQL is located. The simplest solution would be to transfer it to another server via FTP.

Transfer backup to FTP with BASH script

Ubuntu has an FTP client by default, although it is not particularly convenient to work with, especially in a bash script. The LFTP utility is more convenient, and you can install it with two commands.

sudo apt-get update -y
sudo apt-get install -y lftp

To create a backup, compress it, transfer it to FTP and then delete files that are older than 7 days, use the following script:

#credentials to connect to mysql
mysql_user=me
mysql_password=1234

#credentials to connect to ftp
FTPUSER=one
FTPPW=1234                            
FTPSERVER=localhost

#directory that will be created on ftp and the one that will be deleted
TODAY=$(date +"%Y_%m_%d")                   
RMDATE=$(date +"%Y_%m_%d" -d '7 days ago') 

TMPDIR=~/backup
mysql_backup_file=$TMPDIR/backup.sql

echo -n "Database dump"
mysqldump -u $mysql_user -p$mysql_password --all-databases > $mysql_backup_file && 
echo -n "Dump compression"
gzip $mysql_backup_file

echo -n "Uploading files via FTP... "
lftp << EOF
open ${FTPUSER}:${FTPPW}@${FTPSERVER}
cd backups/
mkdir ${TODAY}
cd ${TODAY}
mput -E ${TMPDIR}/*
cd ..
rm -rf ${RMDATE}
bye
EOF
echo "Done."

Now all that remains is to schedule the script for regular execution with cron jobs

crontab -e

and add the following string at the end of the file:

30 22 * * * /home/user/script/mysql_backup.sh

Please note that the above script has a significant drawback – if the launch of the script is skipped, then the old backup will not be removed, and it will remain on FTP.

The solution must be reliable

The worst thing that can happen is that when you need a backup, it turns out that the backup process broke a few months ago and you do not have one.

A script configured in a bash script can stop working for many reasons. Here are some of them:

1. Database password has changed
2. FTP password has changed
3. MySQL settings have changed, which led to backup creation errors
4. Out of disk space (not enough space to create a backup)
5. Network changes related to FTP: FTP server moved, address changed, firewall

Therefore, a solution that makes regular backups should notify you about any such failures and be transparent so that you can check if everything is working at any time.

Any Linux issue can be solved with bash scripts, but creating a truly reliable solution can take a lot of time.

Third-party solutions

SqlBak service is an agent-based service for creating MySQL, PostgreSQL, MongoDB and SQL Server backups. The entire history of backup-related activities is displayed on a single dashboard. And if something goes wrong, the service will notify you immediately.

SqlBak also boasts many useful features:

1. Backup transfer to various cloud storages: Dropbox, Google Drive, OneDrive, Amazon S3, Azure, Backblaze, Wasabi
2. Backup encryption
3. Email notifications
4. Folder backup
5. Automatic backup recovery to another server

To backup MySQL Server with SqlBak, you need to install a thin client on your server. The application can be installed via the standard package managers, please find more details here.

Once you install SqlBak on your server, it appears on your Dashboard page.

sudo sqlbak --add-connection --db-type=mysql --user=root

The application is installed and the connection is set, so it’s time to create a backup job. To do this click the “Add new job” button and choose “Create job.”

Go to the “Select databases” section, and choose all the databases that you want to backup.

The next step is to choose a destination place for your backups. In the “Store backups in destinations” section, choose FTP from the list and add the appropriate settings.

Scroll down to the “Schedule backups” section and set the backup schedule you need. Note, with SqlBak on Linux you can run incremental backups as well.

If you prefer to receive the notifications if your backups were successful or failed, go to the “Send email confirmation section” section and add your email. To receive the notifications on multiple email addresses, specify them separated by a comma.

Below, at your backup job settings page, you can find other interesting and useful settings, such as: Execute After & Before Scripts, Encryption, and Backup Folder.

Another useful option when setting up a backup job is “Restore Tasks,” with which you can configure the restore process to restore the newly created file to a different database server.

You can check your settings by clicking the “Run Now” button, your backup jobs will be immediately started and displayed in the job execution log.