MySQL database synchronization tools
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.
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.1. See also Section 3.2.4, “Modeling Preferences”.
To start, select from the 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 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