How do I use MySQL Workbench to edit an existing, in-production database?

Question

This is basically a repost of this question, which didn't give a satisfactory answer.

I'm finishing up the design of a database using an EER diagram. I now want to forward engineer and import the database into a working database. I will then use phpMyAdmin to start adding data and coding the site.

Now, while coding, what is the best way to make necessary changes to the database structure? Is the only way to export from phpmyadmin and import in MySQL Workbench? If so, how does it handle the existing rows when I add/remove columns? And, when it's time to forward engineer it again and import it via phpmyadmin, will all of my data still be there?

How do you handle this?

Answer 1

This is an arguable opinion, from limited personal experience.

I believe it was the Workbench who screwed up the process of round trip engineering several times. I don't remember all the detail, but it certainly synchronized more objects than had actually changed. It silently deleted some data a few times. And it sometimes generated SQL that the server rejected. It was a development DB, no big deal, especially compared to the benefits. But in production I'd rather refrain from being at such a tool's mercy.

Some other people's experience was similar, e.g., http://dba.stackexchange.com/questions/23389/mysql-workbench-model-source-synchronize-issue

So for a production DB there may be risks, including that of losing your data. I would stick with phpmyadmin and do synchronization back into the model, always checking what the Workbench is going to do. Keeping the model just for reference.

Answer 2

MySQL Workbench uses two-way synchronization that allows to send any changes in your model to the server and at the same time to get any change from the server into your model. The dialog used for this process allows to take out individual changes or even revert the direction. Use the menu Database -> Synchronize Model... for this task.