Thursday 9 January 2014

Migrating mysql database from localhost to bluehost server

The problem
I was facing a problem: my colleague had created some new tables (and added new columns to the existing tables) on his local server. When I pulled in his code via Git, the app stopped working because it now relied on these non-existent tables.

The question was: how would I replicate the tables on my server? I could just overwrite my database completely and replace it with a dump from PHPMyAdmin. This would work on my local server but when it was time to push the code to the production server (which has live data that we don't want to lose), we would have some issues.

PHPMyAdmin doesn't export dumps with ALTER statements. The dump is full of 'drop' and 'create if not exists' statements. Not a nice way to treat customer data;)

Option 1: human generated patch files
One solution would be for both of us to keep a log of any new columns/tables we added. However, I'm quite skeptical of human ability to stick to a routine like this. Chances are, someone would forget to add a column to the patch file and we'd be screwed when we pushed it live.

Option 2: auto-generate using Toad for MySQL
Toad for MySQL has a nice looking 'compare' feature. However, sadly it doesn't work on Bluehost because it requires you to have access to the db table on the mysql schema. They weren't willing to give me access to that because it would let me look at the DBs of all their customers:P

Option 3: autogenerate using mysql-diff
mysql-diff is a Perl script that can compare two databases and auto-generate a patch file. I managed to get it to work after a bit of tweaking.

Here're the steps I took:

1. Install mysql-diff following these instructions

2. Do a schema dump (no data) from PHPMyAdmin for both databases (choose structure only)

3. Edit the dump files to remove the 'USE' statement at the start

4. Run mysql-diff (make sure you put the most recent version of the schema first)
e.g. mysqldiff "dumps/pixcappc_green_test_db.sql" "dumps/pixcappc_green_test_db_localhost.sql"

5. Copy the patch file from the command line/bash into a text file

6. Make sure there are no unnecessary linebreaks (I got caught up for 15 mins because I had TIME
STAMP - with STAMP on a new line)

7. Import it into phpmyadmin

No comments: