Mysql Backup Script
Features
- Full Backup (Mysqldump)
- Incremental Backup (Bin-log)
- Local Backup & Remote(AWS S3) Upload
Sample Environments
- MySQL bin log prefix : mysql-bin
- BINLOG_PATH=/home/mysql/data
- BACKUP_ROOT=/home/backup/mysql
- S3_PATH=s3://system-backup/{SyatemName}/mysql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
#!/bin/bash #---------------------------------------- # MySQL Backup Script # Revision 20160531 # Copyright (c) Aiden Kim # http://www.daemon.pe.kr #---------------------------------------- ARCHIVE_PERIOD=60 BINLOG_PATH=/home/mysql/data BASE_PATH=/home/backup/mysql SYSNAME=$(echo $HOSTNAME | tr "[:lower:]" "[:upper:]") S3_PATH=s3://system-backup/${SYSNAME}/mysql STATUS_PATH=${BASE_PATH}/mysql-backup-status ## prepare path if ! [ -d ${BASE_PATH} ]; then /bin/mkdir -p ${BASE_PATH} fi # Full backup if [[ "$1" == "full" ]]; then echo "MySQL Full Backup started `date`..." # prepare directory TSTR=$(date +"%Y%m%d_%H%M%S") BACKUP_PATH=${BASE_PATH}/${TSTR} if ! [[ -d ${BACKUP_PATH} ]]; then /bin/mkdir -p ${BACKUP_PATH} fi /bin/rm -rf ${BACKUP_PATH}/* # get old full backup information if [[ -r ${STATUS_PATH} ]]; then OLD_BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'` OLD_START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'` fi # mysqldump /usr/local/mysql/bin/mysqldump --all-databases --routines --events --hex-blob --single-transaction --flush-logs --master-data=2 --include-master-host-port > ${BACKUP_PATH}/full_backup-${TSTR}.sql START_BINLOG=`/bin/sed -rn "1,50 s/^.+ MASTER_LOG_FILE\='(mysql-bin\.[0-9]+)'.+$/\1/p" ${BACKUP_PATH}/full_backup-${TSTR}.sql | /bin/sed -n '1p'` echo "${BACKUP_PATH}" > ${STATUS_PATH} echo "${START_BINLOG}" >> ${STATUS_PATH} /bin/gzip ${BACKUP_PATH}/full_backup-${TSTR}.sql # finalize previous full backup data if [[ -n ${OLD_BACKUP_PATH} ]] && [[ -n ${OLD_START_BINLOG} ]] && [[ -d ${OLD_BACKUP_PATH} ]]; then for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g` do if [[ "${FILE}" < "${BINLOG_PATH}/${START_BINLOG}" ]]; then if [[ "${FILE}" == "${BINLOG_PATH}/${OLD_START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${OLD_START_BINLOG}" ]]; then /bin/cp -afpu ${FILE} ${OLD_BACKUP_PATH}/ fi fi done fi # Incremental backup else echo "MySQL Incremental Backup started..." # get previous full backup data if ! [[ -r ${STATUS_PATH} ]]; then echo "Could not find full backup status file." exit 1 fi BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'` START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'` TSTR=`/bin/echo ${BACKUP_PATH} | sed -rn "s/^.+\/(20[0-9]{6}_[0-9]{6})$/\1/p"` if ! [[ -d ${BACKUP_PATH} ]]; then echo "Could not find last full backup path." exit 2 fi if ! [[ -d ${BINLOG_PATH} ]]; then echo "Could not find mysql bin-log path." exit 2 fi # flush logs & copy bin-logs /usr/local/mysql/bin/mysqladmin flush-logs BINLOG_CURR=`ls -d ${BINLOG_PATH}/mysql-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1` for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g` do if [[ "${BINLOG_PATH}/mysql-bin.${BINLOG_CURR}" != "${FILE}" ]]; then if [[ "${FILE}" == "${BINLOG_PATH}/${START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${START_BINLOG}" ]]; then /bin/cp -afpu ${FILE} ${BACKUP_PATH}/ fi fi done fi # sync to s3 bucket /usr/bin/aws s3 sync ${BACKUP_PATH}/ ${S3_PATH}/${TSTR}/ # remove old backups oldDirs=($(/bin/find ${BASE_PATH} -type d -mtime +${ARCHIVE_PERIOD})) oldDirLen=${#oldDirs[@]} for (( i=0; i<${oldDirLen}; i++ )); do /bin/rm -rf ${oldDirs[$i]} done exit 0 |
Executing
|
1 2 3 4 5 |
# Full Backup ./backup.mysql.sh full # Incremental Backup ./backup.mysql.sh |
