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?