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

urgel_babay發表於2016-09-16
MySql

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

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

【背景說明】

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

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

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

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

【思路說明】

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

【具體指令碼】


點選(此處)摺疊或開啟

  1. #!/bin/bash
  2. #註釋：mysqldump多執行緒備份多表
  3. #Auther:cyt
  4. #date:2016-06-23
  5. #按照多例項迴圈框架
  6. function instance()
  7. {
  8.         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}'`
  9.           do
  10.              ##避免迴圈的port和sock不匹配
  11.              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}'`
  12.              #由於該指令碼是並行備份，以防由於繁忙，導致獲取不到dump連線，故將該引數調大（備份完後會調小）
  13.              mysql -u$DB_USER -p$DB_PASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL net_write_timeout=1800";
  14.              #呼叫輸出備份命令的日誌函式
  15.              log
  16.              echo "-----埠號為"$port"的mysql例項開始按表併發備份:開始時間為"`date "+%Y-%m-%d %H:%M:%S"`
  17.              #呼叫備份函式
  18.              dumpAllTable
  19.              #計算備份所用時間
  20.              END=`date "+%Y-%m-%d %H:%M:%S"`
  21.              END_T=`date -d "$END" +%s`
  22.              TIME_INVENTAL_M=$[($END_T-$BEGIN_T)/60]
  23.              TIME_INVENTAL_S=$[($END_T-$BEGIN_T)%60]
  24.              echo '-----埠號為'$port'的mysql例項於' $END '備份完成，使用時間為 '$TIME_INVENTAL_M'分鐘'$TIME_INVENTAL_S'秒'
  25.              #呼叫tardump函式，對備份檔案進行壓縮，注意本次壓縮會刪掉原檔案
  26.              tardump
  27.              #將引數改為預設，以防耗盡記憶體
  28.              mysql -u$DB_USER -p$DB_PASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL net_write_timeout=60";
  29.         done
  30. }



  34. #將要備份的單表從大到小輸出到日誌裡面
  35. function log()
  36. {
  37.             BACKUP_DIR=/data/backup/$DATE/$port;
  38.             mkdir -p $BACKUP_DIR
  39.             #過濾掉MySQL自帶的DB
  40.             if [ -e ${BACKUP_DIR}/cyt.log ];
  41.             then rm -rf ${BACKUP_DIR}/cyt.log;
  42.             fi;
  43.             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 `
  44.                  do
  45.                     mkdir -p ${BACKUP_DIR}/${a}
  46.                     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;"`
  47.                     do
  48.                           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;
  49.                     done
  50.                  done
  51. }

  53.    

  55. #呼叫函式log，檢視log日誌呼叫併發函式實現多執行緒備份
  56. function dumpAllTable()
  57. {
  58.         local schemaFile="${BACKUP_DIR}/cyt.log"
  59.         #最大的表先備份（因多程式併發，最短完成時間依賴於最大表的完成）
  60.         allTable=`cat $schemaFile | wc -l`
  61.         i_import=0
  62.         declare -a array_cmds
  63.         i_array=0
  64.         while read file; do
  65.                 i_import=`expr $i + 1`
  66.                 array_cmds[i_array]="${file}"
  67.                 i_array=`expr ${i_array} + 1`
  68.         done < ${BACKUP_DIR}/cyt.log
  69.         execConcurrency "${threadsNum}" "${array_cmds[@]}"
  70. }





  76. #併發函式
  77. function execConcurrency()
  78. {
  79.         #併發資料量
  80.         local thread=$1
  81.         #併發命令
  82.         local cmd=$2
  83.         #定義管道，用於控制併發執行緒
  84.         tmp_fifofile="/tmp/$$.fifo"
  85.         mkfifo $tmp_fifofile
  86.         #輸入輸出重定向到檔案描述符6
  87.         exec 6<>$tmp_fifofile
  88.         rm -f $tmp_fifofile
  89.         #向管道壓入指定資料的空格
  90.         for ((i=0;i<$thread;i++)); do
  91.                 echo
  92.         done >&6
  93.         #遍歷命令列表
  94.         while [ "$cmd" ]; do
  95.                 #從管道取出一個空格（如無空格則阻塞，達到控制併發的目的）
  96.                 read -u6
  97.                 #命令執行完後壓回一個空格
  98.                 { eval $2;echo >&6; } & #> /dev/null 2>&1 &
  99.                 shift
  100.                 cmd=$2
  101.         done
  102.         #等待所有的後臺子程式結束
  103.         wait
  104.         #關閉df6
  105.         exec 6>&-
  106. }

  108. #壓縮備份檔案
  109. function tardump()
  110. {
  111.     #使用tar壓縮
  112.     if [ -d ${BACKUP_DIR} ] && [ -n ${port} ]
  113.     then
  114.         echo "-----開始進行壓縮埠號為"$port"的mysql例項的備份：開始時間"`date "+%Y-%m-%d %H:%M:%S"`
  115.         cd $BACKUP_DIR;
  116.         for b in `find $BACKUP_DIR -maxdepth 1 -type d ! -iname "${port}*" ! -iname '*.sql' ! -iname '*tar.gz' `
  117.             do
  118.                 c=`basename $b`
  119.                 tar -zcvf $c'_'$(date +%F_%H-%M).tar.gz $c --remove-files > /dev/null
  120.         done
  121.      else echo "沒有可以進行壓縮的檔案";
  122.     fi;
  123.     echo "-----壓縮埠號為"$port"的mysql例項的備份檔案：結束時間"`date "+%Y-%m-%d %H:%M:%S"`
  124. }

  126. #主函式
  127. function main()
  128. {
  129.         #獲取本地IP地址
  130.         host=`ifconfig | grep 'inet addr:' | grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'`
  131.         DATE=`date +%F`
  132.         #本次備份mysqldump --host --socket如果是本地使用者備份，建議去掉host；（多例項本地使用者密碼問題需注意）
  133.         #資料庫使用者
  134.         DB_USER='cyt'
  135.         #資料庫使用者對應的密碼
  136.         DB_PASSWORD='cyt'
  137.         #記錄開始的時間
  138.         BEGIN=`date "+%Y-%m-%d %H:%M:%S"`
  139.         BEGIN_T=`date -d "$BEGIN" +%s`
  140.         echo '--------------開始按表併發備份:開始時間為 '$BEGIN
  141.         #設定併發備份的執行緒數
  142.         threadsNum=10
  143.         #呼叫instance函式
  144.         instance
  145.         echo '--------------backup all database successfully！！！結束時間:' `date "+%Y-%m-%d %H:%M:%S"`
  146. }

  148. 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



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30936525/viewspace-2125030/，如需轉載，請註明出處，否則將追究法律責任。

相關文章