Created
January 4, 2018 03:09
-
-
Save craigafinch/cc7cd4780dae90642eb61d0b1ad55f1a to your computer and use it in GitHub Desktop.
Export data from MySQL in a form suitable for import into Google Cloud SQL
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 | |
import subprocess | |
import os | |
import datetime | |
# Get list of views from MySQL | |
getViews = subprocess.Popen(['mysql', 'core'], stdin=subprocess.PIPE, stdout=subprocess.PIPE) | |
views = getViews.communicate("SHOW FULL TABLES IN core WHERE TABLE_TYPE LIKE 'VIEW';")[0].strip() | |
# Build a mysqldump command that skips all views | |
dump_command = [ | |
'mysqldump', '-h', 'localhost', '-u', 'root', | |
'--compress', '--hex-blob', '--force', | |
'--skip-triggers', '--default-character-set=utf8' | |
] | |
for line in views.split(os.linesep): | |
dump_command.append("--ignore-table=core.{} ".format(line.split()[0].strip())) | |
dump_command.append('core') | |
# print(dump_command) | |
# Dump the database | |
today = datetime.date.today() | |
dumpFileName = '/var/lib/mysql/core-{}.sql'.format(today.strftime("%Y-%m-%d")) | |
with open(dumpFileName, 'w') as dumpFile: | |
subprocess.check_call(dump_command, stdout=dumpFile) | |
# Upload uncompressed dump file to Google Cloud Storage and delete dump file | |
# If you have the Google Cloud Client Library, use that instead | |
subprocess.check_call(['gsutil', '-q', 'cp', dumpFileName, 'gs://prod-to-staging/']) | |
os.remove(dumpFileName) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment