How to Backup MySQL to DigitalOcean Spaces

Introduction

DigitalOcean Spaces is an object storage service that provides a cost-effective solution to store your data in a safe and secure location. It is a simple and easy-to-use alternative solution to AWS S3. DigitalOcean offers a simple, affordable, and predictable pricing. For $5 per month, 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 per month.

With SnapShooter, you can automate MySQL database backup to DigitalOcean Spaces easily. SnapShooter provides a simple and user-friendly control panel that helps beginners to control and manage their backups. SnapShooter will work with MySQL hosted on your own droplets as well as MySQL managed in DigitalOcean Managed databases.

This post will show you how to backup MySQL to DigitalOcean Spaces manually and using SnapShooter.

Create Your DigitalOcean Spaces

First, you will need to create a new space on the DigitalOcean website. Follow the below steps to create a new space on the DigitalOcean:

Step 1 - Sign in to your DigitalOcean account and click on the Spaces in the left pane.

Step 2 - Click on the Create => Spaces from the right side of the DigitalOcean control panel.

Step 3 - Select your Region, Restrict File Listing, Provide your unique space name and click on Create a Space button. Once the Space is created, you should see the following screen:

Create Your DigitalOcean Credentials

In order to create backups with DigitalOcean spaces, you will need the correct access key and secret key to authenticate with it. Follow the below steps to create a DigitalOcean Credentials:

Step 1 - Log in to your DigitalOcean Spaces account or go directly to https://cloud.digitalocean.com/spaces.

Step 2 - Click on the Manage Keys button on the right-hand side of the Spaces screen.

Step 3 - Click on the Generate New Key next to Spaces access keys. You should see the following screen:

Step 4 - Provide a name for your key and click the checkmark. A new Key and Secret will be generated as shown below:

Backup MySQL to DigitalOcean Space Manually

In this section, we will show you how to back up a MySQL database from your server to the DigitalOcean Space using the S3cmd tool.

Install S3cmd

Before starting, you will need to install the S3cmd tool on the server from where you want to back up your MySQL database.

S3cmd is a free and open-source command-line tool that allows you to upload, download and manage data in DigitalOcean Space and other cloud storage service providers. Follow the below steps to install the S3cmd to your server.

First, install the Python and other dependencies by running the following command:

apt-get install python3 python3-setuptools curl -y

Next, download the latest version of S3cmd using the following command:

curl -LO https://github.com/s3tools/s3cmd/releases/download/v2.2.0/s3cmd-2.2.0.tar.gz

Next, extract the downloaded file with the following command:

tar -xvzf s3cmd-2.2.0.tar.gz

Next, navigate to the extracted directory and install it using the following command:

cd s3cmd-2.2.0
python3 setup.py install

Once the S3cmd is installed, verify the S3cmd version using the following command:

s3cmd --version

You will get the following output:

s3cmd version 2.2.0

Configure S3cmd

Next, you will need to configure the S3cmd using the DigitalOcean Access key and Secret key. You can configure it using the following command:

s3cmd --configure

You will be asked to provide your DigitalOcean Access Key, Secret Key, Region, and Endpoint as shown below:

Enter new values or accept defaults in brackets with Enter.
Refer to user manual for detailed description of all options.
 
Access key and Secret key are your identifiers for Amazon S3. Leave them empty for using the env variables.
Access Key: 7T4U6JSUYPGNT2M5ILYS
Secret Key: VoYYWhxmpTzDNDxLd7GbQ6SOGceCjSYoUdUHRYuQ/9U
Default Region [US]: Frankfurt 1
 
Use "s3.amazonaws.com" for S3 Endpoint and not modify it to the target Amazon S3.
S3 Endpoint [s3.amazonaws.com]: fra1.digitaloceanspaces.com
 
Use "%(bucket)s.s3.amazonaws.com" to the target Amazon S3. "%(bucket)s" and "%(location)s" vars can be used
if the target S3 system supports dns based buckets.
DNS-style bucket+hostname:port template for accessing a bucket [%(bucket)s.s3.amazonaws.com]: %(bucket)s.fra1.digitaloceanspaces.com
 
Encryption password is used to protect your files from reading
by unauthorized persons while in transfer to S3
Encryption password: jethva
Path to GPG program:
 
When using secure HTTPS protocol all communication with Amazon S3
servers is protected from 3rd party eavesdropping. This method is
slower than plain HTTP, and can only be proxied with Python 2.7 or newer
Use HTTPS protocol [Yes]:
 
On some networks all internet access must go through a HTTP proxy.
Try setting it here if you can't connect to S3 directly
HTTP Proxy server name:
 
New settings:
  Access Key: 7T4U6JSUYPGNT2M5ILYS
  Secret Key: VoYYWhxmpTzDNDxLd7GbQ6SOGceCjSYoUdUHRYuQ/9U
  Default Region: Frankfurt 1
  S3 Endpoint: fra1.digitaloceanspaces.com
  DNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.fra1.digitaloceanspaces.com
  Encryption password: jethva
  Path to GPG program: None
  Use HTTPS protocol: True
  HTTP Proxy server name:
  HTTP Proxy server port: 0
 
Test access with supplied credentials? [Y/n] Y
Please wait, attempting to list all buckets...
Success. Your access key and secret key worked fine :-)
 
Now verifying that encryption works...
Not configured. Never mind.
 
Save settings? [y/N] y
Configuration saved to '/root/.s3cfg'

Once the S3cmd is configured, you can verify your bucket information using the following command:

s3cmd info s3://snapshooter-mysql-backup/

You will get the following output:

s3://snapshooter-mysql-backup/ (bucket):
   Location:  fra1
   Payer:     none
   Expiration Rule: none
   Policy:    none
   CORS:      none
   ACL:       6945323: FULL_CONTROL

Use S3cmd to Backup MySQL to DigitalOcean Spaces

At this point, S3cmd is installed and configured to manage the DigitalOcean Spaces.

First, go to the server from where you want to back up your MySQL database, then log in to the MySQL using the following command:

mysql -u root -p

Once you are log in, list all MySQL databases using the following command:

mysql> SHOW DATABASES;

You will get all databases in the following output:

+--------------------+
| Database           |
+--------------------+
| database1          |
| database2          |
| database3          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Now, exit from the MySQL shell using the following command:

mysql> EXIT;

Next, back up all MySQL databases using the mysqldump utility:

mysqldump -u root -p --all-databases > all_databases.sql

Next, run the S3cmd command to copy the all_databases.sql file to the DigitalOcean Spaces:

s3cmd put all_databases.sql s3://snapshooter-mysql-backup/

Output:

upload: 'all_databases.sql' -> 's3://snapshooter-mysql-backup/all_databases.sql'  [1 of 1]
 1218421 of 1218421   100% in    2s   542.54 KB/s  done

If you want to back up the MySQL data directory to the DigitalOcean Spaces and compress it, run the following command:

tar -czvf - /var/lib/mysql/ | s3cmd put - s3://snapshooter-mysql-backup/mysql_backup.tar.gz

Output:

upload: '<stdin>' -> 's3://snapshooter-mysql-backup/mysql_backup.tar.gz'  [part 1 of -, 4MB] [1 of 1]
 5190654 of 5190654   100% in    1s     3.80 MB/s  done

You can now verify all files on the DigitalOcean Spaces using the following command:

s3cmd ls s3://snapshooter-mysql-backup

You will get the following output:

2022-01-19 12:15      1218421  s3://snapshooter-mysql-backup/all_databases.sql
2022-01-19 12:15      5190654  s3://snapshooter-mysql-backup/mysql_backup.tar.gz

You can also log in to the DigitalOcean Spaces and verify your backup files as shown below:

Backup MySQL Database to DigitalOcean Spaces Using SnapShooter

Manually backing up MySQL to DigitalOcean Spaces is a very time-consuming process. You will need to create and compress backup every time then upload it to DigitalOcean Spaces manually. You can't control and manage it automatically. In this case, SnapShooter provides an easier way to create a backup, compress it and schedule it automatically for you. Also, SnapShooter provides a web-based control panel to manage all your backup jobs from the web browser.

Follow the below steps to Backup MySQL to DigitalOcean Spaces:

Step 1 - First, go to the SnapShooter website and register for their 14-day free trial.

Step 2 - On the SnapShooter dashboard, click on the Backup Jobs in the left sidebar. You should see the following screen:

Step 3 - Click on the Create New Job. You should see the various database option on the following screen:

Step 4 - Click on the All MySQL Databases. You should see the following screen:

Step 5 - Click on the Continue button. You should see the Add Your Server screen:

Step 6 - Now, copy the code shown in the above screen and paste it to your MySQL server as shown below:

curl -sSL "https://ingestor.snapshooter.com/api/server/add?token=eyJpdiI6IkI0R0NmZ2lzQThpdjEzWDRieDhtMGc9PSIsInZhbHVlIjoiQmtBMTUwbkl4VXYzV21URU9ZM083UT09IiwibWFjIjoiYjViZDJjM2Y0Yzg1NjcxZmQ3MDdmNzg5NTdjYjVhMTQ0YzI0NzY0YWEwYWFiMmQ0YjUyOTVmMDBhYmJkZjJlMCIsInRhZyI6IiJ9&key=5895" | bash

Once your server is added to the SnapShooter, you will get the following output:

Welcome to SnapShooter Server Setup
Environment Detected: OS: linux, OS Type: amd64
Installing SSH public key
Creating temp SSH key file
grep: /root/.ssh/authorized_keys: No such file or directory
cp: cannot stat '/root/.ssh/authorized_keys': No such file or directory
Creating authorized_keys backup /root/.ssh/authorized_keys.bak-1642599209
Key Installed /root/.ssh/authorized_keys
Removing temp SSH key file
Scanning SSH config
Checking 22
matched (22)
SSH test confirmed, Server Added to SnapShooter ...

Step 7 - Once your MySQL server is added to the SnapShooter, you should see the following screen:

Step 8 - Click on the Next button. You should see the MySQL database configuration screen:

Step 9 - Provide your MySQL username, password, host, port, and click on the Test button to test the connection. If everything is fine, you should see the following screen:

Step 10 - Click on the Save and Next button. You should see the Storage Selection screen:

Step 11 - Here, you will need to define your DigitalOcean Spaces to store the MySQL database. Click on the Add New Storage Provider. You should see the following screen:

Step 12 - Click on the Config S3 button. You should see the following screen:

Step 13 - Provide your friendly name, select your storage provider, define your region, provide your DigitalOcean Space name, Access Key, and Secret Key, and click the Test and Save button. Once your DigitalOcean Spaces is connected to the SnapShooter, you should see the following screen:

Step 14 - Click on the Set Storage button to set your DigitalOcean Spaces as default storage. You should see the Backup Schedule Setup screen:

Step 15 - Select and define all options as per your requirements and click on the Set Schedule and Finish button. You should see the following screen:

Step 16 - Click on the Backup Now button to run your first backup job. Once your backup job is executed successfully, you should see the following screen:

Now, go back to your DigitalOcean Spaces and verify your backup as shown below:

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.

Conclusion

In this guide, you learned how to backup MySQL database to DigitalOcean Spaces using SnapShooter and manually. You can now manage and control all your backup from the centralized location.