I love automating things. I have a computing philosophy that pretty much forces me to automate anything I’ve done more than a couple of times. So I thought it’d be fun document my latest attempt at automation while I’m doing it. Here goes:

First off, I thought I’d use some tools that I’m pretty familiar with: Fabric and mysqldump. The basic idea is to write something that’ll achieve the following:

• Dump the database on the source machine
• Archive the resulting script
• Download the archive
• Extract the archive on a target machine
• Run the script to create the database

This assumes that the database has been created on the local machine. There are 2 settings modules imported at the start of this script called settings_live and settings_dev - they are both django settings files, one for the live website, and one for the development environment.

def db_to_dev():
    msg = prompt("Sure you want to get '%s' from '%s>%s'?" % (settings_dev.DATABASES['default']['NAME'], env.hosts[0], env.database), default="y/n")
    if msg == "y":
        run('mysqldump --user %s --password=%s %s | gzip > /tmp/%s.sql.gz' % (
                env.db_user, 
                env.db_password, 
                env.database, 
                env.database
                ))
        get('/tmp/%s.sql.gz' % env.database, '/tmp/%s.sql.gz' % env.database)
        local('gunzip < /tmp/%s.sql.gz | mysql -u %s -p%s -D %s' % (
                settings_dev.DATABASES['default']['NAME'],
                settings_dev.DATABASES['default']['USER'], 
                settings_dev.DATABASES['default']['PASSWORD'], 
                settings_dev.DATABASES['default']['NAME'] 
                ), capture=False)

Ok, so this basically runs 3 commands:

mysqldump --user %s --password=%s %s | gzip > /tmp/%s.sql.gz

This one dumps the database as SQL into a gzipped file in /tmp. The get() command downloads the archive.

gunzip < /tmp/%s.sql.gz | mysql -u %s -p%s -D %s

This unzips it and pipes the sql straight to mysql.

There is a support function called live() that sets up the environment for dumping and zipping up the database.

def live():
    """Stub function, doesn't do anything but must be called before
    anything else to set up the environment"""
    env.django_settings = settings_live
    #set up our environment
    env.hosts = ['live.server.website.com']
    env.user = 'serverlogin'
    env.password = 'serverpassword'
    #settings from django
    env.app_name = env.django_settings.APP_NAME
    env.email = env.django_settings.ADMINS[0][1]
    env.database = env.django_settings.DATABASES['default']['NAME']
    env.db_user = env.django_settings.DATABASES['default']['USER']
    env.db_password = env.django_settings.DATABASES['default']['PASSWORD']

The database can then be moved from live to dev with the following command:

fab live db_to_dev

I have also put in a very similar function together for moving from the development database to another server.

def db_from_dev():
    msg = prompt("Sure you want to put '%s' on '%s>%s'?" % (settings_dev.DATABASES['default']['NAME'], env.hosts[0], env.database), default="y/n")
    if msg == "y":
        local('mysqldump --user %s --password=%s %s | gzip > /tmp/%s.sql.gz' % (
                settings_dev.DATABASES['default']['USER'], 
                settings_dev.DATABASES['default']['PASSWORD'], 
                settings_dev.DATABASES['default']['NAME'],
                settings_dev.DATABASES['default']['NAME'] 
                ))
        put('/tmp/%s.sql.gz' % env.database, '/tmp/%s.sql.gz' % env.database)
        run('gunzip < /tmp/%s.sql.gz | mysql -u %s -p%s -D %s' % (
                env.database, 
                env.db_user, 
                env.db_password, 
                env.database
                ), capture=False)

This is basically just a reversal of db_to_dev() and requires a function similar to live() to be called first.

fab [other_env] db_from_dev

Related: databases, django, fabric