How to Backup Databases and Files, Rotate and Rsync to Remote

We should always schedule backups for our website files and databases but should never keep the backups only in the server which your websites and databases reside in. If you are on solid cloud providers like Amazon Web Services (AWS) or VPS providers like DigitalOcean, perhaps you can risk storing backups locally. I can’t imagine AWS or DigitalOcean informing me that my server had crashed or data loss due to technical glitches. Another reason could be you are on budget and had subscribed to only one server (the one hosting your websites).

Backup Strategy – Mirror Local Backup Directory to Remote

This is a straightforward backup strategy. First, I generate my backups and copy them into a backup directory on a daily basis. Second, I activate file rotation to keep 30 days of backups. Finally, initiate a sync to create a mirror of the entire backup directory to remote for safekeeping.

Step 1 – mysqldump to Backup Databases

We backup the WordPress database (wordpress) with mysqldump and append today’s date in YYYY-MM-DD with $(date +\%Y-\%m-\%d) to the SQL files. E.g. wpdb-2020-04-18.sql

docker exec db mysqldump -udbuser -p'dbpwd' wordpress > backups/wpdb-$(date +\%Y-\%m-\%d).sql

Step 2 – tar to Backup Files

Next, we backup the entire WordPress directory (themes and plugins etc.) with tar and append today’s date as well. E.g. wpfiles-2020-04-18.tar.gz

tar zcvf backups/wpfiles-$(date +\%Y-\%m-\%d).tar.gz docker-project/wordpress

Step 3 – Backup Rotation (Delete backups older than X days)

The server disk space that you subscribed to is finite. It can be 5 GB or as much as 100 GB but you should not waste them in case you have plans for future projects e.g. a photo gallery with many photos. Think about this – what is the oldest backup you want to archive so that you will feel reassured? We can delete backups older than 30 days with -mtime +30 and you will only keep the latest 30 backups at any one time.

find backups/* -mtime +30 -delete

Step 4 – rsync to Remote Backup Directory

scp and rsync are commonly used to copy files to remote server. I will not go into the differences between them but will show how to use both commands. You will need to specify -P 2222 for non-standard port (22) transfer. wp*-$(date +\%Y-\%m-\%d) will include both database (e.g. wpdb-2020-04-18.sql) and file (e.g. wpfiles-2020-04-18.tar.gz) backups.

scp -P 2222 backups/wp*-$(date +\%Y-\%m-\%d).* remoteuser@ip:backups

However, I chose rsync instead to complete the backup strategy in this tutorial. This is because the aim of the backup strategy is to mirror the local backup directory to the remote backup directory. Furthermore, since the backups rotation executes locally, rsync is able to delete files older than 30 days from the remote server with –delete option.

rsync -a --delete backups/ -e 'ssh -p 2222' remoteuser@ip:backups

Cron Jobs Overview

Let’s recap. We have four independent jobs and they are backup database, files, rotation and sync.

# Backup WP database
1 3 * * * docker exec db mysqldump -udbuser -p'dbpwd' wordpress > backups/wpdb-$(date +\%Y-\%m-\%d).sql
# Backup WP files
2 3 * * * tar zcvf backups/wpfiles-$(date +\%Y-\%m-\%d).tar.gz docker-project/wordpress
# Delete backups older than X days (backup rotation)
4 3 * * * find backups/* -mtime +30 -delete
# Transfer backup to remote. Delete files at dest if not exist at source
5 3 * * * rsync -a --delete backups/ -e 'ssh -p 2222' remoteuser@ip:backups

Conclusion

Everyone should backup their data and there are many ways to formulate a backup strategy. Most cloud providers can create snapshots of your server for a small storage fee. Git is another efficient backup system because each incremental backup is a single file. Compared to a full backup it is faster since unchanged data is not touched. For now, I am satisfied with the simplistic approach towards a backup, rotate and transfer strategy.