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. You can choose to synchronize only certain tables, enable synchronization to the live database only, enable synchronization from the live database to the model only, or a combination of directions. In effect you have complete control as to whether the synchronization is unidirectional or bidirectional, and which objects exactly are subject to synchronization. This is all controlled in the Select Changes to Apply page of the synchronization wizard.
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.