Schedule Meeting


Creating dynamic configuration files with Ansible

by | Dec 7, 2020 | Database Automation

Need Help?  Click Here for Expert Support

One of the first problems one finds with automation is building custom configuration files. Don’t worry though: it’s a simple problem to solve.

In this article we discuss how to use Ansible templates to generate proper my.cnf files for each MariaDB host.

Mont St Michael, France, surrounded by the sea
Mont St Michael, France

The template module

Ansible has a copy module to copy files to remote servers. It could allow us to upload configuration files. But suppose that each Apache server we manage requires slightly different configuration. Do we want to maintain a separate file for each server? Obviously not.

Instead, we want to use a template. And Ansible has a template module that we can use for this purpose. This module can read a template, generate a file for each server based on existing variables, and then upload it to the specified place.

A simple template

Here’s an example:

- name: Copy my.cnf
  tags: [ mariadb, mariadb-configuration ]
    owner: mysql
    group: mysql
    mode: '644'

In a medium/big deployment there would probably be several files, and a variable would determine which one we are using. But let’s keep things simple here. The key point is that we have a template (my.cnf.j2) and we are copying to the remote server as my.cnf. By convention templates are located in a templates directory in the role directory, with the .j2 extension, because they use Jinja 2. Attributes like owner, group and mode work in the same way as file module attributes, and anyway they are intuitive.

Now, a Jinja example template:


user        = mysql
basedir     = {{ mysql_base_dir }}
datadir     = {{ mysql_data_dir }}
tmpdir      = {{ mysql_tmp_dir }}

server_id = {{ mysql_server_id }}


log_bin = 1
max_binlog_size = 1073741824
expire_logs_days = 7

You can see the placeholders for variables. Which variables? Normal Ansible variables that you can define in group_vars and host_vars, or in the role itself.

Reduce template verbosity

The template includes a line for each variable we want to set. For the purpose of this article, I checked a random MariaDB 10.5 instance (without checking which plugins are enabled) and it has 665 variables. While we’ll never set even 1/10 of them, I hope that we all agree that we don’t want to have one line for each value we set.

An alternative is to declare a list, and put in it the variables we want to set. Each element of the list will be a dictionary with the variable name and its value. In the group variables, we’ll do this:

  - { name: 'innodb_buffer_pool_size', value: '50G' }
  - ...

Then we’ll loop over this list with the Jinja for construct:

# "constant" values:
user = mysql

# if we like, we can explicitly include mandatory variables
basedir     = {{ mysql_base_dir }}
datadir     = {{ mysql_data_dir }}
tmpdir      = {{ mysql_tmp_dir }}

# per group additional variables
{% for var in mysql_group_variables %}
{{ }} = {{ var.value }}
{% endfor %}


Sometimes a host may need to be able to have different values from the rest of its group. So we need a second list, defined in host_vars. We can call it mysql_host_variables. While empty lists are supported, we don’t want to define this list for hosts that don’t need it. So it is more convenient to be sure that Ansible won’t fail if it doesn’t exist. So we’ll add the following at the end of the configuration file:

# per host additional variables
{% if mysql_host_variables is defined %}
{% for var in mysql_host_variables %}
{{ }} = {{ var.value }}
{% endfor %}
{% endif %}

As you can see, Jinja supports if and the is defined Ansible construct.

You may have noticed that, if a variable is present in both the group and the host, it will be written twice in the file. For MariaDB and MySQL this is not a problem: the last occurrence of a variable overrides the previous ones. For other types of log files you may want to check that a variable is not present in mysql_host_variables before printing it from mysql_group_variables:

if not var in mysql_host_variables|map(attribute="name") 

Similarly, we can include portions of a configuration file only if certain conditions are met:

{% if mysql_binlog sameas true %}
log_bin = 1
max_binlog_size = 1073741824
expire_logs_days = 7
{% endif %}

sameas true can usually be omitted. It is a fancy syntax to allows us to use non-boolean values are boolean.


We discussed how to generate correct configuration files for each host based on some variables, using MariaDB configuration file as an example. We saw how to avoid specifying every variable in the template. We saw how to conditionally include some lines.

Federico Razzoli

Did you like this article?

Photo credit

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

How to fix git mistakes

How to fix git mistakes

Code or configuration versioning comes with a potential problem: fixing mistakes can be tricky. Here are some solutions for the most common problems.


Need Help?  Click Here for Expert Support


Submit a Comment

Your email address will not be published. Required fields are marked *