How to list and backup only specific MySQL or MariaDB databases using Ansible Playbook

Written by Claudio Kuenzler - 0 comments

Published on November 16th 2023 - Listed in Ansible Linux MySQL MariaDB

---

While working on an Ansible playbook, which upgrades a specific application, I wanted to make sure that the playbook creates a backup prior to the upgrade. This not only involves a file backup, but also a backup of local MySQL databases. But I wanted to make sure to only dump specific databases, related to the application, not all databases.

Finding the application database(s)

Doing this manually in a Shell, I would first list the databases using mysqlshow:

root@app:~# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| app013             |
| app015             |
| app066             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

The databases of interest here are all starting with the string app. mysqlshow allows to use wildcards and only list these databases:

root@app:~# mysqlshow app*
Wildcard: app%
+-----------+
| Databases |
+-----------+
| app013    |
| app015    |
| app066    |
+-----------+

To create a dump of these databases in a Bash script, I would create a for loop from the mysql output (mysqlshow is not very handy for non-interactive scripts):

root@clement:~# for db in $(mysql -Bse "show databases like 'app%'"); do echo $db; echo "mysqldump $db > /backup/$db.sql"; done
app013
mysqldump app013 > /backup/app013.sql
app015
mysqldump app015 > /backup/app015.sql
app066
mysqldump app066 > /backup/app066.sql

But as mentioned at the beginning, I need/want to do this automated in an Ansible playbook.

The community.mysql module

There are two major types of Ansible modules: builtin and community. The builtin modules are already part of Ansible, if you installed Ansible using a package manager (such as apt). The community plugins can be installed using the ansible-galaxy command:

ck@ansible:~$ sudo ansible-galaxy collection install community.mysql
Starting galaxy collection install process
Process install dependency map
Starting collection install process
Installing 'community.mysql:3.8.0' to '/root/.ansible/collections/ansible_collections/community/mysql'
Downloading https://galaxy.ansible.com/api/v3/plugin/ansible/content/published/collections/artifacts/community-mysql-3.8.0.tar.gz to /root/.ansible/tmp/ansible-local-4140521agfycb2e/tmpqobxaupv
community.mysql (3.8.0) was installed successfully

Compared to the builtin mysql module, the community.mysql module offers additional tasks, which are quite helpful.

Listing MySQL databases

To list the databases, the community.mysql.mysql_info module can be used. The info (sub-) module is able to show quite a large set of information, however in this case I only want to list the databases. This can be achieved by using a filter. I am saving the output of the task in a variable "databases". In the next task I'm using the debug module to show what exactly this variable contains.

  # List databases
  - name: List databases
    community.mysql.mysql_info:
      filter:
      - databases
      return_empty_dbs: true
    register: databases

  # Debug databases variable
  - debug:
      msg: "{{ databases }}"
    ignore_errors: True

The output of a playbook run shows:

TASK [List databases] ****************************************************************************
ok: [app]

TASK [debug] *************************************************************************************
ok: [app] => {
    "msg": {
        "changed": false,
        "databases": {
            "app013": {
                "size": 0
            },
            "app015": {
                "size": 0
            },
            "app066": {
                "size": 0
            },
            "information_schema": {
                "size": 212992
            },
            "mysql": {
                "size": 2523136
            },
            "performance_schema": {
                "size": 0
            }
        },
        "failed": false
    }
}

Ansible stored the different database names under the nested variable "databases". As we registered the whole output in a "databases" variable, the nested "databases" variable therefore becomes "databases.databases" for the list of database names.

To only list the database names, we can use the Ansible loop function and iterate through the dictionary value "databases.databases":

  - name: Debug list only database names
    debug:
      msg: "Database name is {{ item.key }}"
    loop: "{{ databases.databases | dict2items }}"

The playbook run now shows a message for each database showing the database name:

TASK [Debug list only database names] ************************************************************
ok: [app] => (item={'key': 'information_schema', 'value': {'size': 212992}}) => {
    "msg": "Database name is information_schema"
}
ok: [app] => (item={'key': 'mysql', 'value': {'size': 2523136}}) => {
    "msg": "Database name is mysql"
}
ok: [app] => (item={'key': 'performance_schema', 'value': {'size': 0}}) => {
    "msg": "Database name is performance_schema"
}
ok: [app] => (item={'key': 'app013', 'value': {'size': 0}}) => {
    "msg": "Database name is app013"
}
ok: [app] => (item={'key': 'app015', 'value': {'size': 0}}) => {
    "msg": "Database name is app015"
}
ok: [app] => (item={'key': 'app066', 'value': {'size': 0}}) => {
    "msg": "Database name is app066"
}

Backup only specific databases

As I mentioned at the beginning, I only want the playbook to create a backup of the app* databases. This can be achieved with an additional when condition, in combination with the previous loop.

The following example executes the mysqldump command (using the shell module) but only if (when) the database name contains the search string (app):

  # Backup database matching app
  - name: BACKUP - Create database dump
    shell:
      cmd: /usr/bin/mysqldump {{ item.key }} > /backup/{{ item.key }}.sql.$(date +%Y%m%d)
    loop: "{{ databases.databases | dict2items }}"
    when: '"app" in item.key'

The playbook runs through the loop but only executes the mysqldump command when the condition matches. The other databases are skipped.

TASK [BACKUP - Create database dump] *************************************************************
skipping: [app] => (item={'key': 'information_schema', 'value': {'size': 212992}})
skipping: [app] => (item={'key': 'mysql', 'value': {'size': 2523136}})
skipping: [app] => (item={'key': 'performance_schema', 'value': {'size': 0}})
changed: [app] => (item={'key': 'app013', 'value': {'size': 0}})
changed: [app] => (item={'key': 'app015', 'value': {'size': 0}})
changed: [app] => (item={'key': 'app066', 'value': {'size': 0}})

And the database dumps were successfully created on the target server:

root@app:~# ls -la /backup/|grep app
-rw-r--r-- 1 root root    1295 Nov 16 10:13 app013.sql.20231116
-rw-r--r-- 1 root root    1295 Nov 16 10:13 app015.sql.20231116
-rw-r--r-- 1 root root    1295 Nov 16 10:13 app066.sql.20231116

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.