Ansible Role: mysql_query

This is an Ansible tool to enter values into a MySQL table or to add new records. It's useful for web applications that keep their settings in a database. For example, icingaweb2 needs an initial user to be added to the database. While the installation wizard can do this, Ansible helps automate the installation process.

You can find it on the Ansible Galaxy page as zauberpony.mysql-query.

Install

To install, use ansible-galaxy with the command ansible-galaxy install zauberpony.mysql-query, or manually place the mysql_query file in your roles_path.

Requirements:

You’ll need Python libraries for MySQL, which work similar to the core mysql_* modules:

• MySQLdb (only for Python 2.x)
• PyMySQL (for both Python 2.7 and 3.x)

Example Playbook

Example to ensure a record exists in a specific table.

---
- hosts: all
  roles:
    - zauberpony.mysql-query
  tasks:
    - name: insert a row
      mysql_query:
        name: ansible-playbook-example
        table: simple_table
        login_host: ::1
        login_user: root
        login_password: password
        identifiers:
          id: 14
          email: '[email protected]'
        values:
          role: "admin"
          department: 'IT'
        defaults:
          password: "secret"
          last_login: 1469264933

In this example, for the simple_table (which has columns: id, email, role, department, password, last_login):

• It checks for a row with id = 14 and email = 'john@example.com'
  • If the row is not found: it adds a new row with the given details.
  • If the row exists: it checks if the values (role, department) match; if not, it updates them.

So:

• identifiers are used to find a row.
• defaults are used for values when a row is being added (only for insertions).
• values represent the row's state that Ansible ensures.

Example to ensure a record does not exist in a specific table.

---
- hosts: all
  roles:
    - zauberpony.mysql-query
  tasks:
    - name: insert a row
      mysql_query:
        state: absent
        name: ansible-playbook-example
        table: simple_table
        login_host: ::1
        login_user: root
        login_password: password
        identifiers:
          id: 14
          email: '[email protected]'

Running the examples

Make sure you have a MySQL server running (you can use a docker-compose.yml file) and adjust the connection details if needed.

Run with ansible-playbook -i demo.yml or simpler ./demo.yml, and to reset, use ansible-playbook -i reset.yml.

After running ./demo.yml, you can check ansible’s check mode by running ./checkmode-demo.yml -C. Test both ./demo.yml and ./checkmode-demo.yml multiple times with and without -C to understand how it works.