熱衷于分享各種干貨知識，大家有想看或者想學的可以評論區留言，秉承著“開源知識來源于互聯網，回歸于互聯網”的理念，分享一些日常工作中能用到或者頻率比較的內容，希望大家能夠喜歡，并提出寶貴地意見，我們一起提升，守住自己的飯碗。

一、一鍵備份

[root@myoracle ~]# chmod +x Xtrabackup.sh

[root@myoracle ~]# sh -x Xtrabackup.sh

腳本內容:

說明：此腳本可以進行全量和增量的物理備份

cat Xtrabackup.sh

#!/bin/bash

# 定義變量

BACKUP_BASE_DIR="/data/backup"

DATE_STR=$(date +%F)

TIME_STR=$(date +%H%M%S)# 添加時間戳

DAY_OF_WEEK=$(date +%u)

FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_full_${DATE_STR}"

INCREMENTAL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_incr_${DATE_STR}_${TIME_STR}"

LOG_FILE="${BACKUP_BASE_DIR}/db_backup_${DATE_STR}_${TIME_STR}.log"

MYSQL_CONF="/etc/my.cnf"

MYSQL_USER="root"

MYSQL_PASSWORD="123456"

MYSQL_SOCKET="/tmp/mysql.sock"

# 上一次備份目錄（可以是全備份或上一次增量備份）

LAST_BACKUP_DIR="${BACKUP_BASE_DIR}/db_last"

# 判斷是否是周一，如果是，則進行全量備份；否則進行增量備份

if["$DAY_OF_WEEK"-eq 1];then

BACKUP_DIR=$FULL_BACKUP_DIR

BACKUP_TYPE="FULL"

INCREMENTAL_OPTION=""

else

if[-L "$LAST_BACKUP_DIR"]&&[-d "$(readlink -f $LAST_BACKUP_DIR)"]&&[-f "$(readlink -f $LAST_BACKUP_DIR)/xtrabackup_checkpoints"];then

BACKUP_DIR=$INCREMENTAL_BACKUP_DIR

BACKUP_TYPE="INCREMENTAL"

INCREMENTAL_OPTION="--incremental-basedir=$(readlink -f $LAST_BACKUP_DIR)"

else

BACKUP_DIR=$FULL_BACKUP_DIR

BACKUP_TYPE="FULL"

INCREMENTAL_OPTION=""

fi

fi

# 創建新的備份目錄

mkdir -p $BACKUP_DIR

# 執行 xtrabackup 備份命令

xtrabackup --defaults-file=$MYSQL_CONF \

--backup \

--target-dir=$BACKUP_DIR \

$INCREMENTAL_OPTION \

--user=$MYSQL_USER \

--password=$MYSQL_PASSWORD \

--socket=$MYSQL_SOCKET \

> $LOG_FILE 2>&1

# 檢查備份是否成功

if[ $?-eq 0];then

echo "${BACKUP_TYPE} backup completed successfully at $(date)"| tee -a $LOG_FILE

else

echo "${BACKUP_TYPE} backup failed at $(date)"| tee -a $LOG_FILE

exit1

fi

# 更新 LAST_BACKUP_DIR 鏈接指向最新備份目錄

ln -sfn $BACKUP_DIR $LAST_BACKUP_DIR

# 清理3天以前的備份

find $BACKUP_BASE_DIR -type d -name "db_full_*"-mtime +3-exec rm -rf {} \;

find $BACKUP_BASE_DIR -type d -name "db_incr_*"-mtime +3-exec rm -rf {} \;

find $BACKUP_BASE_DIR -type f -name "db_backup_*.log"-mtime +3-exec rm -f {} \;

echo "Cleanup of backups older than 3 days completed at $(date)"| tee -a $LOG_FILE

添加定時任務：

crontab -e

# 每周一凌晨12點進行全量備份

0 0 * * 1 /root/Xtrabackup.sh

# 每周二至周日凌晨1點進行增量備份

0 1 * * 2-7 /root/Xtrabackup.sh

模擬新增數據進行增量備份

root@localhost:(none)>create database test;

root@localhost:(none)>use test;

root@localhost:test>create table test(id int);

root@localhost:test>insert into test values(1);

root@localhost:test>select * from test;

root@localhost:test>flush logs;

#繼續繼續備份

[root@myoracle ~]# sh -x Xtrabackup.sh

二、一鍵恢復

[root@myoracle ~]# pkill mysql

[root@myoracle ~]# ps -ef|grep mysql

[root@myoracle ~]# mv /data/mysql/ /data/mysql.bak

[root@myoracle ~]# chmod +x XtraRestoreall.sh

[root@myoracle ~]# sh -x XtraRestoreall.sh

腳本內容：

說明：此腳本可以進行全量和增量的物理備份恢復

cat XtraRestoreall.sh

#!/bin/bash

# 定義變量

BACKUP_BASE_DIR="/data/backup"

FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_full_2024-09-22"# 第一次全量備份的目錄

INCREMENTAL_BACKUP_DIR="${BACKUP_BASE_DIR}/db_last"# 增量備份目錄

MYSQL_DATA_DIR="/data/mysql"

LOG_FILE="${BACKUP_BASE_DIR}/db_restore_$(date +%F).log"

MYSQL_CONF="/etc/my.cnf"

MYSQL_USER="root"

MYSQL_PASSWORD="123456"

# 日志記錄函數

log(){

echo "$1"| tee -a $LOG_FILE

}

# 檢查是否存在必要文件的輔助函數

check_required_files(){

local backup_dir=$1

if[!-f "${backup_dir}/xtrabackup_checkpoints"]||[!-f "${backup_dir}/backup-my.cnf"];then

log "Error: Backup directory ${backup_dir} is missing necessary files."

return1

fi

return0

}

# 準備全量備份

prepare_full_backup(){

log "Preparing full backup..."

xtrabackup --prepare --apply-log-only --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1

if[ $?-ne 0];then

log "Failed to prepare full backup."

exit1

fi

}

# 應用增量備份

apply_incremental_backups(){

if[-d "$INCREMENTAL_BACKUP_DIR"];then

log "Applying incremental backups..."

for incr in $(ls -d ${INCREMENTAL_BACKUP_DIR}/* | sort); do

check_required_files $incr

if [ $? -eq 0 ]; then

log "Applying incremental backup: $incr"

xtrabackup --prepare --apply-log-only --target-dir=$FULL_BACKUP_DIR --incremental-dir=$incr >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then

log "Failed to apply incremental backup: $incr"

exit 1

fi

else

log "Skipping incremental backup: $incr due to missing files."

fi

done

else

log "No incremental backup directory found, skipping incremental backup application."

fi

}

# 執行最后的準備步驟

finalize_backup_preparation() {

log "Finalizing the backup preparation..."

xtrabackup --defaults-file=$MYSQL_CONF --user=$MYSQL_USER --password=$MYSQL_PASSWORD --prepare --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then

log "Failed to finalize the backup preparation."

exit 1

fi

}

# 恢復備份到數據目錄

restore_backup() {

# 停止MySQL服務

service mysql stop

if [ $? -ne 0 ]; then

log "Failed to stop MySQL service."

exit 1

fi

# 清空當前數據目錄

rm -rf $MYSQL_DATA_DIR/*

if [ $? -ne 0 ]; then

log "Failed to clear MySQL data directory."

service mysql start

exit 1

fi

# 恢復備份到數據目錄

log "Restoring backup to data directory..."

xtrabackup --defaults-file=$MYSQL_CONF --user=$MYSQL_USER --password=$MYSQL_PASSWORD --copy-back --target-dir=$FULL_BACKUP_DIR >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then

log "Failed to restore backup to data directory."

service mysql start

exit 1

fi

# 修復文件權限

chown -R mysql:mysql $MYSQL_DATA_DIR

if [ $? -ne 0 ]; then

log "Failed to set proper file permissions."

service mysql start

exit 1

fi

# 啟動MySQL服務

service mysql start

if [ $? -eq 0 ]; then

log "Database restored successfully and MySQL service started."

else

log "Failed to start MySQL service after restoration."

exit 1

fi

}

# 主函數

main() {

# 判斷是否存在第一次全量備份

check_required_files $FULL_BACKUP_DIR

if [ $? -ne 0 ]; then

log 'Error: No valid first full backup directory found.'

exit 1

fi

# 準備全量備份

prepare_full_backup

# 應用增量備份

apply_incremental_backups

# 執行最后的準備步驟

finalize_backup_preparation

# 恢復備份

restore_backup

}

# 執行主函數main

文中的概念來源于互聯網，如有侵權，請聯系我刪除。

歡迎關注公眾號：小周的數據庫進階之路，一起交流數據庫、中間件和云計算等技術。如果覺得讀完本文有收獲，可以轉發給其他朋友，大家一起學習進步！感興趣的朋友可以加我微信，拉您進群與業界的大佬們一起交流學習。

原文來源：https://mp.weixin.qq.com/s/F3c_rEibKFUszNpueIO04w