List:General Discussion« Previous MessageNext Message »
From:Robert Citek Date:December 9 2010 2:54pm
Subject:Re: migrating a split replication
View as plain text  
On Thu, Dec 9, 2010 at 9:10 AM, Shawn Green (MySQL)
<> wrote:
> On 12/8/2010 22:50, Robert Citek wrote:
>> Greetings to all,
>> Can I migrate slave databases between slave servers?
>> Imagine the following scenario: I have one master database server with
>> 10 databases.  I also have two slave database servers, one replicating
>> 5 of the 10 databases, the other replicating the other 5 databases.
>> Can I migrate one of the replicated databases from one slave to the
>> other, resulting in one slave having 6 databases and the other having
>> 4?  I'm using the term "migrate", but is there a more appropriate
>> term?
>> The docs mention various replication strategies[1], including
>> splitting out different databases to different slaves.  In the extreme
>> case, I would like to do the opposite, consolidate databases among
>> slaves, with the final state being all 10 databases on one slave and
>> none on the second.
>> Thanks in advance for your help, especially pointers to any references.
>> [1]
>> Regards,
>> - Robert
> The trick to moving replicated tables between boxes is to get both slaves to
> the same replication coordinates. Stop replication on one wait 5 minutes
> then stop it on the other. Check the binary log coordinates between the two.
> For the one that's looking at the older data, use a START SLAVE UNTIL ...
> command to get them both to the same binlog position.
> Now, the copy of the data on one slave should be in an identical state to
> the copy of the data on the other slave (if it were replicating the table).
> Move the table(s) or database(s) to the other slave then undo any
> --replicate-* filters you may have that prevented replicating that
> information on the new box before the move.
> At this point, you probably need to copy the same --replicate-* rule you are
> removing from the new box to the old box so that it will stop processing
> commands for the data are trying to move.
> After all looks good, START SLAVE on both machines and observe SHOW SLAVE
> STATUS to ensure that they are both catching up to the master.
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN

Thanks, Shawn.  I'll give that a try.

- Robert
migrating a split replicationRobert Citek9 Dec
  • Re: migrating a split replicationMySQL)9 Dec
    • Re: migrating a split replicationRobert Citek9 Dec