DevOps with MariaDB and Ansible, Part 2

DevOps with MariaDB and Ansible, Part 2In the first blog of these series, we’ve done a rapid walkthrough on how to use Ansible and Vagrant to start a master/slave pair. In this second post, we will delve into the inner workings of Ansible, explaining how to set up server inventories, automate MariaDB deployments, use configuration templates and much more.

Setting up an Ansible inventory

Once Ansible is setup with your usual package manager, the first task should be to create passwordless SSH keys and to setup the servers you want to manage for passwordless access. Setting up SSH keys is extensively described on the internet so I won’t get into any details in this post.

The second task is to create a server inventory. This is as simple as creating a hosts text file in /etc/ansible. The contents should be similar to the following:


Above, we have defined a databases server group by starting a new section within brackets, followed up with each server’s hostname: that’s all it takes to define an inventory. Several groups can be defined as you see fit.

Using Ansible in ad-hoc mode

One of the cool features of Ansible is that it can be used in ad-hoc mode, sending queries from the command line on multiple servers. Default Ansible module is command, which consists of passing your arguments directly to the distant shell. So for example, if I want to know the OS version on each server, I can run the following:


# ansible databases -a "cat /etc/"
db-03 | success | rc=0 >>
Ubuntu 14.04.1 LTS

db-01 | success | rc=0 >>
Ubuntu 14.04.1 LTS

db-02 | success | rc=0 >>
Ubuntu 14.04.1 LTS

Any Ansible module can be used with the command line. A complete list of modules can be found here: So for example, if I want to use the apt module to install the MariaDB client, I can do the following:

# ansible databases -m apt -u root -a "name=mariadb-client state=installed"

Note the -m option which is used to pass the module name, and the -u options used to pass the user name. In this case we have to use the root user in order to be able to run package installations. We could also use the -s option to use sudo if configured.

Ansible playbooks

If we want to automate tasks, we can use Ansible playbooks to define lists of tasks to be executed. Ansible playbooks are written using the YAML markup language. For example, we could define the following playbook to install MariaDB from the official repository:

# file: mariadb.yml
- hosts: databases
  user: root
  - name: Install MariaDB repository
    apt_repository: repo='deb trusty main' state=present
  - name: Add repository key to the system
    apt_key: id=0xcbcb082a1bb943db
  - name: Install MariaDB Server
    apt: name=mariadb-server state=latest update_cache=yes

The first two lines of the playbook define respectively the host group in the inventory that will be targeted by the playbook, and the user that will execute the commands. Then each sequence of following lines define a task with its name and the Ansible module arguments to be executed. We invoke the playbook with the ansible-playbook command:

# ansible-playbook mariadb.yml

Ansible will execute each module in turn on each host and report the output of each command and whether it has failed or succeeded.

Configuration templates with Jinja2

Ansible may use the Jinja2 templating engine to assign variables dynamically in playbooks or templates. Ansible variables may be defined in various places, such as inventory or playbooks and are also collected from the servers at runtime. For example, to get a list of all the possible variables for a given host, try the following command:

# ansible db-01 -m setup

This will return a bunch of variables related to db-01, containing informations such as ip addresses, architecture, disk layout or cpu cores which can all be reused inside templates or Ansible playbooks.

Let’s define a my.cnf template using Jinja2 by creating a copy of the standard Ubuntu my.cnf file in the working directory. Each Jinja2 variable is defined by double brackets as in the example below:

# cp /etc/mysql/my.cnf my.cnf.j2

# add under the [mysqld] section
server-id		= {{ server_id }}
report_host		= {{ ansible_hostname }}
innodb_buffer_pool_size	= {{ (ansible_memtotal_mb * 0.8) | int }}M

In order to set a server id for each database, we need to define variables somewhere. In our case, as we want this server id to be static, we could edit the hosts Ansible inventory file as such:

[databases] server_id=1 server_id=2 server_id=3

Each server_id variable will be pulled from the inventory as defined, so when we execute our template the variable will be replaced by the actual value in the inventory. We could also have defined the server id to be computed by any function, for example inet_aton.


Now for the second variable in our template, we want to use report_host in order to register our servers using the SHOW SLAVE HOSTS statement in a replication setup. Using the {{ ansible_hostname }} variable, the hostname will be pulled from the Ansible setup as described previously.

The third variable is an example on how to use mathematical functions to assign a dynamic result to the innodb_buffer_pool_size variable. We want to use 80% of available memory for the innodb buffer pool, so we use the ansible_memtotal_mb setup variable which returns the server’s total memory, multiply it by 0.8 and get an integer value as the result.

Let’s use the template module to build the final my.cnf template and deploy it on each server:

# ansible -m template -a "src=my.cnf.j2 dest=/etc/mysql/my.cnf"

We could also add the template task to our previously defined Ansible playbook in the following form:

  - name: Configure MariaDB
    template: src=my.cnf.j2 dest=/etc/mysql/my.cnf

This concludes this post on how to use Ansible with MariaDB. I encourage you to experiment with the various Ansible modules and Jinja2 template engine. In the next post, we will configure a MariaDB Galera Cluster using what we’ve learned in this article, so stay tuned for more!