ProxySQL recognition and administration about orchestrator HOT 13 OPEN

Orchestrator should be able to receive a proxysql ip and port and then read all the servers that it connects to.

This sounds like ProxySQL becoming the source of truth for Orchestrator. If my understanding is correct, I think it is quite dangerous and should be the opposite: Orchestrator should be the source of truth and configure accordingly the various ProxySQL instances.

Proxysql should then show up as a master.

I am confused about this too.
I don't think the clients should be re-configured to connect to ProxySQL.
Clients should connect to one or more proxies (depending from the clients topology), while Orchestrator does its job: manage the MySQL replication topology.

In my vision, ProxySQL should delimit the boundary between application layer and database layer: Orchestrator manages the database layer/topology and optionally reconfigure ProxySQL.

from orchestrator.

I'm happy to support ProxySQL and integrate with it.

However I'm not sure I follow:

Proxysql should then show up as a master.

ProxySQL is not a master; what is the reason it should show up as a master? What if ProxySQL has multiple backends? I'd like to clarify this please.
At any case I don't think ProxySQL would in any way be recognized as part of the topology. But if there's a strong operational argument to having it appear as such I'm happy to discuss.

I think later options are administering proxysql via the orchestrator interface.

I'd be happy to get some concrete suggestions

cc @renecannao

from orchestrator.

I've been looking into the possible integration between Orchestrator and ProxySQL as well.

I would be interested in having Orchestrator, as the source of truth regarding the MySQL topology. As such, in the event of a master failover, be able to make calls to ProxySQL to do the following:

1. modify the host groups appropriately
   a) move new master into write hostgroup
   b) remove old master from write hostgroup
   c) optionally add new master as lower priority server in read hostgroup)
2. If old master recovers and is made a slave of the new master, add to the read hostgroup

Currently, the options I'm looking at are to write shell/Perl scripts for Orchestrator to call with it's event hooks to make the changes, but as @shlomi-noach mentioned in his article comparing MaxScale and Orchestrator (http://code.openark.org/blog/mysql/thoughts-on-maxscale-automated-failover-and-orchestrator), resorting to shell scripts and Perl scripts is wrought with other related issues.

from orchestrator.

This article by Tibor Korocz (https://www.percona.com/blog/2016/11/09/orchestrator-and-proxysql/) posted a possible integration, but architecturally, it introduced another MySQL monitor script that would be called by the ProxySQL scheduler.

While it would work, it would seem to be best for Orchestrator to be able to make the call, since it would also be executing all the commands to initiate failover, so adjusting ProxySQL would be closest in time delay, rather than having an outside script detect the failover secondarily.

from orchestrator.

Hi @leeparayno , so that scripts only required if the old master comes back. Because that case ProxySQL are going to send reads to that server. This is why we need a scheduler because ProxySQL does not remove that server form the hostgroup in default. But Orchestrator can handle the master failover and ProxySQL does not need the scheduler to follow those changes.

Adding ProxySQL support could be interesting but maybe Orchestrator also should visualise the topology under ProxySQL , so we can see how the HostGroups looks like and who is the current master etc.. so we can compare the two topologies. I think that would be helpful for many ppl because not just the administrators can see what is under the hood in ProxySQL. Opinion?

from orchestrator.

Adding a node in the visual layout in Orchestrator would be great. It is especially useful in getting a view of the hierarchy, especially where it could pertain to viewing the host groups.

I would liken it to being able to view a load balancer.

Given I believe Orchestrator can display binlog servers in the topology, I would think this would be a good extension.

from orchestrator.

Given I believe Orchestrator can display binlog servers in the topology, I would think this would be a good extension.

However binlog servers are true participant in the replication hierarchy, whereas proxySQL isn't. Where would it be presented and how? Can you visualize this?

from orchestrator.

This is what I meant by "as a master"

basically the cluster would be a single or group of proxysql servers (if group, they have to be configured the same)... then we see all servers in proxysql mysql_servers as "slaves"

from orchestrator.

This is what I meant by "as a master"

Sorry :) What is what you meant by "as a master"?

from orchestrator.

If someone can draw something on a napkin and upload a photo that would be helpful to the discussion

from orchestrator.

This is what I had in mind (just a quick photoshop)

from orchestrator.

@ManjotS thank you -- can you explain the topology we see in the picture?

from orchestrator.

So each mysql "cluster" would still show in clusters as normal. But if we add a proxysql server, all servers in mysql_servers table are also discovered. Then, they show in the proxysql "cluster".

If more proxysql are added but have same config, they fall into the same "instance group" of proxysql. This allows us to then admin them simply with the gear button for proxysql at some future point. I think the first step is just seeing them.

We could also use different colored connectors to show read only, write only or read/write connections from proxy to mysql.

Hopefully this makes sense. While proxysql isnt a "master" of the mysql servers, it is above them in the topology.

from orchestrator.