How to collaborate on mysql schema?

Question

I'm working with another dev and together we're building out a MySQL database. We've each got our own local instances of MySQL 5.1 on our dev machines. We've not yet been able to identify a way for us to be able to make a local schema change (eg: add a field and some values for that field) and then export some kind of script or diff file that the other can import in. I've looked into Toad and Navicat's synchronization features but they seem oriented towards synchronizing between two instances, not an instance and an intermediate file. We thought MySQL Workbench would be great but this but the synchronization feature just seems plain broken. Any other ideas? How do you collaborate with others on the schema?

Answer 1

First of all put your final SQL schema into version control. So you'll always have a version of it with all changes. It can be a plain SQL file. Every developer in the team can use it as starting point to created his copy database. All changes must be applied to it. This will help you to find conflicts faster.

Also I used such file to create a test database to run unit-tests after each submit. So we were always sure that production code is working.

Then you can use any migration tool to move changed between developers. Here is similar question about this:

• Mechanisms for tracking DB schema changes

If you're using PHP then look at Doctrine migrations.