Re: copying a database in real time.
From: Luuk <luuk_at_invalid.lan>
Date: Tue, 16 Jan 2018 20:24:54 +0100
Message-ID: <5a5e5180$0$9963$e4fe514c_at_news.xs4all.nl>
>> 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
Date: Tue, 16 Jan 2018 20:24:54 +0100
Message-ID: <5a5e5180$0$9963$e4fe514c_at_news.xs4all.nl>
On 16-01-18 19:34, The Natural Philosopher wrote:
> On 16/01/18 12:32, 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
>>>
>> 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
> > Actually what I did was create a 'tar' of the existing database, sftp it > across, stop te mmysql servee and untar it into the mysql databse area. > Nasty, but with ISAM it worked.# > > I did lose one record as it overlapped a data update period > > >
[Quoted] The only 'right' thing to do, in my opinion, is to setup a forward* on the new server to the old one.
You will only have 1 correct database.
On the day of migration, you copy this db to the new server, and get rid off the forward. (in case of doubt, you could setup a forward on the old server to the new...)
*) a forward in HTTP-level, off-topic here, in case someone is asking ;) Received on Tue Jan 16 2018 - 20:24:54 CET