Configuring MariaDB

Now that MariaDB is installed and running, we make a start on getting it configured. Our default installation of MariaDB has no root password defined, so that should be the first thing we set up. We can do this using the mysql_user module:

- name: change mysql root password
  mysql_user:
    name: "{{ mariadb_root_username }}" 
    host: "{{ item }}" 
    password: "{{ mariadb_root_password }}"
    check_implicit_admin: "yes"
    priv: "*.*:ALL,GRANT"
  with_items: "{{ mariadb_hosts }}"

As you can see, we have a few different variables in use; these are defined in roles/mariadb/defaults/main.yml as:

mariadb_root_username: "root"
mariadb_root_password: "Pa55W0rd123"
mariadb_hosts:
  - "127.0.0.1"
  - "::1"
  - "{{ ansible_nodename }}"
  - "%"
  - "localhost"

The order of the hosts in mariadb_hosts is important; if localhost is not the last host changed, then Ansible will give an error with a message about not being able to connect to MariaDB. This is because we are using the fact that MariaDB does not ship with a default root password to actually set the root password.

Now, once we have configured the root user's password, we still want to be able to connect to MySQL. I prefer to set up a ~/.my.cnf file under the root users folder. This can be done in Ansible as follows:

- name: set up .my.cnf file
  template:
    src: "my.cnf.j2"
    dest: "~/.my.cnf"

The template file can be found at lamp/roles/mariadb/templates/my.cnf.j2; it contains the following content:

# {{ ansible_managed }}
[client]
password='{{ mariadb_root_password }}'

Once in place, this means that the system root user—not to be confused with the root user we just set up within MariaDB—will have direct access to MariaDB without having to provide a password. Next up, we can delete the anonymous user, which is created by default. Again, we will use the mysql_user module for this:

- name: delete anonymous MySQL user
  mysql_user:
    user: ""
    host: "{{ item }}"
    state: "absent"
  with_items: "{{ mariadb_hosts }}"

Finally, there is a test database created. As we will be creating our own, let's remove this as well, this time using the mysql_db module:

- name: remove the MySQL test database
  mysql_db:
    db: "test" 
    state: "absent"

These configuration tasks are the equivalent of running the mysql_secure_installation command.