MySQL Backup to Google Cloud Storage with Hipchat Integration

mysql-backup-gcs.py

#!/usr/bin/env python

from __future__ import print_function
from datetime import datetime
import json
import os
import subprocess
import sys

# only use a-zA-Z-_, this becomes part of the backup file name
MYSQL_NODE_NAME = 'master'
MYSQL_USERNAME = 'root'
MYSQL_PASSWORD = 'password'
LOCAL_BACKUP_DIRECTORY = '/tmp/'
GCS_BACKUP_DIRECTORY = 'gs://your-bucket-name/'
BACKUP_RETENTION_DAYS = 7
USE_GZIP = True
HIPCHAT_INTEGRATION = True
# build your own integration instructions at https://www.hipchat.com/docs/apiv2
HIPCHAT_INTEGRATION_LINK = 'https://api.hipchat.com/v2/room/<room id>/notification?auth_token=<token>'

MYSQL_BACKUP_COMMAND = (
    'mysqldump',
    '--socket=/var/run/mysqld/mysqld.sock',
    '-u' + MYSQL_USERNAME,
    '-p' + MYSQL_PASSWORD,
    '--routines',
    '--triggers',
    '--all-databases')


class MysqlBackupGcsException(Exception):
    pass


def write_backup_local(local_file):
    # open the output file
    with open(local_file, 'w') as outfile:

        # start the mysqldump process
        mysql_stdout_pipe = subprocess.PIPE if USE_GZIP else outfile
        mysql_proc = subprocess.Popen(MYSQL_BACKUP_COMMAND, stdout=mysql_stdout_pipe, stderr=subprocess.PIPE)

        # optionally start the gzip process
        if USE_GZIP:
            gzip_proc = subprocess.Popen(('gzip', '-c'), stdin=mysql_proc.stdout, stdout=outfile,
                                         stderr=subprocess.PIPE)
        # check the mysqldump process
        _, mysql_stderr = mysql_proc.communicate()
        if mysql_proc.returncode != 0:
            raise MysqlBackupGcsException("Error when running mysqldump: " + mysql_stderr)

        # optionally check the gzip process
        if USE_GZIP:
            _, gzip_stderr = gzip_proc.communicate()
            if gzip_proc.returncode != 0:
                raise MysqlBackupGcsException("Error when gzipping mysqldump output: " + gzip_stderr)


def upload_backup_to_gcs(local_file, gcs_file):
    # gsutil command to copy file to cloud
    gsutil_command = ('gsutil', 'cp', local_file, gcs_file)

    # start the gsutil command
    gsutil_proc = subprocess.Popen(gsutil_command, stderr=subprocess.PIPE)
    _, gsutil_stderr = gsutil_proc.communicate()

    # check the gsutil process
    if gsutil_proc.returncode != 0:
        raise MysqlBackupGcsException("Error when uploading backup file using gsutil: " + gsutil_stderr)


def delete_local_backup_file(local_file):
    os.remove(local_file)


def purge_old_backups_from_gcs(gcs_directory, file_prefix, date_time_format):
    # gsutil command to copy file to cloud
    gsutil_command = ('gsutil', 'ls', gcs_directory + file_prefix + '*')

    # start the gsutil command
    gsutil_proc = subprocess.Popen(gsutil_command, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    gsutil_stdout, gsutil_stderr = gsutil_proc.communicate()

    # check the gsutil process
    if gsutil_proc.returncode != 0:
        raise MysqlBackupGcsException("Error when listing backup files using gsutil: " + gsutil_stderr)

    # parse the gsutil ls output
    backup_files = filter(None, (line.replace(gcs_directory, '').strip() for line in gsutil_stdout.splitlines()))

    time_now = datetime.now()
    gsutil_rm_command = []
    for file in backup_files:
        # parse dates from the filenames currently in the cloud's backup directory
        formatted_time = file.replace(file_prefix, '').split('.')[0]
        file_time = datetime.strptime(formatted_time, date_time_format)
        time_delta = time_now - file_time

        # check to see if the cloud backup is older than the retention period
        if time_delta.days > BACKUP_RETENTION_DAYS:
            print("Purging old backup: " + gcs_directory + file)
            gsutil_rm_command.append(gcs_directory + file)

    # check to see if there are any files to purge
    if gsutil_rm_command:

        # prepend the "gsutil rm" command to the list of files to purge
        gsutil_rm_command.insert(0, 'gsutil')
        gsutil_rm_command.insert(1, 'rm')

        # start the gsutil rm command
        gsutil_proc = subprocess.Popen(gsutil_rm_command, stderr=subprocess.PIPE)
        _, gsutil_stderr = gsutil_proc.communicate()

        # check the gsutil rm process
        if gsutil_proc.returncode != 0:
            raise MysqlBackupGcsException("Error when purging old backup files using gsutil: " + gsutil_stderr)


def log_to_hipchat(message, error=False):
    # hipchat postdata
    post = {
        'color': "green" if error is False else "red",
        'message': message,
        'notify': error,
        'message_format': 'text'
    }

    # curl command to post
    curl_command = ('curl', '-d', json.dumps(post), '-H', 'Content-Type: application/json', HIPCHAT_INTEGRATION_LINK)

    # start the cURL process
    curl_proc = subprocess.Popen(curl_command, stderr=subprocess.PIPE)
    _, curl_stderr = curl_proc.communicate()

    # check the cURL process
    if curl_proc.returncode != 0:
        raise MysqlBackupGcsException("Error when logging to hipchat: " + curl_stderr)


def main():
    # main controller
    try:
        # define file names and paths
        date_time_format = "%Y-%m-%d_%H-%M-%S"
        file_prefix = MYSQL_NODE_NAME + '.'
        filename = file_prefix + datetime.now().strftime(date_time_format) + '.sql'
        if USE_GZIP:
            filename += '.gz'
        local_file = os.path.join(LOCAL_BACKUP_DIRECTORY, filename)
        gcs_directory = GCS_BACKUP_DIRECTORY.rstrip("/") + "/"
        gcs_file = gcs_directory + filename

        # run appropriate functions
        write_backup_local(local_file)
        upload_backup_to_gcs(local_file, gcs_file)
        delete_local_backup_file(local_file)
        purge_old_backups_from_gcs(gcs_directory, file_prefix, date_time_format)

        # log success message
        success_message = MYSQL_NODE_NAME + ": stored " + filename + " to " + gcs_file
        print(success_message)
        if HIPCHAT_INTEGRATION:
            log_to_hipchat(success_message)

        # exit successfully
        exit(0)

    except Exception as e:
        # find the error message
        error_message = MYSQL_NODE_NAME + ": "
        if isinstance(e, MysqlBackupGcsException):
            error_message += str(e)
        else:
            error_message += "Unexpected error:" + str(e)

        # print error message to stderr
        print(error_message, file=sys.stderr)

        # optionally log to hipchat
        if HIPCHAT_INTEGRATION:
            try:
                log_to_hipchat(error_message, error=True)
            except Exception:
                pass

        # throw original exception
        raise


if __name__ == "__main__":
    main()

Requirements:

• Python
• gsutil (https://cloud.google.com/storage/docs/gsutil_install)
• mysqlbackup

Configure constants at top to match your desired configuration. Ensure that the LOCAL_BACKUP_DIRECTORY exists on your local machine and that the GCS_BACKUP_DIRECTORY exists in Google Cloud Storage.

Sample Configuration:

1. Copy file to /root/mysql-backup-gcs.py

2. Make the file executable:

   chmod +x /root/mysql-backup-gcs.py
   

3. Test the script:

   /root/mysql-backup-gcs.py
   

4. Edit your crontab file:

   crontab -e
   

5. Add the desired configuration to your crontab file and save (this example executes the script daily at 3:30 AM):

   30 3 * * * /root/mysql-backup-gcs.py
   

When running gsutil under cron on Google Compute Engine Debian 7 (Wheezy) Backports, gsutil fails with the following error:

    Can't locate LWP/UserAgent.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.14.2 /usr/local/share/perl/5.14.2 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.14 /usr/share/perl/5.14 /usr/local/lib/site_perl) at /usr/bin/gsutil line 26.
    BEGIN failed--compilation aborted at /usr/bin/gsutil line 26.

To resolve this problem, execute the following 2 commands:

    rm /usr/bin/gsutil
    ln -s /usr/local/bin/gsutil /usr/bin/gsutil