Compare two MySQL databases [closed]

Question

I'm currently developing an application using a MySQL database.

The database-structure is still in flux and changes while development progresses (I change my local copy, leaving the one on the test-server alone).

Is there a way to compare the two instances of the database to see if there were any changes?

While currently simply discarding the previous test server database is fine, as testing starts entering test data it could get a bit tricky.
The same though more so will happen again later in production...

Is there an easy way to incrementally make changes to the production database, preferably by automatically creating a script to modify it?

---

Tools mentioned in the answers:

• Red-Gate's MySQL Schema & Data Compare (Commercial)
• Maatkit (now Percona)
• liquibase
• Toad
• Nob Hill Database Compare (Commercial)
• MySQL Diff
• SQL EDT (Commercial)

Answer 1

From the feature comparison list... MySQL Workbench offers Schema Diff and Schema Synchronization in their community edition.

Answer 2

There are many ways certainly, but in my case I prefer the dump and diff command. So here is an script based on Jared's comment:

#!/bin/sh

echo "Usage: dbdiff [user1:pass1@dbname1] [user2:pass2@dbname2] [ignore_table1:ignore_table2...]"

dump () {
  up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
  mysqldump --opt --compact --skip-extended-insert -u $user -p$pass $dbname $table > $2
}

rm -f /tmp/db.diff

# Compare
up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
for table in `mysql -u $user -p$pass $dbname -N -e "show tables" --batch`; do
  if [ "`echo $3 | grep $table`" = "" ]; then
    echo "Comparing '$table'..."
    dump $1 /tmp/file1.sql
    dump $2 /tmp/file2.sql
    diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff
  else
    echo "Ignored '$table'..."
  fi
done
less /tmp/db.diff
rm -f /tmp/file1.sql /tmp/file2.sql

Feedback is welcome :)

Answer 3

I'm working with Nob Hill's Marketing team, I wanted to tell you I'll be happy to hear your questions, suggestion or anything else, please feel free to contact me.

We originally decided to create our tool from scratch because while there are other such products on the market, none of them do the job right. It’s quite easy to show you the differences between databases. It’s quite another to actually make one database like the other. Smooth migration, both of schema and data, has always been a challenge. Well, we have achieved it here.
We are so confident that it could provide you a smooth migration, than if it doesn’t – if the migration scripts it generates are not readable enough or won’t work for you, and we can’t fix it in five business days – you will get your own free copy!

http://www.nobhillsoft.com/NHDBCompare.aspx

Answer 4

SQL Compare by RedGate http://www.red-gate.com/products/SQL_Compare/index.htm

DBDeploy to help with database change management in an automated fashion http://dbdeploy.com/

Answer 5

The apache zeta components library is a general purpose library of loosly coupled components for development of applications based on PHP 5.

eZ Components - DatabaseSchema allows you to:

   .Create/Save a database schema definition;
   .Compare database schemas;
   .Generate synchronization queries;

You can check the tutorial here: http://incubator.apache.org/zetacomponents/documentation/trunk/DatabaseSchema/tutorial.html

Answer 6

Have a look at http://www.liquibase.org/