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:
Configuring AWS permissions
Once done, you'll need to configure AWS with your credentials, which can be found by:
- Signing in to AWS.
- Clicking on your profile on top right.
- Selecting "Security credentials".
- Then clicking "Access keys".
- 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:
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:
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:
Then add the following line to it, changing the path to where your script is located:
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 🎉