How to Backup MySQL to Amazon S3

Introduction

Amazon S3 is a highly available distributed object storage service offered by Amazon Web Services. It offers storage through web services interfaces and provides APIs for you to store objects on it. AWS S3 is an ideal place to store critical data like MySQL or other databases.

SnapShooter is a leading backup solution provider that makes it easy to schedule frequent backups for your servers, websites, and databases. SnapShooter creates backups and archives from your server and stores them in the cloud storage provider of your choice. It supports many cloud providers including, Amazon AWS, Google Cloud, Vultr, DropBox, Hetzner, UpCloud, and more.

In this guide, we will show you two methods to backup MySQL database to Amazon S3:

Backup MySQL to Amazon S3 Manually
Backup MySQL to Amazon S3 using SnapShooter

Create an S3 Bucket in AWS

Before you can store any object or data in S3, you need to create a bucket on AWS S3. Follow the below steps to create a bucket on AWS S3:

Step 1 - Login to Amazon Management Console, search for S3 and open the S3 service.

Step 2 - Click on Create bucket button. You should see the General configuration screen:

Step 3 - Type your bucket name, select your region, Select object ownership, Block all public access, and click on the Create bucket. Once the bucket is created, you will get the following screen:

Create a New IAM User

After creating an S3 bucket, you will need to create a new IAM user and assign permission for your S3 bucket. Follow the below steps to create an IAM user:

Step 1 - Leave S3, search for IAM from the top services menu, and click on Users on the left sidebar. You should see the IAM user screen:

Step 2 - Click on the Add users button to create a new IAM user. You should see the following screen:

Step 3 - Type your username, select "Access Type Programmatic access", then click on the Next: Permissions:

Step 4 - Leave all default options and click on the Next: Tags:

Step 5 - Leave all default options and click on the Next: Review:

Step 6 - Review your configuration and click on the Create user button. You should see the following screen:

Step 7 - Note down the Access key ID and Secret access key in a safe location. You will need both keys to connect to the AWS S3 in the next step. Now, click on the Close button. You should see the following screen:

Step 8 - Click on your newly created user. You should see the following screen:

Step 9 - Click on Add inline policy. You should see the Create Policy screen:

Step 10 - Choose JSON Editor and add the following contents:

{
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": [
                   "s3:ListBucket",
                   "s3:DeleteObject",
                   "s3:GetObject",
                   "s3:PutObject",
                   "s3:PutObjectAcl"
               ],
               "Resource": [
                 "arn:aws:s3:::snapshooter-mysql-backup/*",
                 "arn:aws:s3:::snapshooter-mysql-backup"
               ]
           }
       ]
    }

Replace the snapshooter-mysql-backup with the newly created bucket. Then, click on the Review policy button. You should see the following screen:

Step 11 - Provide your policy name and click on the Create policy button to save the policy.

Backup MySQL Database to Amazon S3 Manually

In this section, we will show you how to back up the MySQL database to Amazon S3 manually using the AWS CLI tool.

Install AWS CLI Tool

Before starting, you will need to install the AWS CLI tool on the MySQL server from where you want to backup your databases. The AWS CLI is a command-line tool to manage your AWS services. It helps you to control multiple AWS services from the command line and automate them through scripts.

First, download the AWS CLI utility using the command given below:

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"

Next, unzip the downloaded file and install it using the following command:

unzip awscliv2.zip
sudo ./aws/install

After the installation, you will need to configure AWS CLI with your AWS credentials.

aws configure

Provide your AWS access key ID, secret key, and the region as shown below:

AWS Access Key ID [None]: YOUR-AWS-ACCESS-KEY
AWS Secret Access Key [None]: YOUR-AWS-SECRET-KEY
Default region name [None]: us-east-1
Default output format [None]:

At this point, the AWS CLI tool is configured on the server to control all AWS services.

Backup MySQL Database to Amazon S3

First, log in to the MySQL console with the following command:

mysql -u root -p

Once you are log in to MySQL, run the following command to list all databases available on the server.

+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

mysql> EXIT;

Next, use the mysqldump utility to backup your desired MySQL databases from the above list:

mysqldump -u root -p db1 > db1_backup.sql
mysqldump -u root -p db2 > db2_backup.sql
mysqldump -u root -p --all-databases > all_databases.sql

Now, use the following syntax to backup your MySQL database to the AWS S3:

aws s3 cp [your-db-name] "s3://your-s3-bucket-name"

For example, to back up a MySQL database named all_databases.sql to AWS S3, use the following command:

aws s3 cp all_databases.sql  "s3://snapshooter-mysql-backup"

You will get the following output:

upload: ./all_databases.sql to s3://snapshooter-mysql-backup/all_databases.sql

To backup db1_backup.sql and db2_backup.sql database to AWS S3, run the following command:

aws s3 cp db1_backup.sql  "s3://snapshooter-mysql-backup"
aws s3 cp db2_backup.sql  "s3://snapshooter-mysql-backup"

You can now list all databases on the AWS S3 bucket using the following command:

aws s3 ls "s3://snapshooter-mysql-backup"

You will get the following output:

2022-01-17 06:50:45    1219017 all_databases.sql
2022-01-17 06:51:30       1264 db1_backup.sql
2022-01-17 06:51:51       1264 db2_backup.sql

You can also verify all databases by logging in to the Amazon Web Console, search for S3 and click on your bucket name. You should see the following screen:

Also Read:

How to Install and Use Rclone on Ubuntu 20.04

Backup MySQL Database to Amazon S3 Using SnapShooter

In this section, we will show you how to back up the MySQL database to Amazon S3 using 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 $ to upgrade for larger resources limits and backup frequency. Click Free Trial in the top right Provide your name, email, and password.

Use SnapShooter to Backup MySQL Database to Amazon S3

SnapShooter provides an easier way to create backups and archives from your server and store them on the Amazon S3 bucket. Follow the below steps to back up the MySQL database to the Amazon S3:

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

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

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

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

Step 5 - 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=eyJpdiI6InJ2MlNIYUZjbXgrNmU2Q3NuN3NJUFE9PSIsInZhbHVlIjoiS3FXUXd6VkFHdThJRkpzYzZxMUtMZz09IiwibWFjIjoiNDY4Y2RiMmM3OTQyY2YyMjIyNGY2YWYxNDJkNmQxYjlkNjk1OTNhY2NmZmMyYjVhOWJmZDQyYTg0YmM3MTY4MCIsInRhZyI6IiJ9&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-1642402786
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 6 - Once your MySQL server is added to the SnapShooter, you should see the MySQL database configuration screen:

Step 7 - 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 8 - Click on the Save and Next button. You should see the Storage Selection screen:

Step 9 - Here, you will need to define your AWS S3 storage to store the MySQL database. Click on the Add New Storage Provider. You should see the following screen:

Step 10 - Click on the Config S3 to connect your AWS S3. You should see the following screen:

Step 11 - Provide your friendly name, select your storage provider, define your region, provide your S3 bucket name, Access Key, Secret Key and click on the Test and Save button. Once your AWS S3 is connected to the SnapShooter, you should see the following screen:

Step 12 - Click on the Set Storage button to set your AWS S3 as default storage. You should see the Backup Schedule Setup screen:

Step 13 - 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 14 - 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:

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 the above guide, you learned how to backup MySQL database to Amazon S3 using SnapShooter and manually. I hope you can now easily back up any databases to Amazon S3 using SnapShooter.

Was this page helpful?

Thank you for helping us improve!