Wednesday, December 29, 2010

Use MySQL Workbench to sync schemas between dev and test environments

With the newer versions of MySQL Workbench they've made it fairly easy to sync schemas. I often use this to sync my development and test schemas so that they match when I push a new release. This is useful to preserve the data in the test or live environment, as opposed to just dumping the dev database and restoring it to the test schema.

First, make sure you backup both databases in case something goes wrong.

On the home screen in the Data Modeling section, click Create EER Model From Existing Database. Connect it to your dev database, or the database that has the schema that needs to be pushed out to another database. Choose your database and use the defaults.

You should end up with a diagram of your database. At the top click Database->Synchronize Model. Now connect it to your test/live database or the database that needs to be updated. You should now be on the Model and Database Differences screen. Expand your database to see the tables that will be altered. Click next to see the code that will be executed. Click execute to the sync the databases.

No comments: