Simple script for backing up MySQL databases to Amazon S3. Designed to be used in a periodic task.

mysql-backup.py

#!/usr/bin/python
# Simple MySQL + S3 backup script
# By John <[email protected]>
from __future__ import print_function

import sys
import boto3
import argparse
from boto3.s3.transfer import S3Transfer
from tempfile import NamedTemporaryFile
from datetime import datetime
from sh import mysqldump, xz
from pipes import quote

# Parse command line arguments. This can be removed entirely if you set all of
# the configuration variables below.
parser = argparse.ArgumentParser(description='Tool to backup MySQL databases')
parser.add_argument('--database', dest='database', type=str, help='MySQL database to back up', required=True)
parser.add_argument('--bucket', dest='bucket', type=str, help='S3 bucket to use', required=True)
parser.add_argument('key', type=str, help='Key to store backup under', default='mysql/{db}/{date}.sql.xz')
args = parser.parse_args()

# ---
# Configuration
# ---

# MYSQL_CNF: Configuration file to use. This is set to /etc/mysql/debian.cnf
#            by default, which requires root privileges to access
MYSQL_CNF = '/etc/mysql/debian.cnf'

# MYSQL_OPTS: Additional options to pass to MySQLDump
MYSQL_OPTS = []

# S3_BUCKET: S3 bucket name
S3_BUCKET = args.bucket

# S3_KEY: S3 key name. {date} and {db} will be replaced.
S3_KEY = args.key

# DATABASE: database to backup
DATABASE = args.database

# ---
# Program
# ---

# Add MySQL options file.
MYSQL_OPTS = ['--defaults-file={0}'.format(quote(MYSQL_CNF))] + MYSQL_OPTS + [DATABASE]

# Grab S3 resource
s3 = boto3.resource('s3')

# Get datetime
date = datetime.now().isoformat()

# Make the final key name.
key = S3_KEY.format(date=date, db=DATABASE)

# Get a temporary file (important for large databases)
with NamedTemporaryFile() as f:
    # Perform the actual dump, pipe to file-like f.
    print('Backup initiated.')
    xz(mysqldump(*MYSQL_OPTS), _out=f, _tty_out=False)
    f.flush()

    # Upload the dump from file-like f to S3 Key 'key' - N.B.: This will fail on
    # Windows unless we close the temporary file first.
    print('Uploading to S3.')
    s3.Bucket(S3_BUCKET).upload_file(f.name, key)

# Print information to terminal.
print('Uploaded dump of {db} to {key} successfully.'.format(db=DATABASE, key=key))

requirements.txt

sh==1.11
six==1.10.0
boto3==1.2.4