Data export guide

  • Data export is basically the operation where SQL dump is built from existing database.
  • SQL dump is a file that includes the content of the database and/or the data.
  • So user may choose what is exported:
    • Database structure and data (CREATE TABLE and INSERT statements)
    • Database structure only (CREATE TABLE statements)
    • Data only (INSERT statements)
  • Data export can be done from MySQL Workbench client application.
  • It uses mysqldump.exe (mysqldump in Mac and Linux environments) file in the background which basically does the data export operation.

Exporting the selected database

  • Follow this guide in order to build SQL dump file to your computer.
  1. Open MySQL Workbench and connect to your database.
  2. From the Navigator view on the left choose Administration tab.
  3. Select Data Export.
  4. Under Tables to Export choose the database from which you want to create the SQL dump file.
  5. From the dropdown menu select Dump Structure and Data (selected by default).
  6. Under Export Options choose Export to Self-Contained File and select the path where to save the SQL dump file on your computer.
  7. Click Start Export
  • Navigate to the path where the SQL dump file was saved and open it either with MySQL Workbench or text editor (VSCode, Notepad etc.).
  • Ensure that all the content is included (CREATE TABLE and INSERT statements).

Possible errors in export

  • There are many possible errors that could happen during the export.
  • The most common error is mysqldump Version Mismatch which means that mysqldump executable version that came with MySQL Workbench application (this is used by default) is not compatible with the serverside hosted in XAMPP or other server platforms.
  • This will then result error during the export operation and the SQL dump file will not be created.
  • In order to fix this, you need to set the path for mysqldump executable (let's use the one that came with XAMPP in this guide) with the guide below.

Important: It is suggested to check the path from your computer before setting it here (In Windows environments this path is C:\xampp\mysql\bin\mysqldump.exe or similar by default.)!

Path for mysqldump executable

  1. From the topmost menu in MySQL Workbench click Edit and choose Preferences....
  2. From the popup window choose Administration.
  3. Set the path to your mysqldump executable to the topmost field (Path to mysqldump Tool).
  4. Save the changes by clicking OK.
  • Another possible error might be this: Unknown table 'column_statistics' in information_schema (1109).
  • This error can be fixed by following this guide:
    • Navigate to the modules subfolder in your MySQL Workbench installation folder (In Windows environments can be for example C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules)
    • Open the following file for editing: wb_admin_export.py
    • Find the following setting: skip_column_statistics and change the value to be True (admin rights required to edit this)
    • Save the changes and restart the MySQL Workbench application

Setting for column statistics