Database configuration is one of those things that you can easily automate with Ansible.

I discussed this in my presentation at PerconaLive 2021 on Practical Database Automation.

Another good question that came out of that session was what to do with complicated config files.

Today I will provide an overview of MySQL database configuration using Ansible, how it might get out of hand, and what you can do about it.

Overview of configuring MySQL

So configuring your database is a critical task for any database administrator.

It’s critical because this is how you allow the database to take advantage of the resources the server provides to it.

Configuration is also how you prevent the database from crashing the server when it grows beyond the available resources.

Because of the second point, MySQL historically had extremely conservative default settings.

If you are running in production with more than about 100MB of data and you have never touched the MySQL configuration, you are limiting the performance of MySQL.

The typical method of configuring MySQL is by editing the ‘my.cnf‘ file. This file can exist in many places, depending on what operating system you are using.

But it’s basically a plaintext file that allows grouping of config settings for the mysql server, or the mysql client, or several other pieces.

There are some settings that you can modify in runtime, and some that require a restart.

But even if you are changing settings for a running MySQL server, you eventually want that setting to exist in the config file.

This prevents the change from being lost if MySQL is restarted.

Static files vs Jinja2 templates in Ansible

If you are managing the database configuration with Ansible, congratulations!

This allows you to easily configure settings across all of your environments without manually connecting to those databases.

It helps prevent configuration drift, where settings don’t match from what you expect.

Ansible allows you to configure the database in two ways.

First, you can copy a static my.cnf to each server. This is simple, and straight forward.

Unfortunately, it means you are guaranteed to have one file per MySQL instance.

This is because you can’t use Ansible variables.

So you are left creating a new file for a simple replication environment that must have different server-ids.

The second method for configuring MySQL with Ansible is by using Jinja2 templates.

This does allow you to use variables and other Jinja2 constructs to generate the MySQL configuration file.

For that reason, Jinja2 templates are typically the preferred method for configuring a MySQL database.

Creating complicated files

During my presentation at PerconaLive, I received an interesting question.

Someone asked if there were any alternatives to Jinja2 templates for configuration.

When digging into the question more, apparently this person has extremely complicated MySQL configuration files.

I don’t know if this caused the configuration to take a long time generating.

But I can imagine scenarios where it becomes complicated to read.

Using Jinja2, you can leverage conditionals and loops and filters to generate the file.

This allows you to craft a single my.cnf template that can handle almost any scenario.

For instance, you can choose to enable binary logging only on source servers, not on replicas.

Or you might have some environments that run Galera, or MySQL’s InnoDB Cluster.

All of them could use the same configuration template.

This can become really complicated with the different switches and logic paths.

The final output depends on the variables available at runtime of the playbook, so it can be difficult to know what a particular server will generate.

And complicated usually means hard to maintain.

Using multiple config files

If this is a scenario that affects you, one option to consider is splitting your Jinja2 template into multiple templates.

MySQL configuration files offer the ability to include other files, or even a directory that contains multiple cnf files.

So if you do have multiple environments that run different MySQL technologies, you might have one config template for each.

You could have a mysqld.cnf template and a galera.cnf template. You could also have a file specific to replication settings and one to InnoDB settings.

This can help with the readability of the templates as well as limiting the impact to breaking changes.

You are basically breaking the monolithic config file into more manageable segments.

Pre-generated configuration files

However, using multiple configuration files still has maintenance issues.

You haven’t solved the issue of knowing what a particular server will have.

The only solution then is to use a flat file, one per server.

Unfortunately, if you have a complex environment, maintaining a flat file per server is unmanageable.

If you make a config change, you have to make it to multiple files.

And you increase the risk of making an error.

To avoid losing all benefit of automating your configuration, you might think of using a solution that pre-generates your configuration files.

This steals from the idea of a website plugin that generates static html pages and stores them in a CDN to avoid the need to generate a page on each load.

Simply run a script that generates the config file for each server and stores those files in your playbook repository.

If you need to make a change to a setting, do it upstream.

When the static files are regenerated, run your MySQL configuration playbook to deploy the change.

Conclusion

If you are using Ansible for MySQL configuration, and are struggling with maintaining your configuration template, there may be a solution.

You can break a monolithic configuration template into smaller segments.

Or you can pre-generate static configuration files per server using those same Jinja2 templates.

Whatever you do, do not manually maintain a static configuration file per server.

That would defeat the benefits of using Ansible!

What other ways do you manage your database configuration?