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
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:
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.
In this section, we will show you how to back up the MySQL database to Amazon S3 manually using the 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.
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
-
0
1
-
17
0
6
:
50
:
45
1219017
all_databases.sql
2022
-
0
1
-
17
0
6
:
51
:
30
1264
db1_backup.sql
2022
-
0
1
-
17
0
6
:
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:
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.
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.
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.
With high quality products and considerate service, we will work together with you to enhance your business and improve the efficiency. Please don't hesitate to contact us to get more details of mysql backup to s3.