Making compressed MySQL backups of multiple databases using Cron

Cron JobsI’ve posted a couple of articles now on making MySQL backups using Cron, time-based scheduler, on Linux based hosting.

This is, currently, the last post on this subject. The examples so far have been on backing up single databases, but it is possible to backup multiple databases using one command.

The below example shows the syntax to make a compressed backup of all mySQL databases, 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}' --all-databases | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

The below example shows the syntax to make a compressed backup of multiple named mySQL databases, with the sections to change highlighted (any additional database names can be specified):

/*
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}' --databases {databasename} {databasename} | gzip -c > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

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.

Making folder backups using Cron

Cron JobsAs I mentioned in yesterdays post on ,a href=’https://www.azurecurve.co.uk/2019/12/making-mysql-backups-using-cron/’>making backups of mySQL databases, earlier this year I migrated from a Microsoft Windows based web hosting package to a Linux based one. This meant I had to learn a few new ways of doing things, such as backing up databases or files, on a regular repeating basis.

This is accomplished on Linux web hosts using the Cron time-based scheduler. I needed to create two types of cron jobs; one to backup databases and one to backup files. Yesterdays post was on the databases and todays is on backing up files.

The syntax for backing up a files using Cron jobs will largely be the same on all web hosts.

The below example shows the syntax to backup a files, 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). */
/bin/tar -czvf /home/{username}/backups/backup_{sitename}_$(date +\%Y\%m\%d\%H\%M\%S).tar.gz /home/{username}/{foldername}

Making mySQL backups using Cron

Cron JobsEarlier this year I migrated from a Microsoft Windows based web hosting package to a Linux based one. This meant I had to learn a few new ways of doing things, such as backing up databases or files, on a regular repeating basis.

This is accomplished on Linux web hosts using the Cron time-based scheduler. I needed to create two types of cron jobs; one to backup databases and one to backup files.

The syntax for backing up a MySQL database using Cron jobs will largely be the same on all web hosts.

The below example shows the syntax to backup 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} > /home/{username}/backups/backup_{databasename}_$(date +\%Y\%m\%d\%H\%M\%S).sql