Database change management is a complex process that involves maintaining different versions of database schemas and manually modifying existing databases. To help with this administration task, MySQL Workbench includes schema synchronization and comparison utilities. You can compare two live databases, two models, or models with live databases, to visually see the differences and optionally perform a synchronization routine.
9.5.1 Database Synchronization
Synchronize data between models, databases, and SQL files. These three types can be the target (destination), source, or both. You can also select/deselect individual objects and modify their direction during the synchronization. For example, synchronize tables from a model to your database, other tables from your database to your model, and skip a few tables, all during the same synchronization process.
To start, select Synchronize With Any Source from the Database navigation menu. Alternatively, select Synchronize Model to open the same wizard that defaults to a model. A Model or EER diagram must be selected for these synchronization options to be present under the Database navigation menu.
Figure 9.50 Start the Synchronization Wizard
Caution
Because MySQL databases correspond to directories within the data directory, you must consider case sensitivity for database, table, and trigger names, which follow the case sensitivity rules of the underlying file system for your operating system. Synchronizing models with objects that differ in case may lead to MySQL Workbench producing a DROP statement for that object, before recreating it as lowercase. For more information, see Identifier Case Sensitivity
Workarounds include using a consistent convention, where the most portable code uses lower case database and table names. Or a temporary workaround is to delete the DROP SCHEMA IF EXISTS line from the generated query.
MySQL Workbench enables control over objects to synchronize, and the direction of synchronization for each object. Synchronization options include:
Specify all or specific tables and objects to synchronize
Synchronize both the model and live database, or only update one or the other (unidirectional or bidirectional)
Optionally update from or to an SQL script file
Instead of executing the synchronization, you may generate an ALTER Script File to later perform the appropriate updates
Fine-tune how the synchronization will be performed by choosing the direction of each individual object, or configuring particular objects to be ignored
There are two similar database synchronization wizards available from the Database menu. The simpler Synchronize Model wizard, and the more flexible Synchronize with Any Source wizard. The descriptions below apply to both, unless explicitly told otherwise.
Synchronize Model (with database)
To start the wizard, open a model and select Database, Synchronize Model from the main menu. Follow the steps until you reach the Select Changes to Apply step:
Figure 9.51 Model and Database Differences
In the preceding example, the live database and model both have movies shows tables. In the MySQL Workbench, an additional table, educational, has been created in the model, but it lacks an equivalent in the live database. Further, friends exists in the live database, but it is not in the model. By default, the actions will synchronize the database with the model, so in this example the educational table will be added to the source, and the friends table will be removed from the source.
As described in the GUI, double-clicking the arrows will alternate between the Update Model, Ignore, and Update Source actions. You may also select a row and click one of the three action buttons. Also note that clicking on a row will reveal the associated SQL statement, as shown in the screenshot above.
The next example shows how the direction of synchronization can be changed.
Figure 9.52 Controlling Synchronization Direction
In this case, the synchronization direction has been changed so that rather than the default action of friends being dropped from the live database, it will be incorporated into the MySQL Workbench model. As before, educational table will be added to the live (source) database.
The three actions available actions are:
Update Model: Causes the selected changes to be applied to the model, from the live database.
Ignore: Causes the changes to be ignored. No synchronization will take place for those changes. This is designated with a double arrow that is crossed out.
Update Source: Causes the changes to be applied only to the live database.
Clicking Table Mapping offers additional mapping options:
Figure 9.53 Table Mapping
Pressing Next will reveal the SQL statement to perform the configured model and live database (source) synchronization:
Figure 9.54 Previewing The Synchronization SQL Statement
You may now save the SQL statement to a file or the clipboard, or execute the SQL statement. If you choose to execute the change in MySQL Workbench, then you may optionally choose to skip “DB changes” so that only your model is altered.
Synchronize With Any Source
To start the wizard, open a model and select Database, Synchronize With Any Source from the main menu. The steps are similar to the Synchronize Model wizard, but with additional options to create and/or use SQL script files. See the Select Sources page:
Figure 9.55 Synchronize With Any Source: Select Sources
Notice how the source and destination types can be altered. The steps that follow depend on these source and destination types, and the Synchronize Model describes the basic functionality of this wizard.
9.5.2 Compare and Report Differences in Catalogs
This facility enables you to create a report detailing the differences between your MySQL Workbench model, and a live database or script. Choose Database, Compare Schemas from the main menu to run the Compare and Report Differences in Catalogs wizard.
The first step in the wizard is to specify which catalogs to compare. For example, you may wish to compare your live database against your current MySQL Workbench model.
Figure 9.56 Catalog Sources Selection
You then proceed through the wizard, providing connection information if accessing a live database. The wizard then produces a catalog diff report showing the differences between the compared catalogs.
Figure 9.57 Catalog Differences Report
