Pythian Blog: Technical Track

Ansible Inventory Automation Using Consul and Orchestrator

by Pythian Marketing on Nov 13, 2020 12:00:00 AM

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. 
 
  cluster= role=master
 
 
  cluster= role=replica datacenter=
 
 
 []
 
 
 
 
 [-master]
 
 
 
 [-replicas]
After configuring Consul-template to work with this template file, it created the following Ansible inventory file: 
mysql1 cluster=osdb-lab-cluster role=master
 mysql6 cluster=pythian-lab-cluster role=master
 mysql2 cluster=osdb-lab-cluster role=replica datacenter=DC-1
 mysql3 cluster=osdb-lab-cluster role=replica datacenter=DC-1
 mysql4 cluster=osdb-lab-cluster role=replica datacenter=DC-2
 mysql5 cluster=osdb-lab-cluster role=replica datacenter=DC-2
 mysql10 cluster=pythian-lab-cluster role=replica datacenter=DC-2
 mysql7 cluster=pythian-lab-cluster role=replica datacenter=DC-1
 mysql8 cluster=pythian-lab-cluster role=replica datacenter=DC-1
 mysql9 cluster=pythian-lab-cluster role=replica datacenter=DC-2
 
 [osdb-lab-cluster]
 mysql1
 mysql2
 mysql3
 mysql4
 mysql5
 
 [pythian-lab-cluster]
 mysql6
 mysql10
 mysql7
 mysql8
 mysql9
 
 [osdb-lab-cluster-master]
 mysql1
 
 [pythian-lab-cluster-master]
 mysql6
 
 [osdb-lab-cluster-replicas]
 mysql2
 mysql3
 mysql4
 mysql5
 
 [pythian-lab-cluster-replicas]
 mysql10
 mysql7
 mysql8
 mysql9
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.
Topics: MySQL Ansible Technical Track Open Source Docker

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.