通过shell实现数据结构升级脚本
#!/bin/bash
#execute all script in specified directory
#--------------- 全量脚本----------------------
full_upgradesql()
{
MYDATE=`date +%F'-'%T'-'%w`
OraclesqL_PATH=/home/giacp/apps/GiacpMySQL/CompleteSQL #指定的目录
LOG_FILE=/home/log/exec_${MYDATE}.log
CompleteSQL=/home/giacp/apps/GiacpMySQL/CompleteSQL #指定的目录
if [ -d ${CompleteSQL} ];then
echo "全量文件目录${CompleteSQL}"
else
echo "文件目录${CompleteSQL}不存在"
exit
fi
OraclesqL()
{
echo "-----当前全量sql版本有"
MySqL_Sql_PATHql_PATH=`find ${CompleteSQL}/ -type f|grep '.sql$'|awk -F "/" '{print $(NF)}'|grep 'structure'|awk -F "-" '{print $4}'|awk -F ".sql" '{print $1}'|sort` #查询出所有sql版本
for OraclesqL_file1 in ${MySqL_Sql_PATHql_PATH}
do
echo "$OraclesqL_file1"
done
}
while true
do
#if [ ! $db_name ] ; then #如果没有指定数据库名
read -p "请输入数据用户名:" db_name
#fi
#if [ ! $db_pass ] ; then #如果没有指定数据库密码
read -s -p "请输入数据库密码:" db_pass
echo ""
#fi
#if [ ! $db_SID ] ; then #如果没有指定数据库服务名
read -p "请输入数据库名:" db_SID
#fi
echo "检查是否有${db_SID}库名,没有则创建。并检查账号、密码、服务名是否正确"
sleep 3
#echo "${db_pass}"
mysql -u${db_name} -p${db_pass} -e "create database if not exists ${db_SID}" >& error.log
error_user_or_password=`grep '(using password: YES)' error.log`
error_database=`grep 'Unknown database' error.log`
if [ -n "$error_user_or_password" ]; then
echo "用户或密码错误,请重新执行"
continue
else
OraclesqL
break;
fi
done
CompleteSQL1 ()
{
echo "你输入要升级的版本是:${db_version}"
giacp_data_structure=`find ${CompleteSQL}/ -type f|grep '.sql$'|grep "structure"|grep "${db_version}"`
giacp_data_init=`find ${CompleteSQL}/ -type f|grep '.sql$'|grep -E "init-data|data-init" | grep "${db_version}"`
for data_structure in ${giacp_data_structure}
do
echo "现在开始插入数据结构:$data_structure"
mysql -u${db_name} -p${db_pass} ${db_SID} < $data_structure >& error.log
sleep 5
for data_init in ${giacp_data_init}
do
echo "现在开始插入基础数据:${data_init}"
mysql -u${db_name} -p${db_pass} ${db_SID} < $data_init >& error.log
Repeat_insert=`grep 'Duplicate entry' error.log`
if [ -n "$Repeat_insert" ] ; then
echo "重复插入!"
fi
done
cat error.log >>${LOG_FILE} #输出执行日志
error=`grep ERROR error.log` #读取错误日志信息
if [ -n "$error" ] ; then #如果有错误就退出程序
echo $error
echo "直接退出啦!"
exit
fi
done
echo "##########升级完成#########"
exit
}
CompleteSQL2 ()
{
echo "你退出了执行全量操作!"
exit
}
while true
do
read -p "请输入要升级的全量数据库版本:例(1.2.3)" db_version
DATEPATTERN="([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})"
if [[ "$db_version" =~ $DATEPATTERN ]]; then
#echo "正确${db_version}"
for OraclesqL_file2 in ${MySqL_Sql_PATHql_PATH}
do
#echo "$OraclesqL_file2"
if [ "$db_version" != "$OraclesqL_file2" ]; then
continue;
else
while true
do
read -p "确认执行请输入:(输入yes/no)" choose
if [ $choose = "yes" ]; then
CompleteSQL1
elif [ $choose = "no" ]; then
CompleteSQL2;
else
echo "请输入yes/no"
continue
fi
done
fi
done
else
echo "不符合格式,请正确输入!例(1.2.3)"
continue
fi
done
}
#-----------------------新增脚本-------------------------------------
upgredesql ()
{
UpdateSQL=/home/giacp/apps/GiacpMySQL/UpdateSQL
SqlScript=/home/giacp/apps/GiacpMySQL/Scripts
current_upgrade()
{
#获取数据库当前版本信息
upgrade=`mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/query-current-version.sql|awk 'END{print}'`
echo "你当前数据库的版本信息为:$upgrade"
}
while true
do
#if [ ! $db_name ] ; then #如果没有指定数据库名
read -p "请输入数据用户名:" db_name
#fi
#if [ ! $db_pass ] ; then #如果没有指定数据库密码
read -s -p "请输入数据库密码:" db_pass
echo ""
#fi
#if [ ! $db_SID ] ; then #如果没有指定数据库服务名
read -p "请输入数据库名:" db_SID
#fi
#执行版本信息sql
mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/prepare-update.sql >& upgrade_error.log
error_user_or_password=`grep '(using password: YES)' upgrade_error.log`
error_database=`grep 'Unknown database' upgrade_error.log`
if [ -n "$error_user_or_password" ]; then
echo "用户或密码错误,请重新执行"
continue
elif [ -n "$error_database" ]; then
echo "没有该数据库服务名${db_SID},请重新执行"
continue
else
current_upgrade
break
fi
done
#获取当前可更新的版本
upgrade1=`mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/query-updateble-version.sql`
echo "可更新版本有"
for new_version_ud in ${upgrade1}
do
echo "${new_version_ud}"
done
while true
do
read -p "请输入需要升级到的数据库版本:例(1.2.3):" db_to_upgrade_version
DATEPATTERN="([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})"
if [[ "$db_to_upgrade_version" =~ $DATEPATTERN ]]; then
#echo "正确${db_version}"
for db_to_upgrade_version_tow in ${upgrade1}
do
if [ "$db_to_upgrade_version_tow" != "$db_to_upgrade_version" ]; then
continue
else
old_version=`echo "${upgrade}"|awk 'END{print}'|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g` #格式化输出整数
new_version=`echo "${db_to_upgrade_version}"|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g` #格式化输出整数
OraclesqL_PATH=${UpdateSQL}/*.sql #版本指定的文件
echo "将要执行以下sql脚本:"
sleep 3
for new_version_ud1 in ${OraclesqL_PATH}
do
#echo "$new_version_ud1"
version_new=`echo "${new_version_ud1}"|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g|awk -F "-" '{print $4}'` #格式化输出整数
#echo "$version_new"
if [[ ${old_version} -lt ${version_new} ]] && [[ ${new_version} -ge ${version_new} ]] ; then
echo "$new_version_ud1"
mysql -u${db_name} -p${db_pass} ${db_SID} < ${new_version_ud1} >& upgrade_error.log
character_set_error=`grep '\\xB2\\xE9\\xD1\\xAF\\xCA\\xD3' upgrade_error.log` #读取错误日志信息
if [ -n "$character_set_error" ] ; then #如果有错误就退出程序
echo $character_set_error
exit
fi
fi
done
#执行清空version_lookup的版本
sleep 3
mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/finish-update.sql >& upgrade_error.log
echo "##########升级完成#########"
exit
fi
done
else
echo "格式不正确"
continue
fi
done
}
echo "##############请选择全量升级sql还是增量sql升级#############"
while true
do
echo "1.全量->指定版本sql升级"
echo "2.增量sql升级"
echo "#####请选择:#####"
read softType
if ((softType == 1)); then
echo "#####全量/指定版本sql升级#####"
full_upgradesql
elif ((softType == 2)); then
echo "#####升级/增量sql升级#####"
upgredesql
else
echo "#####输入错误#####"
continue
fi
done