Learn Docker With My Newest Course

Dive into Docker takes you from "What is Docker?" to confidently applying Docker to your own projects. It's packed with best practices and examples. Start Learning Docker →

Automatic MySQL / PostgreSQL Backups with a Shell Script and Cron Job

blog/cards/automatic-mysql-and-postgresql-backups-with-a-shell-script-and-cron-job.jpg

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.

Quick Jump: Going Over Everything

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.

How are you backing up your databases in production? Let me know below.

Never Miss a Tip, Trick or Tutorial

Like you, I'm super protective of my inbox, so don't worry about getting spammed. You can expect a few emails per month (at most), and you can 1-click unsubscribe at any time. See what else you'll get too.



Comments