Replication Question

[Sabika M]

I have replication setup in the following way:

A -> B ->C

I am making updates to server A. I want to stop all my updates and point them to server C. After I start writing to server C, can I use the change master statement to make the C the master of A (take B out of the topology) and proceed to set up peer-to-peer between A <-> C without taking a new backup of c to set up the replication between A->C?

I guess what I am really asking is if the data is the same, is a backup required for initialization?

[Justin Swanhart]

Hi,

1) stop the slave threads on B and C

2) stop writes to A using FLUSH TABLES WITH READ LOCK.

2.1) set read_only=true on A to prevent any writes if the read lock is accidently released

2.2) get A's coordinates with SHOW MASTER STATUS

3) issue START SLAVE UNTIL ... on B using the coordinates from A's SHOW MASTER STATUS

4) issue SHOW MASTER STATUS ON B

5) START SLAVE UNTIL ... on C using the coordinates from B

6) Issue SHOW MASTER STATUS on C

7) Issue CHANGE MASTER TO on A with coordinates from C

8) Issue CHANGE MASTER TO on C with coordinates from A (captured in step 2.2)

10) Release READ LOCK (with UNLOCK TABLES) on A 

11) A should remain read_only to prevent unexpected writes to it

12) send writes to C

--Justin

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
 
 

[Bill Karwin]

On Oct 25, 2012, at 11:47 AM, Sabika M wrote:
> I guess what I am really asking is if the data is the same, is a backup required for initialization?

No, in theory reinitializing C is not necessary as long as the data is the same. I'd recommend using pt-table-checksum to verify that A, B, C are all truly in sync before establishing A <-> C replication.

I would suggest practicing this operation with some virtual machines, or use MySQL Sandbox. It's worth your time to do a dry run until you are familiar with the steps, without risk to your production instances.

Regards,
Bill Karwin