How can I push data from my local MAMP database to my remote database?

Question

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?

Answer 1

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.

Answer 2

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.