Skip to content

(Optional) Run regular backups

Goal:

set up regular backups of an entire database after population

Requirements:

  • data stored in your database (and/or MinIO)

Steps:

  1. Create a config file with your MySQL credentials
  2. Write your backup script
  3. Schedule your backups with a cron job

1. Create a config file with your MySQL credentials

You shouldn't save your MySQL credentials directly in your backup script, as this can pose a security risk. Instead, we'll make a separate config file to save your credentials, and then restrict access to the root user only.

Create a config file called .my.cnf:

touch .my.cnf
sudo nano .my.cnf

Now add your data to that file: 

bash
# needed for dump
[mysqldump]
user = root
password = <password>

# needed for restore
[mysql]
user = root
password = <password>

Be sure to change the file's permissions to restrict access to the root user only:

chmod 600 .my.cnf

2. Write your backup script

Create a shell script backup.sh:

touch backup.sh
sudo nano backup.sh

Add the following lines to your script. Be sure to update the paths accordingly (for finding your config file, and where you want to send the backup to). Ideally you should send your backups to a separate piece of hardware than where your database is running, or store them in the cloud.

#!/bin/bash

# Update paths accordingly in the following lines:
# --defaults-extra-file=~/path/to/config_file
# > ~/destination/file/for/backups
echo "$(date -u +\%H:\%M) Dumping database..."
mysqldump --defaults-extra-file=~/path/to/config_file/.my.cnf -h 127.0.0.1 -u root --column-statistics=0 --all-databases --skip-triggers > ~/destiantion/file/for/backups/mysql_backup_$(date -u +\%FT\%H\%MZ).sql

# Removes backups older than 30 days
echo "$(date -u +\%H:\%M) Deleting old dumps..."
find ~/backups/mysql/* -mtime +30 -exec rm {} \;

echo "MySQL backup is finished!"

If you're storing data on MinIO as well, you can add the following lines to backup your MinIO data using rsync (sample file paths are given, be sure to again update file paths accordingly):

# Syncs everything from bulk-minio to backup
# -q: does syncronizing quietly (only error outputs will be shown)
echo "$(date -u +\%H:\%M) Archiving MinIO..."
rsync -a ~/minio ~/backups/minio/minio_backup_$(date -u +\%FT\%H\%MZ) -q

# Removes backups older than 30 days
echo "$(date -u +\%H:\%M) Deleting old archieves..."
find ~/backups/minio/* -maxdepth 1  -mtime +30 -exec rm -r "{}" \;

echo "MinIO backup is finished!"

3. Schedule your backups with a cron job

A "cron job" is a scheduled task used in Unix and Unix-like operating systems to automate the execution of scripts at specified times or intervals. We'll set up a cron job to run backups at 1 a.m. server time.

Edit a new crontab:

crontab -e

Add the following lines to the crontab and update the path to your backup script accordingly: 

bash
0 1 * * * /bin/bash ~/path/to/backup/script/backup.sh >/dev/null 2>&1

Whenever a crontab has been edited successfully crontab: installing new crontab will pop up.

You can check the status of your crontab via crontab -l.

Your backups should now be good to go!