ansible-role-mysql-backup/tasks/main.yml

# 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 %
    # $command .= ' | sed \'s/ AUTO_INCREMENT=[0-9]*\b//\'';
    backup_command: |-
      target_path="{{item.target_dir_path}}/$(date -u +'\%Y-\%m-\%dT\%H:\%M:\%SZ').sql"; previous_path="{{item.target_dir_path}}/$(ls --sort=time --format=single-column "{{item.target_dir_path}}" | head --lines 1)";
      {%- if item.source_host is defined %}
       ssh
      {%- if item.ssh_key_path is defined %}
       -o IdentityFile='{{item.ssh_key_path}}'
      {%- endif %}
      {%- if item.ssh_user is defined %}
       -o User={{item.ssh_user}}
      {%- endif %}
       {{item.source_host}}
      {%- endif %}
       /usr/bin/mysqldump --opt --order-by-primary --skip-dump-date {{item.database}} | sed 's$),($),\n($g' >"$target_path"; if [ "$previous_path" ] && cmp --quiet "$previous_path" "$target_path"; then rm "$target_path"; else chmod 440 "$target_path"; fi
  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