Basic database synchronisation

from the Artful MySQL Tips List

In one-way synchronisation, one database is brought to concordance with another database. In two-way synchronisation, both are brought into concordance with each other.

One-way synchronisation

The most thorough method is replication. If that's not a desirable solution, one-way synchronisation can be coded manually. Given the need to make db1 table data correspond with db2 table data, if there have been only inserts since the time of db1 ...

insert ignore into db1.t select * from db1.t;

Otherwise, if there've also been updates and deletes, it's more complicated. For any such table t ...

-- deletes
delete db1.t from db1.t left join db2.t using(k) where db2.t.k is null;

-- inserts
insert ignore into db1.t select * from db2.t;

-- updates are the killer: for each column colx ...
update db1.t a join db2.t b using(k) set a.colx=b.colx where a.colx<>b.colx;

Two-way synchronisation

There are open source (eg Talend) and commercial (eg dbConvert) products that do this. To do it yourself, you need to implement point-in-time architecture ( time validity in MySQL.pdf)

Return to the Artful MySQL Tips page