dbms/mysqldump-wrapper.sh

#!/usr/bin/env bash

# DESCRIPTION:
#   creating database dump, copying to additional smb share, copies rotating
#   and
#   sending report to email
#
# DEPENDENCIES:
#   - privileged rights
#   - mysqldump
#   - gzip
#   - cifs-utils
#   - Python 3
#   - sendmail.py
#
# PARAMETERS:
#   1: "/path/to/file.conf" - path to config file
#
# FUNCTIONS:
#

#######################################
# Print message and add to log.
# Globals:
#   logs: /path/to/file.log
# Arguments:
#   1: message to print and logging
#######################################
addtologs() {
  printf "%s\n" "$(date +'%Y.%m.%d-%H:%M:%S') $1" | tee -a "${logs}"
}

#######################################
# Exit procedure.
# Globals:
#   time: time of script start
# Arguments:
#   None
#######################################
execquite() {
  addtologs "execution time is $(($(date +%s)-time)) seconds, exit."
  exit "${1}"
}

#######################################
# Error exit procedure.
# Globals:
#   None
# Arguments:
#   1: message to print and logging
#######################################
execerror() {
  addtologs "error: $1"
  execquite 1
}

#######################################
# Parsing config file and creating global vars.
# Globals:
#   conf: /path/to/file.conf
# Arguments:
#   None
#######################################
getconfig() {
  # mysql connection parameters
  db_host=$(grep 'db_host=' "${conf}" | cut -d= -f2)
  db_user=$(grep 'db_user=' "${conf}" | cut -d= -f2)
  db_pass=$(grep 'db_pass=' "${conf}" | cut -d= -f2)
  db_name=$(grep 'db_name=' "${conf}" | cut -d= -f2)

  # dump repository parameters
  dump_root=$(grep 'dump_root=' "${conf}" | cut -d= -f2)
  dump_name="${db_name}_backup_$(date +%Y_%m_%d_%H%M%S).sql.gz"
  dump_file="${dump_root}/${dump_name}"
  dump_save=$(grep 'dump_save=' "${conf}" | cut -d= -f2)

  # copy smb-repository parameters
  smb_host=$(grep 'smb_host=' "${conf}" | cut -d= -f2)
  smb_path=$(grep 'smb_path=' "${conf}" | cut -d= -f2)
  smb_user=$(grep 'smb_user=' "${conf}" | cut -d= -f2)
  smb_domn=$(grep 'smb_domn=' "${conf}" | cut -d= -f2)
  smb_pass=$(grep 'smb_pass=' "${conf}" | cut -d= -f2)
  copy_root="/mnt/${smb_host}/${smb_path}"
  copy_file="${copy_root}/${dump_name}"
  copy_save=$(grep 'copy_save=' "${conf}" | cut -d= -f2)

  # sendmail parameters
  smtp_pyth=$(grep 'smtp_pyth=' "${conf}" | cut -d= -f2)
  smtp_send=$(grep 'smtp_send=' "${conf}" | cut -d= -f2)
  smtp_host=$(grep 'smtp_host=' "${conf}" | cut -d= -f2)
  smtp_port=$(grep 'smtp_port=' "${conf}" | cut -d= -f2)
  smtp_from=$(grep 'smtp_from=' "${conf}" | cut -d= -f2)
  smtp_pass=$(grep 'smtp_pass=' "${conf}" | cut -d= -f2)
  smtp_dest=$(grep 'smtp_dest=' "${conf}" | cut -d= -f2)

  # common parameters
  logs="${dump_file}.log"
}

#######################################
# Checking user rights.
# Globals:
#   None
# Arguments:
#   None
# return:
#   0 - if privileged rights, 1 - if not privileged rights
#######################################
checkroot() {
  if [ "${EUID}" -ne 0 ]; then
    return 1 # false
  else
    return 0 # true
  fi
}

#######################################
# Start mysqldump without database blocking (WARNING: InnoDB required).
# Globals:
#   db_user: db auth username
#   db_pass: db auth password
#   db_host: db host address
#   db_name: db name
#   dump_file: database_dump.sql.gz
#   dump_root: /path/to/dump/directory
#   addtolog: log-writer function
# Arguments:
#   None
# return:
#   0 - dump created, 1 - dump failed
#######################################
createdump() {
  if mysqldump --single-transaction --skip-lock-tables \
               --user="${db_user}" --password="${db_pass}" \
               -h "${db_host}" "${db_name}" | \
               gzip > "${dump_file}"; then
    addtologs "created ${dump_file}"
    return 0 # true

  else
    addtologs "failed ${dump_file}"
    return 1 # false
  fi
}

#######################################
# Search and delete files, keep last file's amount.
# Globals:
#   addtolog: log-writer function
# Arguments:
#   1: path for search
#   2: name for search (grep's regex)
#   3: amount of last files to keep
#######################################
# shellcheck disable=SC2010,SC2207
cleanolder(){
  file_root=$1
  file_name=$2
  keep_last=$3
  file_list=()

  file_list=($(ls -tra "${file_root}" | grep "${file_name}"))
  list_size=${#file_list[@]}
  list_stop=$((list_size-keep_last))
  if [ "${list_stop}" -gt 0 ]; then
    for ((i=0; i<list_stop; i++)); do
      rm -f "${file_root}/${file_list[i]}"
      addtologs "deleted ${file_root}/${file_list[i]}"
    done
  fi
}

#######################################
# Send email notification.
# Globals:
#   smtp_pyth: /path/to/bin/python
#   smtp_send: /path/to/sendmail.py
#   smtp_from: from@domain.zone
#   smtp_pass: password for from@domain.zone
#   smtp_dest: destination@domain.zone
#   smtp_host: mail-server.domain.zone
#   smtp_port: 25,465,587
#   smtp_subj: subject of mail
#   addtolog: log-writer function
# Arguments:
#   None
#######################################
startsendmail() {
  (
    "${smtp_pyth}" "${smtp_send}" \
      -u "${smtp_from}" -p "${smtp_pass}" -d "${smtp_dest}" \
      --smtp "${smtp_host}" --port "${smtp_port}" --stls "True" \
      --subj "${smtp_subj}" --text "$(cat "${logs}")"
  ) > /dev/null 2>&1
  addtologs "sent mail with subject '${smtp_subj}' to '${smtp_dest}'"
}

#
# VARIABLES:
#

conf=$1
time=$(date +%s)
newl=$'\n'

#
# MAIN:
#

if [ -e "${conf}" ]; then
  getconfig

else
  logs=/dev/null
  execerror "Config not found. Usage example: $0 '/path/to/dump.conf'"
fi

if ! command -v mysqldump &> /dev/null || \
   ! command -v gzip &> /dev/null || \
   ! command -v "${smtp_send}" &> /dev/null || \
   ! command -v "${smtp_pyth}" &> /dev/null || \
   ! command -v mount.cifs &> /dev/null; then
  execerror "Not found dependencies."
fi

if ! checkroot; then
  execerror "Restart this as root!"

else
  smtp_subj="[Failed] MySQL backup: '${db_name}' on ${db_host}"
  db_orig_size_byte=$(mysql --user="${db_user}" --password="${db_pass}" -h "${db_host}" \
    -e 'SELECT table_schema AS "Database", (SUM(data_length)+SUM(index_length)) AS "Size" FROM information_schema.TABLES GROUP BY table_schema;' | \
    grep "${db_name}" | awk '{print $2}')
  fs_root_free_byte=$(df -B1 "${dump_root}" | tail -1 | awk '{print $4}')
  addtologs "${db_orig_size_byte} bytes are in '${db_name}' database."
  addtologs "${fs_root_free_byte} bytes are free in ${dump_root}."

  if [ "${db_orig_size_byte}" -gt "${fs_root_free_byte}" ]; then
    addtologs "%s\n" "Not enough free space"

  else
    addtologs "Start backuping MySQL '${db_name}' database.${newl}"

    if ! createdump; then
      addtologs "Failed backuping MySQL '${db_name}' database."

    else
      smtp_subj="[Warning] MySQL backup: '${db_name}' on ${db_host}"
      db_copy_size_unit=$(du -h "${dump_file}" | awk '{print $1}')
      fs_root_free_unit=$(df -h "${dump_root}" | tail -1 | awk '{print $4}')
      addtologs "created ${dump_file} of ${db_copy_size_unit}."
      addtologs "${fs_root_free_unit} are free in ${dump_root}."

      addtologs "Start cleaning. Keep last ${dump_save}.${newl}"
      cleanolder "${dump_root}" "^${db_name}_backup_.*.sql.gz$" "${dump_save}"
      cleanolder "${dump_root}" "^${db_name}_backup_.*.sql.gz.log$" "${dump_save}"
      fs_root_free_unit=$(df -h "${dump_root}" | tail -1 | awk '{print $4}')
      addtologs "${fs_root_free_unit} are free in ${dump_root}."

      if mkdir -p "${copy_root}" && \
        mount.cifs "//${smb_host}/${smb_path}" "${copy_root}" \
        -o "user=${smb_user},domain=${smb_domn},password=${smb_pass},vers=2.0"; then
        sleep 1

        db_copy_size_byte=$(du -B1 "${dump_file}" | awk '{print $1}')
        fs_copy_free_byte=$(df -B1 "${copy_root}" | tail -1 | awk '{print $4}')
        addtologs "${db_copy_size_byte} bytes are in ${dump_file}."
        addtologs "${fs_copy_free_byte} bytes are free in ${copy_root}."

        if [ "${db_copy_size_byte}" -gt "${fs_copy_free_byte}" ]; then
          addtologs "%s\n" "Not enough free space"

        else
          addtologs "Start copying backup to //${smb_host}/${smb_path}${newl}"

          if ! cp "${dump_file}" "${copy_file}"; then
            addtologs "failed ${db_name} dump copy in ${copy_root}"

          else
            smtp_subj="[Success] MySQL backup: '${db_name}' on ${db_host}"
            addtologs "created ${copy_file}"
            fs_copy_free_unit=$(df -h "${copy_root}" | tail -1 | awk '{print $4}')
            addtologs "${fs_copy_free_unit} are free in ${copy_root}."
            sleep 1

            addtologs "Start cleaning. Keep last ${copy_save}.${newl}"
            cleanolder "${copy_root}" "^${db_name}_backup_.*.sql.gz$" "${copy_save}"
            fs_copy_free_unit=$(df -h "${copy_root}" | tail -1 | awk '{print $4}')
            addtologs "${fs_copy_free_unit} are free in ${copy_root}."
          fi
        fi

        umount "/mnt/${smb_host}/${smb_path}" && rm -rf "/mnt/${smb_host:?}"
      fi
    fi
  fi

  startsendmail
fi

execquite 0