Mysql分布式数据库变更自动化部署[源码]

朴灿烈づ我的快乐病毒、 2022-08-05 15:13 124阅读 0赞

Author：skate  
Time：2015/03/05

**MySQL数据库变更自动部署**

概述：  
这个Mysql数据库发布程序，可以自动、快速、并发的发布数据变更并记录结果。把部署内容从部署server传输到所有部署database并执行部署内容。  
本程序主要用于分布式数据库部署,比如把一个部署脚本需要同时部署到多台database上。

![Center][]

![Center 1][]

测试环境:  
*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.部署文件已经被成功部署.

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 scripts
    
    CURPID=$$
    DIR=/home/szhao/autodeploy
    DIRLOG=$DIR/log
    RELEASEDIR=$DIR/ex_dbrelease
    POST_RELEASEDIR=$DIR/post_dbrelease
    LOCK=$DIR/autodeploy.lock
    LOG=$DIRLOG/autodeploy.log
    CFG=$DIR/autodeploy_config.txt
    CFGPID=$DIR/${CURPID}_config
    DEPLOYLOG=$DIRLOG/deploy_`date "+%Y%m%d%H%M"`_deploy.log
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    #LFILE=/tmp/tmpsql.log
    RDIR=/tmp/autodeploy
    ISDEPLOY='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
           ;;
        esac
    fi
    
    #############################################################################
    # Display usage message and exit
    #############################################################################
    usage() {
      cat <<EOF
    Usage: $SCRIPTNAME [-t mode] [-u upload_dir] [-f release_file] [-r retention_time]
      -t  Operatation mode(ftp/deploy)
      -f  Release file
      -h  Display basic help
    EOF
      exit 0
    }
    
    
    # Parse parameters
    while 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 ;;
      esac
    done
    
    shift $(($OPTIND - 1))
    
    
    #############################################################################
    # Manage local and remote release file
    #############################################################################
    if [ ! -d "$DIR" ]; then
       mkdir -p $DIR
    fi
    
    if [ ! -d "$DIRLOG" ]; then
       mkdir $DIRLOG
    fi
      
    if [ ! -d "$RELEASEDIR" ]; then
       mkdir $RELEASEDIR
    fi
    
    if [ ! -d "$POST_RELEASEDIR" ]; then
       mkdir $POST_RELEASEDIR
    fi
    
    # Remove loacl released file ago 60 days
    find ${POST_RELEASEDIR}/ -name "*._success" -mtime +60  | xargs rm -rf
    # Remove local deploy log ago 10 days
    find ${DIRLOG}/ -name "*deploy.log" -mtime +10  | xargs rm -rf
    # Keep recently 10000 lines of autodeploy log
    tail -100000 $LOG > $LOG
    
    cd $DIR 
    config_num=`find $DIR/ -name  *_config | wc -l `
    if [ ${config_num} -gt 0 ];then
    for pid in  `ls -l  *_config | awk '{print $9}' | awk -F_ '{print $1}'`
    do
    NUM=`ps -ef | awk '{ print $2 }' | grep -E '^${pid}$' | wc -l`
    if [ $NUM -eq 0 ] ; then
    rm -rf $DIR/${pid}_config
    fi
    done
    fi
    #############################################################################
    # Upload release file to all nodes
    #############################################################################
    # sync file to remote
    function multi_ftp
    {
    HOST=$1
    LFILE=$2
    rsync -avP --bwlimit=1000 $RELEASEDIR/$LFILE $HOST:$RDIR/ >/dev/null 2>&1
    if [  $? == 0 ] ; then
       echo "[$LFILE] is transferd to $HOST success."
    else
       echo "[$LFILE] is transferd to $HOST fail."
       exit 1
    fi
    exit 0
    
    }
    
    function multi_deploy
    {
    host=$1
    releasefile=$2
    ssh $host "mysql -u$USER -p$PASSWD -vvv test -e \"source $releasefile;\""
    if [  $? == 0 ] ; then
       return 0
    else
       return 1
    fi
    
    
    }
    
    # parse configuration file
    function readcfg()
    {
    FIELD=$1
    first=`sed  -n "/\[$FIELD\]/=" $CFG`
    last=`sed  -n "/\[\/$FIELD\]/=" $CFG`
    #echo $first,$last
    sed -n "$((first+1)),$((last-1))p"  $CFG > $CFGPID
    sed -i '/^$/d' $CFGPID
    }
    
    
    # Simulation of multi thread
    exec 6>&-
    tmp_fifofile="$DIR/$.fifo"
    mkfifo $tmp_fifofile
    exec 6<>$tmp_fifofile
    rm $tmp_fifofile
    thread=40
    for (( i=0;i<=$thread;i++ )); do
    echo
    done >&6
    
    # ftp mode
    if [ 'x'$OPTTYPE = 'xftp' ] ; then
    echo ""
    read -p "Please confirm that you have put the FTP into  directory ${RELEASEDIR} [Y/N]: " next
    case $next in
     Y )
       echo '' >/dev/null 2>&1
       ;;
     N )
       echo "exit from ftp mode."
       exit 1
       ;;
     * )
       echo "error input"
        ;;
    esac
    
    readcfg ftpnode
    
    # The number of hosts
    NODES=`cat $CFGPID | wc -l`
    
    # To determine the amount of release files
    TOTAL=`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"
        ;;
       esac
    
    else
       echo "No release file in release directory."
       exit 1
    
    fi
    
    
    
    elif [ 'x'$OPTTYPE = 'xdeploy' ] ;then
    if  [ 'x'$RELEASEFILE = 'x' ] ;then
       echo "Please give release file you want to depoly!"
       exit 1
    fi
    
    if [ -f $RELEASEDIR/$RELEASEFILE ]; then
    
    # try deploy for one node
    readcfg deployfirst
    
    # The number deployed first of hosts
    NODE_FIRST=`cat $CFGPID | wc -l`
    if [ $NODE_FIRST -ge 1 ]; then
    for 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
       fi
    done
    
    else
     echo "Please confirm the number of test nodes deployment is the only one!!!"
     exit 1
    fi
    
    
    # deploy remain nodes
    readcfg 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 &
      wait
    done
    
    echo "*************************************************"  > $DEPLOYLOG
    echo "* $DATE: Deployed results with all nodes" >> $DEPLOYLOG
    echo "*************************************************"  >> $DEPLOYLOG
    
    for f in `ls $DIRLOG/*process_deploy.log`; do
      cat $f >> $DEPLOYLOG
      rm -rf $f
    done
    
    if [ -f "$DIR/isfail" ] ; then
    echo "[$RELEASEFILE] is deployed fail,the detail you refer to [$DEPLOYLOG]."
    rm -rf  $DIR/isfail
    else 
    echo "[$RELEASEFILE] is deployed success on all special nodes,the detail you refer to [$DEPLOYLOG]"
    rm -rf $DIR/issucess
    mv  $RELEASEDIR/$RELEASEFILE $POST_RELEASEDIR/${RELEASEFILE}_`date "+%Y%m%d%H%M"`_success
    fi
    
    #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"`_success
    
    else
      echo "[$RELEASEDIR/$RELEASEFILE] not exist! Please confirm whether the file have been uploaded or deployed."
      exit 1
    fi
    
    else
       echo "Please input operation mode[ftp/deploy]"
       exit 1
    fi
    
    exec 6>&-
    
    exit 0

\-----------end----------

[Center]: /images/20220805/d94c98cb0fbb49d796a8f34fd67d6a96.png
[Center 1]: /images/20220805/0e2ff82a62bb406bb2bfa8b532e1dd8f.png