Script for merging dev mysql database to deployment mysql database for Django app

Question

I am working on a Django app and have now reached the point of setting up my deployment process. I would like to automate this as much as possible and as such am looking for any help I can get in terms of database management. As I see it, this is the rough outline of the process I would like to automate: 1. back up the current database on the live server 2. merge the structure of the dev and deploy databases so as to keep only the data in the deployment db but update the structure to match the dev db

I have considered simply applying all of the new migrations from dev to the live db however I fear that this will result in a series of errors due to default values and such.

Any insights into where I could look to get started would be greatly appreciated!

Answer 1

Here are the commands. You can use whatever your favorite automation language is.

1. back up the current database on the live server

mysqldump -u username -p database_name | gzip -c > live_server_backup.sql.gz

2. merge the structure of the dev and deploy databases so as to keep only the data in the deployment db but update the structure to match the dev db

For this, our typical process is to actually just apply the migrations. If you are at all worried about the migrations, you can do this on your dev server:

# 1.  backup live database
mysqldump -u username -p database_name | gzip -c > live_server_backup.sql.gz  

# 2.  Transfer the backup to the dev server using scp
# 3.  backup dev database
mysqldump -u username -p database_name | gzip -c > dev_server_backup.sql.gz

# 4.  Load the live database into your dev database server
mysql -u username -p -e 'drop database database_name ; create database database_name'
gunzip -c live_server_backup.sql.gz | mysql -u username -p database_name

# 5.  Apply all migrations against dev-copy of live server
cd /path/to/django/root
. bin/activate # if you are using virtualenv
python manage.py migrate

This way you can test whether any of your migrations will cause issues on the live server without actually affecting the live server itself. After you have confirmed, you can:

# 6. 
mysql -u username -p -e 'drop database database_name; create database database_name;'
# 7. Restore dev database
gunzip -c dev_server_backup.sql.gz | mysql -u username -p database_name