Tóm Tắt
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 or deselect individual objects and modify their
direction during the synchronization. For example, you can
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.
Note
Be aware that backward incompatible MySQL syntax changes are
introduced over time, so for this reason it is important to set
the Default Target MySQL Version modeling
preference according to your needs. For example, exporting results
from a MySQL 5.7 target might yield invalid syntax when executed
against MySQL 5.6. See also
Section 3.2.4, “Modeling Preferences”.
To start, select Synchronize With Any
Source from the Database navigation
menu, as the following figure shows. 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.56 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 line from the generated query.
EXISTS
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 by 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 that follow apply to both, unless stated otherwise.
Synchronize Model (with Database)
To start the wizard, open a model and select
Database, Synchronize
Model from the main menu. Follow the sequence of steps
until you reach the Select Changes to Apply
step, as the next figure shows.
Figure 9.57 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 previous
figure.
The next figure shows an example of how the direction of
synchronization can be changed.
Figure 9.58 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, as the following figure shows.
Figure 9.59 Table Mapping
Clicking Next reveals the SQL statement to
perform the configured model and live database (source)
synchronization. The following figure shows an example preview.
Figure 9.60 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 and then Synchronize With
Any Source from the main menu. The steps are similar
to the Synchronize
Model wizard, but with additional options to create SQL
script files, use SQL script files, or both. The following figure
shows the Select Sources settings.
Figure 9.61 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.
