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 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 df -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----------

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。