Backup MariaDB (MySQL) databases with Backup Manager on Debian

This howto is tested on:

• Debian 10.0 Buster

Requirements

This howto requires:

• a MariaDB (MySQL) server, as described by Install MariaDB (MySQL) on Debian.
• Backup Manager, as described by Install Backup Manager on Debian.

Settings

Initialize the settings associative array:

declare -A bmSettings

Set the name of the MySQL account to be created for use by Backup Manager to dump the databases contents:

bmSettings['BM_MYSQL_ADMINLOGIN']='backup'

Generate the password of Backup Manager MySQL account to be created:

bmSettings['BM_MYSQL_ADMINPASS']="$(command apg -q -a  0 -n 1 -M NCL)"

Configuration

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Backup Manager fail to generate MySQL backups if ‘/root/.my.cnf’ exists and contains a password. Fix the bug by replacing ‘–defaults-extra-file’ by ‘–defaults-file’ in backup-manager code:

${cmdProxy} dpkg-divert --divert '/usr/share/backup-manager/backup-methods.sh.orig' \
    --rename '/usr/share/backup-manager/backup-methods.sh'
${cmdProxy} cp -a '/usr/share/backup-manager/backup-methods.sh.orig' '/usr/share/backup-manager/backup-methods.sh'
${cmdProxy} sed -i -e 's/--defaults-extra-file/--defaults-file/g' '/usr/share/backup-manager/backup-methods.sh'

Create the Backup Manager MariaDB user with permissions to use mysqldump command:

command mysql --user=root \
  --execute="GRANT SELECT, LOCK TABLES, SHOW VIEW, EXECUTE ON *.*
        TO '${bmSettings['BM_MYSQL_ADMINLOGIN']}'@'localhost'
        IDENTIFIED BY '${bmSettings['BM_MYSQL_ADMINPASS']}';"

Exclude special databases from dump:

dbExclude=( 'information_schema' 'performance_schema' 'sys' )
IFS=' ' bmSettings['BM_MYSQL_DBEXCLUDE']="${dbExclude[*]}"

Fetch current backup upload methods:

IFS=' ' read -r -a 'backupArchiveMethods' \
  <<< "$(${cmdProxy} grep '^export BM_ARCHIVE_METHOD=' '/etc/backup-manager.conf' \
    | command sed -e 's/^[^=]*="\(.*\)".*$/\1/')"

Add “mysql” to archive methods:

backupArchiveMethods+=( 'mysql' )

Make sure there is no duplicate archive method:

mapfile -t 'backupArchiveMethods' \
    <<< "$(printf '%s\n' "${backupArchiveMethods[@]}" \
                | command sort --unique)"

Update archive method setting:

IFS=' ' bmSettings['BM_ARCHIVE_METHOD']="${backupArchiveMethods[*]}"

Apply the settings:

declare -a sedOptions
sedOptions=()
for settingName in ${!bmSettings[@]}; do
  sedOptions+=( '-e' "s|[#]*\(.*${settingName}=\).*$|\1\"${bmSettings["${settingName}"]}\"|" )
done
${cmdProxy} sed -i "${sedOptions[@]}" '/etc/backup-manager.conf'