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

Written by - 0 comments

Published on - 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.

Ansible playbook creating database dumps for specific 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.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder