• 需求背景
• 備份和還原的拓撲圖
• 邏輯備份的shell腳本
• 邏輯還原的shell腳本

需求背景

數據庫備份的重要性就不再贅述了。前段時間，我們的一個數據庫，由於一些網絡安全組的配置錯誤，導致被黑客攻擊黑掉，數據表全部被刪掉了，如果想要數據就要給黑客指定的賬號充比特幣，更詳細的信息可以參考我之前分享的一篇文章《MySQL被黑掉的一次記錄》。

試想一下，如果被黑掉的數據庫沒有備份，我們該怎麼辦？不就徹底涼涼了。所以，數據庫的備份很重要。

數據庫的備份和還原是數據庫日常維護工作中最為常見的需求，對於MySQL數據庫的備份和還原，你真正的了解了嗎？接下來我們一起來看下備份和還原的一些知識點吧。

備份和還原的拓撲圖

總結了一些MySQL數據庫備份的方式，以及各個備份方式的具體參數，詳情見下圖：(如果想獲取原文件，可以加”coder-home”留言即可。)

mysql備份與還原

邏輯備份的shell腳本

這裡分享一個我平時用到的數據庫邏輯備份腳本。採用的是MySQL原始自帶的邏輯備份命令mysqldump。它的效率和第三方插件xtrabackup相比雖然差些，但是對於一些常見的備份需求，它還是可以滿足大多數備份需求的。

在下面的這個腳本中，對數據庫進行了兩次備份。

• 一次是分庫備份：即一個數據庫(schema)一個備份文件。
• 另外一次是分庫分表備份：即一個表一個備份文件，這樣的備份方式，對於以後還原的時候只還原數據庫下面的某一個表十分的方便，而不用在整個數據庫的備份文件中摘出來對應的表。

大家可以根據自己的需求，自行修改，刪除冗餘的備份邏輯，或者增加自己的備份邏輯。

#!/bin/sh

#--------------------------------------------------
# 
# CreatDate: 2020/03/18 
# Desc:      備份數據庫腳本
#--------------------------------------------------

BACKUP_DIR=$1
HOSTNAME=$2
USERNAME=$3
USERPASSWORD=$4
MAIL_RECIPIENT=$5

if [[ ! -d $BACKUP_DIR ]]; then
	echo "存放備份文件的目錄不存在！"
	exit 5
elif [[ $HOSTNAME"x" == "x" ]]; then
	echo "數據所在主機名稱或IP不能為空！"
	exit 10
elif [[ $USERNAME"x" == "x" ]]; then
	echo "備份數據庫的用戶名稱不能為空！"
	exit 15
elif [[ $USERPASSWORD"x" == "x" ]]; then
	echo "備份數據庫的用戶密碼不能為空！"
	exit 20
elif [[ $MAIL_RECIPIENT"x" == "x" ]]; then
	echo "收件人郵箱地址不能為空！"
	exit 25
fi


LOGINCOMMAND="mysql -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"
DUMPCOMMAND="mysqldump -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"

# 排除mysql,sys和以_schema結尾的數據庫
DATABASE_NAME_LIST=`$LOGINCOMMAND -e "show databases;" |egrep -v "*_schema|mysql|sys" | sed '1d' `


if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d) ]]; then
	echo "備份日期目錄不存在，需要創建該日期目錄"
        mkdir -p $BACKUP_DIR/$(date +%Y%m%d)
fi

BACKUP_LOG_FILE=$BACKUP_DIR/$(date +%Y%m%d)/db_backup_$(date +%Y%m%d).log
if [[ ! -f $BACKUP_LOG_FILE ]]; then
	touch $BACKUP_LOG_FILE
fi

echo "======================分庫備份開始於：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE

# 分庫備份
for dbname in $DATABASE_NAME_LIST
do
	echo "備份$dbname數據庫開始於：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
	
	$DUMPCOMMAND --flush-privileges 
			--master-data=2 
			--flush-logs 
			--single-transaction 
			--triggers 
			--routines 
			--events 
			--databases $dbname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/${dbname}.$(date +%Y%m%d%H%M%S).sql.gz
	
	if [[ $? -ne 0 ]]; then
		echo "ERROR: 備份$dbname數據庫失敗於：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
		continue
	else
		echo "備份$dbname數據庫結束於：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
	fi
done
echo "======================分庫備份結束於：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE


echo "======================分庫分表備份開始於：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
# 分庫分表備份
for dbname in $DATABASE_NAME_LIST
do
	TABLE_NAME_LIST=`$LOGINCOMMAND -e "show tables from $dbname;" | sed '1d' `
	if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d)/$dbname/ ]]; then
		echo "要備份的數據庫名稱目錄不存在，需要創建該目錄" >> $BACKUP_LOG_FILE
		mkdir -p $BACKUP_DIR/$(date +%Y%m%d)/$dbname
	fi
	for tabname in $TABLE_NAME_LIST
	do
		$DUMPCOMMAND --flush-privileges 
                        --master-data=2 
                        --single-transaction 
                        --triggers 
                        --routines 
                        --events 
                        $dbname $tabname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/$dbname/${dbname}.${tabname}.$(date +%Y%m%d%H%M%S).sql.gz
		
		if [[ $? -ne 0 ]]; then
			echo "ERROR: 備份$dbname.$tabname的時候失敗於：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
			continue
		fi
	done
done
echo "======================分庫分表備份結束於：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE


# 獲取日誌文件的名稱和目錄
BACKUP_LOG_FILE_BASENAME=`echo $(basename ${BACKUP_LOG_FILE})`
BACKUP_LOG_FILE_DIRNAME=`echo $(dirname ${BACKUP_LOG_FILE})`

# 對生成的日誌文件打包壓縮，便於以附件的方式發送郵件。
tar -zcvf ${BACKUP_LOG_FILE_BASENAME}.tar.gz -C $BACKUP_LOG_FILE_DIRNAME $BACKUP_LOG_FILE_BASENAME


MAIL_SUBJECT_INFO="INFO: DB backup successfully"
MAIL_CONTENT_INFO="DB backup successfully, detail info pls refer attached log file."

# Send email
echo "$MAIL_CONTENT_INFO" | mail -s "$MAIL_SUBJECT_INFO" -a "${BACKUP_LOG_FILE_BASENAME}.tar.gz" "$MAIL_RECIPIENT"

exit 0

腳本在調用的時候，是在crontab中配置的定時任務，示例如下：

30 1 * * * /root/backup_mysql_db.sh /data/mysql_backup_dir 192.168.5.100 root root

上述定時任務的含義是：每天的凌晨1:30分執行/root目錄下面的數據庫的腳本backup_mysql_db.sh。這個shell腳本在調用的時候，後面需要傳入5個參數，分別為：

1. BACKUP_DIR：生成的備份文件存放的目錄在哪裡
2. HOSTNAME：要備份的數據庫所在的服務器IP地址
3. USERNAME：執行備份的時候，連接到MySQL數據庫的用戶名稱
4. USERPASSWORD：執行備份的時候，連接到MySQL數據庫的用戶對應的密碼
5. MAIL_RECIPIENT：數據庫備份完成後，接受通知郵件的收件人郵箱地址。

備份任務執行完成後，會有對應的郵件發送給配置的郵箱。

邏輯還原的shell腳本

數據庫的還原腳本就比較簡單，只要選擇對應需要還原的數據庫或表就可以了，選擇好對應的待還原的SQL文件。然後執行類似於如下的命令即可。

mysql -uroot -p -D xxxx < xxxx_backup.sql