Handle Multi-source replication in Orchestrator

[sjmudd]

This is to discuss multi-source replication and how it can be handled in orchestrator

What?

• Oracle MySQL provides multi-source replication from MysQL 5.7
• MariaDB provides multi-source replication from MariaDB 10.0
• The implementations are not compatible.

Why?

Some people may actually use this so it's good to for orchestrator to at least not do the wrong thing and not break anything. Better still would be add increasing levels of functionality (according to demand).

Currently orchestrator runs show slave status and records the output, the last row overwrites any data from previous rows. This sort of works but not really.

How?

Recognise when a slave is using multi-source replication

• for Oracle MySQL SHOW SLAVE STATUS returns more than one row and a channel_name
• For MariaDB I'm not sure how to recognise this
• I would add an entry to Instance struct. Something like NumberOfMasters int.

True support for multi-source would require changes to these fields:

• MasterKey
• IsDetachedMaster
• Slave_SQL_Running
• Slave_IO_Running
• HasReplicationFilters
• SupportsOracleGTID
• UsingOracleGTID
• UsingMariaDBGTID
• UsingPseudoGTID
• ReadBinlogCoordinates
• ExecBinlogCoordinates
• IsDetached
• RelaylogCoordinates
• LastSQLError
• LastIOError
• SecondsBehindMaster
• SQLDelay
• ExecutedGtidSet
• GtidPurged
• SlaveLagSeconds
• SlaveHosts
• ReplicationDepth
• IsCoMaster
• HasReplicationCredentials
• ReplicationCredentialsAvailable
• SemiSyncEnforced

This suggests putting them in a different structure which is referenced from the Instance struct.

Show this in some places

• for orchestrator -c topology output we could add an extra field single-source or multi-source to represent this
  • add a new command orchestrator -c multi-source-topology which would show masters and slaves of a specific instance for a specific instance.
• for the HTTP interface we could add a new symbol to indicate the server has more than one master, and if you do a mouse over you get a list of master_name, sql/io replication working?, delay (similar to the output you get when doing a mouse over the compact views

Visualise the output of replication

• not sure how we'd do this and perhaps we could only do it for "simple configurations" based on a specific server? That is not show the whole topology but show the specific server as the centre of connections to masters and slaves

Allow certain operations to work on multi-source servers

• when relocating a slave make orchestrator aware of the different masters so that the appropriate slave configuration can be adjusted.

[ManjotS]

@shlomi-noach is there any progress or plans in regards to this?

[shlomi-noach]

Hi @ManjotS and sorry for the late reply, I was out of office.

On my side there are no plans for multi-source replication, and I leave this in the hands of @sjmudd should he wish to pursue the issue, or anyone else in collaboration with @sjmudd.

Just to explain that for myself this isn't a priority and I have many other things on my plate, orchestrator-wise and non-orchestrator-wise. I do not object to the idea, though I do believe the change will be large and hard to achieve.

[fubizitch]

Would really like this added. Might a donation increase the priority of this request?

[shlomi-noach]

@fubizitch thank you for your offer. A donation isn't a driving factor, and I'm afraid I still have other things on my plate.

[fubizitch]

I guess I didn't mean "donation", but maybe hire you to implement. My company has 500 employees and would love to see this...but if you meant that money isn't a driving factor, then OK.

Anybody know of a good alternative? We're looking at ClusterControl, but I'm assuming the pricing is outrageous.

[shlomi-noach]

Thank you. Merely by way of sharing, I'm at present hired by a company where I author orchestrator as part of my day job, and according to a reasonable balance between the company's needs of orchestrator, the community's need from orchestrator, and my overall other duties.

The company which hires me at present is the company running and operating this very discussion. In fact, orchestrator is at this very moment operating the high availability of this comment thread and drives my confidence that this comment will be submitted and persisted.

Your suggestion and offer is perfectly legitimate and appreciated, but perhaps this very media is not the right place.

I have no insights on other tools at present.

[tomkrouper]

tl;dr the boss is watching. :-D

[shlomi-noach]

Back to being serious, @fubizitch if your company has the means to sponsor this development, would it be open to sponsoring some one else to do these contributions? I don't have anyone specific in mind, just that if someone else provides quality contributions I'm happy.

[fubizitch]

I think we would be open to doing that. If we knew somebody personally who could actually build such functionality in Orchestrator, we probably would've already come up with a solution on our own...

I think we'll be able to achieve monitoring of our cluster, but the visuals and automated recovery would be nice, which is why we're interested in Orchestrator.

We're currently hosting mysql databases in 3 locations, and are planning to expand to 5. Wish we could just use Galera, but the write latency is horrendous (LA -> NY -> Vancouver). Eventually we'll probably just migrate to Amazon Aurora instead of using onsite servers.