Reverse engineering using a create script is done by using the File, Import, Reverse Engineer MySQL Create Script ... menu options. Doing this opens a file open dialog box with the default file type set to an SQL script file, a file with the extension sql.

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 DDL statements only. However, if you are working with a script that also contains DML statements you need not remove them; they will be ignored.

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.

If your script creates a database, a new physical schemata tab is created on the MySQL Model page.

Any database objects may be imported from a script file in this fashion; tables, views, routines, and routine groups. Any indexes, keys, and constraints are also imported. Objects imported using an SQL script can be manipulated within MySQL Workbench in the same way that any other objects can.

Before exiting, 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 16.6.8.1, “Importing a Data Definition SQL Script” for a tutorial on reverse engineering the sakila database.

This section explains how to reverse engineer a live database using MySQL Workbench.

Select the Database, Reverse Engineer ... menu item from the main menu. Doing this opens the Reverse Engineer Database wizard.

Figure 16.51. 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. This enables you to set up a new connection, or select a previously created connection. Typical information required for the connection includes hostname, username and password.

Once this information has been entered, or you have selected a stored connection, click the Next button to proceed to the next page:

Figure 16.52. Connect to DBMS

Review the displayed information to make sure that the connection did not generate errors, then click Next.

On the next page you can select the database schema you want to connect to among those available on the server. Simply click the checkbox or checkboxes associated with the schema you wish to process:

Figure 16.53. Select Schemata

Once you have selected the desired schema, click the Next button to continue.

The wizard then displays the tasks it carried out and summarizes the results of the operation:

Figure 16.54. Fetch Object Info

Review the results before clicking Next to continue.

The next page is the Select Objects page. It is sectioned off by object type. This screen is of special interest if you do not wish to import all the objects from the existing database—this screen 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.

Figure 16.55. Select Objects

For the Import MySQL Table Objects section, if you click the Show Filter button the following page is displayed:

Figure 16.56. Show Filter

This enables you to select specific tables that you wish to 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.

You can click Execute to continue to the next page.

The wizard then displays the tasks that have been carried out and whether the operation was successful or not. If errors were generated then you can click the Show Logs button to see the nature of the errors.

Figure 16.57. Progress

Click Next to continue to the next page.

The final screen of the wizard provides a summary of the reverse engineered objects:

Figure 16.58. Results

Click Finish to exit the wizard.

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.

16.6.7.9.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.

Figure 16.59. Message Log

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.