Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to be able to edit the topology of an existing replication, is that possible?

For example if I have a star topology, I want to edit it to make it a ring one. How can I do that?

I'm thinking maybe first I need to reset all masters and servers. If I have the following topology:

    a -> b
    b -> c
    c -> a

and I want to transform it to:

    a -> c
    b -> c

I tried change master but it didn't work well because the old topology still exists.

share|improve this question
    
if the servers were in ring topology, which server becomes the master? –  neo Jan 21 '13 at 11:03

2 Answers 2

up vote 1 down vote accepted

Based strictly on your question, the topology you want is not possible. Why?

Here is what you presented (call it Topology#1)

    a -> b
    b -> c
    c -> a

and you want it to be (call it Topology#2)

    a -> c
    b -> c

Since ServerC cannot execute CHANGE MASTER TO with multiple host/port combinations, Topology#2 is mechanically impossible.

I have addressed this as to why IT IS NOT POSSIBLE:

Now, it you want to turn Topology#1 into this (call it Topology#3)

    a -> b
    a -> c

this is quite possible. Here are the steps:

STEP01) On ServerB, run STOP SLAVE;

STEP02) On ServerC, run SHOW SLAVE STATUS\G and check to make sure Exec_Master_Log_Pos is no longer updating.

STEP03) On ServerB, run SHOW SLAVE STATUS\G.

STEP04) Record Relay_Master_Log_File (call it RMLF) and Exec_Master_Log_Pos (call it EMLP) from STEP03 (For the sake of example, let RLMF='mysql-bin.000345' and EMLP=98765)

STEP05) On ServerC, run the following (Using RMLF and EMLP from STEP04):

STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.000345',
master_log_pos=98765;`
START SLAVE;

STEP07) On ServerB, run START SLAVE;

STEP08) On ServerA, run STOP SLAVE;

Going forward, have all INSERTs, UPDATEs, and DELETEs occur on ServerA only.

share|improve this answer

It is possible to setup a ring topology with MySQL replication, but not generally a good idea. Ring topology suggests multi-master replication, which doesn't work well with asynchronous replication (transactions are committed on master before being made available to slaves). If updates are made to the same record on two masters at the same time, you lose data integrity. The typical solution to that problem when you have multiple masters is to shard the data such that each master only writes certain areas of data.

Another way to approach this problem is by looking at solutions like Galera Cluster or Percona XtraDB Cluster. These solutions use MySQL but put their own replication technology in place - one which is synchronous instead of asynchronous.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.