How to Backup MySQL to DigitalOcean Spaces
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.
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.0python3 setup.py install
Once the S3cmd is installed, verify the S3cmd version using the following command:
You will get the following output:
s3cmd version 2.2.0
Next, you will need to configure the S3cmd using the DigitalOcean Access key and Secret key. You can configure it using the following command:
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: 7T4U6JSUYPGNT2M5ILYSSecret Key: VoYYWhxmpTzDNDxLd7GbQ6SOGceCjSYoUdUHRYuQ/9UDefault Region [US]: Frankfurt 1Use "s3.amazonaws.com" for S3 Endpoint and not modify it to the target Amazon S3.S3 Endpoint [s3.amazonaws.com]: fra1.digitaloceanspaces.comUse "%(bucket)s.s3.amazonaws.com" to the target Amazon S3. "%(bucket)s" and "%(location)s" vars can be usedif 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.comEncryption password is used to protect your files from readingby unauthorized persons while in transfer to S3Encryption password: jethvaPath to GPG program:When using secure HTTPS protocol all communication with Amazon S3servers is protected from 3rd party eavesdropping. This method isslower than plain HTTP, and can only be proxied with Python 2.7 or newerUse 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 directlyHTTP Proxy server name:New settings:Access Key: 7T4U6JSUYPGNT2M5ILYSSecret Key: VoYYWhxmpTzDNDxLd7GbQ6SOGceCjSYoUdUHRYuQ/9UDefault Region: Frankfurt 1S3 Endpoint: fra1.digitaloceanspaces.comDNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.fra1.digitaloceanspaces.comEncryption password: jethvaPath to GPG program: NoneUse HTTPS protocol: TrueHTTP Proxy server name:HTTP Proxy server port: 0Test access with supplied credentials? [Y/n] YPlease 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] yConfiguration 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: fra1Payer: noneExpiration Rule: nonePolicy: noneCORS: noneACL: 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:
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/
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
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.sql2022-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 SetupEnvironment Detected: OS: linux, OS Type: amd64Installing SSH public keyCreating temp SSH key filegrep: /root/.ssh/authorized_keys: No such file or directorycp: cannot stat '/root/.ssh/authorized_keys': No such file or directoryCreating authorized_keys backup /root/.ssh/authorized_keys.bak-1642599209Key Installed /root/.ssh/authorized_keysRemoving temp SSH key fileScanning SSH configChecking 22matched (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.
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.
Thank you for helping us improve!