create_db_and_users.yml

#
# This play will create databases and user for an application.
# It can be run like so:
#
# ansible-playbook -c local -i 'localhost,' create_dbs_and_users.yml -e@./db.yml
#
# where the content of dbs.yml contains the following dictionaries
#
# database_connection: &default_connection
#   login_host: "mysql.example.org"
#   login_user: "root"
#   login_password: "super-secure-password"

# DEFAULT_ENCODING: "utf8"

# databases:
#   reports:
#     state: "present"
#     encoding: "{{ DEFAULT_ENCODING }}"
#     <<: *default_connection
#   application:
#     state: "present"
#     encoding: "{{ DEFAULT_ENCODING }}"
#     <<: *default_connection

# database_users:
#   migrate:
#     state: "present"
#     password: "user-with-ddl-privs"
#     host: "%"
#     privileges:
#       - "reports.*:SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP,INDEX"
#       - "wwc.*:SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP,INDEX"
#     <<: *default_connection
#   runtime:
#     state: "present"
#     password: "user-with-dml-privs"
#     host: "%"
#     privileges:
#       - "reports.*:SELECT"
#       - "wwc.*:SELECT,INSERT,UPDATE,DELETE"
#     <<: *default_connection

- name: Create databases and users
  hosts: all
  gather_facts: False
  tasks:
    # Install required library, currently this needs to be available
    # to system python.
    - name: install python mysqldb module
      pip: name={{item}} state=present
      with_items:
        - MySQL-python

    - name: create mysql databases
      mysql_db: >
        db={{ item.name}}
        state={{ item.state }}
        encoding={{ item.encoding }}
        login_host={{ item.login_host }}
        login_user={{ item.login_user }}
        login_password={{ item.login_password }}
      with_items: databases
      tags:
        - dbs

    - name: create mysql users and assign privileges
      mysql_user: >
        name="{{ item.name }}"
        priv="{{ '/'.join(item.privileges) }}"
        password="{{ item.password }}"
        host={{ item.host }}
        login_host={{ item.login_host }}
        login_user={{ item.login_user }}
        login_password={{ item.login_password }}
        append_privs=yes
      with_items: database_users
      tags:
        - users