Web.QHMIT.com

Export Data from a MySQL Database

MySQL Workbench provides an easy way to export data to an external source.

There are many ways to export data in MySQL. You can export a whole database. You can export one or more tables. You can also export the result set of a query.

Here, we use MySQL Workbench to perform the export operations.

Exporting the Result Set of a Query

Let's start by exporting the result set of a query.

You can export the result set of a query to a number of formats, including CSV, XML, HTML, JSON, SQL INSERT statements, Excel, and Tab separated.

You can export the result set of a query simply by running the query in MySQL Workbench, then clicking the Export icon where you're prompted to save the file. Steps below.

  1. Start the Export

    MySQL export query 1

    After running a query, click on the Export icon above the result set.

  2. Name the File

    MySQL export query 2

    Enter a name for the file. Note that you can export to many different file formats.

    Click Save.

  3. The Result

    MySQL export query 3

    This will export your query result set and the data will be saved to the file that you specified.

    Now you can open that file in an external application or import it into a different database.

    Here's what the above CSV file looks like when I open it in OpenOffice.

Exporting the Database and/or its Contents

You can export the whole database, its tables, and/or their data to an SQL file. This file could be used later to restore the database and/or its contents.

You can choose whether to create one file for everything or one file for each table. Therefore, you could restore the whole database by running one file, or you could restore individual tables as required. You can also choose whether to export only the database structure, its data, or both.

Here are the steps involved in doing this:

  1. Start the Export

    MySQL export query 4

    Ensure that the MANAGEMENT tab is selected on the left menu.

    Click on the Data Export link.

  2. Configure & Run the Export

    MySQL export query 5

    The Data Export screen will appear. Select the database to export (as well as which tables/objects to include). Choose whether to a self–contained file or separate files.

    In this case I chose Dump Structure and Data because I wanted the whole database, including its tables and the data. If you only want to export the data, change this to Dump Data Only. If you only want the database without the data, select Dump Structure Only.

    Also, I chose Export to Self–Contained File and then Create Dump in a Single Transaction (self–contained file only), and Include Create Schema so that the script will create the database before trying to insert its contents.

    Once done, click Start Export

  3. Enter Password

    MySQL export query 6

    If prompted for password, enter it and click OK

  4. The Result

    MySQL export query 7

    The export operation will now be executed. Once completed, the Export Progress screen reads Export Completed.

Next we'll open this file and run it to restore our whole database.