Mysql – How to sync MySQL database between two servers

First of all in this scenario you have two remote MySQL server you need to have two connections. MySQL does not support such a query wherein it connects to other server and get details, in my understanding no other RDBMS does this kind of thing.

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Conclusion: NO. You can't write such query on Two Mysql servers.

We are using a merge replication topology between our servers where:

• Initial snapshot is made available to subscribers through ftp
• further data exchange (in our case, merge sync between servers) is done through web replication

Here is a script I once proposed to launch such a replication. You will notice that one of the sp_addmergepullsubscription_agent parameters, @use_ftp, is set to True. The @internet_url parameter sets the https adress used for replication. There are other options allowing the use of an alternate snapshot location and/or its compaction (which could make sense when you are talking about a 1To database ...)

Hope it helps