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_db_and_users.yml -e@./db.yml
#
# If running ansible from a python virtualenv you will need a command like the following
#
# ansible-playbook -c local -i 'localhost,' create_db_and_users.yml -e@./db.yml -e "ansible_python_interpreter=$(which python)"
#
# if you get an SSL error connecting to MySQL, make sure you have the following in ~/.my.cnf
# 
# [client]
# ssl_mode=DISABLED
# 
# the content of db.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:
        - mysqlclient

    - 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 }}"
        state: "{{ item.state | default('present') }}"
        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
      when: item.mysql_plugin is not defined
      with_items: "{{ database_users }}"
      tags:
        - users

    # If plugin is AWSAuthenticationPlugin, then we can’t create mysql user using the mysql_user module
    # to create user for AWS RDS IAM authentication as it does not support plugin as parameter

    - name: create mysql users for AWS RDS IAM authentication
      shell: |
         mysql -u"{{ item.login_user }}" -p"{{ item.login_password }}" -h"{{ item.login_host }}" -e "SET @sql := CASE WHEN (SELECT count(*) FROM mysql.user WHERE User='{{ item.name }}') = 0 THEN 'CREATE USER {{ item.name }} IDENTIFIED WITH AWSAuthenticationPlugin as \'RDS\'' ELSE 'Select 0' END;PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt"
      when: item.mysql_plugin is defined and item.state == 'present' and item.mysql_plugin == 'AWSAuthenticationPlugin'
      with_items: "{{ database_users }}"
      tags:
        - users

    - name: assign privileges to AWS RDS IAM users
      shell: |
         mysql -u"{{ item.login_user }}" -p"{{ item.login_password }}" -h"{{ item.login_host }}" -e "GRANT {{ item.privileges }} to '{{ item.name }}'@'{{ item.host }}'"
      when: item.mysql_plugin is defined and item.state == 'present' and item.mysql_plugin == 'AWSAuthenticationPlugin'
      with_items: "{{ database_users }}"
      tags:
        - users

    # https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_configuration.html
    - name: Set binlog retention length
      shell: |
         mysql -u"{{ database_connection.login_user }}" -p"{{ database_connection.login_password }}" -h"{{ database_connection.login_host }}" -e "call mysql.rds_set_configuration('binlog retention hours', {{RDS_BINLOG_RETENTION_HOURS | default(168)}});"
      when: RDS_BINLOG_RETENTION_HOURS is defined
      tags:
        - users

    - name: Ensure Datadog user exists
      mysql_user:
        name: "{{ datadog_user }}"
        host: '%'
        password: "{{ datadog_user_password }}"
        priv: "*.*:REPLICATION CLIENT,PROCESS"
        append_privs: yes
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false) 

    - name: Set max connections for Datadog user
      mysql_query:
        query: "ALTER USER '{{ datadog_user }}'@'%' WITH MAX_USER_CONNECTIONS {{ datadog_max_connections }};"
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)

    - name: Grant SELECT on performance_schema to Datadog user
      mysql_user:
        name: "{{ datadog_user }}"
        host: '%'
        priv: "performance_schema.*:SELECT"
        append_privs: yes
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)

    - name: Create Datadog schema if it does not exist
      mysql_db:
        name: "{{ datadog_schema }}"
        state: present
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)
 
    - name: Grant EXECUTE on Datadog schema to Datadog user
      mysql_user:
        name: "{{ datadog_user }}"
        host: '%'
        priv: "{{ datadog_schema }}.*:EXECUTE,CREATE TEMPORARY TABLES"
        append_privs: yes
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)

    - name: Create the explain_statement procedure in datadog schema
      mysql_query:
        query: |
         CREATE PROCEDURE {{ datadog_schema }}.explain_statement(IN query TEXT)
             SQL SECURITY DEFINER
         BEGIN
             SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
             PREPARE stmt FROM @explain;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
         END
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)
      ignore_errors: yes

    - name: Grant EXECUTE on explain_statement procedure to Datadog user
      mysql_query:
        query: "GRANT EXECUTE ON PROCEDURE {{ datadog_procedure_schema }}.explain_statement TO {{ datadog_user }}@'%';"
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)

    - name: Create Runtime setup consumer for datadog
      mysql_query:
        query: |
         CREATE PROCEDURE {{ datadog_schema }}.enable_events_statements_consumers()
             SQL SECURITY DEFINER
         BEGIN
             UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
             UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';
         END
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)
      ignore_errors: yes

    - name: Grant EXECUTE on enable_events_statements_consumers procedure to Datadog user
      mysql_query:
        query: "GRANT EXECUTE ON PROCEDURE {{ datadog_procedure_schema }}.enable_events_statements_consumers TO {{ datadog_user }}@'%';"
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      when: datadog_mysql_monitoring_enabled | default(false)

    - name: Grant EXECUTE on explain_statement procedure in database {{ item }} to Datadog user
      mysql_query:
        query: |
         CREATE PROCEDURE {{ item }}.explain_statement(IN query TEXT)
             SQL SECURITY DEFINER
         BEGIN
             SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
             PREPARE stmt FROM @explain;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
         END
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      with_items: "{{ datadog_monitored_dbs }}" 
      when: datadog_mysql_monitoring_enabled | default(false)
      ignore_errors: yes

    - name: Grant EXECUTE on explain_statement procedure in database {{ item }} to Datadog user
      mysql_query:
        query: "GRANT EXECUTE ON PROCEDURE {{ item }}.explain_statement TO {{ datadog_user }}@'%';"
        login_host: "{{ database_connection.login_host }}"
        login_user: "{{ database_connection.login_user }}"
        login_password: "{{ database_connection.login_password }}"
      with_items: "{{ datadog_monitored_dbs }}"
      when: datadog_mysql_monitoring_enabled | default(false)