How to Backup MySQL Database to Backblaze with Bash

Introduction

If you are a system administrator then setting up an auto backup database is always an important thing in your backlog. It will help you to recover your data in the event of a database or system failure.

Backblaze is a scalable and low-cost cloud backup and storage solution that offers the cheapest pricing options compared to other providers. It is an alternative solution to AWS S3 and offers affordable pricing. Storage is $0.005/GB/Month, with download costs of $0.01/GB.

You can use SnapShooter to automate MySQL database backup to Backblaze easily. SnapShooter provides a user-friendly control panel to control and manage all your backups. SnapShooter will work with MySQL hosted on any VPS or dedicated server.

This post will show you how to backup the MySQL database to Backblaze manually and using SnapShooter.

Create your Backblaze Bucket

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

Step 1 - Sign in to your Backblaze account and go to the Bucket page to create a new Bucket.

Step 2 - Click on the Create a Bucket button. You should see the following page:

Step 3 - Provide all required information and click on the Create a Bucket button. Once the Bucket is created, you should see the following page:

Note down the Backblaze Bucket URL from the above image. You will need it later in this article.

Create Your Backblaze Credentials

In order to create backups with Backblaze, you will need to create the credentials to access it from the remote application and server. Follow the below steps to generate the application key and secret key:

Step 1 - On the Backblaze dashboard, click on the "App Keys" in the left pane. You should see the following page:

Step 2 - Now, click on "Add a new Application Key" button. You should see the following page:

Step 3 - Provide all required details and click on the Create New Key button. You should see the following page:

Note down the keyID, and applicationKey from the above image. You will need both to integrate Backblaze with the application.

Backup MySQL to Backblaze Bucket Manually

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

Install S3cmd

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

You will need to install the S3cmd tool on the server from where you want to back up your MySQL database. 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, you can verify the S3cmd version as shown below:

s3cmd --version

You will get the S3cmd version in the following output:

s3cmd version 2.2.0

Configure S3cmd to Connect Backblaze Bucket

Next, you will need to configure the S3cmd using the Backblaze credentials to connect to the Backblaze Bucket. You can configure it using the following command:

s3cmd --configure

You will be asked to provide your Backblaze 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: 004d55130756c060000000001
Secret Key: K004n/fcTBqu9wR36NBkXzZMAWpdyfY
Default Region [US]: us-west-004
 
Use "s3.amazonaws.com" for S3 Endpoint and not modify it to the target Amazon S3.
S3 Endpoint [s3.amazonaws.com]: s3.us-west-004.backblazeb2.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.s3.us-west-004.backblazeb2.com
 
Encryption password is used to protect your files from reading
by unauthorized persons while in transfer to S3
Encryption password: securepassword
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: 004d55130756c060000000001
  Secret Key: K004n/fcTBqu9wR36NBkXzZMAWpdyfY
  Default Region: us-west-004
  S3 Endpoint: s3.us-west-004.backblazeb2.com
  DNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.s3.us-west-004.backblazeb2.com
  Encryption password: securepassword
  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 Backblaze Bucket information using the following command:
 
s3cmd info s3://snapshooter-mysql-backup/
 
You will get the following output:
 
s3://snapshooter-mysql-backup/ (bucket):
   Location:  us-west-004
   Payer:     none
   Expiration Rule: none
   Policy:    none
   CORS:      none
   ACL:       d55130756c06: FULL_CONTROL

Backup MySQL to Backblaze Bucket Using S3cmd

At this point, S3cmd is installed and configured to manage the Backblaze Bucket.

Next, log in 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           |
+--------------------+
| db1                |
| db2                |
| 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 Backblaze Bucket:

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

Output:

upload: 'all_databases.sql' -> 's3://snapshooter-mysql-backup/all_databases.sql'  [1 of 1]
 477265 of 477265   100% in    0s  1072.65 KB/s  done

You can now verify all files on the Backblaze Bucket using the following command:

s3cmd ls s3://snapshooter-mysql-backup

You will get the following output:

2022-03-28 14:53       477265  s3://snapshooter-mysql-backup/all_databases.sql

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

Scheduled MySQL Single Databases Backups SnapShooter

Backup a single MySQL database to your external storage

Learn more about MySQL Single Databases Backups

Get started for free

No credit card required. Cancel anytime!

Conclusion

In this guide, you learned how to back up the MySQL database to Backblaze Bucket using SnapShooter and manually. You can now manage and control all your backup from the SnapShooter control panel.