Any large project started with a couple of servers. First, there was one DB server, then slaves were added to scale the read operations. And then — hold on! There's one master and many slaves; if one of the slaves goes down, it’s fine, but if the master goes down — that's bad: downtime, admins frantically trying to bring the server back up. What to do? Reserve the master. My colleague Pavel has already written about this. , and I won't repeat it. Instead, I'll explain why you definitely need an Orchestrator for MySQL!
Let's start with the main question: "How will we switch the code to a new machine when the master goes down?"
- I prefer the VIP (Virtual IP) scheme the most; we will discuss it below. It’s the simplest and most obvious, but it has a clear limitation: the master we want to reserve must be in the same L2 segment as the new machine, which means we can forget about a second data center. Moreover, ideally, if we follow the rule that a large L2 is evil because L2 should only be rack-level, while L3 is between racks, this scheme has even more limitations.
- We can specify the DNS name in the code and resolve it through /etc/hosts. In reality, there won't be any resolution. The advantage of this scheme: there is no limitation typical of the first method, meaning we can also organize cross-data center setups. But then the obvious question arises: how quickly will we pull changes to /etc/hosts via Puppet-Ansible?
- We can slightly modify the second method: install a caching DNS on all web servers, through which the code will access the master database. We can set a TTL of 60 for this record in DNS. It seems that if implemented correctly, this method is good.
- The scheme with service discovery, implying the use of Consul and etcd.
- An interesting option with . We need to route all MySQL traffic through ProxySQL, which can determine who is currently the master. By the way, one of the use cases for this product can be read in my .
The author of Orchestrator, while working at GitHub, first implemented the VIP scheme and then switched to the scheme with consul.
A typical infrastructure scheme:
I’ll immediately describe the obvious situations that need to be considered:
- The VIP address should not be configured on any of the servers. Imagine this: the master has rebooted, and while it’s booting up, Orchestrator switches to failover mode and makes one of the slaves the master; then the old master comes back up, and now the VIP is on two machines. This is bad.
- For the orchestrator, you will need to write a script to communicate with the old master and the new master. On the old master, you must execute ifdown, while on the new master — ifup vip. It would also be good to include in this script that in the event of a failover, the port on the switch of the old master is simply turned off to avoid any split-brain scenario.
- After the Orchestrator has called your script to first take down the VIP and/or turn off the port on the switch, and then on the new master called the script to bring up the VIP, don’t forget to use the arping command to tell everyone that the new VIP is now here.
- All slaves must have read_only=1, and as soon as you promote a slave to master, it must switch to read_only=0.
- Remember that any slave we choose for this can become the master (the Orchestrator has a whole mechanism for prioritizing which slave should be considered a candidate for new master first, which second, and which slave should never be chosen as a master under any circumstances). If a slave becomes the master, it will retain the load of a slave and additionally take on the load of a master, which needs to be taken into account.
Why do you absolutely need Orchestrator if you don't have it?
- Orchestrator has a very user-friendly graphical interface that displays the entire topology (see the screenshot below).
- Orchestrator can track which slaves are lagging behind and where replication has actually broken down (we have scripts attached to Orchestrator for sending SMS notifications).
- Orchestrator informs you about which slaves have a GTID errant error.
Orchestrator interface:
What is a GTID errant?
There are two main requirements for Orchestrator to function:
- All machines in the MySQL cluster need to have pseudo GTID enabled; we have GTID enabled.
- There must be a single type of binlogs everywhere, which can be statement. We had a configuration where on the master and most slaves it was Row, and two historically remained in Mixed mode. As a result, these slaves were simply not able to connect to the new master by Orchestrator.
Remember that the most important thing in a production slave is its consistency with the master! If both the master and the slave have Global Transaction ID (GTID) enabled, you can use the gtid_subset function to determine if the same data-changing queries have been executed on these machines. You can read more about this. .
Thus, Orchestrator shows you through the GTID errant error that there are transactions on the slave that are not on the master. Why does this happen?
- The slave is not set to read_only=1, someone connected and executed a data modification request.
- The slave is not set to super_read_only=1, then an admin mistakenly connected to the wrong server and executed a request there.
- If you have considered both previous points, there is another trick: in MySQL, a flush command also ends up in the binary log, so at the first flush on the master and all slaves, a GTID errant will appear. How to avoid this? In perona-5.7.25-28, a setting called binlog_skip_flush_commands=1 was introduced, which prevents flush from being written to the binary logs. There is documentation on mysql.com. .
To summarize all that has been said above. If you are not yet ready to use Orchestrator in failover mode, then set it to monitoring mode. This way, you will always have a map of MySQL machines at your fingertips and clear information about the type of replication on each machine, whether the slaves are lagging behind, and most importantly—how consistent they are with the master!
An obvious question: "How should Orchestrator work?" It must choose a new master from the current slaves and then reconnect all slaves to it (this is precisely why GTID is needed; using the old mechanism with binlog_name and binlog_pos makes switching a slave from the current master to the new one simply impossible!). Before we had Orchestrator, I once had to do all this manually. The old master was hanging due to a faulty Adaptec controller, and it had about 10 slaves. I needed to transfer the VIP from the master to one of the slaves and reconnect all the other slaves to it. How many consoles I had to open, how many simultaneous commands I had to enter… I had to wait until 3 AM, offload all slaves except two, make the first machine the master, immediately connect the second machine to it, then connect all other slaves to the new master and restore the load. In short, it was a nightmare…
How does Orchestrator work when it switches to failover mode? It is easiest to illustrate this with an example situation when we want to make a more powerful, more modern machine the master than the one currently in use.
The image shows the midpoint of the process. What has been done up to this point? We stated that we wanted to make a certain slave the new master, and the Orchestrator began simply reconnecting all the other slaves to it, with the new master acting as a transit machine. In this scheme, no errors occur; all slaves operate, the Orchestrator removes the VIP from the old master, transfers it to the new one, sets read_only=0, and forgets about the old master. That's it! The downtime of our service is the time taken to move the VIP, which is 2-3 seconds.
That's all for today, thank you everyone. Soon there will be a second article about the Orchestrator. In a famous Soviet film "Garage", one character said, "I wouldn't go on a reconnaissance mission with him!" So, Orchestrator, I would go on a reconnaissance mission with you!
Source: habr.com
