Backing up databases and files to Amazon S3

Set up an Amazon S3 bucket

Here are the steps to create a new bucket for the server:

1. log in to the Amazon AWS management console
2. go to S3 management and create a new bucket
3. click the bucket, and on the right select Versioning and enabled it for the bucket
4. again on the right, select Lifecycle and set a lifecycle that permanently deletes old version items after 60 or 90 days. (see example)
5. Create a unique IAM user for this backup account (see below)

Use the IAM management console to create a new unique user for the backup account.

• Create a new user with the name backup-live.server.com
• Add the group ListAllMyBuckets to the user
• Create a new inline policy AccessBucket for the user. The policy is detailed below.
• Please note that you will need to use a client that is compatible with the latest auth method to access the bucket

A policy to allow sync backups (you will need to change live.server.com to your server’s folder):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowOperationsOnFolder",
            "Effect": "Allow",
            "Action": [
                "s3:DeleteObject",
                "s3:PutObject",
                "s3:PutObjectAcl"
            ],
            "Resource": [
                "arn:aws:s3:::my-servers/live.server.com",
                "arn:aws:s3:::my-servers/live.server.com/*"
            ]
        },
        {
            "Sid": "AllowListingOfFilesInFolder",
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::my-servers"
            ],
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "live.server.com/*"
                    ]
                }
            }
        }
    ]
}

Set up s3cmd

Install s3cmd via apt-get:

sudo aptitude install s3cmd

Next, set up s3cmd to have access to your S3 account for user root:

sudo su
s3cmd --configure

Use the separate IAM user you created for the bucket above.

Backing up mysql databases

You can use a combination of automysqlbackup and s3cmd to back up your database easily to Amazon S3.

First, install automysqlbackup for this task (Ubuntu LTS 12 and up):

sudo aptitude install automysqlbackup

Note: you probably need the Python-based MIME detection library, otherwise the automysqlbackup will not return with exit code 0. (Anyway, it can detect the MIME time at alternative way)

sudo apt-get install python-magic

Next, set up automysqlbackup to save to /var/backups/db and send those files to S3 after backup. Simply edit the conf file using sudo nano /etc/default/automysqlbackup

BACKUPDIR="/var/backups/db"
MAILADDR="you@example.com" # Just so you have error emails sent in case something goes wrong
POSTBACKUP="/opt/s3cmd/s3cmd sync --server-side-encryption --delete-removed /var/backups/db/ s3://outlast-servers/live.server.com/database/"

Since automysqlbackup already is set up to run daily via cron, the rest should be automatic. You can try and test it by running the code below and making sure it works:

sudo /etc/cron.daily/automysqlbackup

Backing up files

You need to create a bash script and set it up in cron to run daily (or more often). As above, you’ll need s3cmd and the proper bucket policies to make it work.

Check latest script in outlast-server-scripts!

Now don’t forget to add the script to your cron so that it runs daily or hourly.

Keep in mind that using the  --delete-removed option will remove files that have been removed from your server. Use versioning combined with a generous lifecycle policy to ensure that files are accessible for quite a while even after being deleted on the server.

Enable MySQL binary logs

The MySQL binary log is log of all database transactions. It allows you to restore the database to a specific time. Click here to see info on restoring from bin log.

Typically you just need to uncomment the bin-log parameter in /etc/mysql/my.cnf:

sudo nano /etc/mysql/my.cnf

The lines to uncomment specifically:

log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

It’s unrelated, but while you’re there, you can also turn on logging of slow queries:

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

Once done, restart your mysql server:

sudo service mysql restart