It is possible to synchronize a model in MySQL Workbench with a live database. By default, the synchronization process will change the live database to be the same as the model, but this is configurable during the synchronization process.
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 the direction of synchronization, and which objects to synchronize, in a completely flexible way. 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.
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:
In the preceding example, the live database and model both have
shows tables. In the
MySQL Workbench, an additional table,
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
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.
In this case, the synchronization direction has been changed so
that rather than the default action of
being dropped from the live database, it will be incorporated into
the MySQL Workbench model. As before,
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:
Pressing Next will reveal the SQL statement to perform the configured model and live database (source) synchronization:
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.
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:
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.