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
- Start backup wizard
- SSH Setup Step
- 1) Configure SSH Connection
- How do I create a new User for SnapShooter?
- 1) Test SSH Connection
- By Hand
- Copy Paste
- Test the Result
- Common Errors
- Permission Denied
- Configure MySQL
- Configure Storage
- Creating a New Bucket
- Creating a Spaces Key
- Attaching S3 to SnapShooter
- Configure Schedule
- Finished MySQL Setup
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.
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
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
1. firstname.lastname@example.org: Permission denied (publickey).
This error normally means the server does not have the SSH key installed correctly. Try following the steps again.
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 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.
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.
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
Login to your DigitalOcean Control Panel
Top right click the Create button and select Spaces (at the bottom)
Choose your backup region
Do not select CDN as this is for private files, which we do not need to be distributed around the world.
Select Restrict File Listing
Choose a name - E.g. snapshooter
Press Create a Space - Spaces should be created instantly
Note down the URL
Creating a Spaces Key
Go to the API settings in your account (for the left menu under Manage)
Find Spaces access keys
Press Generate New Keys
Give it a useful name, e.g. SnapShooter
Copy the Key and secret (if you do not, you cannot view them again and will have to regenerate them)
Attaching S3 to SnapShooter
Go back to SnapShooter
Choose a Friendly Name
Select DigitalOcean Spaces
Select the region of correct space selected before
Paste in The saved access key and secret access key
Enter the correct bucket name
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.
Scheduling is where the power and ease of use for SnapShooter shins.
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.
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 BackupsGet started for free
Thank you for helping us improve!