MySQL diff generator for use in a Joomla context

Question

Is there any (mysql) db diff tool that can output the differences between 2 dump files (or 2 db) as a series of ddl and dml sql statements?

I would like to maintain the db version controlled and so far the 'best' option i've seen is create a initial dump of the database and then put the modifications as a serie of scripts.

The problem is, we are using joomla, so is hard to create those modifications scripts by hand. (You make a menu, db changes, create content, db change, etc.)

For the id's collisions problem a guess we could configure every developer machine to generate ids in determined ranges, lets say dev1 [1..1000], dev2 [1001..2000] and try hard to avoid several developers working in the same 'content'

Supposing that the tool exists what other things i have to consider to get the smoothes experience?

Answer 1

Take a look at MySQL Workbench 5.2.21+. It allows you to create a database model by reverse engineering an existing DB. You can then create diffs against that model and future changes. It has really become an excellent tool, a must-have for all MySQL DBA's. That will handle schema changes.

mk-table-sync from Maatkit will help you create SQL that will allow you to sync your data.