MySQL distributed database change automation deployment

Author:skate
Time:2015/03/05

MySQL Database change automatic deployment

Overview:
This MySQL database Publisher program can automatically, quickly, and concurrently publish data changes and record results. Transfer the deployment content from the deployment server to all deployed database and execute the deployment content.
This program is mainly used for distributed database deployment, such as the need to deploy a deployment script to multiple databases at the same time.

Test environment:
Deploydb:
[email protected] skate]# ll Autodeploy
Total 36
-rw-r--r--1 root root 9378 Feb 20:23 Autodeploy
-rw-r--r--1 root root 18:40 autodeploy_config.txt
Drwxr-xr-x 2 root root 4096 Feb 18:50 ex_dbrelease
Drwxr-xr-x 2 root root 4096 Feb 18:50 log
Drwxr-xr-x 2 root root 4096 Feb 18:50 post_dbrelease
[Email protected] skate]#

Directory:
Ex_dbrelease: Used to store files that are about to be deployed
Post_dbrelease: Files that have been successfully deployed
LOG: Journal file
Autodeploy_config.txt: Configuration file
Autodeploy: Main program

Deployment environment:
1. Deploy server-to-database SSH connectivity.
2.rsync is installed in the deployment server and database
3. The database deployment script requires a schema prefix, such as: Database.tablename

Steps to use:
1. This deployment program is only responsible for deploying database change scripts, so you need to do the backup work beforehand

2. If you want to know the number of rows affected before and after a database change, you need to execute "SELECT COUNT (*) from table" Before and after the deployment content (the program itself will also show the number of rows affected by the change)

3. Edit the configuration file, the configuration file total three pieces of content, Deployfirst represents the deployment test node, Deploynode represents the rest of the deployment node, Ftpnode on behalf of the deployment files need to be FTP node

There is three sections in this configuration File,eg:
[Email protected] 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 files in the Ex_dbrelease directory of the deployment server

5. Deployment files are concurrent FTP to all specified nodes
eg

[Email protected] 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


[[Email protected] autodeploy]# SH autodeploy-t FTP

Please confirm so you had put the FTP into Directory/home/skate/autodeploy/ex_dbrelease [y/n]: Y

The following is the transmitted release file:
********************************
T1.sql
********************************
Is you sure want to upload the above files to all specified nodes[y/n]? Y
[T1.sql] is transferd to DFNG1DB4.SE2 success.

All release files is transferd to all specified nodes
[Email protected] autodeploy]#

The command with FTP parameter would transfer all release files from directory ex_dbrelease.

6. Attempt to deploy a node that is automatically deployed to the remaining nodes if successful

eg
[Email protected] 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 Conti Nue.
[Email protected] autodeploy]# rm/home/skate/autodeploy/issucess
Rm:remove regular file '/home/skate/autodeploy/issucess '? Y

[Email protected] 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 is insecure.
[2015-02-26 18:50:22]: The release file [T1.sql] has been deployed to DFNG1DB4.SE2 success.
The detail refer to [/home/skate/autodeploy/log/deploy_201502261850_deploy.log].
Is you sure-want to continut deploy to remaining nodes[y/n]? Y
Warning:using a password on the command line interface can is insecure.
[T1.sql] is deployed success on all special nodes,the detail your refer to [/home/skate/autodeploy/log/deploy_201502261850_ Deploy.log]
[Email protected] autodeploy]#

Questions:
1. Deployment files have been successfully deployed.

[Email protected] 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 Conti Nue.
[Email protected] autodeploy]# rm/home/skate/autodeploy/issucess
Rm:remove regular file '/home/skate/autodeploy/issucess '? Y

2. The deployment file does not exist
[Email protected] autodeploy]# sh autodeploy-t deploy-f t1.sql
[/home/skate/autodeploy/ex_dbrelease/t1.sql] Not exist! Please confirm whether the file has been uploaded or deployed.
[Email protected] autodeploy]#

3. Multiple deployment programs run the confirmation at the same time

[Email protected] 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-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 daysfind ${post_releasedir}/-name "*._success"-mtime +60 | Xargs rm-rf# Remove Local Deploy log ago 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 '} ' donum= ' Ps-ef | awk ' {print $} ' | 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] was 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 $releasef Ile;\ "" 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 so you had 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: "EC Ho "********************************" Ls-lrth $RELEASEDIR |grep-e-V ' ^total ' |awk ' {print $9} ' echo ' **************** "Read-p" is sure you want to upload the above files to all specified nodes[y/n]? "Answer case $answer in Y) i=0-in $LIST does i= ' expr $i + 1 ' for h in ' cat $CFGPID ' do # Create remote dire Ctory 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 is transferd to all specified nodes "FI;   N) echo "You had 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 "Give release file you wan   T 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 toDeploy $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] has been deployed to $h success."      echo "The detail refer to [$DEPLOYLOG]." echo "[$RELEASEFILE] has been deployed one or more node,please check log!!!" > $DIR/issucess read-p "is you su Re 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 refer to [$DEPLOYLOG]." echo "Isdeploy=n" > $DIR/isfail exit 1 fidoneelse echo "Please confirm the number of test nodes deployment is th E 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] has been deployed to $h success."            echo "The detail refer to [$DEPLOYLOG]." else echo "[$DATE]: Release file [$RELEASEFILE] is deployed to $h fail,please check reLease file. "      echo "The detail refer to [$DEPLOYLOG]." echo "Isdeploy=n" > $DIR/isdeploy Exit 1 fi echo >&6} > $deploylog & Waitdoneecho "********* "> $DEPLOYLOGecho" * $DATE: Deployed results with all nodes ">> $DEPLOY Logecho "*************************************************" >> $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 your refer to [$DEPLOYLOG] "Rm-rf $DIR/issucessmv $RELEASEDIR/$RELEASEFILE $POST _releasedir/${releasefile}_ ' Date" +%y%m%d%h%m "' _successfi# echo "[$RELEASEFILE] is deployed-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 has been uploaded or deployed. " Exit 1fielse echo "Please input operation Mode[ftp/deploy]" exit 1fiexec 6>&-exit 0

-----------End----------

MySQL distributed database change automation deployment