Automated deployment of Mysql Distributed Database changes [Source Code], mysql

Automated deployment of Mysql Distributed Database changes [Source Code], mysql

Author: skate
Time: 2015/03/05

 

Automatic deployment of MySQL database changes

 

Overview:
This Mysql database publishing program can automatically, quickly, and concurrently publish data changes and record results. Transfer the deployment content from the Deployment server to all deployment databases and execute the deployment content.
This program is mainly used for Distributed database deployment. For example, you need to deploy a deployment script to multiple databases at the same time.

 

 

 

Test environment:
Deploydb:
[Root @ deploydb skate] # ll autodeploy
Total 36
-Rw-r -- 1 root 9378 Feb 26 20:23 autodeploy
-Rw-r -- 1 root 120 Feb 26 18:40 autodeploy_config.txt
Drwxr-xr-x 2 root 4096 Feb 26 18:50 ex_dbrelease
Drwxr-xr-x 2 root 4096 Feb 26 log
Drwxr-xr-x 2 root 4096 Feb 26 :50 post_dbrelease
[Root @ deploydb skate] #

Directory:
Ex_dbrelease: used to store files to be deployed.
Post_dbrelease: successfully deployed File
Log: log File
Autodeploy_config.txt: Configuration File
Autodeploy: Main Program

Deployment environment:
1. Deploy ssh connection between the server and the database.
2. rsync is installed on the deployed server and database.
3. The database deployment script must have a schema prefix, for example, database. tablename.

Procedure:
1. This deployment Program is only responsible for deploying the Database Change script. Therefore, you need to back up the database in advance.

2. if you want to know the number of rows affected before and after the Database Change, You need to execute "select count (*) from table" before and after the deployment content is executed (this program will also display the number of rows affected by the change)

3. Edit the configuration file. The configuration file contains three parts. deployfirst indicates the deployment of the test node, deploynode indicates the other deployment nodes, and ftpnode indicates the node where the deployment file needs to be 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. Place the deployment file in the ex_dbrelease directory of the Deployment server.

5. The deployment file is concurrently ftp to all specified nodes.
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. Try to deploy a node. If it succeeds, it will be automatically deployed on the remaining nodes.

Eg:
[Root @ deploydb autodeploy] # sh autodeploy-t deploy-f t1. SQL
**************************************** *********
[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
**************************************** *********
[18:50:22]: Preparing to deploy t1. SQL to [dfng1db4. se2]...
**************************************** *********
Warning: Using a password on the command line interface can be insecure.
[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. The deployment file has been successfully deployed.

[Root @ deploydb autodeploy] # sh autodeploy-t deploy-f t1. SQL
**************************************** *********
[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. The deployment file does not exist.
[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. Multiple deployment programs run at the same time for confirmation

[Root @ deploydb autodeploy] # sh autodeploy
**********************************
Root 8560 8520 0 00:00:00 pts/2 sh autodeploy-t ftp
Root 8580 1632 0 00:00:00 pts/11 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 ----------