master-gracefull-takeover in a multi-instance (mysqld) setup

159 views
Skip to first unread message

Laurent Indermühle

unread,
Jul 15, 2019, 4:38:46 AM7/15/19
to orchestrator-mysql
Good morning, everyone,

I would like to know if there is an interest in a tool that makes it easier to manage multi-instances (mysqld) MySQL servers.

This is my case and I am looking to create a script that manages a graceful-master-takeover of all instances of the same server duo (for simple replication setup we only need 2 servers). Then the script would switch the virtual IP when all the masters are on the other server.

Does anyone among you have any advice on how best to do this?

I have been trying differents ways to script this. On way is to get a list of all the instances of the same server, then with this information to verify that all masters are on one server and all slaves are on another.

Here is the layout:


                                  Server 1                 Server 2
                          ╔════════════════════╗    ╔════════════════════╗
                          ║ mysql-customer1-p1 ║    ║ mysql-customer1-p2 ║
                          ║                    ║    ║                    ║
              ┌─────────┬─╨────────────────────╨────╨────────────────────╨─┐
    Cluster 1 │ MYSQLD1 │         MASTER                    SLAVE          │
              └─────────┴─╥────────────────────╥────╥────────────────────╥─┘
                          ║          ▲         ║    ║         ▲          ║
      4 Instances ────────╫──────────┼─────────╫────╫─────────┤          ║
                          ║          ▼         ║    ║         ▼          ║
              ┌─────────┬─╨────────────────────╨────╨────────────────────╨─┐
    Cluster 2 │ MYSQLD2 │         MASTER                    SLAVE          │
              └─────────┴─╥────────────────────╥────╥────────────────────╥─┘
                          ║                    ║    ║                    ║
                          ╚════════════════════╝    ╚════════════════════╝



As in this case the word "Cluster" does not represent the same thing as in the Orchestrator terminology, I took the liberty of renaming the fields "cluster_name' to "server_alias" and adding a field "instance_name' has follow:

CREATE DATABASE IF NOT EXISTS meta;

CREATE TABLE IF NOT EXISTS meta.cluster (
  anchor         TINYINT NOT NULL,
  server_alias   VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
  instance_name  VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
  cluster_domain VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
  repl_user      VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
  repl_pass      VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
  PRIMARY KEY (anchor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



I configured the Detect* requests like this:

"DetectClusterAliasQuery":     "SELECT CONCAT(server_alias, ':', instance_name) FROM meta.cluster WHERE anchor=1",
"DetectClusterDomainQuery":    "SELECT cluster_domain FROM meta.cluster WHERE anchor=1",
"DetectInstanceAliasQuery":    "SELECT instance_name FROM meta.cluster WHERE anchor=1",

Here is the name of each part of the result of the command `orchestrator -c clusters-alias`:

      server_name       port        server_alias       instance_alias
    ╒════════╧═══════╕ ╒═╧═╕     ╒═════════╧════════╕ ╒══╧══╕
    mysql-customer1-p1:33005     mysql-customer1-prod:mysqld5
    ╘═══════════╤══════════╛     ╘═════════════╤════════════╛
          instance_name                 cluster_alias

I did this because some of my hostnames have no logic. Thanks to the server_alias I can create much clearer list:

server_name         server_alias          instances_alias
================    ====================  =========================
mysql-customer1-p1  mysql-customer1-prod  mysqld1, mysqld2, ...
mysql-customer1-p2  mysql-customer1-prod  mysqld1, mysqld2, ...
mysql-customer2-p1  mysql-customer2-prod  mysqld1, mysqld2, ...
mysql-customer2-p2  mysql-customer2-prod  mysqld1, mysqld2, ...
mysql-test-t1       mysql-customer1-test  mysqld1, mysqld2, ...
mysql-test-t2       mysql-customer1-test  mysqld1, mysqld2, ...
mysql-test-t3       mysql-customer2-test  mysqld1, mysqld2, ...
mysql-test-t4       mysql-customer2-test  mysqld1, mysqld2, ...


One way to ensure that all masters end up on the same server is to specify the destination:

orchestrator -c graceful-master-takeover -alias mysql-customer1-prod:mysqld1 -d mysql-customer1-p1:33001

In case of a master was already on the right server, Orchestrator does nothing else that displaying:

2019-07-15 09:58:29 FATAL GracefulMasterTakeover: indicated designated instance mysql-customer1-p1:33001 must be directly replicating from the master mysql-customer1-p1:33001


But this does not make the script any easier. The alias alone is not enough because it is also necessary to retrieve the cluster_name to pass it to the orchestrator command. I think we should then do it like this:

  1. Create an array: "server_name" => "server_alias" by removing the port numbers and instance_alias. For example: "mysql-test-t1" => "mysql-customer1-test"
  2. Ask the user to choose a server from a list of server_alias whose duplicates have been deleted.
  3. List all servers with the server_alias chosen by the user.
  4. For each server, list the contained instances and their role 'Master' or 'Slave'.
  5. Run graceful-master-takeover on all instances_name using the -d to ensure that at the end all masters are on the same server.
  6. If successful -> VIP switchover

Maybe I missed an easier way to do things?
If not, do you think it would be useful to continue an external script or could this be integrated into Orchestrator?

Best regards,
Laurent

Shlomi Noach

unread,
Jul 15, 2019, 5:10:16 AM7/15/19
to Laurent Indermühle, orchestrator-mysql
I confess I did not fully understand the situation. Please correct me if I'm wrong: you have two physical servers, sharing one VIP. You have multiple master<->replica pairs with multiple mysqld processes running on each host. You want the masters to always be on the same server, and if you need to switch to another server you need to graceful-takeover all clusters to 2nd server, then move the VIP.

So far am I correct?

I'm confused by the use of server_alias and what it's meant to represent. I'm confused about "Ask the user to choose a server from a list of server_alias whose duplicates have been deleted." Not sure what the "duplicates" are, and why they would be deleted.

I'm therefore a bit confused about the process. However, let me suggest an approach which you may take one step further.

The approach I'm suggesting is to use 

   "DetectDataCenterQuery": "select @@hostname",

so that all servers located on one server will appear to be in same "data center", and all servers on the 2nd host will appear to all be on a 2nd "data center".

That will make it easy for you to make some queries:

- Are all masters located in same datacenter? e.g. something like:
  orchestrator-client -c api -path masters | jq -r '.[].DataCenter' | sort | uniq
- Which clusters have a master in <dc1> ?
  orchestrator-client -c api -path masters | jq -r '.[] | select(.DataCenter="dc1") | .ClusterName'  

Will that help solve your problem?

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orchestrator-mysql/1d9708ab-6a60-454e-8b8b-4c937e99af94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Laurent Indermühle

unread,
Jul 15, 2019, 5:23:17 AM7/15/19
to orchestrator-mysql
You're correct for the first part :)

Sorry that I wasn't clear in the workflow description of the script. By "Ask the user to choose a server from a list of server_alias whose duplicates have been deleted.", I wanted to say : since I have multiple instances per server, I only want to display each server once. By user I meant the user of the script.

But, your advice about de DataCenterQuery feel very promising. It's exactly what my "servers" are, one per DataCenter. And the master-takeover will be used in case of a DataCenter failure.

Since my script are executed on the server that hosts Orchestrator, I use orchestrator -c command directly. Why are you suggesting to use orchestrator-client ? Does it offer more functionality?

I'll use the "DetectDataCenterQuery" and try orchestrator-client. I'll keep you posted. Thank you very much for the help!
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

Laurent Indermühle

unread,
Jul 15, 2019, 7:16:40 AM7/15/19
to orchestrator-mysql
Ahhh, the query won't work for mysql-test-t3 and mysql-test-t4 :(
p1 and t1 are in Datacenter1.
p2 and t2 are in Datacenter2.
t3 is in Datacenter 1 <- Problem
t4 is in Datacenter 2 <- Problem

server_name         server_alias          instances_alias
================    ====================  =========================
mysql-customer1-p1  mysql-customer1-prod  mysqld1, mysqld2, ...
mysql-customer1-p2  mysql-customer1-prod  mysqld1, mysqld2, ...
mysql-customer2-p1  mysql-customer2-prod  mysqld1, mysqld2, ...
mysql-customer2-p2  mysql-customer2-prod  mysqld1, mysqld2, ...
mysql-test-t1       mysql-customer1-test  mysqld1, mysqld2, ...
mysql-test-t2       mysql-customer1-test  mysqld1, mysqld2, ...
mysql-test-t3       mysql-customer2-test  mysqld1, mysqld2, ...
mysql-test-t4       mysql-customer2-test  mysqld1, mysqld2, ...

I need a way to store the DataCenter name for every instance. And the meta.cluster table won't work because it is replicated from the master to the slave :(
Maybe I could use --replicate-ignore-db=meta ? I don't like this idea, I'm using GTID and it will insert errant transaction...

Shlomi Noach

unread,
Jul 15, 2019, 7:33:18 AM7/15/19
to Laurent Indermühle, orchestrator-mysql
Hi,

No, please do not use the `meta` table, since it replicates as you said.

Just turn  "DetectDataCenterQuery" into something smarter:

 "DetectDataCenterQuery": "case
  when @@hostname rlike '%-p1.%' then 'DC1'
  when @@hostname rlike '%-p2.%' then 'DC2' 
  when @@hostname rlike '%-t1.%' then 'DC1'
  when @@hostname rlike '%-t2.%' then 'DC2'
  else 'DC-unknown'
end"
  
or whatever makes sense.



To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orchestrator-mysql/5fef5b7e-ae7a-453a-82a1-0eff8acb3f4a%40googlegroups.com.

Laurent Indermühle

unread,
Jul 15, 2019, 8:54:52 AM7/15/19
to orchestrator-mysql
Thanks again Shlomi Noach, it works now :) rlike didn't work with %. Had to use LIKE instead :

"DetectDataCenterQuery": "SELECT CASE WHEN @@hostname like '%-p1%' THEN 'DC1' WHEN @@hostname like '%-t1%' THEN 'DC1' WHEN @@hostname like '%-t3%' THEN 'DC1' WHEN @@hostname like '%-p2%' THEN 'DC2' WHEN @@hostname like '%-t2%' THEN 'DC2' WHEN @@hostname like '%-t4%' THEN 'DC2' ELSE 'DC-unknown' END AS DataCenter;",

I managed to use orchestrator-client with --auth option (I'm using "AuthenticationMethod": "basic"). Now I can use the api from my script, which I hope, will lead to a way more clean code. It's a shame I didn't realize sooner that the api was that much verbose than plain orchestrator commands. Maybe I missed that in the documentation.

I'll post here any progress I made with my "mass-master-takeover" script !

Regards,
Laurent
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

Laurent Indermühle

unread,
Jul 15, 2019, 10:18:51 AM7/15/19
to orchestrator-mysql
I'm totally in love with jq! I had to fix your Select statement but it works like a charm, here per example I retrieve all servers in DC1:

orchestrator-client -c api -path all-instances | jq -r '.[] | select(.DataCenter == "DC1") | .Key.Hostname' | sort | uniq

> mysql-customer1-p1
> mysql-customer2-p1
> mysql-test-t1
> mysql-test-t3


Getting a nice array is so easy (remove the dot before a fields and you get a key: Value pair):

orchestrator-client -c api -path all-instances | jq -r '.[] | {Hostname: .Key.Hostname, DataCenter}'

{
  "Hostname": "mysql-test-t1",
  "DataCenter": "DC1"
}
{
  "Hostname": "mysql-test-t2",
  "DataCenter": "DC2"
}
...



I get back to my script now that I have met the API :) Orchestrator is fantastic to use!

Shlomi Noach

unread,
Jul 15, 2019, 10:20:08 AM7/15/19
to Laurent Indermühle, orchestrator-mysql
The power of http api + shell scripting cannot be overestimated...

To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orchestrator-mysql/a6648de9-4146-4326-9cb8-6f1f8a3bf967%40googlegroups.com.

Laurent Indermühle

unread,
Jul 18, 2019, 5:47:59 AM7/18/19
to orchestrator-mysql
I may have found a bug with graceful-master-takeover. I need to run the command and get the result, either a success, a failure or a refusal (If the destination is already the master per ex.).

Here's what I got so far (orchestrator 3.1.0):

1) Using orchestrator command. It works, but the output is not easy to grab from a script as it output SQL syntax errors :
orchestrator -c graceful-master-takeover -i mysql-customer1-t1:33005 -d mysql-customer1-t2:33005


2019-07-18 ERROR Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and cluster_name='mysql-customer1-t2:33005'' at line 6
2019-07-18 ERROR Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and cluster_name='mysql-customer1-t2:33005'' at line 6
mysql-test-t1:33005
mysqld5-bin.000010:1563


2) Using the API works in case of success or refusal. But in case of error, the timeout is super long and the output is again not easy to grab :

orchestrator-client -c api -path graceful-master-takeover/mysql-customer1-t2/33006/mysql-customer1-t1/33006 | jq -r '.[].Code, .Message'

#Already master
GracefulMasterTakeover: indicated designated instance mysql-customer1-t1:33006 must be directly replicating from the master mysql-customer1-t1:33006

# Success
OK
graceful-master-takeover: successor promoted
(syslog also report the SQL syntax error here)

# Failure (network down)
[looooong pause]
dial tcp 192.168.100.101:33006: connect: no route to host

# Failure (slave stopped too long for master to have binlog anymore)
Start SLAVE UNTIL is past coordinates: mysql-test-t2:33006

I was hopping orchestrator-client will always return a code. It's not the case.

And regarding the SQL syntax. Does it depends on my configuration (Detect queries in previous message I guess) or should I create an issue on Github?

Regards,
Laurent
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

Shlomi Noach

unread,
Aug 1, 2019, 1:25:33 AM8/1/19
to Laurent Indermühle, orchestrator-mysql
Sorry for the late response. Can you please open an issue with this information? Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-my...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orchestrator-mysql/aa65d58b-ef01-4512-98e4-7ae5665ceef0%40googlegroups.com.

Laurent Indermühle

unread,
Aug 4, 2019, 9:09:49 AM8/4/19
to orchestrator-mysql
No worries for the delay. You are super kind to answer every question ;)
I'm off this week. But if you need more information, I'll be back soon.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "orchestrator-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orchestrator-mysql+unsub...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages