Re: copying a database in real time.
From: bill <william_at_TechServSys.com>
Date: Tue, 16 Jan 2018 07:32:26 -0500
Message-ID: <p3krcq$e35$1_at_gioia.aioe.org>
>> I am migrating an application onto a new faster server.
>>
>> It features a database with around 140Mbytes of data which is updated in
>> real time every 5 minutes.
>>
>> the two machines probably have around 100Mbps connection between them
>> across the internet.
>>
>> It is important that the primary keys in the records match.
>>
>> I have full control of both machines and could set up SQL conversations
>> between them if needs be.
>>
>> How can I best synchronise the two databases before separating them and
>> letting each be fed by the same program running every 5 minutes? I will
>> need to run them for some days in parallel until DNS records propagate
>> to the new one.
The problem with using replication in this situation is that the slave (the new system) will create new records with new primary keys at the same time that the old system is creating new records with new primary keys which are then copied to the slave system causing a duplicate record error.
Date: Tue, 16 Jan 2018 07:32:26 -0500
Message-ID: <p3krcq$e35$1_at_gioia.aioe.org>
[Quoted] On 1/15/2018 6:40 PM, Lew Pitcher wrote:
> The Natural Philosopher wrote: >
>> I am migrating an application onto a new faster server.
>>
>> It features a database with around 140Mbytes of data which is updated in
>> real time every 5 minutes.
>>
>> the two machines probably have around 100Mbps connection between them
>> across the internet.
>>
>> It is important that the primary keys in the records match.
>>
>> I have full control of both machines and could set up SQL conversations
>> between them if needs be.
>>
>> How can I best synchronise the two databases before separating them and
>> letting each be fed by the same program running every 5 minutes? I will
>> need to run them for some days in parallel until DNS records propagate
>> to the new one.
> > You might try database replication. See > https://dev.mysql.com/doc/refman/5.7/en/replication.html > for details >
The problem with using replication in this situation is that the slave (the new system) will create new records with new primary keys at the same time that the old system is creating new records with new primary keys which are then copied to the slave system causing a duplicate record error.
I _think_ that master-master replication would work.
bill Received on Tue Jan 16 2018 - 13:32:26 CET