MySQL

返回上一级

MySQL备份

#!/bin/bash

set -e

USER="backup"
PASSWORD="backup"
# 数据库数据目录 #
DATA_DIR="/data/mysql"
BIN_INDEX=$DATA_DIR"/mysql-bin.index"
# 备份目录 #
BACKUP_DIR="/data/backup/mysql"
BACKUP_LOG="/var/log/mysql/backup.log"

DATE=`date +"%Y%m%d"`
TIME=`date +"%Y%m%d%H"`

LOG_TIME=`date +"%Y-%m-%d %H:%M:%S"`
DELETE_BINLOG_TIME="7 day"
INCREMENT_INTERVAL="3 hour"

note() {
    printf "[$LOG_TIME] note: $*\n" >> $BACKUP_LOG;
}

warning() {
    printf "[$LOG_TIME] warning: $*\n" >> $BACKUP_LOG;
}

error() {
    printf "[$LOG_TIME] error: $*\n" >> $BACKUP_LOG;
    exit 1;
}

full_backup() {
    local dbs=`ls -l $DATA_DIR | grep "^d" | awk -F " " '{print $9}'`

    for db in $dbs
    do
        local backup_dir=$BACKUP_DIR"/full/"$db
        local filename=$db"."$DATE
        local backup_file=$backup_dir"/"$filename".sql"

        if [ ! -d $backup_dir ]
        then
            mkdir -p $backup_dir || { error "创建数据库 $db 全量备份目录 $backup_dir 失败"; continue; }
            note "数据库 $db 全量备份目录 $backup_dir  不存在，创建完成";
        fi

        note "full backup $db start ..."
        mysqldump --user=${USER} --password=${PASSWORD} --flush-logs --skip-lock-tables --quick $db > $backup_file || { warning "数据库 $db 备份失败"; continue; }

        cd $backup_dir
        tar -cPzf $filename".tar.gz" $filename".sql"
        rm -f $backup_file
        chown -fR mysql:mysql $backup_dir

        note "数据库 $db 备份成功";
        note "full backup $db end."
    done
}

increment_backup() {
    local StartTime=`date "-d $INCREMENT_INTERVAL ago" +"%Y-%m-%d %H:%M:%S"`
    local DELETE_BINLOG_END_TIME=`date "-d $DELETE_BINLOG_TIME ago" +"%Y-%m-%d %H:%M:%S"`
    local dbs=`ls -l $DATA_DIR | grep "^d" | awk -F " " '{print $9}'`

    mysql -u$USER -p$PASSWORD -e "purge master logs before '$DELETE_BINLOG_END_TIME'" && note "delete $DELETE_BINLOG_TIME days before log";

    filename=`cat $BIN_INDEX | awk -F "/" '{print $2}'`
    for i in $filename
    do
        for db in $dbs
        do
            local backup_dir=$BACKUP_DIR"/increment/"$db
            local filename=$db"."$TIME
            local backup_file=$backup_dir"/"$filename".sql"

            if [ ! -d $backup_dir ]
            then
                mkdir -p $backup_dir || { error "创建数据库 $db 增量备份目录 $backup_dir 失败"; continue; }
                note "数据库 $db 增量备份目录 $backup_dir  不存在，创建完成";
            fi

            note "increment backup $db form time $StartTime start ..."

            mysqlbinlog -d $db --start-datetime="$StartTime" $DATA_DIR/$i >> $backup_file || { warning "数据库 $db 备份失败"; continue; }

            note "increment backup $db end."
        done
    done

    for db in $dbs
    do
        local backup_dir=$BACKUP_DIR"/increment/"$db
        local filename=$db"."$TIME
        local backup_file=$backup_dir"/"$filename".sql"

        cd $backup_dir
        tar -cPzf $filename".tar.gz" $filename".sql"
        rm -f $backup_file

        note "数据库 $db 备份成功";
    done
}

case "$1" in
    full)
        full_backup
    ;;
    increment)
        increment_backup
    ;;
    *)
        exit 2
    ;;
esac

exit 1