bash script to dump mailman3's mysql tables excluding hyperkitty archives

mailman3-mysqldump.bash

#!/bin/bash

# Needs bash because uses bash arrays.

# Use mysqldump to dump out the mailman3 and mailman3web databases
# through a pipe to gzip, then move it on top of the last backup. It
# is assumed your general backup system takes care of backing up those
# files elsewhere, keeping historical copies.
#
# All tables from the mailman3 database are dumped out, but only the
# schema (not the data) from tables named hyperkitty_% in the
# mailman3web dataase are included. This is to exclude mailing list
# archives. It is assumed you are backing those up in some other way,
# e.g. using the "prototype" archiver or taking mbox downloads.
#
# Your MySQL/MariaDB credentials should go into the file
# /etc/mysql/backup_credentials.cnf something like this:
#
# [mysql]
# user=your_dbdump_user
# password=your_chosen_password
#
# [mysqldump]
# user=your_dbdump_user
# password=your_chosen_password

set -euf

umask 0066

# TODO: Write to proper secure temporary files rather than .new files,
# which could be used in a symlink attack to overwrite arbitrary
# paths. Though only root should be able to write to
# /srv/backup/mariadb/.

/usr/bin/mysqldump \
    --defaults-extra-file=/etc/mysql/backup_credentials.cnf \
    --default-character-set=utf8mb4 \
    mailman3 \
    --single-transaction \
    | /bin/gzip --best --rsyncable -c \
    > /srv/backup/mariadb/mailman3.sql.gz.new \
    && mv /srv/backup/mariadb/mailman3.sql.gz.new \
          /srv/backup/mariadb/mailman3.sql.gz

read -r -d '' table_query << End_of_SQL || :
SELECT table_name
FROM tables
WHERE table_schema='mailman3web'
      AND table_name LIKE 'hyperkitty_%'
End_of_SQL

readarray -t hyperkitty_tables < <(/usr/bin/mysql \
    --defaults-extra-file=/etc/mysql/backup_credentials.cnf -NB \
    information_schema -e "$table_query")

# Annoyingly, for ignoring tables you have to do --ignore-table once
# per table and specify the database as well.
ignore_args=() # Empty array
for table in "${hyperkitty_tables[@]}"; do
    ignore_args=("${ignore_args[@]}" --ignore-table "mailman3web.${table}")
done

# Only dump the schema for the hyperkitty tables, not any of the data.
( /usr/bin/mysqldump \
    --defaults-extra-file=/etc/mysql/backup_credentials.cnf \
    --default-character-set=utf8mb4 \
    mailman3web \
    --no-data \
    --single-transaction \
    --tables "${hyperkitty_tables[@]}";

# Now dump the schema and data from the other tables.
  /usr/bin/mysqldump \
    --defaults-extra-file=/etc/mysql/backup_credentials.cnf \
    --default-character-set=utf8mb4 \
    mailman3web \
    --single-transaction \
    "${ignore_args[@]}" ) \
    | /bin/gzip --best --rsyncable -c \
    > /srv/backup/mariadb/mailman3web.sql.gz.new \
    && mv /srv/backup/mariadb/mailman3web.sql.gz.new \
          /srv/backup/mariadb/mailman3web.sql.gz