How to Periodically Backup Your Databases to Git

Context

Every week, I spend many hours on my blog to write a few articles that may be useful to the community or to a future me. Even though the value of my website continues to increase every time I create an article, I could have lost it completely if I had been unlucky until recently. 😨

Why? I hadn't taken to time to set a periodic backup of my databases. I haven't even activated the weekly backup of my droplet that DigitalOcean offers me for only $4.80 per month.

Last month I finally turned it on, but since it's weekly, I could still lose a week of work at most. It's better than two years, but it was still too much for me... 😕

It was time to find a better way to back up my databases periodically! 😎

Solution

There are probably many ways to save a database server, but in the end, I choose to save it as multiple SQL dumps in a Git repository.

Why choose Git instead of normal storage such as Dropbox, Microsoft OneDrive or Google Drive? Simply because it's free and comes with history! If something goes wrong during an update of one of the software I use, I can easily recreate a database with the previous data. 🧐

Are you ready to learn how I did it?

Step 1 - Set up a Git repository

Go to your favourite repository manager, GitLab or GitHub, and create a new empty Git repository.

As you probably already know them, I will not describe here the steps to create a new repository. 😉

Already done? Let's move on to the second step!

Step 2 - Create the directories

You need to decide where you are going to create the SQL dumps for each of your databases. Personally, I choose /var/opt/databases-backups on my server, but it could be anything.

And since my infrastructure currently has two types of database servers, MariaDB and MySQL, I'll also create two more directories to separate the databases in their place.

Let's create those directories!

mkdir -p /var/opt/databases-backups
mkdir -p /var/opt/databases-backups/mariadb
mkdir -p /var/opt/databases-backups/mysql

The root folder will serve as your Git repository, so let's initiate it!

cd /var/opt/databases-backups
git init --initial-branch=main
git remote add origin git@gitlab.com:benjaminrancourt/databases-backups.git

Step 3 - Create an SQL configuration file

As we want to easily manage access to our SQL servers, we will create an options file to store your credentials.

[mysqldump]
password=ThisIsThePassword
user=root

Step 4 - Mount new Docker volumes

Before creating our script to back up our databases, we need to allow our containers to access the directories and the SQL option file we created earlier.

Let's mount them!

version: "3.8"

services:
  mariadb:
  	image: mariadb:10.8.3
    volumes:
      - /var/opt/databases-backups/mariadb:/databases-backups
      - /var/opt/mariadb:/var/lib/mysql
      - /var/opt/mariadb.cnf:/etc/my.cnf:ro
 
  mysql: 	
    image: mysql:8.0.29-debian
    volumes:
      - /var/opt/databases-backups/mysql:/databases-backups
      - /var/opt/mysql:/var/lib/mysql
      - /var/opt/mysql.cnf:/etc/my.cnf:ro

Step 5 - Create a Bash script

All the magic will be done in a special bash script. 🧙‍♂️

You can create the /var/opt/databases-backups/backup-data.sh file on your server with the below content.

It has been heavily commented on, so you should be able to understand it easily and make some adaptations if necessary. 😉

#!/bin/bash

# This script periodically saves databases dump to a GitLab repository that as
# been previously set up.

# To transfer this script to DigitalOcean server, use the following command:
# scp backup-data.sh USER@SERVER:/var/opt/databases-backups

# To install this script as a cron job:
# crontab -e
# */15 * * * * /var/opt/databases-backups/backup-data.sh

# List of MySQL servers that are in Docker
SERVERS=("mariadb" "mysql")

# Iterate on each server
for SERVER in "${SERVERS[@]}"; do
  echo "Beginning to dump databases for the ${SERVER} server..."

  # Find the container name to be able to run commands in it
  CONTAINER_NAME_PREFIX="phpmyadmin_${SERVER}"
  CONTAINER="$(docker ps --format='{{.Names}}' | grep "${CONTAINER_NAME_PREFIX}")"

  if [ -z "${CONTAINER}" ]; then
    echo "The container with name ${CONTAINER_NAME_PREFIX} was not found on this server ($(hostname))... :("
    continue
  fi

  # Obtain the list of databases names that we want to dump
  case "${SERVER}" in
    "mariadb")
      DATABASES=(
         "unami"
      )
    ;;

    "mysql")
      DATABASES=(
         "ghost"
      )
    ;;
  esac

  # Remove previous backup to not keep databases that we have removed from the script
  LOCAL_DIRECTORY="/var/opt/databases-backups/${SERVER}"
  echo "Removing old databases dumps (${LOCAL_DIRECTORY})..."
  rm -rf "${LOCAL_DIRECTORY:?}"/*

  # Iterate on each database
  for DATABASE in "${DATABASES[@]}"; do
    echo "Database dump for ${DATABASE} (${SERVER})..."

    # Create the command that is going to be executed in the container, where /databases-backups
    # is the directory inside the container of the volume we have mounted
    COMMAND="mysqldump ${DATABASE} \
      --quick \
      --single-transaction \
      --skip-dump-date \
      --skip-lock-tables \
      > /databases-backups/${DATABASE}.sql"

    # Execute the command inside the container
    docker exec "${CONTAINER}" sh -c "${COMMAND}"
  done

  echo "All databases has been dumped for ${SERVER}!"
  echo ""
done

# Go to the local directory which contains all databases backups
cd /var/opt/databases-backups || exit

# Add all changes to Git
git add .

# Commit them with the current date YYYY-MM-DD HH:MM
git commit -m "$(date +"%F %T") - Databases backups"

# Push the changes to GitLab
git push origin main

Once the script is created, don't forget to make it executable and test it!

# Make it executable
chmod +x backup-data.sh

# Test it by running it
./backup-data.sh

# Check your Git repository and make sure there is a new commit with the databases dumps

Step 6 - Create a new cronjob

Go to your server and type crontab -e to add a new cron.

# Connect to your server with ssh
ssh USER@SERVER

# Edit or create the cron file
crontab -e

0 3 * * * /usr/bin/docker system prune -f
*/15 * * * * /var/opt/databases-backups/backup-data.sh

Conclusion

If you performed all the previous steps, your database should now be backup every 15 minutes! It's nice, isn't it? 😋

Do you have other ways to back up your databases? 😙