Thread

In my work I often have three setups for a site:

Local, so I can work quickly off localhost
Staging, so I can show a client any changes and get them approved
Live, where I upload the approved changes

Is there any way to synchronize the databases in an easy way?

Currently I reimport the sql into each database and change some settings relevant to each server, but it’s pretty tedious and I was wondering if there was a better way…

Does anyone have a better workflow to share?

I used to sync between 3 environments for a customer on a weekly basis, so I would just export the deltas from the tables that changed regularly. In my case, the tables were exp_weblog_titles, exp_weblog_data, and exp_category_posts. I’d run a query like

SELECT * FROM exp_weblog_titles WHERE (edit_date >= '20090604000000');

then export it to an SQL file as INSERT statements, and run those on the databases that needed synching. It’s still a manual process, but at least you don’t have to fudge with the server specific settings each time.

You can also export the data in tables that hold the settings information (like exp_upload_prefs) in case you need to do a full database sync. Just run those inserts after you perform the full database restore; it’s way faster and less tedious than using the web interface to change the settings.

EDIT: It was late last night when I wrote this so a couple things worth mentioning. 20090604000000 would be the date and time you last ran the sync. Also, for entries that already exist and were updated, you’ll need to export those out in a separate SQL file as UPDATE statements.

I use Navicat and its data transfer capability.  Once I have the set up I want, it is a farily well automated process. HTH

Steve

i am at the same stage as Nick, starting to go fro localhost to stage to live with certain deploy methods. Just the database importing is not ideal. Will keep an eye on this post.

Same need here.  Will look at ‘Navicat’.

I use Navicat and its data transfer capability.  Once I have the set up I want, it is a farily well automated process. HTH

Steve

I didn’t know about this and it sounds awesome. I’ll have to check it out. The database transfer part of it always was a sore point for me. It’s easy to use something like Subversion to keep track of files between localhost and development/staging, and coupling it with this should makes thing pretty easy, thanks!

I use Navicat and its data transfer capability.  Once I have the set up I want, it is a farily well automated process. HTH

Steve

I didn’t know about this and it sounds awesome. I’ll have to check it out. The database transfer part of it always was a sore point for me. It’s easy to use something like Subversion to keep track of files between localhost and development/staging, and coupling it with this should makes thing pretty easy, thanks!

Cost of Navicat looks moderately steep.  Are there similar apps?

That’s a good point carving. I checked out Querious (I’m on a Mac) but there was no mention of a similar feature.

I came across another DB tool which looks promising.

SQLYog

The version with DB syncing support runs $139 for 1 user.