Re: copying a database in real time.
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Tue, 16 Jan 2018 18:34:30 +0000
Message-ID: <p3lgjm$te5$2_at_dont-email.me>
>> 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
>>
Date: Tue, 16 Jan 2018 18:34:30 +0000
Message-ID: <p3lgjm$te5$2_at_dont-email.me>
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
>>
[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
[Quoted] 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
-- Microsoft : the best reason to go to Linux that ever existed.Received on Tue Jan 16 2018 - 19:34:30 CET