# create user 'mysql-backup'@localhost identified with auth_socket;
# grant select, lock tables, show view on *.* to 'mysql-backup'@localhost;

- name: generate commands
  set_fact:
    # escape %
    backup_command: |-
      ssh -i {{item.ssh_key_path}} {{item.ssh_user}}@{{item.source_host}} /usr/bin/mysqldump --opt --order-by-primary --skip-dump-date {{item.database}} | sed 's$),($),\n($g' >"{{item.target_dir_path}}/$(date -u +'\%Y-\%m-\%dT\%H:\%M:\%SZ').sql"
  with_items: '{{mysql_backups}}'
  register: mysql_backup_commands
- name: setup cron jobs
  blockinfile:
    dest: '/etc/crontab'
    marker: '# ANSIBLE MYSQL BACKUPS {mark}'
    block: |-
      {% for backup in mysql_backup_commands.results %}
      {% for backup_job in backup.item.cron_jobs %}
      {{backup_job.minute}} {{backup_job.hour}} * * * {{backup.item.local_user}} {{backup.ansible_facts.backup_command}}
      {% endfor %}
      {% endfor %}
  become: yes