数据库备份恢复方案（MySQL 5.7）

一、数据库部署方案

在生产环境中，数据库部署的拓扑结构至关重要，以确保数据的高可用性和可靠性。本方案采用主从复制（Replication）来实现数据的同步备份。具体部署如下：

+-----------------------+             +-----------------------+
|      Docker           |             |      Docker           |
|    Container 1        |             |    Container 2        |
|   +---------------+   |             |   +---------------+   |
|   |     db1       |   |             |   |     db2       |   |
|   |               |   |             |   |               |   |
|   |     Port:     |   |             |   |     Port:     |   |
|   |     3306      |   |             |   |     3306      |   |
|   +---------------+   |             |   +---------------+   |
|          ^             |             |         ^             |
|          | Replication |             |         | Replication |
|          |             |             |         |             |
+-----------------------+             +-----------------------+

说明

主库 (db1)：主要用于处理数据库的写操作，监听3306端口。
从库 (db2)：用于处理数据库的读操作，并从主库同步数据，监听3306端口。

二、数据备份方案

数据备份是确保数据库安全的重要措施。本方案采用了全备份与增量备份相结合的策略，以确保在数据丢失或损坏时能够快速恢复。

备份策略

每周全备份：每周六对数据库进行完整备份。
每日增量备份：在每天（除周六外）进行增量备份，记录自上次全备份以来的变化数据。
历史备份管理：每周六进行全备份前，将上一个周期的全备份和增量备份进行压缩并迁移至历史备份目录，保留最近30个周期的备份数据。

备份脚本

备份脚本 (backup_database.sh) 定时任务（crontab）配置如下：

# 每周六凌晨0:10进行全备份，其他时间进行增量备份
10 0 * * 6 /bin/bash /backup/database/backup_database.sh backup

备份脚本的主要功能包括：

执行全备份和增量备份
将旧的备份文件迁移至历史备份目录并压缩
日志记录备份过程的详细信息

三、数据恢复方案

数据恢复是确保数据库在发生数据丢失或损坏时能够快速恢复的关键环节。以下是详细的恢复步骤和脚本说明。

1. 准备工作

在恢复数据库之前，需要进行以下准备工作：

安装数据库管理系统：确保目标数据库服务器已安装相应的数据库管理系统（如 MySQL）。
检查磁盘空间：确保目标数据库服务器有足够的磁盘空间以存储恢复的数据。
获取备份文件：确保备份文件已可用，并且能够访问。

2. 恢复脚本 `restore_database.sh`

为了简化恢复过程，提供了恢复脚本 restore_database.sh，该脚本包括全备份恢复和增量备份恢复的功能。

使用说明

恢复完整备份：
- 确保数据库处于关闭状态。
- 执行恢复脚本，指定 full 操作：
```
/bin/bash /backup/database/restore_database.sh full
```
恢复增量备份：
- 确保数据库处于关闭状态。
- 执行恢复脚本，指定 inc 操作，并提供备份ID：
```
/bin/bash /backup/database/restore_database.sh inc <backup_id>
```

3. 重新启动数据库

恢复完成后，重新启动数据库服务，并检查数据是否正确恢复。

4. 日志检查

查看恢复日志，确保恢复过程没有出现错误。恢复日志文件位于 /backup/database/restore.log。

通过以上备份和恢复方案，可以有效地保证数据库在出现故障时能够快速恢复，确保业务的连续性和数据的安全性。

5. 附件

5.1 backup_database.sh

#!/bin/bash

#
# 备份策略
#
# 1. 每周六进行完整备份
# 2. 每天进行增量备份
# 3. 每周六进行完整备份前，将上个周期的全备和增量备份进行压缩并迁移至历史备份目录
# 4. 每周六进行完整备份前，将上个周期的全备和增量备份迁移至历史备份目录
# 5. 保留最近30个周期的备份数据
#
# crontab配置
# 10 0 * * 6 /bin/bash /backup/database/backup_database.sh backup

export WEEK=$(date +%V)
export YEAR=$(date +%Y)
export WEEKDAY=$(date +%w)

export BACKUP_HOME=/backup/database
export BACKUP_HIST=$BACKUP_HOME/history
export BACKUP_LOG=$BACKUP_HOME/backup.log

[ ! -f $BACKUP_HOME/.id ] && echo ${WEEK} >$BACKUP_HOME/.id
export CURRENT_ID=$(cat "$BACKUP_HOME/.id")
export ID=$((CURRENT_ID + 1))

export BACKUP_DIR=$BACKUP_HOME/backup/${ID}
export BACKUP_FULL_DIR=$BACKUP_DIR/full
export BACKUP_INC_DIR=$BACKUP_DIR/inc

export BACKUP_PROGRAM=/usr/bin/xtrabackup
export CONFIG_FILE=/etc/mysql/my.cnf
export DBA_USER=root
export DBA_PASSWORD=PASSWORD
export DBA_HOST=localhost
export today=$(date +%Y%m%d)

function write_log() {
    msg="$(date '+%Y-%m-%d %H:%M:%S') INFO $1"
    if [ "$1" == "ERROR" ]; then
        msg="$(date '+%Y-%m-%d %H:%M:%S') ERROR $2"
    fi

    echo ${msg}
    echo ${msg} >>$BACKUP_LOG
}

# 全备数据迁移
function relocate_history_backup() {

    local LAST_BACKUP_HOME=$BACKUP_HOME/backup/${CURRENT_ID}
    local LAST_FULL_BACKUP_DIR=$LAST_BACKUP_HOME/full
    local LAST_INC_BACKUP_DIR=$LAST_BACKUP_HOME/inc

    write_log "begin relocate history backup"
    write_log "last full backup directory:$LAST_FULL_BACKUP_DIR"
    write_log "last incremental backup directory:$LAST_INC_BACKUP_DIR"

    if [ ! -d $LAST_FULL_BACKUP_DIR ]; then
        write_log "ERROR" "full backup dir:$LAST_FULL_BACKUP_DIR is not exist!"
    fi

    if [ ! -d $LAST_INC_BACKUP_DIR ]; then
        write_log "ERROR" "incremental backup dir:$LAST_INC_BACKUP_DIR is not exist!"
    fi

    if [ ! -d $LAST_FULL_BACKUP_DIR ] && [ ! -d $LAST_INC_BACKUP_DIR ]; then
        write_log "ERROR" "full backup dir:$LAST_FULL_BACKUP_DIR and incremental backup dir:$LAST_INC_BACKUP_DIR is not exist!"
        return
    fi

    [ ! -d $BACKUP_HIST ] && mkdir -p $BACKUP_HIST

    local now=$(date +%Y%m%d%H%M%S)
    if [ -d $LAST_FULL_BACKUP_DIR ]; then
        local full_backup_zip_file="full_${now}_${CURRENT_ID}.tar.gz"
        cd $LAST_BACKUP_HOME
        tar -zcvf $full_backup_zip_file full
        mv $full_backup_zip_file $BACKUP_HIST
        rm -rf full
    fi

    if [ -d $LAST_INC_BACKUP_DIR ]; then
        local inc_backup_zip_file="inc_${now}_${CURRENT_ID}.tar.gz"
        cd $LAST_BACKUP_HOME
        tar -zcvf $inc_backup_zip_file inc
        mv $inc_backup_zip_file $BACKUP_HIST
        rm -rf inc
    fi
    rm -rf $LAST_BACKUP_HOME

    write_log "relocate history backup succeed!"
}

function full_backup() {
    write_log "begin full backup"
    write_log "full backup dir is:$BACKUP_FULL_DIR"

    if [ ! -d $BACKUP_FULL_DIR ]; then
        write_log "full backup dir:$BACKUP_FULL_DIR is not exist,begin create the directory..."
        mkdir -p $BACKUP_FULL_DIR
        write_log "full backup dir created succeed!"
    fi

    echo "$BACKUP_PROGRAM --defaults-file=$CONFIG_FILE --user=$DBA_USER --password=$DBA_PASSWORD --host=$DBA_HOST --backup --target-dir=$BACKUP_FULL_DIR 2>&1 | tee -a $BACKUP_LOG"
    $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
        --user=$DBA_USER --password=$DBA_PASSWORD \
        --host=$DBA_HOST --backup \
        --target-dir=$BACKUP_FULL_DIR 2>&1 | tee -a $BACKUP_LOG
    if [ $? -eq 0 ]; then
        write_log "full backup succeed!"
    else
        write_log "ERROR" "full backup failed!"
    fi
}

function incremental_backup() {
    write_log "begin incremental backup"
    write_log "incremental backup dir is:$BACKUP_INC_DIR"

    if [ ! -d $BACKUP_INC_DIR ]; then
        write_log "incremental backup dir:$BACKUP_INC_DIR is not exist,begin create the directory..."
        mkdir -p $BACKUP_INC_DIR
        write_log "incremental backup dir created succeed!"
    fi

    # $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE --user=$DBA_USER --password=$DBA_PASSWORD --host=$DBA_HOST --backup --target-dir=$BACKUP_INC_DIR 2>&1 | tee -a $BACKUP_LOG
    $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
    --user=$DBA_USER --password=$DBA_PASSWORD \
    --host=$DBA_HOST --backup \
    --target-dir=$BACKUP_INC_DIR/$today --incremental-basedir=$BACKUP_FULL_DIR

    if [ $? -eq 0 ]; then
        write_log "incremental backup succeed!"
    else
        write_log "ERROR" "incremental backup failed!"
    fi
}

function usage() {
    echo "########################################"
    echo "Usage: $0 [backup|full|inc|relocate]"
    echo "backup: backup database"
    echo "full: full backup"
    echo "inc: incremental backup"
    echo "relocate: relocate history backup"
    echo "########################################"

    exit 1
}

action=$1
case $action in
backup)
    write_log "begin backup database"
    if [ $WEEKDAY -eq 6 ]; then
        write_log "begin relocate history backup"
        relocate_history_backup
        write_log "relocate history backup succeed!"

        write_log "begin full backup"
        full_backup
        write_log "full backup succeed!"
    else
        write_log "begin incremental backup"
        incremental_backup
        write_log "incremental backup succeed!"
    fi

    if [ $WEEKDAY -eq 5 ]; then
        write_log "update backup id to $ID"
        echo $ID >$BACKUP_HOME/.id
    fi
    ;;
full)
    write_log "begin full backup"
    full_backup
    write_log "full backup succeed!"
    ;;
inc)
    write_log "begin incremental backup"
    incremental_backup
    write_log "incremental backup succeed!"
    ;;
relocate)
    write_log "begin relocate history backup"
    relocate_history_backup
    write_log "relocate history backup succeed!"
    ;;
*)
    usage
    ;;
esac

5.2 restore_database.sh

#!/bin/bash

export BACKUP_HOME=/backup/database
export RESTORE_DIR=$BACKUP_HOME/restore
export BACKUP_PROGRAM=/usr/bin/xtrabackup
export CONFIG_FILE=/etc/mysql/my.cnf
export DBA_USER=root
export DBA_PASSWORD=PASSWORD
export DBA_HOST=localhost

function write_log() {
    msg="$(date '+%Y-%m-%d %H:%M:%S') INFO $1"
    if [ "$1" == "ERROR" ]; then
        msg="$(date '+%Y-%m-%d %H:%M:%S') ERROR $2"
    fi

    echo ${msg}
    echo ${msg} >>$BACKUP_HOME/restore.log
}

function usage() {
    echo "########################################"
    echo "Usage: $0 [full|inc]"
    echo "full: restore from full backup"
    echo "inc: restore from incremental backup"
    echo "########################################"

    exit 1
}

function full_restore() {
    write_log "begin full restore"
    write_log "restore directory is:$RESTORE_DIR"

    if [ ! -d $RESTORE_DIR ]; then
        write_log "restore dir:$RESTORE_DIR is not exist,begin create the directory..."
        mkdir -p $RESTORE_DIR
        write_log "restore dir created succeed!"
    fi

    echo "$BACKUP_PROGRAM --defaults-file=$CONFIG_FILE --user=$DBA_USER --password=$DBA_PASSWORD --host=$DBA_HOST --copy-back --target-dir=$RESTORE_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log"
    $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
        --user=$DBA_USER --password=$DBA_PASSWORD \
        --host=$DBA_HOST --copy-back \
        --target-dir=$RESTORE_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log

    if [ $? -eq 0 ]; then
        chown -R mysql:mysql $RESTORE_DIR
        write_log "full restore succeed!"
    else
        write_log "ERROR" "full restore failed!"
    fi
}

function incremental_restore() {
    write_log "begin incremental restore"
    write_log "restore directory is:$RESTORE_DIR"

    if [ ! -d $RESTORE_DIR ]; then
        write_log "restore dir:$RESTORE_DIR is not exist,begin create the directory..."
        mkdir -p $RESTORE_DIR
        write_log "restore dir created succeed!"
    fi

    local FULL_BACKUP_DIR=$BACKUP_HOME/backup/$1/full
    local INC_BACKUP_DIR=$BACKUP_HOME/backup/$1/inc

    echo "$BACKUP_PROGRAM --defaults-file=$CONFIG_FILE --user=$DBA_USER --password=$DBA_PASSWORD --host=$DBA_HOST --apply-log --redo-only --target-dir=$FULL_BACKUP_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log"
    $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
        --user=$DBA_USER --password=$DBA_PASSWORD \
        --host=$DBA_HOST --apply-log --redo-only \
        --target-dir=$FULL_BACKUP_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log

    if [ $? -eq 0 ]; then
        for inc in $(ls -tr $INC_BACKUP_DIR); do
            write_log "apply incremental backup $inc"
            $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
                --user=$DBA_USER --password=$DBA_PASSWORD \
                --host=$DBA_HOST --apply-log --redo-only \
                --incremental-dir=$INC_BACKUP_DIR/$inc --target-dir=$FULL_BACKUP_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log

            if [ $? -ne 0 ]; then
                write_log "ERROR" "apply incremental backup $inc failed!"
                exit 1
            fi
        done

        $BACKUP_PROGRAM --defaults-file=$CONFIG_FILE \
            --user=$DBA_USER --password=$DBA_PASSWORD \
            --host=$DBA_HOST --apply-log --target-dir=$FULL_BACKUP_DIR 2>&1 | tee -a $BACKUP_HOME/restore.log

        if [ $? -eq 0 ]; then
            write_log "incremental restore succeed!"
            chown -R mysql:mysql $FULL_BACKUP_DIR
        else
            write_log "ERROR" "final apply log failed!"
            exit 1
        fi
    else
        write_log "ERROR" "full apply log failed!"
        exit 1
    fi
}

action=$1
id=$2

case $action in
full)
    write_log "begin full restore"
    full_restore
    write_log "full restore succeed!"
    ;;
inc)
    if [ -z "$id" ]; then
        write_log "ERROR" "incremental restore requires backup id"
        usage
    fi
    write_log "begin incremental restore with backup id $id"
    incremental_restore $id
    write_log "incremental restore succeed!"
    ;;
*)
    usage
    ;;
esac

mysql5.7的备份与恢复