Re: copying a database in real time.

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Tue, 16 Jan 2018 19:54:32 -0500
Message-ID: <p3m6ra$pcm$1_at_jstuckle.eternal-september.org>


On 1/16/2018 7:32 AM, bill wrote:

> 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
>>
[Quoted] [Quoted] > 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

That only happens if you're updating the slave. With a master-slave relationship all updates should be done only through the master; the slave can be used for fetching data.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================
Received on Wed Jan 17 2018 - 01:54:32 CET

Original text of this message