Configuring automatic SingleStoreDB Backups

July 8, 2022 (2y ago)

I've been using SingleStore DB for my new start-up (Analyse), and it's been fantastic, however I realised sooner or later I was going to need to start backing up our database.

After reading the SingleStore docs, I got an understanding of how we could back-up to S3, but wasn't sure how I could then automate this. So, I wrote a script to do that! It will run an incremental backup every Monday, and a differential backup every other day, this allows for faster backups in turn.

What I'll be using

  • Debian OS (Ubuntu will work too)
  • Cronjob
  • AWS CLI

Installing the AWS CLI

To begin, you'll need to install the AWS CLI if you already haven't, which can be done like so:

apt install awscli

Configuring AWS permissions

Once done, you'll need to configure AWS with your credentials, which can be found by:

  1. Signing in to AWS.
  2. Clicking on your profile on top right.
  3. Selecting "Security credentials".
  4. Then clicking "Access keys".
  5. Then adding a new key.

After this, type aws configure which will bring you a series of options, asking for your credentials, paste this from AWS.

Installation

Next you'll need to choose the folder where you'd like your backup script to be, in my case I'll be going with /home/charlie/aws. Once done, create a file within there called backup.sh (you can name it how you like, I find it's easier to reference). Paste in the following:

/home/charlie/aws/backup.sh
#!/bin/bash
 
# Change these.
DB_NAME=analyse_db
S3_BUCKET=analyse-db-backups
S3_FOLDER=backups
S3_REGION=eu-west-2
 
DOW=$(date +%u)
WEEK_SUFFIX=week_$(date +%W_%Y)
BACKUP_TYPE=$(if [ $DOW -eq 1 ]; then echo "init"; else echo "differential"; fi)
 
sdb-admin create-backup $DB_NAME --repository "s3://$S3_BUCKET/$S3_FOLDER?region=$S3_REGION" --backup-type $BACKUP_TYPE --backup-suffix $WEEK_SUFFIX -y

You'll need to set the database that you wish to use, alongside your S3 bucket and sub-folder to be placed in. Finally, ensure that your region is set to the same one from your AWS console. Once done, you'll need to give the script run permission by doing the following:

chmod +x backup.sh

Automatically scheduling

Next we will add this to our crontab, which will allow us to automatically run this periodically. We can pull up our crontab by running the following:

crontab -e

Then add the following line to it, changing the path to where your script is located:

0 0 * * * cd /home/charlie/aws && bash backup.sh

Now save and exit this (usually ESC and then typing :wq! to save and exit), this will now automatically run the script at midnight (by the servers time), you may change the 0 0 values, such as 30 15 for 3:30pm every day.

Best Practices

  • Configure an IAM user.
  • Ensure the user only has Put* access.

And that's it! You've configured automatic backups to S3 with SingleStore 🎉