Automatic MySQL / PostgreSQL Backups with a Shell Script and Cron Job
In this video, we'll go over both backing up and restoring a database with a few lines of shell scripting and 2 cron jobs.
Having automated backups of your database is a very good idea. We’ll set things up so that we can control the backup interval and how many copies of our backups we’ll keep around.
Besides backups, it’s nice to be able to have a stress free way to restore a backup. In the end we’ll have 2 scripts. One for backups and another for restoring.
This video goes over code I’m using on a production server to backup the database of a tool I’m using to help manage my email list. The working example is MySQL but it can be easily modified for PostgreSQL and other databases too. The gist has a PostgreSQL example.
# Going Over Everything
Timestamps
- 0:44 – Sendy is the app we’re backing up, which uses MySQL / MariaDB
- 1:44 – Using mysqldump to save a compressed version of the database backup
- 4:00 – Parsing out DB credentials from a file using grep, cut and sed
- 8:20 – Demonstrating how the backup script works when calling it manually
- 9:32 – Scheduling the backup to happen once a day with a cron job
- 12:13 – Creating a separate backup rotation cron job to delete old backups
- 14:20 – Going over how mtime works and how to set a custom time for testing
- 16:23 – Creating another script to restore your database
- 22:02 – Modifying things for PostgreSQL
- 22:51 – When disaster strikes it’s nice to have scripts to help restore things quickly
- 23:30 – Potentially modifying things to write to block storage or an S3 bucket
Code
Here’s the gist that includes both the MySQL and PostgreSQL scripts and cron jobs.
Reference Links
- https://sendy.co
- https://digitalocean.com
- https://nickjanetakis.com/blog/using-diff-process-substitution-and-pipes-to-solve-a-real-problem
- https://crontab.guru
How are you backing up your databases in production? Let me know below.