How to automatically backup MYSQL to Amazon S3?
Tags: mysql, aws, bash
Published: 2019-08-06 19:09:52

Most of companies deal with the loss of data. That's why we should have database backups to at any time to recover from it. I won't be talking about data loss much, if you need to learn more about data loss and the importance of the backups, read [this](https://www.datacenterknowledge.com/uptime/your-server-died-and-your-backups-are-gone-heres-what-do-next).

If you need to motivate yourself to create backups, [**read this**](https://www.reddit.com/r/cscareerquestions/comments/6ez8ag/accidentally_destroyed_production_database_on/) discussion on reddit.

# Why Amazon S3?

Up to today, I saved my mysql backups in the server itself. But, I thought it's the time to use a cloud storage to save backups. Why? The reason is, if you face with an issue of getting deleted both database and backups, you will need to have a backup somewhere else. **Cloud** is the best place.


![Cloud](https://media.giphy.com/media/3o7TKNOYAv36eKJJra/giphy.gif)

Among cloud services, I choose Amazon S3 because of their powerful [CLI tool](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-install.html) and reliability. (Also, Amazon S3 is simple to understand if you are new to Amazon services)

# Our Target

Here's what were are going to do here.

* Dump MYSQL into a folder. (`/var/local/backups`)
* Sync that folder with a S3 folder using AWS CLI
* Finally, we will create a shell script to automate the process with the help of cron jobs.

# Dumping MYSQL

I use `mysqldump` to dump MYSQL databases, which is easy to use.

```sh
mysqldump -u [user] -p[password] my_database > /where/to/dump
```

# Setting up Amazon S3

* If you haven't, create an [AWS Account](https://aws.amazon.com/console/) and login to the console.
* Then, go to Amazon S3.
* Create a bucket with default configurations. (I named it `company-backups`. Use a company name or your name to make it unique as it required to be unique globally)
* Create a folder inside the bucket. (`company-backups/mysql`)

Now you are done! Let's setup AWS CLI

# Setting up AWS CLI

As I mentioned earlier, AWS CLI is a powerful tool. It allows us to sync folders between our server and Amazon S3.

[Here's the guide](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-install.html) to setting up AWS CLI.

After installing AWS CLI, make sure it's working. 

```sh
aws --version
```

This should show something like, `aws-cli/1.16.170 Python/3.7.3 Linux/4.14.123-111.109.amzn2.x86_64 botocore/1.12.160`

Then, [configure AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-configure.html) with your credentials.

# Testing Synchronizing

Let's do some testing before we create the shell script.

Run the following command and dump a database to a temporary directory called `backups` in your home directory for testing. (Make sure you enter your password correctly when prompted)

```sh
mysqldump -u [user] -p my_database > backups/my_database.sql
```

Let's try synchronization.

```sh
aws s3 sync backups s3://company-backups/mysql/
```

Now, visit the S3 console and check if the file is uploaded to the folder. If yes, great!

# Writing the Shell Script

This is the interesting part! Let's write the shell script which does following things.
* Dump databases
* Sync with S3

```sh
#!/bin/bash

# 1 to 7
DAY_OF_WEEK=$(date +%u)

# database username
USER="myusername"

# database user's password
PASSWORD="mypassword"

# the directory to save the dumped .sql files
DUMP_DIR="/var/local/backups"

# list of databases to dump
DATABASES="database_1 database_2 database_3" 

# where to sync in S3
S3_SYNC_URL="s3://company-backups/mysql"

# loop through each database and dump
for db in $DATABASES; do
    echo "Dumping database: $db"
    # dump the database
    # filename ~= /var/local/backups/database_1.1.sql
    mysqldump -u $USER -p$PASSWORD --databases $db > $DUMP_DIR/$db.$DAY_OF_WEEK.sql
done

# sync the dumb directory with the directory in the s3 bucket
aws s3 sync $DUMP_DIR $S3_SYNC_URL
```

I have added comments in the bash script to understand more about each step.

Save the above file as `backup-databases.sh` in any directory (I use `/var/www/shell` for these kind of scripts) and then run the script.

```sh
/bin/bash /path/to/backup-databases.sh
```

And, check if everything works. You must see the dumped .sql files in your S3 folder. If yes, you win!

# Setting up Cron Jobs

So, you gonna call the above command each day to backup your databases?

Nope, cron is there.

Run this:
```sh
crontab -e
```

Add this at the end of the file
```sh
# back up databases
00 00 * * * /bin/bash /path/to/backup-databases.sh
```

Save and exit.

Now, our script will run the script every day at midnight. You will have 7 backups per each database, in your server as well as in Amazon S3.

If you have any questions feel free to comment below.

Thank you for reading!