MySQL Workbench Export and Import Database

Are you using MySQL Workbench and want to export your database? Or do you have an export file and want to import your database?

In this guide, you’ll learn how to export a database and import a database into MySQL Workbench, with screenshots and step-by-step instructions.

Let’s get into it.

How to export a MySQL database To export the MySQL database

from MySQL Workbench

:

Step 1: Go to Server >

Data Export.

Alternatively, you can right-click on a table in the Schema Browser on the left and select Data Export. However, this only exports a single table (even if you select multiple tables). To use the MySQL Workbench database export feature, you must use >the Server Data Export option.

The screen will look like this.

Step 2: Select the tables you want to export by clicking on the database or schema in the left pane, then clicking the checkbox next to the table name in the right pane.

If you want to export all tables in a schema or database, click the database check box on the left.

Step 3: Select whether you want the data, structure, or both by selecting one of the following options from the drop-down menu below the panels

: Dump structure and data: include table structure and data in tables Dump data only

• : include data
• only in tables, no tables
• Dump structure only: include only the table structure, no

data

Step

4: Decide whether you want to export stored procedures, functions, events, or triggers, and select the appropriate check boxes if you do.

Step 5: Select to export the data in one of two ways:

• Export to Dump Project Folder: Select the folder and each table will be exported to a separate file. This will allow you to select which tables to restore, but it would be slower.

Export to

• standalone file: Select the folder and file, and all tables will be exported to a single SQL file.

Step 6: Indicate whether

you want to create the dump in a single transaction (which only applies to the “standalone file” export option).

Step

7: Indicate whether you want to include the Create Schema step

.

Step 8: Click Start Export

.

The export process will start. Depending on the amount of data, it may take some time.

Here’s what it looks like when it completes

:

<img src="https://www.databasestar.com/wp-content/uploads/2021/09/data-export-07-log.png" alt="data export 07 log

” />

The text of this example reads

: 12:50:56 Dump test (all tables) Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump -defaults-file=”/var/folders/81/9mr565sx0_94jm_zwy45dk3h0000gn/T/tmpOtsG7d/extraparams.cnf” -host=127.0.0.1 -protocol=tcp -user=root -column-statistics= FALSE – port=3306 -default-character-set=utf8 -skip-triggers “test” 12:50:56 Dump desc_test (all tables) Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump -defaults-file=”/var/folders/81/9mr565sx0_94jm_zwy45dk3h0000gn/T/tmpSDQVzd/extraparams.cnf” -host=127.0.0.1 -protocol=tcp -user=root -column-statistics=FALSE -port=3306 -default-character-set=utf8 -skip-triggers “desc_test” 12:50:56 The export of /Users/BB/dumps/Dump20210915.sql has Finished We can see that it

took about 1 second, but the tables I chose were very small

.

We can also see that it uses the “mysqldump” tool to perform the export. If you want to see how to do this directly using the command line, check out the guide here.

Your database has been exported from MySQL Workbench! Browse to the specified location to view one or more generated files.

Optional

: Advanced options

Before exporting the data, you can set some additional options by clicking the Advanced button

.

You’ll see a screen like this

:

Here are the options you can choose from

:

• create-options: Include all MySQL-specific table options in the CREATE TABLE statements.
• allow-keywords: allows the creation of column names that are keywords.
• quote-names: appointment identifiers with backtick characters.
• column-statistics: Write ANALYZE TABLE statements to generate statistics histograms
• lock-tables: Lock tables for reading. Disable if the user does not have LOCK TABLES privileges.
• dump-date: Include the dump date as a comment “Dump completed on” if -comments are given.
• flush-logs: Empty the MySQL service log files before starting the dump.
• delete-master-logs: On a master replication server, delete the binary logs after performing the dump operation.

Show internal schemas

• : Displays internal MySQL schemas (mysql, information_schema, performance_schema) in the export schema list.
• hex-blob: dump binary columns using hexadecimal notation (e.g. ‘abc’ becomes 0x616263)
• compress: Use compression in the server/client protocol

.

• flush-privileges: Issue a FLUSH PRIVILEGES statement after dumping the MySQL database

.

• set-gtid-purged: Add ‘SET @@GLOBAL. GTID_PURGED’ to

the

• output disable keys: For each table, surround the INSERT statements with instructions for disabling and enabling keys

.

• force: Continue even if we get a sql error

.

• Sort by primary: Dump the rows of each table sorted by their primary key or by their first unique index

.

• tz-utc: Add SET TIME_ZONE=’+00:00′ to the dump file.
• Maximum size of a package or any generated/intermediate string
• add-locks: Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

. complete-insert: Use complete INSERT statements that include column names.

• extended-insert: Use multi-row INSERT syntax that includes multiple VALUES lists.
• insert-ignore: Type the INSERT IGNORE statement instead of the INSERT statements
• . replace: Type REPLACE statements instead of INSERT statements.

These options adjust the mysqldump command that

is run as part of the export process

. How to import a MySQL database

You can use MySQL Workbench to import data that has been exported using the data export operation shown above or by using the mysqldump command

. This MySQL Workbench

database

import function is

performed using >the Server Data Import menu item

. There is also a feature called Table Data Import Wizard.

However, that is used to import CSV or JSON files into the database, and not files generated using the previous export process. We want to use MySQL’s sql file import functionality.

Let’s see how to do this.

Step 1: In MySQL Workbench, go to Server > Data Import.

The screen should look like this.

Step 2: Select “Import from dump project folder” or “Import from standalone file”, depending on how your data is stored. This would have been specified during the data export process.

Step 3: Select the folder from which you want to import (if you selected “Import from dump project folder”) or the standalone SQL file (if you selected “Import from standalone file”).

Step 4: Select the default destination scheme to import.

This is only used if the dump file does not contain a schema.

Step 5: If you have selected “Import from dump project folder”, select the schematics and tables to import in the screen panels.

Step 6: Select whether you want the data, structure, or both, by selecting one of the following options from the drop-down menu below the panels

: Dump structure and data: Include table structure and data in tables Dump data only: Include data only in tables

• , not in tables Dump structure only: include only
• the table structure
• , No

data

Step 7: Click Start Import.

Here’s what the screen looks like after the

import is complete:

The commands shown here are

: Newschema schema creation 13:10:08 Restore /Users/BB/dumps/dump20210915.sql Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysql -defaults-file=”/var/folders/81/9mr565sx0_94jm_zwy45dk3h0000gn/T/tmpmT9LmF/extraparams.cnf” -protocol=tcp -host=127.0.0.1 -user=root -port=3306 -default-character-set=utf8 -comments -database=newschema < “/Users/BB/dumps/Dump20210915.sql” 13:10:09 Import of /Users/BB/dumps/Dump20210915.sql complete

Your data has now been imported using MySQL Workbench!

Conclusion

Here’s how you can export and import data into MySQL Workbench. Import and export options are available from the Server menu.