mysqldump5.7以下版本多執行緒備份單表

作者：sylar版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

【背景說明】

mysqldump適用於備份單表，或者數量級較小的庫的備份。一般情況下innobackupex備份數量級大的庫，速度是很快的。但是其瓶頸在於如果業務需要多例項部分物件遷移到新的例項裡，此時就無法滿足該情況。（mysqldumper在此不做討論）。

下面簡單列舉mysqldump適用的場景：

備份多個單表
備份一個或多個庫
備份儲存過程、自定義函式或事件
只備份資料不備份表結構
只備份表結構不備份資料
其他

mysqldump雖然使用起來比較靈活，但是它無法實現併發備份，故本文描述的就是實現如何用mysqldump實現併發備份

【思路說明】

把需要備份的一個庫或多個庫，提取這些庫下面所有的表進行一個個備份：這樣可以利用指令碼進行多執行緒備份這些單表，從而實現庫級的併發備份。

【具體指令碼】

點選(此處)摺疊或開啟

#!/bin/bash
#註釋：mysqldump多執行緒備份多表
#Auther:cyt
#date:2016-06-23
#按照多例項迴圈框架
function instance()
{
for port in `ps -ef | grep -v -E "mysqld_safe|awk" | awk '/mysqld /,/port=/''{for(i=1;i<=NF;i++){if($i~/port=/) print gsub(/--port=/,""),$i}}' | awk '{print $2}'`
do
##避免迴圈的port和sock不匹配
sock=`ps -ef | grep "${port}"| grep -v -E "mysqld_safe|awk" | awk '/mysqld /,/socket=/''{for(i=1;i<=NF;i++){if($i~/socket=/) print gsub(/--socket=/,""),$i}}' | awk '{print $2}'`
#由於該指令碼是並行備份，以防由於繁忙，導致獲取不到dump連線，故將該引數調大（備份完後會調小）
mysql -u$DB_USER -p$DB_PASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL net_write_timeout=1800";
#呼叫輸出備份命令的日誌函式
log
echo "-----埠號為"$port"的mysql例項開始按表併發備份:開始時間為"`date "+%Y-%m-%d %H:%M:%S"`
#呼叫備份函式
dumpAllTable
#計算備份所用時間
END=`date "+%Y-%m-%d %H:%M:%S"`
END_T=`date -d "$END" +%s`
TIME_INVENTAL_M=$[($END_T-$BEGIN_T)/60]
TIME_INVENTAL_S=$[($END_T-$BEGIN_T)%60]
echo '-----埠號為'$port'的mysql例項於' $END '備份完成，使用時間為 '$TIME_INVENTAL_M'分鐘'$TIME_INVENTAL_S'秒'
#呼叫tardump函式，對備份檔案進行壓縮，注意本次壓縮會刪掉原檔案
tardump
#將引數改為預設，以防耗盡記憶體
mysql -u$DB_USER -p$DB_PASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL net_write_timeout=60";
done
}
#將要備份的單表從大到小輸出到日誌裡面
function log()
{
BACKUP_DIR=/data/backup/$DATE/$port;
mkdir -p $BACKUP_DIR
#過濾掉MySQL自帶的DB
if [ -e ${BACKUP_DIR}/cyt.log ];
then rm -rf ${BACKUP_DIR}/cyt.log;
fi;
for a in `mysql -u$DB_USER -p$DB_PASSWORD --socket=$sock --host=$host -BN -e"show databases;" |sed '/^performance_schema$/'d|sed '/^mysql/'d |sed '/^information_schema$/'d|sed '/^information_schema$/'d|sed '/^test$/'d|sed '/^sys$/'d `
do
mkdir -p ${BACKUP_DIR}/${a}
for j in `mysql -u$DB_USER -p$DB_PASSWORD --host= --socket=$sock --host=$host -BN -e "select table_name from information_schema.tables where table_schema='${a}' order by table_rows desc;"`
do
echo 'mysqldump -u'$DB_USER' -p'$DB_PASSWORD' --socket='$sock' --host='$host' --set-gtid-purged=OFF -c --single_transaction=OFF -q --skip-add-locks ' ${a} ${j}'>'$BACKUP_DIR'/'${a}'/'${j}'.sql'>>$BACKUP_DIR/cyt.log;
done
done
}
#呼叫函式log，檢視log日誌呼叫併發函式實現多執行緒備份
function dumpAllTable()
{
local schemaFile="${BACKUP_DIR}/cyt.log"
#最大的表先備份（因多程式併發，最短完成時間依賴於最大表的完成）
allTable=`cat $schemaFile | wc -l`
i_import=0
declare -a array_cmds
i_array=0
while read file; do
i_import=`expr $i + 1`
array_cmds[i_array]="${file}"
i_array=`expr ${i_array} + 1`
done < ${BACKUP_DIR}/cyt.log
execConcurrency "${threadsNum}" "${array_cmds[@]}"
}
#併發函式
function execConcurrency()
{
#併發資料量
local thread=$1
#併發命令
local cmd=$2
#定義管道，用於控制併發執行緒
tmp_fifofile="/tmp/$$.fifo"
mkfifo $tmp_fifofile
#輸入輸出重定向到檔案描述符6
exec 6<>$tmp_fifofile
rm -f $tmp_fifofile
#向管道壓入指定資料的空格
for ((i=0;i<$thread;i++)); do
echo
done >&6
#遍歷命令列表
while [ "$cmd" ]; do
#從管道取出一個空格（如無空格則阻塞，達到控制併發的目的）
read -u6
#命令執行完後壓回一個空格
{ eval $2;echo >&6; } & #> /dev/null 2>&1 &
shift
cmd=$2
done
#等待所有的後臺子程式結束
wait
#關閉df6
exec 6>&-
}
#壓縮備份檔案
function tardump()
{
#使用tar壓縮
if [ -d ${BACKUP_DIR} ] && [ -n ${port} ]
then
echo "-----開始進行壓縮埠號為"$port"的mysql例項的備份：開始時間"`date "+%Y-%m-%d %H:%M:%S"`
cd $BACKUP_DIR;
for b in `find $BACKUP_DIR -maxdepth 1 -type d ! -iname "${port}*" ! -iname '*.sql' ! -iname '*tar.gz' `
do
c=`basename $b`
tar -zcvf $c'_'$(date +%F_%H-%M).tar.gz $c --remove-files > /dev/null
done
else echo "沒有可以進行壓縮的檔案";
fi;
echo "-----壓縮埠號為"$port"的mysql例項的備份檔案：結束時間"`date "+%Y-%m-%d %H:%M:%S"`
}
#主函式
function main()
{
#獲取本地IP地址
host=`ifconfig | grep 'inet addr:' | grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'`
DATE=`date +%F`
#本次備份mysqldump --host --socket如果是本地使用者備份，建議去掉host；（多例項本地使用者密碼問題需注意）
#資料庫使用者
DB_USER='cyt'
#資料庫使用者對應的密碼
DB_PASSWORD='cyt'
#記錄開始的時間
BEGIN=`date "+%Y-%m-%d %H:%M:%S"`
BEGIN_T=`date -d "$BEGIN" +%s`
echo '--------------開始按表併發備份:開始時間為 '$BEGIN
#設定併發備份的執行緒數
threadsNum=10
#呼叫instance函式
instance
echo '--------------backup all database successfully！！！結束時間:' `date "+%Y-%m-%d %H:%M:%S"`
}
main

【指令碼說明】

由於該指令碼是並行備份，以防由於繁忙，導致獲取不到dump連線，故將該引數調大（該資料庫版本是5.6.19，指令碼在備份完後會調小）

mysql -u$DBUSER -p$DBPASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL netwritetimeout=1800";

由於想要利用併發函式，將要使用的命令匯入到${BACKUP_DIR}/cyt.log日誌裡，然後透過併發函式execConcurrency和陣列dumpAllTable來實現本指令碼的目的
本指令碼可以實現多例項備份，如果多例項備份的使用者名稱和密碼不同，可以使用case命令,下面是簡單舉例
點選(此處)摺疊或開啟
1. if [ $port -eq 3306 ]; then
2. case $IP in
3. '10.240.5.11')
4. DB_USER='CYT1'
5. DB_PASSWORD='1'
6. ;;
7. '10.240.5.12')
8. DB_USER='CYT2'
9. DB_PASSWORD='2'
10. ;;
11. '10.240.5.13')
12. DB_PASSWORD='3'
13. ;;
14. esac
15. else
16. DB_PASSWORD='4'
17. fi

mysqldump5.7以下版本實現併發備份