mysql-backup

#!/bin/bash
#===============================================================================
# mysql-backup :
#===============================================================================

SCRIPT_BASE=`echo $(cd $(dirname $0); pwd)`
SCRIPT_NAME=`basename $0`

#-------------------------------------------------------------------------------
# User Configuration -----------------------------------------------------------
BACKUP_BASE=/backup
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
COMPRESS_PROG=/usr/bin/pigz
ARCHIVE_FILE_PREFIX=mysql-
ARCHIVE_FILE_SUFFIX=.gz
DATE_FORMAT=+%Y%m%d-%H%M%S
RETENTION=
MYSQL_USER=root
MYSQL_PASSWORD=
CONTAINER_NAME=
#-------------------------------------------------------------------------------

DUMP_OPT=(--lock-all-tables --hex-blob --add-drop-database --add-drop-table)
DUMP_OPT_INFORMATION_SCHEMA=""
DUMP_OPT_MYSQL=(--lock-all-tables --events)
TAR_OPT=(-c)

cat /proc/$$/cgroup | egrep -q '^[0-9]+:name=systemd:/system.slice'
[[ $? -eq 0 ]] && SYSTEMD_SLICE=yes

[[ "${SYSTEMD_SLICE}" != 'yes' ]] && __MSG_COL=70

#-------------------------------------------------------------------------------
#- common functions ------------------------------------------------------------

__setup_color() { #{{{
  local I
  local COLOR_MAP=(\
    BLACK 0 MAROON 1 GREEN 2 OLIVE 3 NAVY 4 PURPLE 5 TEAL 6 SILVER 7 GREY 8 \
    RED 9 LIME 10 YELLOW 11 BLUE 12 FUCHSIA 13 AQUA 14 WHITE 15 \
    MAGENTA 5 CYAN 6 MALIBU 74 PINK 218 ORANGE 214 DARK_ORANGE3 166 \
  )

  # export color map when `__COLOR_MAP` is defined
  [[ -v __COLOR_MAP ]] && __COLOR_MAP=("${COLOR_MAP[@]}")

  if [[ $- == *u* ]] && [[ `type -t __setup_color_prepare_empty` == 'function' ]]; then
    __setup_color_prepare_empty ${COLOR_MAP[@]}
  fi

  C_OFF=`tput sgr0`   # Reset attribute
  C_BOLD=`tput bold`  # Bold mode
  C_REV=`tput rev`    # Reverse mode
  C_UL=`tput smul`    # Underline mode

  for ((I=0; I<${#COLOR_MAP[@]}; I+=2)); do
    # echo "$I : ${COLOR_MAP[$I]} ${COLOR_MAP[(($I + 1))]}"
    eval "C_${COLOR_MAP[$I]}=\`tput setaf ${COLOR_MAP[(($I + 1))]}\`"
    eval "C_B_${COLOR_MAP[$I]}=\`tput bold\`\`tput setaf ${COLOR_MAP[(($I + 1))]}\`"
    eval "C_U_${COLOR_MAP[$I]}=\`tput smul\`\`tput setaf ${COLOR_MAP[(($I + 1))]}\`"
    eval "C_R_${COLOR_MAP[$I]}=\`tput bold\`\`tput rev\`\`tput setaf ${COLOR_MAP[(($I + 1))]}\`"
  done
}
#}}}

__show_ok() { #{{{
  [[ -n "${__SILENT}" ]] && return
  local COL
  [[ ! "$1" =~ [0-9]+ ]] && COL="${__MSG_COL}" || COL="$1" && shift 1
  local MSG="$1"

  [[ -n "${COL}" ]] && echo -en "\\033[${COL}G"
  echo -en " [ ${C_GREEN}OK${C_OFF}"
  [[ -n "${MSG}" ]] && echo " ] ${MSG}" || echo " ]"
}
#}}}

__show_failed() { #{{{
  [[ -n "${__SILENT}" ]] && return
  local COL
  [[ ! "$1" =~ [0-9]+ ]] && COL="${__MSG_COL}" || COL="$1" && shift 1
  local MSG="$1"

  [[ -n "${COL}" ]] && echo -en "\\033[${COL}G"
  echo -en " [ ${C_RED}FAILED${C_OFF}"
  [[ -n "${MSG}" ]] && echo " ] ${MSG}" || echo " ]"
}
#}}}

__show_info() { #{{{
  [[ -n "${__SILENT}" ]] && return
  [[ "$1" == "-n" ]] && echo -en "${C_CYAN}${2}${C_OFF}" || echo -e "${C_CYAN}${1}${C_OFF}"
}
#}}}

__show_warn() { #{{{
  echo -e "[${C_YELLOW}WARN${C_OFF}]  $1"
}
#}}}

__show_error() { #{{{
  echo -e "[${C_RED}ERROR${C_OFF}] $*" >&2
}
#}}}

__error_end() { #{{{
  __show_error "$*"; exit 1
}
#}}}

__script_end() { #{{{
  local FUNC
  while read FUNC; do
    $FUNC
  done < <(declare -F | sed -e 's/^declare -f //' | egrep '^__?script_end_.+' | sort)
}
trap '__script_end' EXIT
#}}}

__prepare_dir() { #{{{
  local DIR="$1"
  if [[ ! -d "$DIR" ]]; then
    mkdir -p "$DIR"
    [[ ! -d "$DIR" ]] && return 1
    return 0
  fi
  return 0
}
#}}}

__make_temp_simple() { #{{{
  [[ "$1" == '-d' ]] && local MAKE_DIR="$1"
  if [[ `uname` == Darwin || ! -d /dev/shm ]]; then
    mktemp ${MAKE_DIR}
  else
    mktemp -p /dev/shm ${MAKE_DIR}
  fi
}
#}}}

__rotate_files() { #{{{
  local RETENTION="$1"
  local PATTERN="$2"
  local RESULT=0
  local F CNT

  ___do_rotate() {
    local F="$1"
    local MSG
    if [[ ${__DRY_RUN} != 'yes' ]]; then
      echo -n "  Deleting... $F"
      MSG=`rm "$F" 2>&1`
      if [[ $? -eq 0 ]]; then
        __show_ok
        return 0
      else
        echo -e "\n  [${C_RED}FAILED${C_OFF}] ${MSG}"
        return 1
      fi
    else
      echo "  will be deleted : $F"
    fi
  }

  if [[ ! "${RETENTION}" =~ [0-9]+ ]]; then
    __show_error "Invalid retention : ${RETENTION}"
    return 1
  fi

  if [[ ! "$PATTERN" =~ ^(.+)/([^/]+)$ ]]; then
    __show_error "Invalid pattern : ${PATTERN}"
    return 1
  fi
  local BASEDIR="${BASH_REMATCH[1]}"
  local FILE_PATTERN="${BASH_REMATCH[2]}"

  local FIND_ARGS=("${BASEDIR}" -mindepth 1 -maxdepth 1 -type f -name "${FILE_PATTERN}")
  echo ">>> ${FIND_ARGS[@]}"

  # delete empty files
  __show_info "Deleting empty files..."
  CNT=0
  while read F; do
    ___do_rotate "$F" && CNT=$(( $CNT + 1 )) || RESULT=1
  done < <(find ${FIND_ARGS[@]} -size 0c)
  [[ "$CNT" -eq 0 ]] && echo "  nothing to delete."

  # delete old files
  __show_info "Deleting old files..."
  CNT=0
  while read F; do
    ___do_rotate "$F" && CNT=$(( $CNT + 1 )) || RESULT=1
  done < <(find ${FIND_ARGS[@]} -size +1c | sort -n | head --lines=-${RETENTION})
  [[ "$CNT" -eq 0 ]] && echo "  nothing to delete."

  return $RESULT
}
#}}}

#-------------------------------------------------------------------------------
#- functions -------------------------------------------------------------------

_usage() { #{{{
  local STATUS=0
  if [[ -n "$@" ]]; then
    __show_error "$@"
    STATUS=1
    echo
  fi
  local B=${C_B_MALIBU}
  echo -e "${B}usege :${C_OFF} ${SCRIPT_NAME} [--silent] [-h] [-d] -c CONF_FILE"
  cat <<EOM
  ${B}OPTIONS${C_OFF}
    ${B}--silent${C_OFF} will not display messages (for batch job)
    ${B}-c${C_OFF}       specify configuration file path
    ${B}-f${C_OFF}       force overwrite archive file
    ${B}-d${C_OFF}       dry run
    ${B}-h${C_OFF}       show this help
  
  ${B}CONFIGURATION EXAMPLE${C_OFF}
    MYSQL_USER=root
    MYSQL_PASSWORD=**********
    BACKUP_BASE=/backup
    MYSQL=/usr/bin/mysql
    MYSQLDUMP=/usr/bin/mysqldump
    COMPRESS_PROG=/usr/bin/pigz
    # when specified, backup MySQL in the docker container
    CONTAINER_NAME=             
    ARCHIVE_FILE_PREFIX=mysql-
    ARCHIVE_FILE_SUFFIX=.gz
    # Follow the specification of the date command
    DATE_FORMAT=+%Y%m%d
    # (Optional) If not specified, no rotation will be performed  
    RETENTION=5

  If MYSQL_PASSWORD is not specified, it will be read from the standard input.

  ${B}DEFAULT CONFIGURATION${C_OFF}
    MYSQL_USER=${MYSQL_USER}
    MYSQL_PASSWORD=
    BACKUP_BASE=${BACKUP_BASE}
    MYSQL=${MYSQL}
    MYSQLDUMP=${MYSQLDUMP}
    COMPRESS_PROG=${COMPRESS_PROG}
    CONTAINER_NAME=${CONTAINER_NAME}
    ARCHIVE_FILE_PREFIX=${ARCHIVE_FILE_PREFIX}
    ARCHIVE_FILE_SUFFIX=${ARCHIVE_FILE_SUFFIX}
    DATE_FORMAT=${DATE_FORMAT}
    RETENTION=${RETENTION}
EOM
  exit $STATUS
}
#}}}

_load_conf() { #{{{
  local CONF_FILE="$1"
  [[ -z "$CONF_FILE" ]] && __error_end "Configuration file is not specified."
  if [[ ! -f "${CONF_FILE}" ]]; then
    __error_end "Configuration file is not found. : ${CONF_FILE}"
  fi

  local PARAMS=(MYSQL_USER MYSQL_PASSWORD BACKUP_BASE MYSQL MYSQLDUMP)
  PARAMS+=(COMPRESS_PROG ARCHIVE_FILE_PREFIX ARCHIVE_FILE_SUFFIX DATE_FORMAT RETENTION)
  PARAMS+=(CONTAINER_NAME)

  local PAT="^[ \t]*(`echo ${PARAMS[@]} | tr ' ' '|'`)="
  source <(egrep "${PAT}" "${CONF_FILE}")

  if [[ -z "${__SILENT}" ]]; then
    echo -e "${C_CYAN}Configuration file loaded.${C_OFF} : `readlink -f $CONF_FILE`"
  fi
}
#}}}

_list_database() { #{{{
  local IGNORES="^(information_schema|performance_schema|sys)$"
  local CMD="show databases;"

  if [[ -z "${CONTAINER_NAME}" ]]; then
    ${MYSQL} --defaults-file=<(echo -e "[client]\npassword=${MYSQL_PASSWORD}") \
      -u${MYSQL_USER} -N <<<"${CMD}" \
      | egrep -v "${IGNORES}" | xargs
  else
    local SECRET=`__make_temp_simple`
    echo -e "[client]\npassword=${MYSQL_PASSWORD}" > ${SECRET}
    docker cp ${SECRET} ${CONTAINER_NAME}:/secret
    rm "${SECRET}"
    docker exec -i ${CONTAINER_NAME} ${MYSQL} --defaults-extra-file=/secret -u${MYSQL_USER} -N <<< "${CMD}" \
      | egrep -v "${IGNORES}" | xargs
    docker exec -i ${CONTAINER_NAME} rm /secret
  fi
}
#}}}

_do_dump() { #{{{
  local OPT=("$@")
  local RESULT=0
  local DUMP_ERR=`__make_temp_simple`

  if [[ -z "${CONTAINER_NAME}" ]]; then
    ${MYSQLDUMP} --defaults-extra-file=<(echo -e "[client]\npassword=${MYSQL_PASSWORD}") ${OPT[@]} 2> ${DUMP_ERR}
    RESULT=$?
  else
    local SECRET=`__make_temp_simple`
    echo -e "[client]\npassword=${MYSQL_PASSWORD}" > ${SECRET}
    docker cp ${SECRET} ${CONTAINER_NAME}:/secret
    rm "${SECRET}"
    OPT=(--defaults-extra-file=/secret ${OPT[@]})
    docker exec ${CONTAINER_NAME} ${MYSQLDUMP} ${OPT[@]} 2> ${DUMP_ERR}
    local RESULT=$?
    docker exec ${CONTAINER_NAME} rm /secret
  fi

  # show error
  if [[ ${RESULT} -ne 0 ]]; then
    echo >&2
    echo -en "${C_RED}" >&2
    cat ${DUMP_ERR} >&2
    echo -en "${C_OFF}" >&2
  fi
  [[ -f ${DUMP_ERR} ]] && rm ${DUMP_ERR}

  return $RESULT
}
#}}}

_script_end_cleanup() { #{{{
  [[ -d "${_BACKUP_TMP_DIR}" ]] && rm -rf "${_BACKUP_TMP_DIR}"
}
#}}}

_check_container_executable() { #{{{
  local CONTAINER="$1"
  local CMD="$2"

  docker exec "${CONTAINER}" /bin/sh -c "type ${CMD}" > /dev/null 2>&1
  return $?
}
#}}}

#-------------------------------------------------------------------------------
#- Main process ----------------------------------------------------------------
#-------------------------------------------------------------------------------
[[ "${SYSTEMD_SLICE}" != 'yes' ]] && __setup_color

#- Get options -----------------------------------------------------------------
#{{{
[[ "$1" = "--silent" ]] && __SILENT=yes && shift
while getopts c:fdh OPT; do
  case "$OPT" in
    c) CONF_FILE=$OPTARG
      ;;
    f) FORCE_OVERWRITE=yes
      ;;
    d) __DRY_RUN=yes
      ;;
    h|\?) _usage
      ;;
  esac
done
shift `expr $OPTIND - 1`
#}}}

#- Load configuration file -----------------------------------------------------
_load_conf "${CONF_FILE}"

#- Check options ---------------------------------------------------------------
if [[ -z "$MYSQL_PASSWORD" ]]; then
  tty -s && MSG="Enter ${MYSQL_USER}'s password. : " 
  tty -s && TIMEOUT=(-t 10)
  read ${TIMEOUT[@]} -sp "${MSG}" MYSQL_PASSWORD
  tty -s && echo
fi

#- Check requirements ----------------------------------------------------------
#{{{
# check if docker can execute
if [[ -n "${CONTAINER_NAME}" ]]; then
  if ! docker info > /dev/null 2>&1; then
    __error_end "Unable to run docker command"
  fi
fi

# check mysql / mysqldump executable
if [[ -z "${CONTAINER_NAME}" ]]; then
  # for not container
  [[ -z "${MYSQL}" ]] && MYSQL=`command -v mysql 2> /dev/null`
  [[ ! -x "${MYSQL}" ]] && __error_end "Unable to find mysql executable. : ${MYSQL}"

  [[ -z "${MYSQLDUMP}" ]] && MYSQL=`command -v mysqldump 2> /dev/null`
  [[ ! -x "${MYSQLDUMP}" ]] && __error_end "Unable to find mysqldump executable. : ${MYSQLDUMP}"
else
  # for container
  _check_container_executable ${CONTAINER_NAME} bash || __error_end "Unable to find bash in ${CONTAINER_NAME}"

  [[ -z "${MYSQL}" ]] && MYSQL=mysql
  _check_container_executable ${CONTAINER_NAME} ${MYSQL} || __error_end "Unable to find ${MYSQL} in ${CONTAINER_NAME}"

  [[ -z "${MYSQLDUMP}" ]] && MYSQL=mysqldump
  _check_container_executable ${CONTAINER_NAME} ${MYSQLDUMP} || __error_end "Unable to find ${MYSQLDUMP} in ${CONTAINER_NAME}"
fi

if [[ ! -x "$COMPRESS_PROG" ]]; then
  __show_warn "$COMPRESS_PROG is not found. Use gzip."
  COMPRESS_PROG=""
  TAR_OPT+=(-z)
  ARCHIVE_FILE_SUFFIX=.gz
fi
ARCHIVE_FILE=${BACKUP_BASE}/"${ARCHIVE_FILE_PREFIX}`LANG=C date "${DATE_FORMAT}"`.tar${ARCHIVE_FILE_SUFFIX}"
ROTATE_PATTERN=${BACKUP_BASE}/${ARCHIVE_FILE_PREFIX}*.tar${ARCHIVE_FILE_SUFFIX}
TAR_OPT+=(-f ${ARCHIVE_FILE})
#}}}

#- Main process ----------------------------------------------------------------

[[ "$__DRY_RUN" == 'yes' ]] && echo -e "${C_YELLOW}[DRY-RUN]${C_OFF}"

__show_info "Backup start." 

if [[ "${__DRY_RUN}" != 'yes' ]]; then
  DIR="`dirname $ARCHIVE_FILE`" 
  __prepare_dir "${DIR}" || __error_end "Unable to create directory. : ${DIR}"
fi

if [[ -f "${ARCHIVE_FILE}" ]]; then
  [[ "${FORCE_OVERWRITE}" != 'yes' ]] && __error_end "Always exists ${ARCHIVE_FILE}"
  [[ "${FORCE_OVERWRITE}" == 'yes' && ! -w "${ARCHIVE_FILE}" ]] && __error_end "Unable to overwrite ${ARCHIVE_FILE}"
fi

_BACKUP_TMP_DIR=`mktemp -d`
DUMP_STATUS=0

#-----------------------------------------------------------
# Dump each databases
#-----------------------------------------------------------
for DBNAME in `_list_database`; do
  BACKUP_SQL=${_BACKUP_TMP_DIR}/${DBNAME}.sql
  OPT=(-u ${MYSQL_USER})

  if [[ "$DBNAME" == "mysql" ]]; then
    OPT+=(${DUMP_OPT_MYSQL[@]})
  else
    OPT+=(${DUMP_OPT[@]})
  fi
  OPT+=(--databases ${DBNAME})

  if [[ -z "${__SILENT}" ]]; then
    echo -n "  Backup ${DBNAME} to ${DBNAME}.sql ... "
  fi

  if [[ "$__DRY_RUN" == 'yes' ]]; then
    echo
    echo -e "  ${C_YELLOW}[dry-run]${C_OFF} ${MYSQLDUMP} ${OPT[@]}"
    continue
  fi

  # do mysqldump
  _do_dump ${OPT[@]} > ${BACKUP_SQL}
  RESULT=$?
  if [[ ${RESULT} -eq 0 ]]; then
    # check file size
    SIZE=`ls -l $BACKUP_SQL | awk '{print $5}'`
    if [[ $SIZE -ge 0 ]]; then
      __show_ok
    else
      __show_failed
      DUMP_STATUS=1
    fi
   else
     __show_failed
     DUMP_STATUS=1
   fi
done

#-----------------------------------------------------------
# Archive
#-----------------------------------------------------------

TAR_OPT+=(--use-compress-prog=${COMPRESS_PROG} *.sql)
# When dry-run mode exit here
if [[ "$__DRY_RUN" ]]; then
  echo -n "${C_YELLOW}[DRY-RUN]${C_OFF}"
  echo " Will be archived : tar ${TAR_OPT[@]}"
  exit 0
fi

if [[ "${DUMP_STATUS}" -eq 1 ]]; then
  __error_end "Failed to dump databases."
fi

# Archive
pushd ${_BACKUP_TMP_DIR} > /dev/null
[[ -z "${__SILENT}" ]] && echo "  Archiving... : ${ARCHIVE_FILE}"
tar ${TAR_OPT[@]}
popd > /dev/null

if [[ -e $ARCHIVE_FILE ]]; then
  __show_info "Backup was successfully completed. : ${ARCHIVE_FILE}"
else
  __show_error "Backup failed. : ${ARCHIVE_FILE}"
  exit 1
fi

# Rotate old files
if [[ -n "${RETENTION}" ]]; then
  __rotate_files ${RETENTION} "${ROTATE_PATTERN}"
fi

# vim: ts=2 sw=2 sts=2 et nu foldmethod=marker