Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
Re: migrating a split replication

From: Shawn Green (MySQL) (shawn.l.greenoracle.com)
Date: Thu Dec 09 2010 - 08:10:34 CST

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] http://dev.mysql.com/doc/refman/5.0/en/replication-solutions.html
> 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

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql