What three are ways to export and import data in mysql workbench

The world's most popular open source database

Table of Contents Show

6.5.2 SQL Data Export and Import Wizard
How do I export data from MySQL workbench?
How do I import data into MySQL workbench?
How do I import export data into MySQL?
How do I export and import a stored procedure in MySQL workbench?

There are three ways to export and import data in MySQL Workbench, each serving a different purpose.

Table 6.1 Methods to Export or Import data in MySQL Workbench

GUI Location	Data Set	Export Types	Import Types	Additional Details
Object Browser context menu	Tables	JSON, CSV	JSON, CSV	Simple table operations, includes moderate control over the output type (this method was added in version 6.3.0).
Result Grid menu under the SQL editor	Result set (after performing an SQL query)	CSV, HTML, JSON, SQL, XML, Excel XML, TXT	CSV	Simple data operations, includes little control.
Management Navigator	Databases and/or Tables	SQL	SQL	Detailed database and table operations, standard backup/restore behavior using the mysqldump command and meta data, includes control over how data is handled, and includes meta data.
Management Navigator	Databases and/or Tables	SQL	SQL	Detailed database and table operations, includes control over how data is handled, can be scheduled and incremental, includes meta data, uses MySQL Enterprise Backup (commercial).

6.5.2 SQL Data Export and Import Wizard

Use this wizard to either export or import SQL generated from MySQL Workbench or with the mysqldump command.

Access these wizards from either the Navigator area of the sidebar, or by selecting from the main menu, and then either or .

Data Export

This tab allows you to export your MySQL data. Select each schema you want to export, optionally choose specific schema objects/tables from each schema, and generate the export. Configuration options include exporting to a project folder or self-contained SQL file, optionally dump stored routines and events, or skip table data.

Note

Alternatively, use Export a Result Set to export a specific result set in the SQL editor to another format such as CSV, JSON, HTML, and XML.

Select the schema objects to export and then configure the related options. The figure that follows shows the sakila database ready for export.

Note

Click Refresh to load the current objects.

Figure 6.19 Navigator Administration: Data Export: Object Selection

Optionally open the Advanced Options tab that allows you to refine the export operation. The next figure shows an example that adds table locks, uses replace instead of insert statements, quotes identifiers with backtick characters, and so on.

Figure 6.20 Navigator Administration: Data Export: Advanced Options

Click Start Export to begin the export process. As the next figure shows, status information indicates when the export is finished.

Figure 6.21 Navigator Administration: Data Export: Export Progress

This functionality uses the mysqldump command.

Data Import/Restore

Restore exported data from the Data Export operation, or from other exported data from the mysqldump command.

Choose the project folder or self-contained SQL file, choose the schema that the data will be imported to, or choose New to define a new schema. The following figure shows an example of an import from a dump project folder.

Figure 6.22 Navigator Administration: Data Import: Import From Disk

Note

You may only select specific data objects (tables) to import if the data export operation used project folders instead of a self-contained SQL file.

Click Start Import to begin the import process. Use the Import Progress tab to monitor the progress. Status information indicates when the import is finished and displays the log.

How do I export data from MySQL workbench?

Create a backup using MySQL Workbench.

Connect to your MySQL database..

Click Server on the main tool bar..

Select Data Export..

Select the tables you want to back up..

Under Export Options, select where you want your dump saved. ... .

Click Start Export. ... .

You now have a backup version of your site..

How do I import data into MySQL workbench?

To import a file, open Workbench and click on + next to the MySQL connections option. Fill in the fields with the connection information. Once connected to the database go to Data Import/Restore. Choose the option Import from Self-Contained File and select the file.