1

We have to make some changes to our databases (creating tables, Running special SQL queries).

Is there some way in MySQL Workbench to import a structure, edit it with a graphical interface and then getting some piece of SQL Code that I can use to transform my old structure into the new structure?

To explain my problem a bit further: I need a way to transform my MySQL database structure to a new structure (adding databases and values into those tables) without interrupting the production environment for too long.

I would prefer a solution in MySQL Workbench (I'm using version 6.3) but also accept any other answer to do this (for free)

Improve this question
3
  • you can export your existing DB as a script (without data if you like), import it to another DB (on another server if you like). Then in workbench every time you apply a change to the DB structure using the GUI, it pops up with the actual SQL script that will be executed. You can copy/paste this SQL out into a file each time, and so build up a big patch script to alter your structure. Commented Jul 24, 2017 at 10:01
  • I recall using an option called "Reverse Engineer" (4+ years ago) which would produce an ERD by analyzing the DDL used to create/define the tables. You'd be able to work on the diagram and then let Workbench generate SQL based on your new/modified diagram. Is that still available? Commented Jul 24, 2017 at 10:12
  • Reverse Enginnering the old structure worked. I got a Model and can edit the tables. Exporting this will result in "CREATE TABLE IF NOT EXISTS" statements with all columns. I want "ALTER TABLE" statements that will add the newly created columns Commented Jul 24, 2017 at 10:41

1 Answer 1

Reset to default
3

Okay, I actually found a solution!!

What I was looking for is called an "SQL ALTER Script" or a Schema Sync.

Steps to generate a SQL Alter Script with MySQL Workbench:

  1. Open MySQL Workbench
  2. Click File -> New Model
  3. Click Database -> Synchronize With Any Source
  4. Click Next
  5. Select a SQL Dump File as Source (this is the new structure)
  6. Select a SQL Dump File as Destination (this is the old structure)
  7. At Send Updates To select a Folder and Filename where the ALTER Script will be placed.
  8. Click Next
  9. Click Next
  10. Make sure default_schema is checked and Click Next
  11. Click Next
  12. Preview your SQL Code and Click Next

You now successfully generated your SQL Alter Script and it got saved into the specified file.

But Note that you will have to open the SQL File in an editor and replace default_schema with your database name

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.