For documentation about the mariadb-tzinfo-to-sql utility, see mysql_tzinfo_to_sql. This page is about running it using Ansible.

Installing or Upgrading the Package

First, we should install mariadb-tzinfo-to-sql if it is available on our system. For example, to install it on Ubuntu, we can use this task. For other systems, use the proper module and package name.

- name: Update timezone info
  tags: [ timezone-update ]
  apt:
    name: tzdata
    state: latest
    install_recommends: no
  register: timezone_info

This task installs the latest version of the tzdata, unless it is already installed and up to date. We register the timezone_info variables, so we can only run the next task if the package was installed or updated.

We also specify a timezone-update tag, so we can apply the role to only update the timezone tables.

Running the Script

The next task runs mariadb-tzinfo-to-sql.

We use the shell module to run the command. Running a command in this way is not idempotent, so we specify when: timezone_info.changed to only run it when necessary. Some warnings may be generated, so we pipe the output of mysql_tzinfo_to_sql to grep to filter warnings out.

Using Galera

If we're using we'll want to only update the timezone tables in one node, because the other nodes will replicate the changes. For our convenience, we can run this operation on the first node. If the nodes hostnames are defined in a list called cluster_hosts, we can check if the current node is the first in this way:

Content initially contributed by .

_{This page is licensed: CC BY-SA / Gnu FDL}

Ansible can be used to manage Docker container upgrades and configuration changes. Docker has native ways to do this, namely and . But sometimes there are reasons to start basic containers from an image and then manage configuration with Ansible or similar software. See .

In this page we'll discuss how to use Ansible to manage Docker containers.

How to Deploy a Container with Ansible

Ansible has modules to manage the Docker server, Docker containers, and Docker Compose. These modules are maintained by the community.

A dynamic inventory plugin for Docker exists. It retrieves the list of existing containers from Docker.

This page refers to the operations described in Installing MariaDB .deb Files. Refer to that page for a complete list and explanation of the tasks that should be performed.

Here we discuss how to automate such tasks using Ansible. For example, here we show how to install a package or how to import a GPG key; but for an updated list of the necessary packages and for the keyserver to use, you should refer to Installing MariaDB .deb Files.

Adding apt Repositories

To add a repository:

If you prefer to keep the repository information in a source list file in the Ansible repository, you can upload that file to the target hosts in this way:

Updating the Repository Cache

Both the Ansible modules and have an update_cache attribute. In ansible.builtin.apt it is set to "no" by default. Whenever a task sets it to 'yes', apt-get update is run on the target system. You have three ways to make sure that repositories are updated.

The first is to use ansible.builtin.apt_repository to add the desired repository, as shown above. So you only need to worry about updating repositories if you use the file method.

The second is to make sure that update_cache is set to 'yes' when you install a repository:

But if you run certain tasks conditionally, this option may not be very convenient. So the third option is to update the repository cache explicitly as a separate task:

Importing MariaDB GPG Key

To for MariaDB we can use the Ansible module. For example:

Installing Packages

To install Deb packages into a system:

To make sure that a specific version is installed, performing an upgrade or a downgrade if necessary:

To install a package or upgrade it to the latest version, use: state: latest.

To install multiple packages at once:

If all your servers run on the same system, you will always use ansible.builtin.apt and the names and versions of the packages will be the same for all servers. But suppose you have some servers running systems from the Debian family, and others running systems from the Red Hat family. In this case, you may find convenient to use two different task files for two different types of systems. To include the proper file for the target host's system:

The variables you can use to run the tasks related to the proper system are:

There is also a system-independent , but if the package names depend on the target system using it may be of very little benefit.

See Also

Content initially contributed by .

_{This page is licensed: CC BY-SA / Gnu FDL}

If we manage several remote servers, running commands on them manually can be frustrating and time consuming. Ansible allows one to run commands on a whole group of servers.

This page shows some examples of ansible-playbook invocations. We'll see how to deploy roles or parts of them to remote servers. Then we'll see how to run commands on remote hosts, and possibly to get information from them. Make sure to read Ansible Overview first, to understand Ansible general concepts.

Pinging Remote Servers

Let's start with the simplest example: we just want our local Ansible to ping remote servers to see if they are reachable. Here's how to do it:

Before proceeding with more useful examples, let's discuss this syntax.

• ansible is the executable we can call to run a command from remote servers.

• -i production-mariadb means that the servers must be read from an inventory called production-mariadb.

• all means that the command must be executed against all servers from the above inventory.

• -m ping specifies that we want to run the ping module. This is not the ping Linux command. It tells us if Ansible is able to connect a remote server and run a simple commands on them.

To run ping on a specific group or host, we can just replace "all" with a group name or host name from the inventory:

Running Commands on Remote Servers

The previous examples show how to run an Ansible module on remote servers. But it's also possible to run custom commands over SSH. Here's how:

This command shows the value of $PATH on all servers in the inventory "production-mariadb".

We can also run commands as root by adding the -b (or --become) option:

Applying Roles to Remote Servers

We saw how to run commands on remote hosts. Applying roles to remote hosts is not much harder, we just need to add some information. An example:

Let's see what changed:

• ansible-playbook is the executable file that we need to call to apply playbooks and roles.

• production-mariadb.yml is the play that associates the servers listed in the inventory to their roles.

If we call ansible-playbook with no additional arguments, we will apply all applicable roles to all the servers mentioned in the play.

To only apply roles to certain servers, we can use the -l parameter to specify a group, an individual host, or a pattern:

We can also apply tasks from roles selectively. Tasks may optionally have tags, and each tag corresponds to an operation that we may want to run on our remote hosts. For example, a "mariadb" role could have the "timezone-update" tag, to update the contents of the . To only apply the tasks with the "timezone-update" tag, we can use this command:

Using tags is especially useful for database servers. While most of the technologies typically managed by Ansible are stateless (web servers, load balancers, etc.) database servers are not. We must pay special attention not to run tasks that could cause a database server outage, for example destroying its data directory or restarting the service when it is not necessary.

Check mode

We should always test our playbooks and roles on test servers before applying them to production. However, if test servers and production servers are not exactly in the same state (which means, some facts may differ) it is still possible that applying roles will fail. If it fails in the initial stage, Ansible will not touch the remote hosts at all. But there are cases where Ansible could successfully apply some tasks, and fail to apply another task. After the first failure, ansible-playbook will show errors and exit. But this could leave a host in an inconsistent state.

Ansible has a check mode that is meant to greatly reduce the chances of a failure. When run in check mode, ansible-playbook will read the inventory, the play and roles; it will figure out which tasks need to be applied; then it will connect to target hosts, read facts, and value all the relevant variables. If all these steps succeed, it is unlikely that running ansible-playbook without check mode will fail.

To run ansible-playbook in check mode, just add the --check (or -C) parameter.

References

Further documentation can be found in the Ansible website:

• tool.

• tool.

• .

Content initially contributed by .

_{This page is licensed: CC BY-SA / Gnu FDL}

An Ansible role often runs commands that require certain privileges, so it must perform some forms of login, using passwords or key pairs. In the context of database automation, we normally talk about: SSH access, sudo access, and access to MariaDB. If we write these secrets (passwords or private keys) in clear text in an Ansible repository, anyone who has access to the repository can access them, and this is not what we want.

Let's see how we can manage secrets.

The SSH Password or Keys

Most of the times, Ansible connects to the target hosts via SSH. It is common to use the system username and the SSH keys installed in /.ssh, which is the SSH clients default. In this case, nothing has to be done on the clients to be able to allow Ansible to use SSH, as long as they are already able to connect to the target hosts.

Ansible is a tool to automate servers configuration management. It is produced by Red Hat and it is open source software released under the terms of the GNU GPL.

It is entirely possible to use Ansible to automate MariaDB deployments and configuration. This page contains generic information for MariaDB users who want to learn, or evaluate, Ansible.

For information about how to install Ansible, see Installing Ansible in Ansible documentation.

Automation Hubs

Normally, Ansible can run from any computer that has access to the target hosts to be automated. It is not uncommon that all members of a team has Ansible installed on their own laptop, and use it to deploy.

Red Hat offers a commercial version of Ansible called Ansible Tower. It consists of a REST API and a web-based interface that work as a hub that handles all normal Ansible operations.

An alternative is . AWX is the open source upstream project from which many Ansible Tower features are originally developed. AWX is released under the terms of the Apache License 2.0. However, Red Hat does not recommend to run AWX in production.

AWX development is fast. It has several features that may or may not end up in Ansible Tower. Ansible Tower is more focused on making AWS features more robust, providing a stable tool to automate production environments.

Design Principles

Ansible allows us to write playbooks that describe how our servers should be configured. Playbooks are lists of tasks.

Tasks are usually declarative. You don't explain how to do something, you declare what should be done.

Playbooks are idempotent. When you apply a playbook, tasks are only run if necessary.

Here is a task example:

"Install Perl" is just a description that will appear on screen when the task is applied. Then we use the package module to declare that a package called "perl" should be installed. When we apply the playbook, if Perl is already installed nothing happens. Otherwise, Ansible installs it.

When we apply a playbook, the last information that appears on the screen is a recap like the following:

This means that six tasks were already applied (so no action was taken), and two tasks were applied.

As the above example shows, Ansible playbooks are written in YAML.

Modules (like package) can be written in any language, as long as they are able to process a JSON input and produce a JSON output. However the Ansible community prefers to write them in Python, which is the language Ansible is written in.

Concepts

A piece of Ansible code that can be applied to a server is called a playbook.

A task is the smallest brick of code in a playbook. The name is a bit misleading, though, because an Ansible task should not be seen as "something to do". Instead, it is a minimal description of a component of a server. In the example above, we can see a task.

A task uses a single module, which is an interface that Ansible uses to interact with a specific system component. In the example, the module is "package".

A task also has attributes, that describe what should be done with that module, and how. In the example above, "name" and "state" are both attributes. The state attribute exists for every module, by convention (though there may be exceptions). Typically, it has at least the "present" and "absent" state, to indicate if an object should exist or not.

Other important code concepts are:

• An inventory determines which hosts Ansible should be able to deploy. Each host may belong to one or more groups. Groups may have children, forming a hierarchy. This is useful because it allows us to deploy on a group, or to assign variables to a group.

• A role describes the state that a host, or group of hosts, should reach after a deploy.

• A play associates hosts or groups to their roles. Each role/group can have more than one role.

• A role is a playbook that describes how certain servers should be configured, based on the logical role they have in the infrastructure. Servers can have multiple roles, for example the same server could have both the "mariadb" and the "mydumper" role, meaning that they run MariaDB and they have mydumper installed (as shown later).

Example

Let's describe a hypothetical infrastructure to find out how these concepts can apply to MariaDB.

The inventory could define the following groups:

• "db-main" for the cluster used by our website. All nodes belong to this group.

• "db-analytics" for our replicas used by data analysts.

• "dump" for one or more servers that take dumps from the replicas.

• "proxysql" for one or more hosts that run ProxySQL.

Then we'll need the following nodes:

• mariadb-node for the nodes in db-main. This role describes how to setup nodes of a cluster using Galera.

• mariadb-replica for the members of db-analytics. It describes a running replica, and it includes the tasks that are necessary to provision the node if the data directory is empty when the playbook is applied. The hostname of the primary server is defined in a variable.

• mariadb

Architecture

Ansible architecture is extremely simple. Ansible can run on any host. To apply playbooks, it connects to the target hosts and runs system commands. By default the connection happens via ssh, though it is possible to develop connection plugins to use different methods. Applying playbooks locally without establishing a connection is also possible.

Modules can be written in any language, though Python is the most common choice in the Ansible community. Modules receive JSON "requests" and facts from Ansible core, they are supposed to run useful commands on a target host, and then they should return information in JSON. Their output informs Ansible whether something has changed on the remote server and if the operations succeeded.

Ansible is not centralized. It can run on any host, and it is common for a team to run it from several laptops. However, to simplify things and improve security, it may be desirable to run it from a dedicated host. Users will connect to that host, and apply Ansible playbooks.

Ansible Resources and References

Further information about the concepts discussed in this page can be found in Ansible documentation:

• .

• .

Content initially contributed by .

_{This page is licensed: CC BY-SA / Gnu FDL}

This page contains links to Ansible modules and roles that can be used to automate MariaDB deployment and configuration. The list is not meant to be exhaustive. Use it as a starting point, but then please do your own research.

Modules

At the time of writing, there are no MariaDB-specific modules in Ansible Galaxy. MySQL modules can be used. Trying to use MySQL-specific features may result in errors or unexpected behavior. However, the same applies when trying to use a feature not supported by the MySQL version in use.

Currently, the MySQL collection in Ansible Galaxy contains at least the following modules:

• : manages MySQL databases.

• : gathers information about a MySQL server.

• : runs SQL queries against MySQL.

• : configures and operates asynchronous replication.

• : creates, modifies and deletes MySQL users.

• : manages MySQL configuration.

Note that some modules only exist as shortcuts, and it is possible to use mysql_query instead. However, it is important to notice that mysql_query is not idempotent. Ansible does not understand MySQL queries, therefore it cannot check whether a query needs to be run or not.

To install this collection locally:

MariaDB Corporation maintains a on GitHub.

Other Useful Modules

Let's see some other modules that are useful to manage MariaDB servers.

shell and command

Modules like and allow one to run system commands.

To deploy on Windows, and can be used.

Among other things, it is possible to use one of these modules to run MariaDB queries:

The main disadvantage with these modules is that they are not idempotent, because they're meant to run arbitrary system commands that Ansible can't understand. They are still useful in a variety of cases:

• To run queries, because mysql_query is also not idempotent.

• In cases when other modules do not allow us to use the exact arguments we need to use, we can achieve our goals by writing shell commands ourselves.

• To run custom scripts that implement non-trivial logic. Implementing complex logic in Ansible tasks is possible, but it can be tricky and inefficient.

• To call . There may be specific roles for some of the most common tools, but most of the times using them is an unnecessary complication.

copy and template

An important part of configuration management is copying to remote servers.

The allows us to copy files to target hosts. This is convenient for static files that we want to copy exactly as they are. An example task:

As you can see, the local name and the name on remote host don't need to match. This is convenient, because it makes it easy to use different configuration files with different servers. By default, files to copy are located in a files subdirectory in the role.

However, typically the content of a configuration file should vary based on the target host, the group and various variables. To do this, we can use the module, which compiles and copies templates written in .

A simple template task:

Again, the local and the remote names don't have to match. By default, Jinja templates are located in a templates subdirectory in the role, and by convention they have the .j2 extension. This is because Ansible uses Jinja version 2 for templating, at the time writing.

A simple template example:

Other Common Modules

The following modules are also often used for database servers:

• , or . Package is package manager-agnostic. Use them to install, uninstall and upgrade packages.

• , useful to create the system user and group that run MariaDB binary.

• can be used to make sure that MariaDB directories (like the data directory) exist and have proper permissions. It can also be used to upload static files.

• allows to create configuration files (like

Roles

Specific roles exist for MariaDB in Ansible Galaxy. Using them for MariaDB is generally preferable, to be sure to avoid and to probably be able to use some MariaDB specific . However, using MySQL or Percona Server roles is also possible. This probably makes sense for users who also administer MySQL and Percona Server instances.

To find roles that suits you, check . Most roles are also available on GitHub.

You can also search roles using the tool:

See Also

• (video)

Content initially contributed by .

_{This page is licensed: CC BY-SA / Gnu FDL}