Multi-site master-slave semi-sync replication and failover setup

[kkli...@syntaxhighlighted.com]

Hi,

I'm currently testing orchestrator with a goal of using it to manage our future multi-site MySQL cluster.

The goal I'm trying to achieve is to create a topology where there is a "core" part of the cluster spanning two datacenters, and multiple "leaf" nodes that are asynchronously replicated to other locations for better read latencies.

The topology I have in mind is like that:

                 DC #1                                  DC #3

                                           |

 +----------+             +------------+   |          +------------+

 |          |  async repl |            |  async repl  |            |

 |  Master  +------------>+  Slave #1  +---+---------->  Slave #1  |

 |          |             |            |   |          |            |

 +----+-----+             +------------+   |          +------------+

      |                                    |

-----------------------------------------  |

      | semi-sync repl                     |

      v                                    |

+-----+------+            +------------+   |

|            | async repl |            |   |

|  Slave #2  +----------->+  Slave #3  |   |

|            |            |            |   |

+------------+            +------------+   |

                                           |

                 DC #2

One master in DC #1 that serves writes and reads, with all slaves running in read-only mode. I'd like to achieve "seamless" failover between nodes in DC#1 and DC#2 when master or entire DC bites the dust.

In case of master failure, I'd like to promote one of the slaves between DC#1 and DC#2 and then setup semi-sync replication to slave in another DC. For example, if Slave #2 is promoted I'd like to enable semi-sync replication to Slave #1.

In case of total failure of DC#1 I'd like to promote one of slaves from DC#2 and enable semi-sync replication to other slave in same DC - so in my example Slave #2 become master, and we configure semi-sync replication to Slave #3 (or vice-versa).

Two questions:

- do this topology and failover scenarios make sense?

- how would i approach it from orchestrator side of things?

My initial understanding was that I should create a post-failover script that communicates with orchestrator and uses its APIs (enable-semi-sync-master et al.) to enable semi-sync on the host that my script will designate "best candidate for semi-sync replication" but then I started looking into Semi-Sync Enforcement code where commends talks about how semi sync must be enabled before "START SLAVE" is issued, and this code to be guarded by SemiSyncEnforced flag - should I also enable this mode? If so, what would my.cnf configuration on master and slaves look like? Right now I'm thinking something similar to:

skip-slave-start

read_only

super_readonly

rpl_semi_sync_master_wait_no_slave = ON

rpl_semi_sync_master_wait_for_slave_count = 1

rpl_semi_sync_master_timeout = 31536000

gtid-mode = ON

enforce-gtid-consistency = ON

log-slave-updates