乐趣区

关于脚本:基于dos与shell-实现数据库批量跑库脚本dmmysql

jiezi

  1. bat

    mysql 跑库 

    @echo off
chcp 65001
%~d0
cd %~dp0
set basepath=%~dp0
set | findstr mysql
if "%ERRORLEVEL%" == "0" (goto paraminit) ELSE ECHO THERE IS NOT MYSQL ENV !
goto end

:paraminit
set /p mysqlhost="ip 地址, 默认值 ==> (127.0.0.1) :"
set /p mysqlport="请输出 mysql 服务端口, 默认值 ==> (3306) :"
set /p rootpwd="请输出 mysql 超管明码, 默认值 ==> (root) :"
set /p kupwd="请输出 oneaccess 各个库拥有者对应的明码, 默认值 ==> (Bamboocloud@1234) :"
set /p suffix="请输出 oneaccess 对应的库及用户的后缀, 默认为空串:"

if "%mysqlhost%"=="" set mysqlhost=127.0.0.1
if "%mysqlport%"=="" set mysqlport=3306
if "%rootpwd%"=="" set rootpwd=root
if "%kupwd%"=="" set kupwd=Bamboocloud@1234
if "%suffix%"=="" set suffix=
set sql_dir=sql_cmd_execute
rmdir /s/q %basepath%\%sql_dir%
mkdir -p %basepath%\%sql_dir% 
xcopy /y/e/i %basepath%\bam %basepath%\%sql_dir% 
xcopy /y/e/i %basepath%\bim %basepath%\%sql_dir% 
xcopy /y/e/i %basepath%\epass %basepath%\%sql_dir% 
xcopy /y/e/i %basepath%\apphub %basepath%\%sql_dir% 
goto replacepwdfohcs


:replacepwdfohcs
@setlocal enabledelayedexpansion
for /f "delims=" %%i in (%basepath%\%sql_dir%\bam_mysql_db_create.sql) do (
set content=%%i
set content=!content:bam_password=abcdefghijklmnopqrst!
set content=!content:bam=bam%suffix%!
set content=!content:abcdefghijklmnopqrst=%kupwd%!
echo !content!>>$1)
move $1 %basepath%\%sql_dir%\bam_mysql_db_create.sql || EXIT \B

for /f "delims=" %%i in (%basepath%\%sql_dir%\bim_mysql_db_create.sql) do (
set content=%%i
set content=!content:bim_password=abcdefghijklmnopqrst!
set content=!content:bim=bim%suffix%!
set content=!content:abcdefghijklmnopqrst=%kupwd%!
echo !content!>>$2)
move $2 %basepath%\%sql_dir%\bim_mysql_db_create.sql || EXIT \B

for /f "delims=" %%i in (%basepath%\%sql_dir%\apphub_mysql_db_create.sql) do (
set content=%%i
set content=!content:apphub_password=abcdefghijklmnopqrst!
set content=!content:apphub=apphub%suffix%!
set content=!content:abcdefghijklmnopqrst=%kupwd%!
echo !content!>>$3)
move $3 %basepath%\%sql_dir%\apphub_mysql_db_create.sql || EXIT \B

for /f "delims=" %%i in (%basepath%\%sql_dir%\epass_mysql_db_create.sql) do (
set content=%%i
set content=!content:epass_password=abcdefghijklmnopqrst!
set content=!content:epass=epass%suffix%!
set content=!content:abcdefghijklmnopqrst=%kupwd%!
echo !content!>>$4)
move $4 %basepath%\%sql_dir%\epass_mysql_db_create.sql || EXIT \B
goto mysqlsqlexec

:mysqlsqlexec
set _timeTemp=0
call:GetTimeSeconds %time%
set time1=%_timeTemp%
rem choice  期待 5 秒 再计算另外一个工夫点
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "mysql" <  %basepath%\%sql_dir%\bam_mysql_db_create.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "mysql" <  %basepath%\%sql_dir%\bim_mysql_db_create.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "mysql" <  %basepath%\%sql_dir%\apphub_mysql_db_create.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "mysql" <  %basepath%\%sql_dir%\epass_mysql_db_create.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "bam%suffix%" <  %basepath%\%sql_dir%\bam\bam_mysql_table_and_data.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "bim%suffix%" <  %basepath%\%sql_dir%\bim_mysql_table_and_data.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "apphub%suffix%" <  %basepath%\%sql_dir%\apphub_mysql_table_and_data.sql -vvv
 mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -D "epass%suffix%" <  %basepath%\%sql_dir%\epass_mysql_table_and_data.sql -vvv
@echo off
@REM "
goto printexetimes

:printexetimes
call:GetTimeSeconds %time%
set time2=%_timeTemp%
set /a interTime=%time2% - %time1%
echo 
echo 已全量跑库实现 hcs_mysql_bat 耗时: %interTime% 秒 !!
goto end




:GetTimeSeconds
set tt=%1
rem windows 下 %time% 工夫的格局个别为 hour:minute:second.number 上面两行只是去掉冒号和顿号变成 hour minute second number , 此处只是取 hour minute second
set tt=%tt:.= %
set tt=%tt::= %
set index=1
for %%a in (%tt%) do (
 if !index! EQU 1 (set hh=%%a)^
 else if  !index! EQU 2 (set mm=%%a)^
 else if  !index! EQU 3 (set ss=%%a)
set /a index=index+1
)
set /a _timeTemp=(%hh%*60+%mm%)*60+%ss%

goto:end


:end

    mysql 删库 

    @echo off
chcp 65001
set | findstr mysql
if "%ERRORLEVEL%" == "0" (goto paraminit)
echo there is no mysql env
goto end


:paraminit
set /p mysqlhost="请输出 ip:"
set /p mysqlport= 请输出端口
set /p rootpwd= 请输出明码
set /p suffix= 请输出后缀

if "%mysqlhost%"=="" set mysqlhost=127.0.0.1
if "%mysqlport%"=="" set mysqlport=3306
if "%rootpwd%"=="" set rootpwd=root
if "%suffix%"=="" set suffix=136
goto mysqldelexec

:mysqldelexec
mysql -h %mysqlhost% -uroot -p%rootpwd% -P %mysqlport% -e "use mysql;DROP USER IF EXISTS'bam%suffix%'@'%%';DROP USER IF EXISTS'bam%suffix%'@'localhost';DROP DATABASE IF EXISTS `bam%suffix%`;DROP USER IF EXISTS'bim%suffix%'@'%%';DROP USER IF EXISTS'bim%suffix%'@'localhost';DROP DATABASE IF EXISTS `bim%suffix%`;DROP USER IF EXISTS'apphub%suffix%'@'%%';DROP USER IF EXISTS'apphub%suffix%'@'localhost';DROP DATABASE IF EXISTS `apphub%suffix%`;DROP USER IF EXISTS'epass%suffix%'@'%%';DROP USER IF EXISTS'epass%suffix%'@'localhost';DROP DATABASE IF EXISTS `epass%suffix%`;" -vvv
goto end

:end
pause

  2. bash

    mysql 跑库 

    #!/bin/bash
set -oe
#- u 为数据库用户名 - p 为明码如果数据库明码不是 root 记得批改这里
read -p "ip 地址, 默认值 ==> (127.0.0.1) :" mysqlhost
read -p "请输出 mysql 服务端口, 默认值 ==> (3306) :" mysqlport
read -p "请输出 mysql 超管明码, 默认值 ==> (root) :" rootpwd
read -p "请输出 oneaccess 各个库拥有者对应的明码, 默认值 ==> (Bamboocloud@1234) :" kupwd
read -p $'\033[40;32m 请输出 oneaccess 对应的库及用户的后缀, 默认为空串: \033[0m' suffix
starttime=`date +'%Y-%m-%d %H:%M:%S'`

suffix=${suffix:-""}
rootpwd=${rootpwd:-"root"}
mysqlport=${mysqlport:-"3306"}
mysqlhost=${mysqlhost:-"127.0.0.1"}
kupwd=${kupwd:-"Bamboocloud@1234"}
basepath=$(cd $(dirname $0)
 pwd
)
rm -rf ${basepath}/sql_execute
mkdir -p ${basepath}/sql_execute
\cp -r ${basepath}/{bam,bim,apphub,epass} ${basepath}/sql_execute

sed -i "s/bam_password/kupwd/g" ${basepath}/sql_execute/bam/bam_mysql_db_create.sql
sed -i "s/bam/bam${suffix}/g" ${basepath}/sql_execute/bam/bam_mysql_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ${basepath}/sql_execute/bam/bam_mysql_db_create.sql

sed -i "s/bim_password/kupwd/g" ${basepath}/sql_execute/bim/bim_mysql_db_create.sql
sed -i "s/bim/bim${suffix}/g" ${basepath}/sql_execute/bim/bim_mysql_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ${basepath}/sql_execute/bim/bim_mysql_db_create.sql

sed -i "s/epass_password/kupwd/g" ${basepath}/sql_execute/epass/epass_mysql_db_create.sql
sed -i "s/epass/epass${suffix}/g" ${basepath}/sql_execute/epass/epass_mysql_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ${basepath}/sql_execute/epass/epass_mysql_db_create.sql

sed -i "s/apphub_password/kupwd/g" ${basepath}/sql_execute/apphub/apphub_mysql_db_create.sql
sed -i "s/apphub/apphub${suffix}/g" ${basepath}/sql_execute/apphub/apphub_mysql_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ${basepath}/sql_execute/apphub/apphub_mysql_db_create.sql

mysql -h$mysqlhost -uroot -p$rootpwd -P$mysqlport -e \
"use mysql;
source ${basepath}/sql_execute/bam/bam_mysql_db_create.sql;  
source ${basepath}/sql_execute/bim/bim_mysql_db_create.sql; 
source ${basepath}/sql_execute/apphub/apphub_mysql_db_create.sql;
source ${basepath}/sql_execute/epass/epass_mysql_db_create.sql; 
use bam${suffix}; 
source ${basepath}/sql_execute/bam/bam_mysql_table_and_data.sql; 
use bim${suffix}; 
source ${basepath}/sql_execute/bim/bim_mysql_table_and_data.sql; 
use apphub${suffix}; 
source ${basepath}/sql_execute/apphub/apphub_mysql_table_and_data.sql; 
use epass${suffix}; 
source ${basepath}/sql_execute/epass/epass_mysql_table_and_data.sql;" -vvv

endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo
echo "运行 hcs-mysql 跑库脚本......"
echo
echo "====================hcs============================"
echo "开始工夫:" $starttime
echo "完结工夫:" $endtime
echo "本次跑库耗时："$((end_seconds-start_seconds))"s"
echo "====================hcs============================"

mysql 删库 

#!/bin/bash
#- u 为数据库用户名 - p 为明码如果数据库明码不是 root 记得批改这里
read -p "ip 地址, 默认值 ==> (127.0.0.1) :" mysqlhost
read -p "请输出 mysql 服务端口, 默认值 ==> (3306) :" mysqlport
read -p "请输出 mysql 超管明码, 默认值 ==> (root) :" rootpwd
read -p $'\033[40;32m 请输出须要删除 oneaccess 对应的库及用户的后缀, 默认为空串: \033[0m' suffix

suffix=${suffix:-""}
rootpwd=${rootpwd:-"root"}
mysqlport=${mysqlport:-"3306"}
mysqlhost=${mysqlhost:-"127.0.0.1"}
mysql -h$mysqlhost -uroot -p$rootpwd -P$mysqlport -e \
    "use mysql;
DROP USER IF EXISTS 'bam${suffix}'@'%';
DROP USER IF EXISTS 'bam${suffix}'@'localhost';
DROP DATABASE IF EXISTS \`bam${suffix}\`;
DROP USER IF EXISTS 'bim${suffix}'@'%';
DROP USER IF EXISTS 'bim${suffix}'@'localhost';
DROP DATABASE IF EXISTS \`bim${suffix}\`;
DROP USER IF EXISTS 'apphub${suffix}'@'%';
DROP USER IF EXISTS 'apphub${suffix}'@'localhost';
DROP DATABASE IF EXISTS \`apphub${suffix}\`;
DROP USER IF EXISTS 'epass${suffix}'@'%';
DROP USER IF EXISTS 'epass${suffix}'@'localhost';
DROP DATABASE IF EXISTS \`epass${suffix}\`;" -vvv
  1. dm/shell

跑库 

#!/bin/bash
#==============================================================#
# 脚本名     :   hcs_dm_initall.sql
# 创立工夫   :   2022-12-28 10:02:09
# 更新工夫   :   2022-12-29 10:30:18
# 形容      :   应用脚本, 依据输出的后缀不同 一键跑完 DM 的数据库脚本
# 门路      :   需与 oneaccess dm 版本的 sql 目录在同一门路下
# 版本      :   1.0.0
# 作者      :   dongjelababy(18271632505@163.com)
# Copyright (C) 2021-2022 HCS
#==============================================================#

#==============================================================#
#                         HCS_DM                              #
#==============================================================#
# 获取达梦的装置目录
dmdispwd=$(find / -path *bin/disql | head -1)
read -p "请输出 dm 服务端口, 默认值 ==> (5236) :" dmport
read -p "请输出 dm 超管明码, 默认值 ==> (SYSDBA) :" sysdbapwd
read -p "请输出 oneaccess 各个库拥有者对应的明码, 默认值 ==> (Bamboocloud@1234) :" kupwd
read -p "请输出 oneaccess 对应的库及用户的后缀, 默认为空串:" suffix
suffix=${suffix:-""}
sysdbapwd=${sysdbapwd:-"SYSDBA"}
dmport=${dmport:-"5236"}
kupwd=${kupwd:-"Bamboocloud@1234"}

if ["$suffix" = ""]; then
read -p "后缀为空, 请确认 (yes/no)：yes 持续,no 退出" input
input=$(echo "$input" | tr "[A-Z]" "[a-z]")
if ["$input" = "yes"]; then
   echo "yes go"
else 
echo "exit zero"
exit
fi
else
   echo "go"
fi
starttime=`date +'%Y-%m-%d %H:%M:%S'`

dy="'"sy='"'

dir=hcs_dm_execute
rm -rf ./${dir}
mkdir ./${dir}
# bam 
cat bam/bam_dm_db_create.sql >./${dir}/bam_dm_db_create.sql
cat >>./${dir}/bam_dm_db_create.sql<<hcs
exit;
hcs
cat bam/bam_dm_20.0.2_all.sql >./${dir}/bam_dm_20.0.2_all.sql
cat >>./${dir}/bam_dm_20.0.2_all.sql<<hcs

exit;
hcs
# bim
cat bim/bim_dm_db_create.sql >./${dir}/bim_dm_db_create.sql

cat >>./${dir}/bim_dm_db_create.sql<<hcs

exit;
hcs
cat bim/bim_dm_table_create.sql >./${dir}/bim_dm_table_create.sql
cat >>./${dir}/bim_dm_table_create.sql<<hcs

exit;
hcs
cat bim/business_dm_table_create.sql >./${dir}/business_dm_table_create.sql
cat >>./${dir}/business_dm_table_create.sql<<hcs

exit;
hcs
cat bim/bim_dm_trigger.sql >./${dir}/bim_dm_trigger.sql
cat >>./${dir}/bim_dm_trigger.sql<<hcs

exit;
hcs
cat bim/bim_dm_data_init.sql >./${dir}/bim_dm_data_init.sql
cat >>./${dir}/bim_dm_data_init.sql<<hcs

exit;
hcs
cat bim/business_dm_data_init.sql >./${dir}/business_dm_data_init.sql
cat >>./${dir}/business_dm_data_init.sql<<hcs

exit;
hcs
# epass
cat epass/epass_dm_db_create.sql >./${dir}/epass_dm_db_create.sql
cat >>./${dir}/epass_dm_db_create.sql<<hcs

exit;
hcs
cat epass/epass_dm_20.0.2_all.sql >./${dir}/epass_dm_20.0.2_all.sql
cat >>./${dir}/epass_dm_20.0.2_all.sql<<hcs

exit;
hcs
# apphub
cat apphub/apphub_dm_db_create.sql >./${dir}/apphub_dm_db_create.sql
cat >>./${dir}/apphub_dm_db_create.sql<<hcs

exit;
hcs
cat apphub/apphub_dm_table_create.sql >./${dir}/apphub_dm_table_create.sql
cat >>./${dir}/apphub_dm_table_create.sql<<hcs

exit;
hcs
cat apphub/apphub_dm_data_sample.sql >./${dir}/apphub_dm_data_sample.sql
cat >>./${dir}/apphub_dm_data_sample.sql<<hcs

exit;
hcs

sed -i "s/Bamboocloud@1234/kupwd/g"  ./${dir}/bam_dm_db_create.sql
sed -i "s/BAM/BAM${suffix}/g" ./${dir}/bam_dm_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ./${dir}/bam_dm_db_create.sql

sed -i "s/Bamboocloud@1234/kupwd/g"  ./${dir}/bim_dm_db_create.sql
sed -i "s/BIM/BIM${suffix}/g" ./${dir}/bim_dm_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ./${dir}/bim_dm_db_create.sql

sed -i "s/Bamboocloud@1234/kupwd/g"  ./${dir}/epass_dm_db_create.sql
sed -i "s/EPASS/EPASS${suffix}/g" ./${dir}/epass_dm_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ./${dir}/epass_dm_db_create.sql

sed -i "s/Bamboocloud@1234/kupwd/g"  ./${dir}/apphub_dm_db_create.sql
sed -i "s/APPHUB/APPHUB${suffix}/g" ./${dir}/apphub_dm_db_create.sql
sed -i "s/kupwd/${kupwd}/g" ./${dir}/apphub_dm_db_create.sql


$dmdispwd SYSDBA/"${sysdbapwd}":"${dmport}" \`./${dir}/bam_dm_db_create.sql
$dmdispwd SYSDBA/"${sysdbapwd}":"${dmport}" \`./${dir}/bim_dm_db_create.sql
$dmdispwd SYSDBA/"${sysdbapwd}":"${dmport}" \`./${dir}/epass_dm_db_create.sql
$dmdispwd SYSDBA/"${sysdbapwd}":"${dmport}" \`./${dir}/apphub_dm_db_create.sql



# bam 跑库表
eval "$dmdispwd BAM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/bam_dm_20.0.2_all.sql"

# bim 跑库表

eval "$dmdispwd BIM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/bim_dm_table_create.sql"
eval "$dmdispwd BIM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/business_dm_table_create.sql"
eval "$dmdispwd BIM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/bim_dm_trigger.sql"
eval "$dmdispwd BIM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/bim_dm_data_init.sql"
eval "$dmdispwd BIM${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/business_dm_data_init.sql"

# epass 跑库表
eval "$dmdispwd EPASS${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/epass_dm_20.0.2_all.sql"

# apphub 跑库表
eval "$dmdispwd APPHUB${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/apphub_dm_table_create.sql"
eval "$dmdispwd APPHUB${suffix}/$dy$sy${kupwd}$sy$dy:${dmport} \\\`./${dir}/apphub_dm_data_sample.sql"

endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo
echo 
echo
echo "====================hcs============================"
echo "开始工夫:" $starttime
echo "完结工夫:" $endtime
echo "本次跑库耗时："$((end_seconds-start_seconds))"s"
echo "====================hcs============================"

删库 

#!/bin/bash
#==============================================================#
# 脚本名     :   hcs_dm_destroyall.sh
# 创立工夫   :   2022-12-28 10:02:09
# 更新工夫   :   2022-12-29 10:30:18
# 形容      :   应用脚本, 依据输出的后缀不同 一键删除 dm 数据库的表空间和表
# 门路      :   需与 oneaccess dm 版本的 sql 目录在同一门路下
# 版本      :   1.0.0
# 作者      :   dongjelababy(18271632505@163.com)
# Copyright (C) 2021-2022 HCS
#==============================================================#

#==============================================================#
#                         HCS_DM                              #
#==============================================================#
# 获取达梦的装置目录
dmdispwd=$(find / -path *bin/disql | head -1)
read -p "请输出 dm 服务端口, 默认值 ==> (5236) :" dmport
read -p "请输出 dm 超管明码, 默认值 ==> (SYSDBA) :" sysdbapwd
read -p "请输出须要删除的 oneaccess 库及用户的后缀, 默认为空串:" suffix
suffix=${suffix:-""}
sysdbapwd=${sysdbapwd:-"SYSDBA"}
dmport=${dmport:-"5236"}

if ["$suffix" = ""]; then
    read -p "后缀为空, 请确认是否删除 (yes/no)：" input
    input=$(echo "$input" | tr "[A-Z]" "[a-z]")
    if ["$input" = "yes"]; then
        echo "yes go"
    else
        exit
    fi
else
    echo go!
fi

$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop user bam${suffix} cascade;"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop TABLESPACE bam${suffix};"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop user bim${suffix} cascade;"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop TABLESPACE bim${suffix};"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop user epass${suffix} cascade;"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop TABLESPACE epass${suffix};"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop user apphub${suffix} cascade;"
$dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e "drop TABLESPACE apphub${suffix};"

# $dmdispwd SYSDBA/${sysdbapwd}:${dmport} -e <<eof
# drop user bam${suffix} cascade;
# drop tablespace bam${suffix};
# eof
退出移动版