MySQL Workbench provides capabilities to forward engineering physical database designs. A visual data model can be transformed into a physical database on a target MySQL Server by executing the forward engineering wizard. All SQL code is automatically generated to help eliminate the normal error-prone process of manually writing complex SQL code. MySQL Workbench also enables you to reverse engineer an existing database or packaged application to get better insight into its database design. In addition to forward and reverse engineering existing databases, it can also import SQL scripts to build models, and export models to DDL scripts to execute at a later time.
9.4.1 Forward Engineering
It is possible to forward engineer a database using an SQL script or by connecting to a live database.
9.4.1.1 Forward Engineering Using an SQL Script
To create a script of your database model, choose the Export item from the File menu. You may export a script to alter an existing database or create a new database. The script to create a database is similar to the one created using the mysqldump db_name command.
Choosing to create a database yields additional options.
9.4.1.1.1 Creating a Schema
Select the File, Export, Forward Engineer SQL CREATE Script menu item to start the Forward Engineer SQL Script wizard. The following figure shows the first page of the wizard.
Figure 9.30 SQL Export Options
The SQL Export Options displays the following facilities:
Output SQL Script FileTo specify the output file name, enter it into the Output SQL Script File field, or use the Browse button to select a file. Leave this field blank to view, but not save, the generated output.
Generate DROP Statements Before Each CREATE StatementSelect this option to generate a statement to drop each object before the statement that creates it. This ensures that any existing instance of each object is removed when the output is executed.
Generate DROP SCHEMASkip creation of FOREIGN KEYSSkip creation of FK Indexes as wellOmit Schema Qualifier in Object NamesSelect this option to generate unqualified object names in SQL statements.
Generate USE statementsGenerate Separate CREATE INDEX StatementsSelect this option to create separate statements for index creation instead of including index definitions in
CREATE TABLEstatements.Add SHOW WARNINGS after every DDL statementSelect this option to add
SHOW WARNINGSstatements to the output. This causes display of any warnings generated when the output is executed, which can be useful for debugging.Do Not Create Users. Only Export PrivilegesSelect this option to update the privileges of existing users, as opposed to creating new users. Exporting privileges for nonexistent users will result in errors when you execute the
CREATEscript. Exporting users that already exist will also result in an error.Don't create view placeholder tablesGenerate INSERT Statements for TablesSelect this option if you have added any rows to a table. For more information about inserting rows, see Section 8.1.1, “SQL Query Window”.
Disable FK checks for insertsCreate triggers after inserts
Clicking Next takes you to the SQL Object Export Filter page where you select the objects you wish to export.
Figure 9.31 SQL Object Export Filter
Use Show Filter to fine tune (filter) the objects for export. After selecting the objects to export, click Hide Filter to hide the filter panel.
After selecting the objects to export, click Next to review the generated script.
Figure 9.32 Review Generated Script
You may return to the previous page using the Back button.
The Finish button saves the script file and exits. You can then use the saved script to create a database.
9.4.1.1.2 Altering a Schema
The menu item for creating an ALTER Script File is Database, Synchronize With Any Source. Typically, this option is used when the SQL script of a database has been imported into MySQL Workbench and changed, and then you want to create a script that can be executed against the database to alter it to reflect the adjusted model. For instructions on importing a DDL script, see Section 9.4.2.1, “Reverse Engineering Using a Create Script”.
Select the Database, Synchronize With Any Source menu item to start the wizard. You will be presented with the first page showing the introduction, and then the available options:
Figure 9.33 Synchronize With Any Source: Options
For additional information, see Section 9.5.1, “Database Synchronization”.
9.4.1.2 Forward Engineering to a Live Server
Use forward engineering to export your schema design to a MySQL server.
Select the model that you wish to forward engineer and then choose the Database, Forward Engineer… menu item from the main menu.
The first step of the process is to connect to a MySQL server to create the new database schema. This page enables you to use a previously stored connection, or enter the connection parameters.
Figure 9.34 Set Parameters for Connecting to a DBMS
Click Next after setting the connection parameters. The next page of the wizard displays is Catalog Validation (validation is available only in the Commercial Edition).
Figure 9.35 Catalog Validation
Click Run Validations to validate the catalog.
Click Next to continue.
The next page enables you to set options for the database to be created. These options are as described in Section 9.4.1.1.1, “Creating a Schema”.
Figure 9.36 Options
Select the required options and then click Next.
The next page enables you to select the objects to forward engineer.
Figure 9.37 Select Objects to Forward Engineer
To select a subset of objects to forward engineer, use the Show Filter/Hide Filter button, then select specific objects. After you have selected your objects, click Next to continue
On the Review Script page you may review and edit the SQL script that will be executed.
Figure 9.38 Review Script
Click Next to continue if you are satisfied with the generated script.
The next page of the wizard displays the results of the forward engineering process.
Figure 9.39 Forward Engineering Progress
You can confirm that the script created the schema by connecting to the target MySQL server and issuing a SHOW DATABASES statement.
9.4.2 Reverse Engineering
With MySQL Workbench, you can reverse engineer a database using a MySQL create script or you can connect to a live MySQL server and import a single database or a number of databases. All versions of MySQL Workbench can reverse engineer using a MySQL DDL script. Only commercial versions of MySQL Workbench can reverse engineer a database directly from a MySQL server.
9.4.2.1 Reverse Engineering Using a Create Script
To reverse engineer using a create script, choose the File, Import, Reverse Engineer MySQL Create Script… menu item for a model.
Tables, views, routines, routine groups, indexes, keys, and constraints can be imported from an SQL script file. Objects imported using an SQL script can be manipulated within MySQL Workbench the same as other objects.
Figure 9.40 Reverse Engineer SQL Script: Input
Select SQL script file: Open a file open dialog box with the default file type set to an SQL script file, a file with the extension
sql.File encoding: Defaults to UTF8.
Place imported objects on a diagram: Also create an EER diagram in MySQL Workbench.
Note
Importing a large number (about 300+) objects could fail to create an EER diagram and instead emit a resource warning with the text “Too many objects are selected for auto placement. Select fewer elements to create the EER diagram.” In this case, execute the reverse engineering wizard with this option disabled, manually create the EER diagram, and then import the 300+ objects using the EER diagram catalog viewer.
If your script creates a database, MySQL Workbench creates a new physical schemata tab on the MySQL Model page.
Click Execute to reverse engineer the SQL script, verify its results, and optionally place the objects in a new EER diagram.
Figure 9.41 Reverse Engineer SQL Script: Execution
Click Next to view a summary of the results, and then Finish to close the wizard.
Figure 9.42 Reverse Engineer SQL Script: Results
Before exiting MySQL Workbench, be sure to save the schema. Choose the File, Save menu item and the reverse-engineered database will be saved as a MySQL Workbench file with the extension mwb.
See Section 9.3.3, “Importing a Data Definition SQL Script”, for a tutorial on reverse engineering the sakila database.
Creating a DDL script
You can create a data definition (DDL) script by executing the mysqldump db_name --no-data > script_file.sql command. Using the --no-data option ensures that the script contains only DDL statements. However, if you are working with a script that also contains DML statements you need not remove them; they will be ignored.
Note
If you plan to redesign a database within MySQL Workbench and then export the changes, be sure to retain a copy of the original DDL script. You will need the original script to create an ALTER script. For more information, see Section 9.4.1.1.2, “Altering a Schema”.
Use the --databases option with mysqldump if you wish to create the database as well as all its objects. If there is no CREATE DATABASE db_name statement in your script file, you must import the database objects into an existing schema or, if there is no schema, a new unnamed schema is created.
9.4.2.2 Reverse Engineering a Live Database
To reverse engineer a live database, choose the Database, Reverse Engineer… menu item from the main menu. This opens the Reverse Engineer Database wizard.
Figure 9.43 Reverse Engineer Database Wizard
The first page of the wizard enables you to set up a connection to the live database you wish to reverse engineer. You can set up a new connection or select a previously created stored connection. Typical information required for the connection includes host name, user name and password.
After this information has been entered, or you have selected a stored connection, click the Next button to proceed to the next page.
Figure 9.44 Connect to DBMS
Review the displayed information to make sure that the connection did not generate errors, then click Next.
The next page displays the schemata available on the server. Click the check box or check boxes for any schemata you wish to process.
Figure 9.45 Select Schemas
After you have selected the desired schemas, click the Next button to continue.
The wizard then displays the tasks it carried out and summarizes the results of the operation.
Figure 9.46 Retrieve Objects
Review the results before clicking Next to continue.
The next page is the Select Objects page. It has a section for each object type present in the schema (tables, views, routines, and so forth). This page is of special interest if you do not wish to import all the objects from the existing database. It gives you the option of filtering which objects are imported. Each section has a Show Filter button. Click this button if you do not want to import all the objects of a specific type. Here is this page with the filter open:
Figure 9.47 Select Objects
This page enables you to select specific tables for import. Having selected the desired tables, you can optionally hide the filter by clicking the Hide Filter button.
The other sections, such as MySQL Routine Objects, have similar filters available.
Click Execute to continue to the next page.
The wizard then imports objects, displaying the tasks that have been carried out and whether the operation was successful. If errors were generated, you can click the Show Logs button to see the nature of the errors.
Figure 9.48 Reverse Engineer Progress
Click Next to continue to the next page.
The final page of the wizard provides a summary of the reverse engineered objects.
Figure 9.49 Results
Click Finish to exit the wizard.
Before exiting MySQL Workbench be sure to save the schema. Choose the File, Save menu item to save the reverse-engineered database as a MySQL Workbench file with the extension mwb.
9.4.2.2.1 Errors During Reverse Engineering
During reverse engineering, the application checks for tables and views that duplicate existing names and disallows duplicate names if necessary. If you attempt to import an object that duplicates the name of an existing object you will be notified with an error message. To see any errors that have occurred during reverse engineering, you can click the button Show Logs. This will create a panel containing a list of messages, including any error messages than may have been generated. Click the Hide Logs button to close the panel.
If you wish to import an object with the same name as an existing object, rename the existing object before reverse engineering.
If you import objects from more than one schema, there will be a tab in the Physical Schemata section of the MySQL Model page for each schema imported.
You cannot reverse engineer a live database that has the same name as an existing schema. If you wish to do this, first rename the existing schema.