python code to migrate the MySQL database

  |   Source

The script can read configuration file and runs on python 2.4.

#!/usr/bin/python
import sys

import os

import string

import time

import datetime

import getopt

#############

# CONFIG

#############

DB_NAME='mydb'

DB_PASSWORD='111111'

DB_USER='root'

DB_HOST='mylinux0'

SQL_DB_SCHEMA=os.path.abspath("db_schema.sql")

SQL_CREATE_TABLE=os.path.abspath("000.sql")

CLONE_TABLES=['MyJobs','MyUsers']

#############

# GLOBALS

#############

backup_db=False

migration=False

config=None

verbose=False

delete=False

remote=False

def sql_cmd(cmd): if remote: return 'mysql -h %s -u %s -p%s -e " %s " %s' % (DB_HOST,DB_USER,DB_PASSWORD,cmd,DB_NAME) return 'mysql -u %s -p%s -e " %s " %s' % (DB_USER,DB_PASSWORD,cmd,DB_NAME)

def sql_script(script): if remote: return 'mysql -h %s -u %s -p%s %s < %s' % (DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,script) return 'mysql -u %s -p%s %s < %s' % (DB_USER,DB_PASSWORD,DB_NAME,script)

def make_sql_to_create_table(): s=open(SQL_DB_SCHEMA).read() for t in CLONE_TABLES: s=s.replace(t,t+'2') open(SQL_CREATE_TABLE,'w').write(s)

def backup_database(): fname="%s-%s.dump.gz" % (DB_NAME,time.strftime('%Y%m%d%H%M%S', time.gmtime())) if remote: cmd="mysqldump -h %s -u %s -p%s --compact %s|gzip -9 > %s" % (DB_HOST,DB_USER,DB_PASSWORD,DB_NAME,fname) else: cmd="mysqldump -u %s -p%s --compact %s|gzip -9 > %s" % (DB_USER,DB_PASSWORD,DB_NAME,fname)

<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

def delete_db_records(): cmd=sql_cmd("delete from sources;delete from cameras;") print "==WILL RUN [ %s ]" % (cmd) os.system(cmd) print "==DONE"

def package(): cmd="tar zcvf migrate-db.tar.gz .sql migrate.py .migrate_" print "==WILL RUN [ %s ]" % (cmd) os.system(cmd) print "==DONE"

def read_config(config): global DB_NAME global DB_PASSWORD global DB_USER global DB_HOST f=open(config,"r") for line in f: a=line.split("=") if len(a)!=2: continue n=a[0].strip() v=a[1].strip() if n=="DB_NAME": DB_NAME=v elif n=="DB_PASSWORD": DB_PASSWORD=v elif n=="DB_USER": DB_USER=v elif n=="DB_HOST": DB_HOST=v

def usage(): print ''' USAGE: python migrate.py [OPTIONS]

OPTIONS:

-h, --help

print this help

-b, --backup-database

backup the old database before migration (recommended)

-m, --migrate

donot start migration

-v, --verbose

print database related information

-p, --package

packge all the scripts into migrate-db.tar.gz

-d, --delete-db-records

delete database records so we can restart migration

-c, --config

SAMPLES:

python migrate.py -b #back database and exit

python migrate.py -m #start migration right now

python migrate.py -b -m #backup the database and start the migration (recommended)

python migrate.py -v #print database information and exit

python migrate.py -c .migrate_staging -v #print staging database information and exit

'''

def show_info(): print ''' ==Database Information

name:{name}

password:{password}

user:{user}

host:{host}

'''.format(name=DB_NAME, password=DB_PASSWORD,user=DB_USER,host=DB_HOST)

def confirm(prompt=None, resp=False): """prompts for yes or no response from the user. Returns True for yes and False for no.

'resp' should be set to the default value assumed by the caller when

user simply types ENTER.

>>> confirm(prompt='Create Directory?', resp=True)

Create Directory? [y]|n:

True

>>> confirm(prompt='Create Directory?', resp=False)

Create Directory? [n]|y:

False

>>> confirm(prompt='Create Directory?', resp=False)

Create Directory? [n]|y: y

True

"""

<span style="color: #af00ff;">if</span> prompt <span style="color: #af00ff;">is</span> <span style="color: #008787;">None</span>:
    prompt = <span style="color: #87005f;">'WARNING: DONOT run me twice and migration CANNOT be rolled back, GO?'</span>

<span style="color: #af00ff;">if</span> resp:
    prompt = <span style="color: #87005f;">'%s [%s]|%s: '</span> % (prompt, <span style="color: #87005f;">'y'</span>, <span style="color: #87005f;">'n'</span>)
<span style="color: #af00ff;">else</span>:
    prompt = <span style="color: #87005f;">'%s [%s]|%s: '</span> % (prompt, <span style="color: #87005f;">'n'</span>, <span style="color: #87005f;">'y'</span>)

<span style="color: #af00ff;">while</span> <span style="color: #af00ff;">True</span>:
    ans = <span style="color: #5f5f87;">raw_input</span>(prompt)
    <span style="color: #af00ff;">if</span> <span style="color: #af00ff;">not</span> ans:
        <span style="color: #af00ff;">return</span> resp
    <span style="color: #af00ff;">if</span> ans <span style="color: #af00ff;">not</span> <span style="color: #af00ff;">in</span> [<span style="color: #87005f;">'y'</span>, <span style="color: #87005f;">'Y'</span>, <span style="color: #87005f;">'n'</span>, <span style="color: #87005f;">'N'</span>]:
        <span style="color: #af00ff;">print</span> <span style="color: #87005f;">'please enter y or n.'</span>
        <span style="color: #af00ff;">continue</span>
    <span style="color: #af00ff;">if</span> ans == <span style="color: #87005f;">'y'</span> <span style="color: #af00ff;">or</span> ans == <span style="color: #87005f;">'Y'</span>:
        <span style="color: #af00ff;">return</span> <span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">if</span> ans == <span style="color: #87005f;">'n'</span> <span style="color: #af00ff;">or</span> ans == <span style="color: #87005f;">'N'</span>:
        <span style="color: #af00ff;">return</span> <span style="color: #af00ff;">False</span>

if name=="main": ''' @see http://linux.byexamples.com/archives/366/python-how-to-run-a-command-line-within-python/

''' try: opts, args = getopt.getopt(sys.argv[1:], "hbmvpdc:r", ["help", "backup-database","--migrate","--verbose","--package","--delete-db-records","--config=","--remote"]) except getopt.GetoptError, err: print str(err) # will print something like "option -a not recognized" usage() exit(2)

<span style="color: #af00ff;">if</span> <span style="color: #5f5f87;">len</span>(opts)==0:
    usage()
    <span style="color: #5f5f87;">exit</span>(2)

<span style="color: #af00ff;">for</span> o, a <span style="color: #af00ff;">in</span> opts:
    <span style="color: #af00ff;">if</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-h"</span>, <span style="color: #87005f;">"--help"</span>):
        usage()
        sys.exit()
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-v"</span>, <span style="color: #87005f;">"--verbose"</span>):
        verbose=<span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-c"</span>, <span style="color: #87005f;">"--config"</span>):
        config=a
        <span style="color: #af00ff;">print</span> config
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-d"</span>, <span style="color: #87005f;">"--delete-db-records"</span>):
        delete=<span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-p"</span>, <span style="color: #87005f;">"--package"</span>):
        package()
        <span style="color: #5f5f87;">exit</span>(0)
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-b"</span>, <span style="color: #87005f;">"--backup-database"</span>):
        backup_db=<span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-m"</span>, <span style="color: #87005f;">"--migrate"</span>):
        migration=<span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">elif</span> o <span style="color: #af00ff;">in</span> (<span style="color: #87005f;">"-r"</span>, <span style="color: #87005f;">"--remote"</span>):
        remote=<span style="color: #af00ff;">True</span>
    <span style="color: #af00ff;">else</span>:
        <span style="color: #af00ff;">assert</span> <span style="color: #af00ff;">False</span>, <span style="color: #87005f;">"unhandled option"</span>

<span style="color: #af00ff;">if</span> config!=<span style="color: #008787;">None</span>:
    read_config(config)

<span style="color: #af00ff;">if</span> delete:
    delete_db_records()
    sys.exit()

<span style="color: #af00ff;">if</span> verbose:
    show_info()
    sys.exit()

<span style="color: #af00ff;">if</span> backup_db:
    backup_database()

<span style="color: #af00ff;">if</span> migration==<span style="color: #af00ff;">False</span>:
    <span style="color: #5f5f87;">exit</span>(0)

<span style="color: #af00ff;">if</span> confirm()==<span style="color: #af00ff;">False</span>:
    <span style="color: #af00ff;">print</span> <span style="color: #87005f;">"Aborting ..."</span>
    <span style="color: #5f5f87;">exit</span>(0)

make_sql_to_create_table()

<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==START MIGRATION"</span>

<span style="color: #af0000;"># test data base connection</span>
cmd=sql_cmd(<span style="color: #87005f;">'show databases;use %s;'</span> % (DB_NAME))
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

cmd=sql_script(<span style="color: #87005f;">"000.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('001.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"001.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('002.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"002.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('003.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"003.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('004.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"004.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('005.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"005.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('006.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"006.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

os.system(sql_cmd(<span style="color: #87005f;">"insert into migrate (Action) values ('100.sql')"</span>))
cmd=sql_script(<span style="color: #87005f;">"100.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

cmd=sql_script(<span style="color: #87005f;">"insert_sample_sources.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

cmd=sql_script(<span style="color: #87005f;">"app_config.sql"</span>)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==WILL RUN [ %s ]"</span> % (cmd)
os.system(cmd)
<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==DONE"</span>

<span style="color: #af00ff;">print</span> <span style="color: #87005f;">"==END MIGRATION"</span>

# vim: set expandtab tabstop=4 shiftwidth=4:

Comments powered by Disqus