Although earlier in the book, we installed the native mariadb-server package that ships with CentOS 7, most enterprises that need a MariaDB server would choose to standardize on a specific release directly from MariaDB. This is often more up to date than the version shipped with a given Linux release, and hence provides newer features and, sometimes, performance improvements. In addition, standardizing on a release directly from MariaDB ensures consistency of your platform, a principle we have kept to throughout this book.

Let's take a simple example—suppose you are running your infrastructure on Red Hat Enterprise Linux (RHEL) 7. This ships with MariaDB version 5.5.64. Now, suppose you want to standardize your infrastructure on the newly released RHEL 8—if you are relying on the packages supplied by Red Hat, this suddenly moves you to version 10.3.11 of MariaDB, meaning not only an upgrade to your Linux infrastructure but also to your databases, too.

Instead, it would be better to standardize upfront on a release directly from MariaDB itself. At the time of writing, the latest stable release of MariaDB is 10.4—but let us suppose that you have standardized on the 10.3 release, as it is known, and tested successfully in your environment.

The installation process is quite straightforward and is well documented on the MariaDB website—see https://mariadb.com/kb/en/library/yum/ for CentOS- and Red Hat-specific examples. However, this details the manual installation process, and we wish to automate this with Ansible. Let's now build this into a real, working Ansible example.

1. First of all, we can see from the installation documentation that we need to create a .repo file, to tell yum where to download the packages from. We can use a template to provide this, such that the MariaDB version can be defined by a variable and thus changed in the future when migration to version 10.4 (or indeed, any other future version) is deemed necessary.

Thus, our template file, defined in roles/installmariadb/templates/mariadb.repo.j2, would look like this:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/{{ mariadb_version }}/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. Once we have created this, we should also create a default for this variable, to prevent any issues or errors if it is not specified when the role is run—this will be defined in roles/installmariadb/defaults/main.yml. Ordinarily, this variable would be provided in the inventory file for a given server or group of servers, or by one of the many other supported methods in Ansible, but the defaults file provides a catch-all, in case it gets overlooked. Run the following code:

---
mariadb_version: "10.3"

3. With this defined, we can now begin to build up the tasks in our role in roles/installmariadb/tasks/main.yml, as follows:

---
- name: Populate MariaDB yum template on target host
  template:
    src: templates/mariadb.repo.j2
    dest: /etc/yum.repos.d/mariadb.repo
    owner: root
    group: root
    mode: '0644'

This will ensure that the correct repository file is written to the server, and if it is ever incorrectly modified, restored to its original, desired state.

4. Next, we should clean out the yum cache—this is especially important when upgrading MariaDB to a new version, as package names will be the same, and cached information could cause issues with the installation. At present, cleaning the yum cache is achieved using the shell module, to run the yum clean all command. However, as this is a shell command, it will always run, and this could be considered inefficient—especially as this command being run would result in any future package operations needing to update the yum cache again, even if we didn't modify the MariaDB repository definition. Thus, we want to run it only when the template module task results in a changed state.

To do this, we must first add this line to our template task, to store the results of the task:

  register: mariadbtemplate

5. Now, when we define our shell command, we can tell Ansible to only run it if the template task resulted in a changed state, as follows:

- name: Clean out yum cache only if template was changed
  shell: "yum clean all"
  when: mariadbtemplate.changed

6. With our cache appropriately cleared out, we can then install the required MariaDB packages—the list used in the task shown in the following code block is taken from the MariaDB documentation referenced earlier in this section, but you should tailor it to your exact requirements:

- name: Install MariaDB packages
  yum:
    name:
      - MariaDB-server
      - galera
      - MariaDB-client
      - MariaDB-shared
      - MariaDB-backup
      - MariaDB-common
    state: latest

The use of state: latest ensures that we always install the latest packages from the repository file created by our template task. Thus, this role can be used equally for initial installation and upgrade to the latest version. However, if you do not want this behavior, change this statement to state: present—this simply ensures that the packages listed are installed on our target host. If they are, it does not update them to the latest version—it simply returns an ok status and proceeds to the next task, even if updates are available.

7. With the packages installed, we must ensure that the server service is then started at boot time. We would probably also want to start it now so that we can perform any initial configuration work on it. Thus, we will add a final task to our installmariadb role that looks like this:

- name: Ensure mariadb-server service starts on boot and is started now
  service:
    name: mariadb
    state: started
    enabled: yes

8. Also, we know that CentOS 7 has a firewall enabled by default—as such, we must change the firewall rules to ensure that our newly installed MariaDB server can be accessed. The task to perform this would look something like this:

- name: Open firewall port for MariaDB server
  firewalld:
    service: mysql
    permanent: yes
    state: enabled
    immediate: yes

9. Let's now run this role and see it in action—the output should look something like this:

The output has been truncated to conserve space, but clearly shows the installation in progress. Note that the warning can safely be ignored—the Ansible engine has detected our yum clean all command and is helpfully advising us to use the yum module—however, the yum module in this instance does not provide the function we need, and hence, we used the shell module instead.

With the database installed and running, we have the following three high-level tasks to perform next:

• Update the MariaDB configuration.
• Secure the MariaDB installation.
• Load initial data (or schemas) into the database.

Of these tasks, we explored, in detail, methods to use the Ansible template module effectively to manage the MariaDB configuration in Chapter 7, Configuration Management with Ansible (see the Making scalable dynamic configuration changes section). As such, we will not go into detail on this here—however, check the configuration file structure for your chosen version of MariaDB, as it might differ from that shown in the aforementioned chapter.

Thus, assuming that you have the installation and configuration of the database server in hand, let us proceed to secure it. This, at a bare minimum, will entail changing the root password, though good practice states that you should also remove remote root access, the test database, and the anonymous user accounts that come with a default MariaDB installation.

To separate out these tasks from the installation, we'll create a new role called securemariadb. Before we can define the tasks, we must define a variable to contain the root password for the MariaDB installation. Note that normally, you would provide this in a more secure manner—perhaps through an Ansible Vault file, or using some of the advanced features in AWX or Ansible Tower. For simplicity, in this example, we will define a variables file in the role (in roles/securemariadb/vars/main.yml), as follows:

---
mariadb_root_password: "securepw"

Now, let's build up the tasks for the role. Ansible includes a few native modules for use in database management, and we can make use of these here, to make the required changes to our MariaDB database.

Knowing this, the first step in building up our role is to install the prerequisite Python package, as follows:

---
- name: Install the MariaDB Python module required by Ansible
  yum:
    name: MySQL-python
    state: latest

Our most immediate task, once this is installed, is to set the password on the local root account, and prevent anyone from logging in without authentication. Run the following code:

- name: Set the local root password
  mysql_user:
    user: root
    password: "{{ mariadb_root_password }}"
    host: "localhost"

So far, this is a textbook example of how to use the mysql_user module—however, there is a twist in our usage from here. The preceding example takes advantage of the fact that no root password is set—it is implicitly manipulating the database as root, by virtue of the fact that we will put become: yes in our site.yml file, and thus, the playbook will be run as root. At the time that this task is run, the root user has no password, and so, the above task will run satisfactorily.

The answer to this is to add the login_user and login_password parameters to the module for all future tasks, to ensure that we have authenticated successfully with the database to perform the required tasks.

With this condition understood, we now add another task to the role, to remove the remote root accounts, as follows:

- name: Delete root MariaDB user for remote logins
  mysql_user:
    user: root
    host: "{{ ansible_fqdn }}"
    state: absent
    login_user: root
    login_password: "{{ mariadb_root_password }}"

Again, this code is quite self-explanatory—however, note here too that running this task a second time will also yield an error, this time because on the second run, these privileges will not exist because we deleted them on the first run. Thus, this is almost certainly a role to run once only—or where careful consideration must be applied to the code and the error handling logic.

We now add a task to delete the anonymous user accounts, as follows:

- name: Delete anonymous MariaDB user
  mysql_user:
    user: ""
    host: "{{ item }}"
    state: absent
    login_user: root
    login_password: "{{ mariadb_root_password }}"
  loop:
    - "{{ ansible_fqdn }}"
    - localhost

You will see the use of a loop here—this is used to remove both the local and remote privileges within a single task. Finally, we remove the test database, which is redundant in most enterprise scenarios, by running the following code:

- name: Delete the test database
  mysql_db:
    db: test
    state: absent
    login_user: root
    login_password: "{{ mariadb_root_password }}"

With the role fully complete, we can run it in the usual manner, and secure our newly installed database. The output should look something like this:

With these two roles and some input from Chapter 7, Configuration Management with Ansible, we have successfully installed, configured, and secured a MariaDB database on CentOS. This is, obviously, a very specific example—however, if you were to perform this on Ubuntu, the process would be very similar. The differences would be the following:

• The apt module would be used in place of the yum module in all tasks.
• Package names would have to be changed for Ubuntu.
• Defining the repository source would be performed under /etc/apt rather than /etc/yum.repos.d, with the file format adjusted accordingly.
• Configuration paths may be different for MariaDB on Ubuntu.
• Ubuntu normally uses ufw instead of firewalld—by default, you might find that ufw is disabled, so, this step could be skipped.

With these changes taken into account, the preceding process can be very quickly adapted for Ubuntu (or, indeed, any other platform, provided the appropriate changes are made). Once the packages are installed and configured, as the modules such as mysql_user and mysql_db are cross-platform, they will work equally well on all supported platforms.

So far in this book, we have focused very heavily on MariaDB—this is not because of any inherent bias toward this database, nor indeed should it be inferred as any recommendation. It has simply been chosen as a relevant example and built upon throughout the text. Before we proceed to look at the process of loading data or schemes into a newly installed database, we will take a brief look in the next section at how to apply the processes we have learned so far to another popular Linux database—PostgreSQL.