Wednesday, June 25, 2008

Using MySQL Workbench To Generate Structure Differences

As part of the database maintenance and deployment processes, there are always cases where you need to get the differences between the current (legacy) structure used by your customer, and the latest structures.

A fast and easy way to do this is by using the MySQL Workbench. The following are the steps (based on version 5.0.22):

1. Get the scripts of the databases that need to be compared.

- I have an example here with two sql scripts, mydb_old.sql and mydb_new.sql. These two have near identical structures (with same database name), except some changes in the mydb_new, where:
customer table: added an address field of type TEXT
supplier table: altered the phone field to length 100


2. Run Workbench. Close any Physical Schemata if any (e.g. the 'mysql' database) : Right click on the icon, then select 'Delete'.

3. Goto File > Import > Reverse Engineer MySQL Create Script ...
- Input File : Select the new data structure script file (mydb_new.sql), click Next;
- If the script has no error, the progress will show 'Execution completed successfully', click Next;
- 'Autoplace objects in new diagram' left untick if not needed. Click Finish.

4. Now the Physical Schemata will show the database structure that was imported.

5. Goto File > Export > Forward Engineer SQL Alter Script ...
- Input File : Select the old data structure script file (mydb_old.sql), click Next;
- After the process is completed, an 'SQL Diff Tree' screen will show the differences between the two database schema.

6. Click Next. Now you will see the update script for your database schema. Review the generated script, save the script to a file.

7. Exit the Workbench program. Do not save if prompt.

Note:
- Both scripts must have the same database name.
- Remember to always 'Import' the new script first, then 'Export' with the old script.

If you find this post helpful, would you buy me a coffee?


7 comments:

  1. I find SQLyog Enterprise's Structure Sync tool much more effective than MySQL Workbench for generating structure differences.

    I am curious to know what others think about it.

    ReplyDelete
  2. I like ModelRight better; it connects to the database directly and its compare and alter script generation options are far superior...
    http://www.modelright.com

    ReplyDelete
  3. Thanks for the comments.

    So far, Workbench is the only I could find that provide this without purchasing the full package.

    I've been using SQLyog community edition, but unfortunately this feature was not included in this edition. :)

    ReplyDelete
  4. Is the Sync function available in Community edition?

    ReplyDelete
  5. the last comment was 2008 Aug, that was 2 years ago, i wonder if i' get a response. i'm trying to use mysql workbench to sync 2 databases, but they having different names. i know it does not work that way, do you happen to know any workaround?

    ReplyDelete
  6. here's how:
    1. Follow instructions from step 1 - 4.

    2. Rename the name of the schema. Double click the database icon. A section will appear at the bottom for the "Name" to be entered.
    NOTE : The name MUST be the same as the target database name.

    3. Goto to Database > Synchronize Model... >
    Connect to the target database.

    4. Continue with step 6 -7.

    ReplyDelete
  7. Bonjour! Alaina Walker . payday loans

    ReplyDelete