121

How to take database backup using MySQL Workbench? Can we take backup in the following ways-

  1. Backup file(.sql) contains both Create Table statements and Insert into Table Statements
  2. Backup file(.sql) contains only Create Table Statements, not Insert into Table statements for all tables
  3. Backup file(.sql) contains only Insert into Table Statements, not Create Table statements for all tables
Tiny
  • 27,221
  • 105
  • 339
  • 599
Viraj Dhamal
  • 5,165
  • 10
  • 32
  • 41

9 Answers9

171

For Workbench 6.0

Open MySql workbench. To take database backup you need to create New Server Instance(If not available) within Server Administration.

Steps to Create New Server Instance:

  1. Select New Server Instance option within Server Administrator.
  2. Provide connection details.

After creating new server instance , it will be available in Server Administration list. Double click on Server instance you have created OR Click on Manage Import/Export option and Select Server Instance.

Now, From DATA EXPORT/RESTORE select DATA EXPORT option,Select Schema and Schema Object for backup.

You can take generate backup file in different way as given below-

Q.1) Backup file(.sql) contains both Create Table statements and Insert into Table Statements

ANS:

  1. Select Start Export Option

Q.2) Backup file(.sql) contains only Create Table Statements, not Insert into Table statements for all tables

ANS:

  1. Select Skip Table Data(no-data) option

  2. Select Start Export Option

Q.3) Backup file(.sql) contains only Insert into Table Statements, not Create Table statements for all tables

ANS:

  1. Select Advance Option Tab, Within Tables Panel- select no-create info-Do not write CREATE TABLE statement that re-create each dumped table option.
  2. Select Start Export Option

For Workbench 6.3

  1. Click on Management tab at left side in Navigator Panel
  2. Click on Data Export Option
  3. Select Schema
  4. Select Tables
  5. Select required option from dropdown below the tables list as per your requirement
  6. Select Include Create schema checkbox
  7. Click on Advance option
  8. Select Complete insert checkbox in Inserts Panel
  9. Start Export Workbench 6.3 export

For Workbench 8.0

  1. Go to Server tab
  2. Go to Database Export

This opens up something like this

MySQL Workbench

  1. Select the schema to export in the Tables to export
  2. Click on Export to Self-Contained file
  3. Check if Advanced Options... are exactly as you want the export
  4. Click the button Start Export
Tiago Martins Peres
  • 14,289
  • 18
  • 86
  • 145
Viraj Dhamal
  • 5,165
  • 10
  • 32
  • 41
  • 4
    In MySQL Workbench 6.1, you'll have to open the desired MySQL Connection. Then go to this menu: Server/Data Export =) – Metafaniel Feb 24 '15 at 19:38
  • 1
    The image was just more than enough to help. God bless you. – rotimi-best Feb 25 '20 at 19:16
  • choose `Export to Self-Contained File` and then `Create Dump in a Single Transaction (self-contained file only)` for the script to be generated in a single file. If these options are not selected, then it will create separate file for each table. – Muhammad Khuzaima Umair Oct 11 '22 at 17:06
41

Sever > Data Export

enter image description here

Select database, and start export

enter image description here

Community
  • 1
  • 1
code-8
  • 54,650
  • 106
  • 352
  • 604
20

  1. On ‘HOME’ page -- > select 'Manage Import / Export' under 'Server Administration'

  2. A box comes up... choose which server holds the data you want to back up.

  3. On the 'Export to Disk' tab, then select which databases you want to export.

  4. If you want all the tables, select option ‘Export to self-contained file’, otherwise choose the other option for a selective restore

  5. If you need advanced options, see other post, otherwise then click ‘Start Export’

daticon
  • 308
  • 2
  • 6
14

In Window in new version you can export like this

enter image description here enter image description here

enter image description here

Adiii
  • 54,482
  • 7
  • 145
  • 148
9

The Data Export function in MySQL Workbench allows 2 of the 3 ways. There's a checkbox Skip Table Data (no-data) on the export page which allows to either dump with or without data. Just dumping the data without meta data is not supported.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
6

In Workbench 6.3 it is supereasy:

  1. On the "HOME"-view select one of the MySQL Connections: (localhost)
  2. In the "Localhost" view click on "Server"--> "Data export"
  3. In the "Data Export" view select the table(s) and whether you want to export only their structure, or structure and data,...
  4. Click "Start Export"
Manfred
  • 990
  • 9
  • 10
4

In workbench 6.0 Connect to any of the database. You will see two tabs.

1.Management 2. Schemas

By default Schemas tab is selected. Select Management tab then select Data Export . You will get list of all databases. select the desired database and and the file name and ther options you wish and start export. You are done with backup.

Swapnil Shende
  • 305
  • 1
  • 5
  • 17
4

I am using MySQL Workbench 8.0: enter image description here

Tính Ngô Quang
  • 4,400
  • 1
  • 33
  • 33
2

In Workbench 6.3 go to Server menu and then Choose Data Export. The dialog that comes up allows you to do all three things you want.

Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87