Automatically dump SQL data to Google Cloud Storage

backup_sql.py

#!/usr/bin/env python
import subprocess
import os
import pipes
import argparse
from zipfile import ZipFile
from google.cloud import storage
from datetime import datetime

parser = argparse.ArgumentParser()
parser.add_argument("--bucket", metavar='bucket_name', type=str, required=True)
parser.add_argument("--auth", metavar='auth_key_file', type=str, required=True)
parser.add_argument("--db", metavar='database', type=str, required=True)
args = vars(parser.parse_args())

# print(args)

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = args['auth']

def upload_blob(bucket_name, source_file_name, destination_blob_name):
    storage_client = storage.Client()
    bucket = storage_client.bucket(bucket_name)

    blob = bucket.blob(destination_blob_name)
    blob.upload_from_filename(source_file_name)

    print(
        "File {} uploaded to {}.".format(
            source_file_name, destination_blob_name
        )
    )

db = args['db']
date = datetime.now().strftime('%Y-%m-%dT%H_%M_%S')
dumpName = F"{db}_{date}"
dumpcmd = F"sudo mysqldump {db} > {dumpName}.sql"

os.system(dumpcmd)

zipObj = ZipFile(F"{dumpName}.zip", 'w')
zipObj.write(F"{dumpName}.sql")
zipObj.close()

os.remove(F"{dumpName}.sql")

upload_blob(args['bucket'], F"{dumpName}.zip" , F"{dumpName}.zip")

os.remove(F"{dumpName}.zip")

backup_sql.sh

#!/bin/bash
cd /var/www/scripts
source ./venv/bin/activate
python backup_sql.py --bucket sample_app.appspot.com --auth ./service_account_key.json --db laravel

requirements.txt

google-cloud-storage==1.39.0