[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]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.