How to Backup MySQL Database to Backblaze with Bash
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.
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.0python3 setup.py install
Once the S3cmd is installed, you can verify the S3cmd version as shown below:
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:
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: 004d55130756c060000000001Secret Key: K004n/fcTBqu9wR36NBkXzZMAWpdyfYDefault Region [US]: us-west-004Use "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.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.s3.us-west-004.backblazeb2.comEncryption password is used to protect your files from readingby unauthorized persons while in transfer to S3Encryption password: securepasswordPath 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: 004d55130756c060000000001Secret Key: K004n/fcTBqu9wR36NBkXzZMAWpdyfYDefault Region: us-west-004S3 Endpoint: s3.us-west-004.backblazeb2.comDNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.s3.us-west-004.backblazeb2.comEncryption password: securepasswordPath 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 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-004Payer: noneExpiration Rule: nonePolicy: noneCORS: noneACL: 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:
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/
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:
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.
Thank you for helping us improve!