migrating a split replication

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: migrating a split replication

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.

http://dev.mysql.com/doc/refman/5.1/en/start-slave.html

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.

http://dev.mysql.com/doc/refman/5.1/en/replication-options-s lave.html


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
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: migrating a split replication

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 replicat=
ing
>> 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 extr=
eme
>> 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 t=
wo.
> 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.
>
> http://dev.mysql.com/doc/refman/5.1/en/start-slave.html
>
> 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.
>
> http://dev.mysql.com/doc/refman/5.1/en/replication-options-s lave.html
>
>
> 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.

Regards,
- Robert

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg