Workflow question: Reconciling database local <-> remote

[eluser]Crimp[/eluser]
The scenario, and thus the need for database replication, is not uncommon:

1. Local and remote development servers
2. Database updated in both locations by mix of admins/users (not thinking of busy user forums here)

The goal is to keep one regularly updated database in both locations. I am interested in how developers best handle this situation.

My own sync approaches have included manual updates in a GUI, selective sql commands and file diffs on whole sql dumps for drop and replace.

[eluser]Colin Williams[/eluser]
The database synchronization would likely happen with a completely different process. CI, or even PHP for that matter, is not the best tool for that

+ + + + + + + + + + ++ + + + + + + + + + + + + +

The database library was specifically designed for part of your situation. If you look at the config file, you can setup multiple DB configurations. Obviously you'll have a dev configuration and a local configuration. Then, in the db config or elsewhere, use a conditional to select the approprate db configuration as the active one.

If you name your configurations by IP, you can set $active_group to the current server IP, and it works rather elegantly:

Code:

$active_group = $_SERVER['SERVER_ADDR'];

$db['127.0.0.1']['hostname'] = 'localhost';
//... etc

$db['65.72.2.112']['hostname'] = 'localhost';
//... etc

Another tip to reduce code is to copy one config and only set values that need changing

Code:

$db['65.72.2.112'] = $db['127.0.0.1']
$db['65.72.2.112']['username'] = 'db8703_admin';
$db['65.72.2.112']['password'] = 'suP3rs3cReT';

[eluser]Crimp[/eluser]
Yep, I already populate three arrays in those configs: two servers and localhost.

The best-case scenario I haven't yet found, I hope, related to the replication/sync of actual data in the database, in my case MySQL, not switching connections.

How does one best keep database changes, made by various people across two locations, in sync?

[eluser]Colin Williams[/eluser]
Don't know if I would even have two instances of the same data. Sounds like a bad idea to start out. I mean, you could host ONE database on its own server (very typical) and have all dev environments use that one, perhaps with limited permissions for each.

Local database probably just needs to pull from the main one, never push (write/modify), which can be done manually or by some scheduled batch process/cron script

[eluser]Crimp[/eluser]
Two instances of the same data is not always a bad idea ;-)

It's the latter part that poses the challenge. There are situations where updates to schema/content needs to be pushed from test to live, for example. Like you propose, I currently do this with some variation of "manual" labor, outlined in the first post.

So the answer to my question about how others best handle this may be: they prefer not to.

[eluser]Colin Williams[/eluser]
Well, yeah, not if one is a backup. Again, that just comes down to a cron script backing up the db ever once in awhile.

But the thing is, only the production db should be write/modify-able by one db user, otherwise there are bound to be clashes.

Sorry I don't have a best-way-to-do-it-if-you-had-to.