Charlie Joseph
Back to blog

Configuring Automatic SingleStore DB Backups

·2 min read
SingleStoreDevOpsDatabases

I've been using SingleStore DB for my startup (Analyse), and it's been fantastic. However, I realised I needed to start backing up the database.

After reading the SingleStore docs, I understood how to back up to S3, but wasn't sure how to automate it. So I wrote a script. It runs an incremental backup every Monday and a differential backup every other day, allowing for faster backups.

What You'll Need

  • Debian OS (Ubuntu works too)
  • Cronjob
  • AWS CLI

Installing the AWS CLI

First, install the AWS CLI if you haven't already:

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install --bin-dir /usr/local/bin --install-dir /usr/local/aws-cli --update

Configuring AWS Permissions

Next, configure AWS with your credentials:

  1. Sign in to AWS
  2. Click your profile in the top right
  3. Select "Security credentials"
  4. Click "Access keys"
  5. Add a new key

Then run aws configure and paste your credentials when prompted.

Installation

Choose a folder for your backup script. I'll use /home/charlie/aws. Create a file called backup.sh and paste 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

Set the database name, S3 bucket, sub-folder, and ensure your region matches your AWS console. Then give the script run permission:

chmod +x backup.sh

Automatically Scheduling

Add this to your crontab to run the script automatically:

crontab -e

Add the following line, changing the path to your script location:

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

Save and exit (usually ESC then :wq!). This runs the script at midnight server time. You can change 0 0 to other values like 30 15 for 3:30pm.

Best Practices

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

That's it - you've configured automatic backups to S3 with SingleStore.