How to Backup MySQL to AWS S3

Introduction

AWS S3 is the industry leader in cloud storage solution. Storage starts at $0.023/GB/Month, with download costs of $0.09/GB. AWS pricing can get a bit more complex and we recommend all users to read the full pricing. However in our minds they are the leads and when paired with MySQL backups makes a fantastic option for storing backups.

In this guide, we will show you how easy it is to backup MySQL to S3 using SnapShooter. SnapShooter has a free plan for a single database, so you can get started totally for free, just bring the storage!

Signing up for SnapShooter is easy and free -- all you need is an email address.

Once you have an account, you can backup one resource for free daily, with options started from $9 to upgrade for larger resources limits and backup frequency. Click Free Trial in the top right Provide your name, email and a password.

Start backup wizard

Once into the dashboard, you will be greeted by a welcome screen showing you what backup offerings SnapShooter has available. For this guide select, MySQL.

SSH Setup Step

To perform a MySQL backup, you need to configure an SSH connection to a server you wish backups to pipe through; this is within your environment and acts like a jump box between SnapShooter and your MySQL.

1) Configure SSH Connection

You will be asked to provide details of an SSH connection to a server, depending on the backup type you may or may not need root access. (Root access is required when you want to do a file-based backup and backup the whole server)

Set the Backup name to something you will remember. e.g. Production MySQL
From the SSH server select an existing or provide the ssh host (ip or hostname), ssh port (default is 22), and the ssh user
Select an existing SSH key or let SnapShooter generate you a new one

How do I create a new User for SnapShooter?

If you wish to use a new user for backups on your server you can run the following commands as root:

adduser --disabled-password snapshooter

This will generate a user without a password

1) Test SSH Connection

When you press Test and Connect SnapShooter will show the steps to install the key, this involves you pasting the SSH public key into the users authorized_keys

By Hand

SSH into your server
Copy the key we present you
On the server edit the authorizedkeys file, Make sure you edit the correct users authorized keys. SnapShooter will provide you the normal path e.g. for user called SnapShooter /home/snapshooter/.ssh/authorizedkeys

Copy Paste

We allow offer a single copy paste method

Select Single Copy Paste
Login to Server
Copy the command
Paste the command into SSH client connected to the server

Test

When you press test, SnapShooter will connect to your server via SSH to check the connection is good.

SnapShooter will also check the correct packages and libraries are installed on the server, MySQL, tar, curl, unwip, gzip

Common Errors

Permission Denied

1. snapshooter@staging.snapshooter.io: Permission denied (publickey).

This error normally means the server does not have the SSH key installed correctly, try following the steps again

Configure MySQL

Connection

The next step in the process is to configure MySQL backup.

Provide your MySQL Host, if the database is on the same server as the ssh connection, you should use 127.0.0.1, if not change it to the host or IP of the MySQL instance
MySQL Port
MySQL User - You are free to use any user or configure a new one just make sure it it has read access to the databases you want backed up
MySQL password - Your password is stored encrypted and we use it to write a secure my.cnf file on your server

Once ready you can ask SnapShooter to test your MySQL details, we will connect to the database and check we have Auth access

Database

Next step we ask you to select the database you want to backup, depending on your plan features you might also be able to select all databases.

Configure Storage

You need to select the storage provider you wish for your backups to be stored with, we allow users to select from a range of providers and in the guide will focus on S3

If you have already setup the storage provider you want you can select it and proceed if not press Connect new External Storage

Creating a New Bucket

Login to your AWS Console
In top menu select services
Search for S3 and open S3
Press New Droplet button
Choose your bucket name (e.g. s3.snapshooter.io)
Choose your backup region
You can choose a region close to your server to increase speed or further away if you want to mitigate risk
Under Configure Options leave as set
Under Set permissions, leave `Block all public access` checked.
Under Review press create

Creating a New IAM User

Leave S3 and find IAM from the top services menu and search

Press Users on left sidebar

Add User

user name (e.g. snapshooter)
Access Type Programmatic access

Permissions ignore and press Next:tags

Optional Setup Tags

Next Review

Create User

View Details SAVE SECRET KEY, will never be shown again

In IAM find the user list and click on your new user

Press Add Inline Policy

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:DeleteObject", "s3:GetObject", "s3:PutObject", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::BUCKET_NAME/*", "arn:aws:s3:::BUCKET_NAME" ] } ] } { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:DeleteObject", "s3:GetObject", "s3:PutObject", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::s3.snapshooter.io/*", "arn:aws:s3:::s3.snapshooter.io" ] } ] }

Choose JSON Editor

Paste the following:

Replace BUCKET_NAME with the newly created bucket name e.g.

Then press Create Policy (give it a useful name)

Attaching S3 to SnapShooter

Go back to SnapShooter
https://snapshooter.io/dashboard/settings/storage/attach
Choose a Friendly Name
Select AWS Storage Provider
Select the correct s3 region selected before
Paste in saved access key and secret access key
Enter the correct bucket name
Press save
We will check the API key is valid and works before allowing you to save. Once complete you are ready to setup a Backup Job that uses external storage.

Configure Schedule

Scheduling is where the power and ease of use for SnapShooter shins.

Schedule

You need to tell SnapShooter how often you wish to have backups, currently, we support up to hourly backups. You can also set Timezone and Daily backup time. We will then take your daily backup at this time/timezone and tag it as daily. If you have more than daily backups, we will offset the other backups on this.

Retention

With SnapShooter taking backups at the frequency you set you need to decide how many past backups you wish to keep.

Depending on when your backups start SnapShooter will tag them, Hourly, Daily, Weekly and Monthly (Weekly is your first on a Monday, Monthly is your first backup that happens on the 1st of the month)

When a backup is completed SnapShooter will then proceed to prune your backups based on your rules. This way we keep your backups in the most efficient way while also keeping a far horizon

Finished MySQL Setup

Your setup is now complete

We recommend you test your new configuration by pressing the Backup Now button, as backups happen in the background you can expect a small delay (20s) before the backup will start

Once you backup has completed you will be able to see it in the backup table

Depending on your compression level and the size of the backup it may take time to complete. Every minute SnapShooter will go and check the process to see how far it has got. Using the Log button you can see the latest state the backup is in.

When the MySQL has completed you will be shown the restore guide for if you ever needed to recover the database

Was this page helpful?

Thank you for helping us improve!