I'm developing a site locally using MAMP Pro, and I've created a mysql database locally that have the same structure as the remote server. (username and password are different though)

I'm inserting new data into the local database - since that's much faster than interacting with the remote server - and at the end of the day I would like to just update my remote database with the stuff I've added locally.

Now I'm using the "export to file" in phpMyAdmin on each table that I've added new stuff into, and adding the DROP IF EXISTS-statement. It's not that hard, but I kind of have to take the site down because the foregin keys fails when I'm just updating one at a time.

Any suggestion on how to just update the content of tables on the remote server, based on what's in the local server?

I have MySQL Workbench, but all I could find was a way to sync the models. As in updating the table structures, adding new tables and stuff like that...
I might have overlooked some functionality there?

  • What is the purpose of the "remote" server in your scheme? The question does not appear to adequately explain your motivation for doing this, and the amswer, it seems like, will (at least in part) depend on "why?" and "what are the implications of inconsistent data between the two?" Also, "the site" that you talk about having to take down because of foreign key issues... is that site running against the "local" database or the "remote" database? – Michael - sqlbot Feb 9 '14 at 15:46
  • the remote server is where the site is stored online - Bluehost in my case. And there I have a database that is identical to the database locally on my machine - MAMP. When I update the database locally, I would like to somehow update the database online at the end of the day. The site is running against the database on Bluehost - which is what I refer to as "remote server". Did that clear things up a bit? – ThomasK Feb 9 '14 at 18:05

There are a couple of ways to get your data from your local Dev to your remote Dev or Production environment. The first and simplest way is to just dump your db and upload it. You can also use a tool like phpMyAdmin to Sync your db but that would require you to install more software and understand basic networking. If you have a huge Dev database with lots of unused test data you can also dump and import just the db structure which will give you an empty db with no information (users, setting, whatever you are storing). I would highly recommend installing phpMyAdmin on your local Dev server to simplify your life, it makes any of these options extremely simple and pretty much 1 click tasks.

| improve this answer | |

If this synchronization is regularly done (say every day) you may consider replication. I'm however unsure if that works in a scenario like yours where you have to replicate over internet. But I would give that a try.

Otherwise you can only do it manually by taking a dump of your data and applying that to your remote server. That can be done autmoatically using a script. However, this will always transport all records which might become too time consuming (and risky, since you will have to remove all existing records on the remote machine, before you can apply the dump).

You could implement a solution between fully automatic and fully manual if you can export only the changed + new records (with UPDATE and INSERT commands) somehow and simply apply that script then.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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