How to generate the whole database script in MySQL Workbench?

Question

I want to take the whole database. Where do I find the database file?

And is there a way to write the whole database with all data to a text file (like the one in SQL Server)?

score 107 · Answer 1 · answered Oct 23 '11 at 02:27

How to generate SQL scripts for your database in Workbench

In Workbench Central (the default "Home" tab) connect to your MySQL instance, opening a SQL Editor tab.
Click on the SQL Editor tab and select your database from the SCHEMAS list in the Object Browser on the left.
From the menu select Database > Reverse Engineer and follow the prompts. The wizard will lead you through connecting to your instance, selecting your database, and choosing the types of objects you want to reverse engineer. When you're all done, you will have at least one new tab called MySQL Model. You may also have a tab called EER Diagram which is cool but not relevant here.
Click in the MySQL Model tab
Select Database > Forward Engineer
Follow the prompts. Many options present themselves, including Generate INSERT Scripts for Tables which allows you to script out the data contained within your tables (perfect for lookup tables).
Soon you will see the generated script in front of you. At this point you can Copy to Clipboard or Save to Text File.

The wizard will take you further, but if you just want the script you can stop here.

A word of caution: the scripts are generated with CREATE commands. If you want ALTER you'll have to (as far as I can tell) manually change the CREATEs to ALTERs.

This is guaranteed to work, I just did it tonight.

There is a checkbox in the 'forward engineer' that says 'generate insert statements' — Highstead, Jan 11 '13 at 04:52
Thank you, that work for me. It is strange that option "Forward Engineer" does not exist until I performed "Reverse Engineer". — Serhiy Prysyazhnyy, Jun 24 '13 at 14:04
Thanks and this works for me except it doesn't generate the INSERT scripts even when I check that option. — Jason Cheng, Sep 30 '22 at 21:30

score 31 · Accepted Answer · edited May 23 '17 at 10:30

31

Q#1: I would guess that it's somewhere on your MySQL server? Q#2: Yes, this is possible. You have to establish a connection via Server Administration. There you can clone any table or the entire database.

This tutorial might be useful.

EDIT

Since the provided link is no longer active, here's a SO answer outlining the process of creating a DB backup in Workbench.

edited May 23 '17 at 10:30

Community

1
1

answered Feb 08 '11 at 08:10

mingos

23,778
12
70
107

yeees this solve the problem ,thank you so much. thank you every one for the help – HAJJAJ Feb 08 '11 at 08:20
somehow workbench 5.2 ce crashed when i try to backup my old database that is located on remote server 5.0.48; but old gui tools work successfully locally, through remote access. – volody Mar 28 '11 at 01:05
1

The link in this answer is no longer active. – Jason May 27 '14 at 22:58
1

@Jason Thank you for pointing this out; I've provided an alternative link to a SO answer. – mingos May 28 '14 at 08:06

score 14 · Answer 3 · answered Oct 10 '13 at 11:03

14

In MySQL Workbench 6, commands have been repositioned as the "Server Administration" tab is gone.

You now find the option "Data Export" under the "Management" section when you open a standard server connection.

answered Oct 10 '13 at 11:03

Gruber

4,478
6
47
74

1

And in MySQL Workbench 6.1 (Community) you will find it under the "Server" menu when you open a connection. – samazi Aug 27 '14 at 04:50
And if you need to export the INSERTS too, uncheck the option "Skip table data (no-data)" – Vitor Braga Feb 18 '15 at 20:10
There is now a drop down that allows you to pick "structure, data or both" once you are inside the "Data Export" section – Ben D Jan 06 '19 at 14:39

score 11 · Answer 4 · answered Oct 16 '13 at 16:51

11

there is data export option in MySQL workbech

enter image description here

answered Oct 16 '13 at 16:51

Saurabh Chandra Patel

12,712
6
88
78

score 11 · Answer 5 · answered Oct 15 '15 at 21:47

I found this question by searching Google for "mysql workbench export database sql file". The answers here did not help me, but I eventually did find the answer, so I am posting it here for future generations to find:

Answer

In MySQLWorkbench 6.0, do the following:

Select the appropriate database under MySQL Connections
On the top-left hand side of screen, under the MANAGEMENT heading, select "Data Export".

Here is a screenshot for reference:

score 3 · Answer 6 · answered Dec 29 '15 at 14:31

3

None of these worked for me. I'm using Mac OS 10.10.5 and Workbench 6.3. What worked for me is Database->Migration Wizard... Flow the steps very carefully

answered Dec 29 '15 at 14:31

bentzy

1,244
2
15
31

score 2 · Answer 7 · edited May 11 '11 at 17:00

2

Try the export function of phpMyAdmin.

I think there is also a possibility to copy the database files from one server to another, but I do not have a server available at the moment so I can't test it.

edited May 11 '11 at 17:00

Peter Mortensen

30,738
21
105
131

answered Feb 08 '11 at 08:01

Fender

3,055
1
17
25

i am not using phpmyadmin , in fact i am using asp.net with MySQL, and i am using MySQL Workbench 5.2 CE . so is there any way to take the DB script using MySQL Workbench 5.2 CE??? and do you know if this application save database files in some where in the local Machine ??? – HAJJAJ Feb 08 '11 at 08:10
While not what the OP asked for, I would second this as a preferred way of making a db dump over using Workbench. Workbench fails where PhpMyAdmin excels: InnoDB tables with foreign keys. Workbench's dumps will need to be manually modified before being imported. PhpMyAdmin's work out of the box. – mingos Feb 02 '12 at 21:50

score 2 · Answer 8 · answered Feb 08 '11 at 08:11

2

In the top menu of MySQL Workbench click on database and then on forward engineer. In the options menu with which you will be presented, make sure to have "generate insert statements for tables" set.

answered Feb 08 '11 at 08:11

fancyPants

50,732
33
89
96

score 2 · Answer 9 · answered Mar 29 '20 at 10:21

2

Using Windows 10 and MySql Workbench 8.0

Go to Server tab
Go to Database Export

This opens up something like this

Select the schema to export in the Tables to export
Click the button Start Export

answered Mar 29 '20 at 10:21

Tiago Martins Peres

14,289
18
86
145

score 0 · Answer 10 · answered May 28 '14 at 13:12

Surprisingly the Data Export in the MySql Workbench is not just for data, in fact it is ideal for generating SQL scripts for the whole database (including views, stored procedures and functions) with just a few clicks. If you want just the scripts and no data simply select the "Skip table data" option. It can generate separate files or a self contained file. Here are more details about the feature: http://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-data-export.html

score 0 · Answer 11 · answered Aug 15 '15 at 20:57

0

in mysql workbench server>>>>>>export Data then follow instructions it will generate insert statements for all tables data each table will has .sql file for all its contained data

answered Aug 15 '15 at 20:57

Moustafa Bayommy

11

How to generate the whole database script in MySQL Workbench?

11 Answers11

How to generate SQL scripts for your database in Workbench

Linked