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.
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 ...
delete db1.t from db1.t left join db2.t using(k) where db2.t.k is null;
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;
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 (http://www.artfulsoftware.com/infotree/Transaction time validity in MySQL.pdf)
Return to the Artful MySQL Tips page