MySQL Workbench Community Edition: Guide to Schema Synchronization

As you can see in the MySQL Workbench Edition feature grid, Live Schema Synchronization is a Standard Edition feature only. But that does not mean that you cannot make use of the same functionality in the OSS Edition in an offline scenario – which is even more preferable in some cases.

  1. Create an SQL CREATE script from your model
    You might already have the SQL CREATE script if you started your model with an import of an existing schema. If you started designing your model from scratch inside Workbench, you are going to export an SQL CREATE script anyway – in order to create the initial schema on the database server.
  2. Update your Workbench model
    At this point your database is already running. But as we all know you always have to make changes to your first design. Do the necessary changes to the model.
  3. Export SQL ALTER script
    Normally you would have to write the ALTER TABLE statements by hand now, reflecting your changes you made to the model. But you can be smart and let Workbench do the hard work for you. Simply select File -> Export -> Forward Engineer SQL ALTER Script… from the main menu. This will prompt you to specify the SQL CREATE file the current model should be compared to.Select the SQL CREATE script from step 1. The tool will then generate the ALTER TABLE script for you and you can execute this script against your database to bring it up to date. You can do this using the MySQL Query Browser or the mysql client.Voila! Your model and database have now been synchronized!
  4. Export SQL CREATE script
    In order to be able to repeat step 3 you will need a fresh SQL CREATE script for your next synchronization process. Also do this from inside the Workbench by selecting the File -> Export -> Forward Engineer SQL CREATE Script…  from the main menu. If you should forget to do this step at some point you can always use MySQL Administrator or mysqldump to create a new SQL CREATE script with the backup/dump functionality to get a new SQL CREATE script of your live database.
  5. Repeat steps 3-4
    If you repeat these steps for all your changes you never have to write any SQL to modify your database structure. And in addition you get a nice documentation of all your changes.

Of course, with MySQL Workbench SE, all the above manual steps are automated.  See the demo here.

2 thoughts on “MySQL Workbench Community Edition: Guide to Schema Synchronization”

  1. I have been using that when the beta was going!

    One problem though, is it normal that the ALTER script always drops and re-adds the foreign keys even for non-affected tables?

    Some times I just added one column but the ALTER script generates a whole bunch of other SQL statements.

Leave a Reply