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
