Now that our MariaDB installation is complete, we should do something with it. There are a few sample databases available on GitHub. Let's look at importing the employee database provided by datacharmer. We will be using a slightly altered version of the SQL dumps, but more on that later in the section.

We are going to be using nested variables for this part of the playbook; these can be found in mariadb/defaults/main.yml:

mariadb_sample_database:
  create_database: true
  source_url: "https://github.com/russmckendrick/test_db/archive/master.zip"
  path: "/tmp/test_db-master"
  db_name: "employees"
  db_user: "employees"
  db_password: "employees"
  dump_files:
    - "employees.sql"
    - "load_departments.dump"
    - "load_employees.dump"
    - "load_dept_emp.dump"
    - "load_dept_manager.dump"
    - "load_titles.dump"
    - "load_salaries1.dump"
    - "load_salaries2.dump"
    - "load_salaries3.dump"
    - "show_elapsed.sql"

When we call these variables, they will need to be prefixed with mariadb_sample_database. For example, whenever we need to use the db_name variable, we will need to use mariadb_sample_database.db_name. As when we copied the HTML file in the previous section, we will be adding a condition using when to each of the tasks, which will mean they can be skipped if required.

The first thing we will need to do is download a copy of the dump files from GitHub and uncompress them. To do this, we will be using the unarchive module:

- name: download and unarchive the sample database data
  unarchive:
    src: "{{ mariadb_sample_database.source_url }}"
    dest: "/tmp"
    remote_src: "yes"
  when: mariadb_sample_database.create_database == true

We are grabbing the file from a remote location, which is the URL mariadb_sample_database.source_url, and uncompressing it in /tmp. As we have set remote_src to yes, Ansible knows it has to download the file from a remote source. If we didn't provide a full URL, it would have attempted to have copied the file from our control host.

The next two tasks use the mysql_db and mysql_user modules to create the database and a user who has access to it:

- name: create the sample database
  mysql_db:
    db: "{{ mariadb_sample_database.db_name }}" 
    state: "present"
  when: mariadb_sample_database.create_database == true

- name: create the user for the sample database
  mysql_user:
    name: "{{ mariadb_sample_database.db_user }}"
    password: "{{ mariadb_sample_database.db_password }}"
    priv: "{{ mariadb_sample_database.db_name }}.*:ALL"
    state: "present"
  with_items: "{{ mariadb_hosts }}"
  when: mariadb_sample_database.create_database == true

The final part of the playbook imports the MySQL dumps into the database; however, before we import the files, we should first check if the dumps have already been imported. If we do not perform this check every time we run the playbook, the dumps will be imported. To check whether the data has been imported already, we are going to use the stat module; this checks the presence of a file and gathers information on it. 

If we have already imported the data, there will be a file called employees.frm in the /var/lib/mysql/employees folder, so let's check for that:

- name: check to see if we need to import the sample database dumps
  stat:
    path: "/var/lib/mysql/{{ mariadb_sample_database.db_name }}/{{ mariadb_sample_database.db_name }}.frm"
  register: db_imported
  when: mariadb_sample_database.create_database == true

Now we know whether we need to import the database dumps or not, we can proceed with the final task, which imports the database dumps listed in mariadb_sample_database.dump_files if the following conditions are met:

• Is the variable db_imported defined? If not, then we are skipping importing the sample database and should skip this task.
• Does db_imported.stat.exists equal false? If so, then the file does not exist, and we should import the data.

The task itself uses the mysql_db module to import the data:

- name: import the sample database
  mysql_db:
    name: "{{ mariadb_sample_database.db_name }}"
    state: "import"
    target: "{{ mariadb_sample_database.path }}/{{ item }}"
  with_items: "{{ mariadb_sample_database.dump_files }}"
  when: db_imported is defined and db_imported.stat.exists == false

That completes the import of the sample database into our MariaDB installation; let's now run the playbook and call the role.