MySQL バックアップ用シェルスクリプト

backup_mysql.sh

#!/bin/sh

#
# ----------------------------------------------------------------------
# Variables
# ----------------------------------------------------------------------
#

#
# Retention Period of mysqldump files.
#
RETENTION_PERIOD=3

# BACKUP_SERVER='remote_server_address'
BACKUP_DIR='/mnt/nas/backup/mysql/wavia'
BACKUP_SUFFIX=`date +%Y-%m-%d`

BACKUP_LOG=mysqldump_result_"${BACKUP_SUFFIX}".log
BACKUP_ERROR_LOG=mysqldump_error_"${BACKUP_SUFFIX}".log

#
# ----------------------------------------------------------------------
# Dump & Compress & Transfer
# ----------------------------------------------------------------------
#

#
# Mounted File System
#
mysqldump -u root --all-databases --events | bzip2 > "${BACKUP_DIR}"/wavia."${BACKUP_SUFFIX}".sql.bz2 2>>"${BACKUP_ERROR_LOG}"

#
# Remote Server
#
# mysqldump -u root --all-databases --events | bzip2 | ssh "${BACKUP_SERVER}" 'cat > "${BACKUP_DIR}"/wavia."${BACKUP_SUFFIX}".sql.bz2' 2>>"${BACKUP_ERROR_LOG}"


#
# ----------------------------------------------------------------------
# House Keeping
# ----------------------------------------------------------------------
#

# Retention Period = 保存期間を過ぎた mysqldump ファイルを削除
# -mtime +0 で 1 日前と評価されるので "${RETENTION_PERIOD}" - 1 している.
MTIME_ARG=`expr "${RETENTION_PERIOD}" - 1`
find "${BACKUP_DIR}" \( -name "*.sql.bz2" \) -mtime +"${MTIME_ARG}" | xargs rm -f

# old
# OLD_SUFFIX=`date --date "$RETENTION_PERIOD days ago" +%Y-%m-%d`
# rm -f "${BACKUP_DIR}"/wavia."${OLD_SUFFIX}".sql.bz2

exit 0