Last active
December 11, 2015 20:16
-
-
Save caleblloyd/bd2ee168e97cb751dbfe to your computer and use it in GitHub Desktop.
MySQL Backup to Google Cloud Storage with Hipchat Integration
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
When running gsutil under cron on Google Compute Engine Debian 7 (Wheezy) Backports, gsutil fails with the following error:
To resolve this problem, execute the following 2 commands: