Backup and git MySQL DB dump, rotate weekly

backupdb.sh

#!/bin/bash

# CSDev
# Inspired in https://github.com/yamingd/gist/wiki/backup-mysql-and-commit-to-git

# Requirement: Configure user & password
# mysql_config_editor set --login-path=local --host=localhost --user=$DBUSERNAME --password

# Backup mysql, weekly overwrite
NOW=$(date +"%u%a")
DEST="/home/ubuntu/scripts/backups/db/weekly"

# guess binary names
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# destination exists?
[ ! -d "${DEST}" ] && mkdir -p "${DEST}"
cd $DEST

# get all db names
DBS="$($MYSQL --login-path=local  -Bse 'show databases')"
for db in $DBS
do
  # skip unwanted dbs
  case $db in
      (information_schema|mysql|performance_schema|phpmyadmin|sys) continue;
  esac

  # Overrite weekly
  FILE="${HOSTNAME}-${NOW}-mysql-${db}.gz"

  # Backup zipped
  echo "Backing up ${db} to ${FILE}.."
  $MYSQLDUMP --login-path=local $db --single-transaction  -P 33061 | $GZIP -9 > $FILE

  # Add to git
  git add $FILE
done

# Commit and push all
git commit -m "DB WEEKLY BACKUP ${HOSTNAME} - ${NOW}"
git push origin main