DigitalOcean
SnapShooter

How to Backup MySQL to DigitalOcean Space

DigitalOcean Spaces is a friendly and easy-use alternative to AWS S3. Perfect for running computing in a DigitalOcean data centre. Prices are simple, and you get 250GB with 1TB of bandwidth. After this, you are billed $0.02 per GB for storage and $0.01 per GB for transfer. SnapShooter will work with MySQL hosted on your droplets and MySQL managed in DigitalOcean Managed databases.

This guide will show how easy it is to backup MySQL to Spaces using SnapShooter. SnapShooter has a free plan for a single database so that you can get started totally for free. Just bring the storage.

Table Of Contents

Sign up for SnapShooter

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 starting from $9 to upgrade for larger resource limits and backup frequency. Click Free Trial in the top right. Provide your name, email and password.

Start backup wizard

Once on the dashboard, you will be greeted by a welcome screen showing you the available backup offerings SnapShooter has. 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. You may or may not need root access, depending on the backup type. (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 user 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 user authorized keys. SnapShooter will provide you with the normal path, e.g. for a user called SnapShooter /home/snapshooter/.ssh/authorizedkeys

Copy Paste

We allow offering a single copy-paste method

  • Select Single Copy Paste

  • Login to Server

  • Copy the command

  • Paste the command into the SSH client connected to the server

Test the Result

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. It does 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 has read access to the databases you want to be 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 must select the storage provider you wish your backups to be stored with. We allow users to select from a range of providers, and the guide will focus on Spaces

If you have already set up the storage provider you want, you can select it and proceed. If not, press Connect new External Storage

Creating a New Bucket

  1. Login to your DigitalOcean Control Panel

  2. Top right click the Create button and select Spaces (at the bottom)

  3. Choose your backup region

  4. Do not select CDN as this is for private files, which we do not need to be distributed around the world.

  5. Select Restrict File Listing

  6. Choose a name - E.g. snapshooter

  7. Press Create a Space - Spaces should be created instantly

  8. Note down the URL

Creating a Spaces Key

  1. Go to the API settings in your account (for the left menu under Manage)

  2. Find Spaces access keys

  3. Press Generate New Keys

  4. Give it a useful name, e.g. SnapShooter

  5. Copy the Key and secret (if you do not, you cannot view them again and will have to regenerate them)

Attaching S3 to SnapShooter

  1. Go back to SnapShooter

  2. https://snapshooter.io/dashboard/settings/storage/attach

  3. Choose a Friendly Name

  4. Select DigitalOcean Spaces

  5. Select the region of correct space selected before

  6. Paste in The saved access key and secret access key

  7. Enter the correct bucket name

  8. Press save

  9. We will check the API key is valid and works before allowing you to save. Once complete, you are ready to create 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. We support hourly backups. You can also set Timezone and Daily backup time. We will take your daily backup at this time/timezone and tag it as daily. Finally, we will offset the other backups.

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 prune your backups based on your rules. This way, we keep your backups most efficiently and far horizon.

Finished MySQL Setup

Your setup is now complete

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

Once your backup has been completed, you can see it in the backup table.

It may take time to complete depending on your compression level and the backup size. For example, SnapShooter checks the process every minute to see how far it has gotten. Then, you can see the latest state the backup is in using the Log button.

When MySQL has been completed, it will show you the restore guide if you ever need to recover the database.

Scheduled DigitalOcean Managed MySQL Backups SnapShooter

Backup a DigitalOcean MySQL database to your external storage

Learn more about DigitalOcean Managed MySQL Backups

Get started for free
No credit card required. Cancel anytime!

Related

How to Backup MongoDB Database to DigitalOcean Spaces with Bash
IntroductionSetting up database backup is one of the most essential things for any system or...
Was this page helpful?

Thank you for helping us improve!