synchronizing two Mysql databases

[conjurer78]

Hello,

Perhaps someone can give me ideas for what I'm trying to accomplish..

Scenario: I have a MySQL database that is running on a server. If this server goes down, a backup server with the same database is automatically started. The backup database is synchronized with the first one periodically.

Suppose that during the downtime of the primary server several new rows have been added to a table in the backup database. What I wish to do is merge the two databases, in such a way that no rows are lost. The problem is that the tables in both databases are the same up to a point, then the last few records are different. (in both databases, not only in one of them).

I can't simply "merge" the two db files, since they are binary.... anyone got an idea about any tools that can do this?

 

[conjurer78]

or perhaps if you could tell me how to synchronize the database incrementally, like checking for new records and adding only them?

 

[TonyGroves]

Have a look at the section on Replication in the MySQL manual. Basically, you set up the backup server as a replication slave, so that changes to the master databases are automatically propagated to the slave.

 

[TonyGroves]

Sorry, I didn't read your question properly - ignore my previous post!

 

[conjurer78]

Thanks, I checked it out.. it might owrk for me.. However, do u have any idea how much traffic this would generate, compared say to importing the affected changes manually?

 

[TonyGroves]

What looks like a solution to your problem is described in section 4.10.8 of the MySQL manual, under the heading "How can I use replication to provide redundancy / high availability?" I've never used replication myself, but it seems that when you switch back to the primary server, it is updated with only the updates that it missed. This should be much faster and more reliable than doing a manual database comparison.

Another solution that might work is that you could enable update logging (updates are written to the log as SQL queries) on the backup server at the time it takes over primary duties, then when you switch back to the primary server, apply the update log to its databases ([tt]mysql ... <updatelogpath[/tt]).

 

[vanekl]

this will work, provided you use natural keys instead of autoincrement keys:

http://www.phpbuilder.com/columns/tanoviceanu20000912.php3?page=1