2

This is a noobie question. I'm taking over a database project from someone who recently left the company and I'm looking for advice on how to do some development on a test platform before making changes on the active server. I've got MySQL installed on both the server and my local machine, with MySQL Workbench as the interface to both. Apache and PHP are also installed on both platforms.

I think what I want is a local copy of the schema (including data) that I can occasionally synchronize with the server. So when I want to do any development I would:

  • pull any changes from the server

  • make changes locally and test them out

  • then copy the modifications back up to the server.

Is that what people do in practice?
If No: What is the recommended way to do development without messing up the existing database?
If Yes: Can you offer some details on how to do this with MySQL Workbench.

Thank you, and please let me know if you need any more information.

4
  • 2
    Perhaps the simplest method would be to take a dump (backup) of the server database(s), and restore them onto your workstation. Make and test changes on your workstation, then when you are happy, test those changes against a user-acceptance-test platform, then migrate to production.
    – Hannah Vernon
    Jul 29, 2014 at 18:38
  • Hannah's suggestion is great if you do not have much data in place, otherwise, it is a bit heavyweight if you have to include GB upon GB of data in dump files and wait on backup, transfer, and restore. Not sure how to use MySQL Workbench as VCS/migration tool, but saving your modification scripts, or using a free tool like liquibase or a pay tool like Red Gate SQL Compare are also options. Jul 29, 2014 at 18:50
  • My assumption was that you likely already have a backup (you should) that you would restore to the workstation once-in-a-while. This also allows your backups to get tested. Not that I think Source Control is bad.
    – Hannah Vernon
    Jul 29, 2014 at 19:11
  • At least for now, there is not much data in place. In fact, the first task is to populate the database using data compiled in spreadsheets. So in this instance, Hannah's suggestion should do the job. Just to confirm, should I do this using the Server->Data Export and Server->Data Import utilities in MySQL Workbench? I'm getting an error with the Import after adding some foreign keys to the server copy, but I think that's just a bug I have to work through. The method itself looks to be in-line with your suggestion. Thanks!
    – user44627
    Jul 29, 2014 at 20:05

1 Answer 1

1

What I traditionally do, if I can't sync between databases, is take a copy of the schema, add dummy data, and develop on that locally. Then I save all my modification scripts and run them on the main server when I'm ready to push.

I would avoid moving data from development to production. Production -> Development may be fine if you don't have large amounts of data. Otherwise, use a dummy data generator if you can.

Ultimately, you'll have to develop your own methodology just like you do when you introduce version control or backups to a project. It is dependent on the situation.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.