How to generate the whole database script in MySQL Workbench?

mysql sql database mysql-workbench
HAJJAJ picture HAJJAJ · Feb 8, 2011 · Viewed 180.3k times · Source

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)?

Answer

Aaronster picture Aaronster · Oct 23, 2011

How to generate SQL scripts for your database in Workbench

  1. In Workbench Central (the default "Home" tab) connect to your MySQL instance, opening a SQL Editor tab.
  2. Click on the SQL Editor tab and select your database from the SCHEMAS list in the Object Browser on the left.
  3. 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.
  4. Click in the MySQL Model tab
  5. Select Database > Forward Engineer
  6. 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).
  7. 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.

Related questions

Error Code: 2013. Lost connection to MySQL server during query

I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench. I noticed also that it appears whenever I run long query. Is there away …

Read More
Server returns invalid timezone. Go to Advanced tab and set servertimezone property manually

My Intelij IDE wont connect to my database server of MySQL Workbench, it specifies it as a timezone problem and wants me to go to advanced tab and set serverTimezone property manually. I tried looking if there where a similar …

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More