Making compressed MySQL backups using Cron

Cron JobsEarlier this month I posted an article on making MySQL backups using Cron, time-based scheduler, on Linux based hosting.

Since then I’ve realised that I have two databases which are well over 1GB in size and have now looked into making compressed backups.

The below example shows the syntax to make a compressed backup of a mySQL database, with the sections to change highlighted:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
/usr/bin/mysqldump -u {username}-p'{password}' {databasename} | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

This is only a little different to the uncompressed backup syntax, but results in a file of much smaller size.