Mysql分散式資料庫變更自動化部署[源碼]，mysql

Mysql分散式資料庫變更自動化部署[源碼]，mysql

Author：skate
Time：2015/03/05

 

MySQL資料庫變更自動部署

 

概述：
這個Mysql資料庫發布程式，可以自動、快速、並發的發布資料變更並記錄結果。把部署內容從部署server傳輸到所有部署database並執行部署內容。
本程式主要用於分散式資料庫部署,比如把一個部署指令碼需要同時部署到多台database上。

 

 

 

測試環境:
deploydb:
[root@deploydb skate]# ll autodeploy
total 36
-rw-r--r-- 1 root root 9378 Feb 26 20:23 autodeploy
-rw-r--r-- 1 root root  120 Feb 26 18:40 autodeploy_config.txt
drwxr-xr-x 2 root root 4096 Feb 26 18:50 ex_dbrelease
drwxr-xr-x 2 root root 4096 Feb 26 18:50 log
drwxr-xr-x 2 root root 4096 Feb 26 18:50 post_dbrelease
[root@deploydb skate]#

目錄:
ex_dbrelease:用於存放即將部署的檔案
post_dbrelease:已經成功部署的檔案
log: 記錄檔
autodeploy_config.txt:設定檔
autodeploy: 主程式

部署環境:
1.部署server到database之間ssh可聯通的.
2.rsync 被安裝在部署server和database
3.資料庫部署指令碼需要有schema首碼，如:database.tablename

使用步驟:
1.本部署程式只負責部署資料庫變更指令碼，所以需要自己事先做好備份工作

2.如果你想知道資料庫變更前後影響的行數，需要在執行部署內容前後執行“select count (*) from table”(本程式本身也會顯示變更影響的行數)

3.編輯設定檔, 設定檔共有三塊內容，deployfirst代表部署測試節點，deploynode代表其餘部署節點，ftpnode代表部署檔案需要被ftp的節點

There are three sections in this configuration file,eg:
[root@deploydb autodeploy]# vi autodeploy_config.txt
[deployfirst]
db1.server
[/deployfirst]

[deploynode]
db2.server
db3.server
[/deploynode]

[ftpnode]
db1.server
db2.server
db3.server
[/ftpnode]

4.把部署檔案放到部署server的ex_dbrelease目錄下

5.部署檔案被並發的ftp到所有指定節點上
eg:

[root@deploydb autodeploy]# sh autodeploy  -h
Usage:  [-t mode] [-u upload_dir] [-f release_file] [-r retention_time]
  -t  Operatation mode(ftp/deploy)
  -f  Release file
  -h  Display basic help
 
 
[root@deploydb autodeploy]# sh autodeploy  -t ftp

Please confirm that you have put the FTP into  directory /home/skate/autodeploy/ex_dbrelease [Y/N]: Y

The following is the transmitted release file:
********************************
t1.sql
********************************
Are you sure you want to upload the above files to all specified nodes[Y/N]? Y
[t1.sql] is transferd to dfng1db4.se2 success.

ALL release files are transferd to all specified nodes
[root@deploydb autodeploy]#

The command with ftp parameter will transfer all release files from directory ex_dbrelease.

6.嘗試部署一個節點，如果成功則會自動部署到剩餘節點上

eg:
[root@deploydb autodeploy]# sh autodeploy  -t deploy -f t1.sql
*************************************************
[2015-02-26 18:49:48]: Preparing to deploy t1.sql to [dfng1db4.se2]...
*************************************************
You deployed success this file ago on first node,Please delete file [/home/skate/autodeploy/issucess] if you want to continue.
[root@deploydb autodeploy]# rm /home/skate/autodeploy/issucess
rm: remove regular file `/home/skate/autodeploy/issucess'? y

[root@deploydb autodeploy]# sh autodeploy  -t deploy -f t1.sql
*************************************************
[2015-02-26 18:50:22]: Preparing to deploy t1.sql to [dfng1db4.se2]...
*************************************************
Warning: Using a password on the command line interface can be insecure.
[2015-02-26 18:50:22]: The release file [t1.sql] have been deployed to dfng1db4.se2 success.
The detail you refer to [/home/skate/autodeploy/log/deploy_201502261850_deploy.log].
Are you sure you want to continut deploy to remaining nodes[Y/N]? Y
Warning: Using a password on the command line interface can be insecure.
[t1.sql] is deployed success on all special nodes,the detail you refer to [/home/skate/autodeploy/log/deploy_201502261850_deploy.log]
[root@deploydb autodeploy]#

Questions:
1.部署檔案已經被成功部署.

[root@deploydb autodeploy]# sh autodeploy  -t deploy -f t1.sql
*************************************************
[2015-02-26 18:49:48]: Preparing to deploy t1.sql to [dfng1db4.se2]...
*************************************************
You deployed success this file ago on first node,Please delete file [/home/skate/autodeploy/issucess] if you want to continue.
[root@deploydb autodeploy]# rm /home/skate/autodeploy/issucess
rm: remove regular file `/home/skate/autodeploy/issucess'? y

2.部署檔案不存在
[root@deploydb autodeploy]# sh autodeploy  -t deploy -f t1.sql
[/home/skate/autodeploy/ex_dbrelease/t1.sql] not exist! Please confirm whether the file have been uploaded or deployed.
[root@deploydb autodeploy]#

3.多個部署程式同時運行確認

[root@deploydb autodeploy]# sh autodeploy
**********************************
root      8560  8520  0 20:24 pts/2    00:00:00 sh autodeploy -t ftp
root      8580  1632  0 20:24 pts/11   00:00:00 sh autodeploy
**********************************

There is a autodeploy running,continue[Y/N]? N
Input:N,Exit from autodeploy script.

 

Script：

#!/bin/sh## Author:Skate# Time:2015/02/25# Function: automate applying db scriptsCURPID=$$DIR=/home/szhao/autodeployDIRLOG=$DIR/logRELEASEDIR=$DIR/ex_dbreleasePOST_RELEASEDIR=$DIR/post_dbreleaseLOCK=$DIR/autodeploy.lockLOG=$DIRLOG/autodeploy.logCFG=$DIR/autodeploy_config.txtCFGPID=$DIR/${CURPID}_configDEPLOYLOG=$DIRLOG/deploy_`date "+%Y%m%d%H%M"`_deploy.logDATE=`date "+%Y-%m-%d %H:%M:%S"`#LFILE=/tmp/tmpsql.logRDIR=/tmp/autodeployISDEPLOY='Y'. $DIR/.PWD############################################################################## Avoid multipe deployment processes running simultaneously#############################################################################RUNNUM=`ps -ef | grep autodeploy | grep -v grep | wc -l`if [ $RUNNUM -gt 2 ];then   echo "**********************************"   ps -ef | grep autodeploy | grep -v grep   echo "**********************************"   echo  ""   read -p "There is a autodeploy running,continue[Y/N]? " isrun   case $isrun in      Y)         echo "continue run autodeploy script."       ;;      N)        echo "Input:$isrun,Exit from autodeploy script."        exit 0       ;;      *)        echo "Error input"        exit 1       ;;    esacfi############################################################################## Display usage message and exit#############################################################################usage() {  cat <<EOFUsage: $SCRIPTNAME [-t mode] [-u upload_dir] [-f release_file] [-r retention_time]  -t  Operatation mode(ftp/deploy)  -f  Release file  -h  Display basic helpEOF  exit 0}# Parse parameterswhile getopts ":t:u:f:r:h" opt; do  case $opt in    t )  OPTTYPE=$OPTARG ;;#    u )  UPLOAD=$OPTARG ;;    f )  RELEASEFILE=$OPTARG ;;#    r )  RETENTION=$OPTARG ;;    h )  usage ;;    \?)  echo "Invalid option: -$OPTARG"         echo "For help, type: $SCRIPTNAME -h"         exit 1 ;;    : )  echo "Option -$OPTARG requires two argument"         echo "For help, type: $SCRIPTNAME -h"         exit 1 ;;  esacdoneshift $(($OPTIND - 1))############################################################################## Manage local and remote release file#############################################################################if [ ! -d "$DIR" ]; then   mkdir -p $DIRfiif [ ! -d "$DIRLOG" ]; then   mkdir $DIRLOGfi  if [ ! -d "$RELEASEDIR" ]; then   mkdir $RELEASEDIRfiif [ ! -d "$POST_RELEASEDIR" ]; then   mkdir $POST_RELEASEDIRfi# Remove loacl released file ago 60 daysfind ${POST_RELEASEDIR}/ -name "*._success" -mtime +60  | xargs rm -rf# Remove local deploy log ago 10 daysfind ${DIRLOG}/ -name "*deploy.log" -mtime +10  | xargs rm -rf# Keep recently 10000 lines of autodeploy logtail -100000 $LOG > $LOGcd $DIR config_num=`find $DIR/ -name  *_config | wc -l `if [ ${config_num} -gt 0 ];thenfor pid in  `ls -l  *_config | awk '{print $9}' | awk -F_ '{print $1}'`doNUM=`ps -ef | awk '{ print $2 }' | grep -E '^${pid}$' | wc -l`if [ $NUM -eq 0 ] ; thenrm -rf $DIR/${pid}_configfidonefi############################################################################## Upload release file to all nodes############################################################################## sync file to remotefunction multi_ftp{HOST=$1LFILE=$2rsync -avP --bwlimit=1000 $RELEASEDIR/$LFILE $HOST:$RDIR/ >/dev/null 2>&1if [  $? == 0 ] ; then   echo "[$LFILE] is transferd to $HOST success."else   echo "[$LFILE] is transferd to $HOST fail."   exit 1fiexit 0}function multi_deploy{host=$1releasefile=$2ssh $host "mysql -u$USER -p$PASSWD -vvv df -e \"source $releasefile;\""if [  $? == 0 ] ; then   return 0else   return 1fi}# parse configuration filefunction readcfg(){FIELD=$1first=`sed  -n "/\[$FIELD\]/=" $CFG`last=`sed  -n "/\[\/$FIELD\]/=" $CFG`#echo $first,$lastsed -n "$((first+1)),$((last-1))p"  $CFG > $CFGPIDsed -i '/^$/d' $CFGPID}# Simulation of multi threadexec 6>&-tmp_fifofile="$DIR/$.fifo"mkfifo $tmp_fifofileexec 6<>$tmp_fifofilerm $tmp_fifofilethread=40for (( i=0;i<=$thread;i++ )); doechodone >&6# ftp modeif [ 'x'$OPTTYPE = 'xftp' ] ; thenecho ""read -p "Please confirm that you have put the FTP into  directory ${RELEASEDIR} [Y/N]: " nextcase $next in Y )   echo '' >/dev/null 2>&1   ;; N )   echo "exit from ftp mode."   exit 1   ;; * )   echo "error input"    ;;esacreadcfg ftpnode# The number of hostsNODES=`cat $CFGPID | wc -l`# To determine the amount of release filesTOTAL=`ls $RELEASEDIR|wc -l`if [ $TOTAL -gt 0 ] ; then   LIST=`ls -lrth $RELEASEDIR|awk '{print $9}'`   echo ""   echo "The following is the transmitted release file:"   echo "********************************"   ls -lrth $RELEASEDIR|grep -E -v '^total'|awk '{print $9}'   echo "********************************"   read -p "Are you sure you want to upload the above files to all specified nodes[Y/N]? " answer   case $answer in   Y)   i=0   for L in $LIST   do     i=`expr $i + 1`     for h in `cat $CFGPID`     do     # Create remote directory not exist     ssh $h "ls ${RDIR} |wc -l" >/dev/null 2>&1     if [ $? -ne 0 ];then        mkdir ${RDIR}        echo "Created directory ${RDIR} on $h."     fi          read -u6     {      multi_ftp $h $L      echo >&6     }&     done     wait    done    if [ $TOTAL -eq $i ] ; then       echo ""       echo "ALL release files are transferd to all specified nodes"    fi    ;;   N)    echo "You have exit from ftp mode"    exit 0    ;;   *)     echo "error input"    ;;   esacelse   echo "No release file in release directory."   exit 1fielif [ 'x'$OPTTYPE = 'xdeploy' ] ;thenif  [ 'x'$RELEASEFILE = 'x' ] ;then   echo "Please give release file you want to depoly!"   exit 1fiif [ -f $RELEASEDIR/$RELEASEFILE ]; then# try deploy for one nodereadcfg deployfirst# The number deployed first of hostsNODE_FIRST=`cat $CFGPID | wc -l`if [ $NODE_FIRST -ge 1 ]; thenfor h in `cat $CFGPID`do  deploylog=$DIRLOG/${h}_${CURPID}_process_deploy.log  echo "*************************************************"  echo "[$DATE]: Preparing to deploy $RELEASEFILE to [$h]..."  echo "*************************************************"    if [ -f "$DIR/issucess" ] ; then  echo "You deployed success this file ago on first node,Please delete file [$DIR/issucess] if you want to continue."  exit 1  fi  releasefile=$RDIR/$RELEASEFILE  multi_deploy $h $releasefile > $deploylog 2 >&1  #multi_deploy $h $releasefile    if [ $? -eq 0 ]; then      echo "[$DATE]: The release file [$RELEASEFILE] have been deployed to $h success."       echo "The detail you refer to [$DEPLOYLOG]."      echo "[$RELEASEFILE] have been deployed one or more node,please check log!!!" > $DIR/issucess       read -p "Are you sure you want to continut deploy to remaining nodes[Y/N]? " continue      case $continue in          Y)            continue            ;;          N)            echo "Input: $continue, Exit from Deployment process."            exit 1            ;;           *)            echo "Error input"            exit 1            ;;                 esac    else       echo "[$DATE]: Release file [$RELEASEFILE] is deployed to $h fail,Please check release file."       echo "The detail you refer to [$DEPLOYLOG]."       echo "ISDEPLOY=N" > $DIR/isfail      exit 1   fidoneelse echo "Please confirm the number of test nodes deployment is the only one!!!" exit 1fi# deploy remain nodesreadcfg deploynode##LIST=`ls -lrth $RELEASEDIR|awk '{print $9}'`for h in `cat $CFGPID`do  deploylog=$DIRLOG/${h}_${CURPID}_process_deploy.log  {   echo "*************************************************"  echo "[$DATE]: Preparing to deploy $RELEASEFILE to [$h]..."  echo "*************************************************"  read -u6  releasefile=$RDIR/$RELEASEFILE  multi_deploy $h $releasefile    if [ $? -eq 0 ]; then      echo "[$DATE]: The release file [$RELEASEFILE] have been deployed to $h success."      echo "The detail you refer to [$DEPLOYLOG]."            else       echo "[$DATE]: Release file [$RELEASEFILE] is deployed to $h fail,Please check release file."      echo "The detail you refer to [$DEPLOYLOG]."      echo "ISDEPLOY=N" > $DIR/isdeploy      exit 1   fi    echo >&6  } > $deploylog &  waitdoneecho "*************************************************"  > $DEPLOYLOGecho "* $DATE: Deployed results with all nodes" >> $DEPLOYLOGecho "*************************************************"  >> $DEPLOYLOGfor f in `ls $DIRLOG/*process_deploy.log`; do  cat $f >> $DEPLOYLOG  rm -rf $fdoneif [ -f "$DIR/isfail" ] ; thenecho "[$RELEASEFILE] is deployed fail,the detail you refer to [$DEPLOYLOG]."rm -rf  $DIR/isfailelse echo "[$RELEASEFILE] is deployed success on all special nodes,the detail you refer to [$DEPLOYLOG]"rm -rf $DIR/issucessmv  $RELEASEDIR/$RELEASEFILE $POST_RELEASEDIR/${RELEASEFILE}_`date "+%Y%m%d%H%M"`_successfi#echo "[$RELEASEFILE] is deployed to all nodes success,the detail you refer to [$DEPLOYLOG]."#mv  $RELEASEDIR/$RELEASEFILE $POST_RELEASEDIR/${RELEASEFILE}_`date "+%Y%m%d%H%M"`_successelse  echo "[$RELEASEDIR/$RELEASEFILE] not exist! Please confirm whether the file have been uploaded or deployed."  exit 1fielse   echo "Please input operation mode[ftp/deploy]"   exit 1fiexec 6>&-exit 0

 

 

-----------end----------