List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:March 4 2003 2:31pm
Subject:Re: Data sincronization
View as plain text  
* Gaston Escobar
> I need to sincronice two mysql databases that are in different
> places. It is imposible to centralice everything in one database.
> Then I would need to sincronice the changes made in both of them
> one time per day. Is there any way to do this?

This very much depends on your application. Normally some changes needs to
be done in the table definitions.

As a very simple example, consider this table, called 'prospects':

id int not null auto-increment primary key,
name varchar(80) not null,
email varchar(255) not null

You have two servers, A and B, both accepts inserts into this table. When
you try to synchronize, you will get duplicates in the id fields: record
1001 on server A is not the same as record 1001 on server B, but both are
valid.

One way to get around this, is to include a new column in the table, called
'server' in this example. This column is set to 'A' for inserts on server A,
and to 'B' for inserts on server B. The primary key is changed to
(id,server) instead of just 'id'. I would also insert a column named
'crt_date', type 'timestamp'. When you synchronize, you use the 'crt_date'
field to know which rows to include (crt_date>$last_sync_timestamp).

A different approach could be to make sure server A only use id codes >
100000, and server B only use id codes < 100000 or similar. This would
require you to make a guess on how many rows will be inserted on each
server, and could introduce a problem in the future, when this limit is
reached.

Depending on your application, it could be necessary to check for duplicates
even if the primary key ensures no duplicates... in the example, what if the
same participant has joined through both servers? If you don't need to worry
about this, you could possibly use two-way replication for the actual
synchronization.

<URL: http://www.mysql.com/doc/en/Replication.html >
<URL: http://www.mysql.com/doc/en/Replication_FAQ.html >

The manual way to do it would be to use mysqldump with the --where option on
both servers, transfer the resulting files to the opposite servers & insert.

<URL: http://www.mysql.com/doc/en/mysqldump.html >

Otherwise you need to write your own synchronization routines, using the
language of your choice. This is the normal situation for projects of some
size, with multiple tables.

<URL: http://www.mysql.com/doc/en/Clients.html >

HTH,

--
Roger

Thread
Data sincronizationGaston Escobar4 Mar
  • Re: Data sincronizationRoger Baklund4 Mar
    • Re: Data sincronizationAndreas9 Nov
      • Re: Data sincronizationRoger Baklund9 Nov
        • Dupe killing (was: Data sincronization)Andreas10 Nov
        • Re: Data sincronizationKaram Chand10 Nov
          • ANN: Database Workbench 2.4 releasedMartijn Tonies10 Nov
            • Re: ANN: Database Workbench 2.4 releasedNils Valentin10 Nov
          • Re: ANN: Database Workbench 2.4 releasedMartijn Tonies10 Nov