Blog Series: MySQL Configuration Management

MySQL configuration management remains a hot topic, as I’ve noticed on numerous occasions during my conversations with customers.

I thought it might be a good idea to start a blog series that goes deeper in detail into some of the different options, and what modules potentially might be used for managing your MySQL database infrastructure.

Configuration management has been around since way before the beginning of my professional career. I, myself, originally began working on integrating an infrastructure with my colleagues using Puppet.

Why is configuration management important?

• Reproducibility. It’s giving us the ability to provision any environment in an automated way, and feel sure that the new environment will contain the same configuration.

• Fast restoration. Thanks to reproducibility, you can quickly provision machines in case of disasters. This makes sure you can focus on restoring your actual data instead of worrying about the deployment and configuration of your machines.

• Integral part of continuous deployment. Continuous deployment is a terminology everyone loves: being able to deploy changes rapidly and automatically after automated regression testing requires a configuration management solution.

• Compliance and security. Solutions like Puppet and Chef maintain and enforce configuration parameters on your infrastructure. This can sound bothersome at first, but it’s essential for maintaining a well-configured environment.

• Documented environment. Although reading someone’s puppet code can potentially harm you beyond insanity, it provides you with the real truth about your infrastructure.

• Efficiency and manageability. Configuration management can automate repetitive tasks (for example, user grants, database creation, configuration variables), as well as security updates, service restarts, etc. These can potentially bring you less work and faster rollouts.

Which players are active in this field?

The most popular open source solutions are Puppet, Chef, Ansible, and CFengine (among others). In this series, we will go deeper in the first three of them.

Let’s first start by giving you a quick, high-level introduction.

Puppet

Puppet is a language used to describe the desired state of an environment. The Puppet client reads the catalog of the expected state from the server and enforces these changes on the client. The system works based on a client/server principle.

Puppet has as default four essential components:

• Puppet Server: A Java virtual machine offering Puppet’s core services.
• Puppet Agent: A client library that requests configuration catalog info from the puppet-server.
• Hiera: A key-value lookup database, which can store and modify values for specific hosts.
• Facter: An application that keeps an inventory of the local node variables.

How can you integrate puppet in your MySQL infrastructure?

• A ready-made puppet module: https://forge.puppet.com/puppetlabs/mysql

This will allow you and your team to create users, databases, install and configure MySQL

• Use the community Luke: https://forge.puppet.com/modules?utf-8=✓&sort=rank&q=mysql

Probably my old “code from hell” module is still somewhere out there.

Chef

Chef also consists of a declarative language (like Puppet) based on Ruby which will allow you to write cookbooks for potential integrable technologies. Chef is also based on a server/client solution. The client being chef nodes, the server managing the cookbooks, catalogs and recipes.

In short, Chef consists of:

• Chef server: Manages the multiple cookbooks and the catalog
• Chef clients (nodes): The actual system requesting the catalog information from the chef server.
• Workstations: This is a system that is configured to run Chef command-line tools that synchronize with a Chef-repository or the Chef server. You could also describe this as a Chef development and tooling environment.

How can you integrate Chef in your MySQL infrastructure:

• https://supermarket.chef.io/cookbooks/mysql

Ansible

Ansible originated with something different in mind. System engineers typically chose to use their own management scripts. This can be troublesome and hard to maintain. Why wouldn’t you use something easy and automated and standardized? Ansible fills in these gaps, and simplifies management of Ansible targets.

Ansible works by connecting to your nodes (by SSH default) and pushes out Ansible modules to them. These modules represent the desired state of the node, and will be used to execute commands to attain the desired state.

This procedure is different to Puppet and Chef, which are essentially preferably client/server solutions.

Some pre-made modules for MySQL are:

• http://docs.ansible.com/ansible/mysql_db_module.html
• http://docs.ansible.com/ansible/mysql_user_module.html
• http://docs.ansible.com/ansible/mysql_variables_module.html

Conclusion and Next Steps

Choose your poison (or magical medicine, you pick the wording), every solution has its perks.

Keep in mind that in some situations running a complicated Puppet or Chef infrastructure could be overkill. At this moment, a solution like Ansible might be a quick and easily integrable answer for you.

The next blog post will go over the Puppet Forge MySQL module, so stay tuned!