Ansible - How to backup all MySQL databases?

Question

I need to take a backup of all existing MySQL databases on my server with Ansible.

I'm aware of mysql_db module. It takes the names of the databases I'd like to manipulate on one by one, so I must get the list of existing databases before using that module.

Is there any way to backup all MySQL databases at once or to get a list of existing databases with Ansible?

Answer 1

A patch to adds name=all that allows a user to dump or import all data was merged into devel recently, it's not available yet in 1.9.1, but it's already shown in this part of the documentation.

# Dumps all databases to hostname.sql
- mysql_db: state=dump name=all target=/tmp/{{ inventory_hostname }}.sql

Hopefully this will soon be available in a stable release.
(Run sudo pip install ansible --upgrade to upgrade.)

Answer 2

Alternative way, each database in separate file.

---
# This playbook backups all mysql databases into separate files.

- name: backup mysql
  vars:
    - exclude_db:
      - "Database"
      - "information_schema"
      - "performance_schema"
      - "mysql"
  tasks:
    - name: get db names
      shell: 'mysql -u root -p{{ vault_root_passwd }} -e "show databases;" '
      register: dblist
    - name: backup databases
      mysql_db:
        state: dump
        name: "{{ item }}"
        target: "/tmp/{{ item }}.sql"
        login_user: root
        login_password: "{{ vault_root_passwd }}"
      with_items: "{{ dblist.stdout_lines | difference(exclude_db) }}"

Answer 3

The mysql_db module uses the mysqldump executable under the hood, which in turn provides an --all-databases switch, it's just that the Ansible module does not provide an option to use it via the module.

I would suggest using mysqldump executable via command module for now and in the meantime file a feature request on Ansible's GitHub to add support for it.

Something like this should get you going for now:

- name: Dump all MySQL databases to a single file
  command: mysqldump --opt -uroot --all-databases --result-file=/tmp/all-dbs.sql

Adjust the options to mysqldump as desired: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Update Nov 26, 2016:

A patch adding name=all was added to the mysql_db module on May 12, 2015, so the recommended way to dump all databases is:

# Dumps all databases to hostname.sql
- mysql_db: state=dump name=all target=/tmp/{{ inventory_hostname }}.sql