Here at Pythian we get a lot of exposure to new technologies and implementation strategies via the work we do internally and for our clients. The most noteworthy technology stack that I’ve seen get a lot of traction in the MySQL community recently is the high availability stack including Orchestrator, Consul and ProxySQL. I won’t dive too deeply into the details of this implementation as there are several blog posts that our team has submitted on this topic, but the key thing I want you to keep in mind for this particular topic is the usage of Consul as a “source of truth” for the state of your MySQL replication clusters. If Orchestrator or its adjacent scripts are running as expected, Consul should always have the latest information pertaining to the state of your cluster. This is incredibly valuable. In fact, in the high availability stack in question, we take advantage of this source of truth by using it to drive configuration changes for ProxySQL via Consul-template.Another technology we’ve been leveraging for automation for MySQL and other database technologies is Ansible. While this is only one of many automation tools available today, our team has adopted it due to the ability to drive complex process orchestration with simple YAML syntax and no need for client installation. I’ve had the pleasure of creating a number of different playbooks and I’ve come to enjoy working with Ansible overall. My only complaint is inventory management.
Inventory Management in Ansible
In Ansible there are two key methods for managing inventory. You can either create and maintain an Ansible inventory file, or you can point your Ansible playbook at a single database server, then use modules like add_host to manage inventory in memory once the playbook has launched. The problem is that both of these methods can lead to some extra work and possibly expose you to human error.If you opt to go with in-memory inventory management using modules, you may have to write some complex code to not only to parse the environment, but monitor for changes as well. For example, if you have a playbook that involves a promotion of a replica to become a new master, you may need to place additional code to validate the promotion, then update your in-memory inventory before continuing to the next task in the playbook.If you’re using an inventory file you have to take on the responsibility of keeping it up to date. Between the initial authoring and the time you execute a playbook, there may have been changes. Someone may have forgotten to take that recently promoted replica out of the replica group, leading to some pretty catastrophic results. In short, you always need to verify the accuracy of your inventory prior to playbook execution. This always makes me a bit nervous.This drove me to question whether I could automate Ansible inventory management. It turns out the answer is yes!
Ansible Inventory Automation
My thought stemmed from the idea that if we trust Consul enough to have it drive our proxy configuration, we should be able to trust it enough to drive inventory management for process automation.I launched my lab MySQL containers in two clusters, one called “osdb-lab-cluster” and the other called “pythian-lab-cluster.” Both of these clusters had a single master and four replicas. Orchestrator was managing both these clusters. Using the native Consul capabilities for the master of the cluster, as well as our scripts to pass along replica information, I ensured that information for both of these clusters was being passed into Consul. Master information was placed in Consul under /mysql/master/{cluster-name}/{hostname}. Replica information was placed in Consul under /mysql/slave/{cluster-name/{hostname}. Please note that we have expanded the replica script to include information about datacenter, downtiming, etc.Given this structure, I created the following Consul-template file.
This is a complete and accurate representation of my clusters that will automatically update as Orchestrator provides new information to Consul.
Conclusion
By leveraging Consul-template, you no longer have to worry about the creation, updating, or, accuracy of your inventory. When using a playbook that includes a promotion, all you have to do is:
End your playbook at the end of the promotion.
Pause the process to allow Consul to receive new information.
Resume your process launching a new playbook which reads from the same, freshly updated, inventory file.
The main thing I want you to take away from this blog post isn’t simply to take some free code and use it to automate the creation of Ansible inventory files. Instead, consider what you can automate when you have a trusted source of truth for the state of your MySQL replication clusters.
.svg){kind=small}
.svg){kind=small}