达梦数据库主备一键切换脚本(避免明文密码方案)
一、命令行输入口令脚本
达梦数据库使用sql脚本做切换,要确定主库和备库,还有各种状态,比较麻烦。
达梦主备数据库的切换,使用dmmonitor还是很方便的。但每次需要login,输入口令密码,使用shell脚本来使用该工具,却不太容易。
看下面的例子:
[dmdba@kylin101 bin]$ cat test1.sh
#!/usr/bin/bash
cd /dm8/dmdbms/bin
./dmmonitor path=/dm8/dmdbms/bin/dmmonitor_normal.ini <<EOF
login
sysdba
SYSDBA
switchover
exit
EOF
脚本执行异常,如下:
[dmdba@kylin101 bin]$ ./test1.sh
[monitor] 2023-07-03 15:51:03: DMMONITOR[4.0] V8
[monitor] 2023-07-03 15:51:04: DMMONITOR[4.0] IS READY.
[monitor] 2023-07-03 15:51:04: Received message from(DMSVR01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 15:51:04 OPEN OK DMSVR01 OPEN STANDBY NULL 13 234117 234117
[monitor] 2023-07-03 15:51:04:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DMSVR01), THE FIRST LINE IS SELF INFO.
DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2023-07-03 15:51:04 FALSE 1361669200 ::ffff:10.168.100.6 DMMONITOR[4.0] V8
2023-07-03 07:28:18 TRUE 1809688859 ::ffff:10.168.100.168 DMMONITOR[4.0] V8
#--------------------------------------------------------------------------------#
[monitor] 2023-07-03 15:51:04: Received message from(DMSVR02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 15:43:16 OPEN OK DMSVR02 OPEN PRIMARY VALID 13 234117 234118
username:password:
Input command illegal, please input help to get the help information!
[monitor] 2023-07-03 15:51:09: Username or password error, or user does not have DBA privilege!
[monitor] 2023-07-03 15:51:09: Not login dmmonitor or server public key changed, please try to login again!
[dmdba@kylin101 bin]$
二、expect命令输入脚本
1、使用expect 脚本无法解决明文密码问题。
2、达梦数据库监控器命令行启动没有login参数,借助中间脚本构造的expect脚本失败。
[dmdba@kylin101 bin]$ cat test3.sh
#!/usr/bin/bash
cd /dm8/dmdbms/bin
./dmmonitor path=/dm8/dmdbms/bin/dmmonitor_normal.ini <<EOF
login
EOF
[dmdba@kylin101 bin]$ cat ./test4.sh
#!/usr/bin/expect -f
set user sysdba
set password SYSDBA
set timeout 30
cd /dm8/dmdbms/bin
spawn ./test3.sh
expect {
"username:" { send "$user\r"}
}
expect {
"password:" { send "$password\r" }
}
expect eof
[dmdba@kylin101 bin]$ ./test4.sh
spawn ./test3.sh
[monitor] 2023-07-03 17:18:58: DMMONITOR[4.0] V8
[monitor] 2023-07-03 17:18:59: DMMONITOR[4.0] IS READY.
[monitor] 2023-07-03 17:18:59: Received message from(DMSVR01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:18:59 OPEN OK DMSVR01 OPEN PRIMARY VALID 14 236642 236643
[monitor] 2023-07-03 17:18:59:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DMSVR01), THE FIRST LINE IS SELF INFO.
DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2023-07-03 17:18:59 FALSE 1105975184 ::ffff:10.168.100.6 DMMONITOR[4.0] V8
2023-07-03 07:28:18 TRUE 1809688859 ::ffff:10.168.100.168 DMMONITOR[4.0] V8
#--------------------------------------------------------------------------------#
[monitor] 2023-07-03 17:18:59: Received message from(DMSVR02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:11:11 OPEN OK DMSVR02 OPEN STANDBY VALID 14 236641 236641
username:password:
[monitor] 2023-07-03 17:19:00: Username or password error, or user does not have DBA privilege!
三、达梦密码文件(wallet 文件),解决明文密码问题
使用密码文件,实现免密达梦数据库主备一键切换脚本。
1、创建 wallet 文件
[dmdba@kylin101 bin]$ cd /dm8/dmdbms/bin
[dmdba@kylin101 bin]$ ./dmmkstore -wrl /dm8/data/DM01 -create
DM Secret Store Tool: V8
Enter password:
Enter password again:
2、在 wallet 文件中创建凭据
[dmdba@kylin101 bin]$ ./dmmkstore -wrl /dm8/data/DM01 -createCredential dm_monitor_local SYSDBA SYSDBA
DM Secret Store Tool: V8
Enter wallet password:
Create credential DM.security.client.connect_string1
3、修改监控器文件
vi dmmonitor_normal.ini
MON_WALLET_LOCATION =/dm8/data/DM01
4、一键切换shell脚本
[dmdba@kylin101 bin]$ cat test.sh
#!/usr/bin/bash
cd /dm8/dmdbms/bin
./dmmonitor path=/dm8/dmdbms/bin/dmmonitor_normal.ini <<EOF
login /@dm_monitor_local
switchover
exit
EOF
三、完整脚本测试结果
[dmdba@kylin101 bin]$ ./test.sh
[monitor] 2023-07-03 17:42:35: DMMONITOR[4.0] V8
[monitor] 2023-07-03 17:42:35: DMMONITOR[4.0] IS READY.
[monitor] 2023-07-03 17:42:36: Received message from(DMSVR01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:42:35 OPEN OK DMSVR01 OPEN PRIMARY VALID 14 237159 237160
[monitor] 2023-07-03 17:42:36:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DMSVR01), THE FIRST LINE IS SELF INFO.
DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2023-07-03 17:42:35 FALSE 1718136137 ::ffff:10.168.100.6 DMMONITOR[4.0] V8
2023-07-03 07:28:18 TRUE 1809688859 ::ffff:10.168.100.168 DMMONITOR[4.0] V8
#--------------------------------------------------------------------------------#
[monitor] 2023-07-03 17:42:36: Received message from(DMSVR02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:34:47 OPEN OK DMSVR02 OPEN STANDBY VALID 14 237159 237159
[monitor] 2023-07-03 17:42:38: Login dmmonitor success!
[monitor] 2023-07-03 17:42:40: Start to switchover instance DMSVR02
[monitor] 2023-07-03 17:42:40: Notify dmwatcher(DMSVR01) switch to SWITCHOVER status
[monitor] 2023-07-03 17:42:40: Dmwatcher process DMSVR01 status switching [OPEN-->SWITCHOVER]
[monitor] 2023-07-03 17:42:41: Switch dmwatcher DMSVR01 to SWITCHOVER status success
[monitor] 2023-07-03 17:42:41: Notify dmwatcher(DMSVR02) switch to SWITCHOVER status
[monitor] 2023-07-03 17:42:41: Dmwatcher process DMSVR02 status switching [OPEN-->SWITCHOVER]
[monitor] 2023-07-03 17:42:42: Switch dmwatcher DMSVR02 to SWITCHOVER status success
[monitor] 2023-07-03 17:42:42: Instance DMSVR01 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 6)
[monitor] 2023-07-03 17:42:43: Instance DMSVR01 execute sql SP_SET_GLOBAL_DW_STATUS(0, 6) success
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 6)
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 execute sql SP_SET_GLOBAL_DW_STATUS(0, 6) success
[monitor] 2023-07-03 17:42:43: Instance DMSVR01 start to execute sql ALTER DATABASE MOUNT
[monitor] 2023-07-03 17:42:43: Instance DMSVR01 execute sql ALTER DATABASE MOUNT success
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 start to execute sql SP_APPLY_KEEP_PKG()
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 execute sql SP_APPLY_KEEP_PKG() success
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 start to execute sql ALTER DATABASE MOUNT
[monitor] 2023-07-03 17:42:43: Instance DMSVR02 execute sql ALTER DATABASE MOUNT success
[monitor] 2023-07-03 17:42:43: Instance DMSVR01 start to execute sql ALTER DATABASE STANDBY
[monitor] 2023-07-03 17:42:44: Instance DMSVR01 execute sql ALTER DATABASE STANDBY success
[monitor] 2023-07-03 17:42:44: Instance DMSVR02 start to execute sql ALTER DATABASE PRIMARY
[monitor] 2023-07-03 17:42:44: Instance DMSVR02 execute sql ALTER DATABASE PRIMARY success
[monitor] 2023-07-03 17:42:44: Notify instance DMSVR02 to change all arch status to be invalid
[monitor] 2023-07-03 17:42:44: Succeed to change all instances arch status to be invalid
[monitor] 2023-07-03 17:42:44: Instance DMSVR01 start to execute sql ALTER DATABASE OPEN FORCE
[monitor] 2023-07-03 17:42:45: Instance DMSVR01 execute sql ALTER DATABASE OPEN FORCE success
[monitor] 2023-07-03 17:42:45: Instance DMSVR02 start to execute sql ALTER DATABASE OPEN FORCE
[monitor] 2023-07-03 17:42:46: Instance DMSVR02 execute sql ALTER DATABASE OPEN FORCE success
[monitor] 2023-07-03 17:42:46: Instance DMSVR01 start to execute sql SP_SET_GLOBAL_DW_STATUS(6, 0)
[monitor] 2023-07-03 17:42:46: Instance DMSVR01 execute sql SP_SET_GLOBAL_DW_STATUS(6, 0) success
[monitor] 2023-07-03 17:42:46: Instance DMSVR02 start to execute sql SP_SET_GLOBAL_DW_STATUS(6, 0)
[monitor] 2023-07-03 17:42:46: Instance DMSVR02 execute sql SP_SET_GLOBAL_DW_STATUS(6, 0) success
[monitor] 2023-07-03 17:42:46: Notify dmwatcher(DMSVR01) switch to OPEN status
[monitor] 2023-07-03 17:42:46: Dmwatcher process DMSVR01 status switching [SWITCHOVER-->OPEN]
[monitor] 2023-07-03 17:42:47: Switch dmwatcher DMSVR01 to OPEN status success
[monitor] 2023-07-03 17:42:47: Notify dmwatcher(DMSVR02) switch to OPEN status
[monitor] 2023-07-03 17:42:47: Dmwatcher process DMSVR02 status switching [SWITCHOVER-->OPEN]
[monitor] 2023-07-03 17:42:48: Switch dmwatcher DMSVR02 to OPEN status success
[monitor] 2023-07-03 17:42:48: Notify group(GRP1)'s dmwatcher to do clear
[monitor] 2023-07-03 17:42:48: Clean request of dmwatcher processer DMSVR01 success
2023-07-03 17:42:48
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453331 FALSE MANUAL FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
10.168.100.8 65121 2023-07-03 17:35:00 GLOBAL VALID OPEN DMSVR02 OK 1 1 OPEN PRIMARY DSC_OPEN REALTIME VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
10.168.100.8 5236 OK DMSVR02 OPEN PRIMARY 0 0 REALTIME VALID 26691 237621 26691 237621 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
10.168.100.6 65101 2023-07-03 17:42:48 GLOBAL VALID OPEN DMSVR01 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME INVALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
10.168.100.6 5236 OK DMSVR01 OPEN STANDBY 0 0 REALTIME INVALID 26684 237162 26684 237162 NONE
DATABASE(DMSVR01) APPLY INFO FROM (DMSVR02), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[26684, 26684, 26684], (RLSN, SLSN, KLSN)[237162, 237162, 237162], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (237162)
#================================================================================#
[monitor] 2023-07-03 17:42:48: Clean request of dmwatcher processer DMSVR02 success
[monitor] 2023-07-03 17:42:48: Switchover instance DMSVR02 success
[dmdba@kylin101 bin]$ ./test.sh
[monitor] 2023-07-03 17:42:56: DMMONITOR[4.0] V8
[monitor] 2023-07-03 17:42:57: DMMONITOR[4.0] IS READY.
[monitor] 2023-07-03 17:42:57: Received message from(DMSVR01)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:42:57 OPEN OK DMSVR01 OPEN STANDBY NULL 15 237624 237624
[monitor] 2023-07-03 17:42:57:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DMSVR01), THE FIRST LINE IS SELF INFO.
DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2023-07-03 17:42:57 FALSE 461494553 ::ffff:10.168.100.6 DMMONITOR[4.0] V8
2023-07-03 07:28:18 TRUE 1809688859 ::ffff:10.168.100.168 DMMONITOR[4.0] V8
#--------------------------------------------------------------------------------#
[monitor] 2023-07-03 17:42:57: Received message from(DMSVR02)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2023-07-03 17:35:09 OPEN OK DMSVR02 OPEN PRIMARY VALID 15 237624 237624
[monitor] 2023-07-03 17:42:59: Login dmmonitor success!
[monitor] 2023-07-03 17:43:01: Start to switchover instance DMSVR01
[monitor] 2023-07-03 17:43:01: Notify dmwatcher(DMSVR02) switch to SWITCHOVER status
[monitor] 2023-07-03 17:43:01: Dmwatcher process DMSVR02 status switching [OPEN-->SWITCHOVER]
[monitor] 2023-07-03 17:43:02: Switch dmwatcher DMSVR02 to SWITCHOVER status success
[monitor] 2023-07-03 17:43:02: Notify dmwatcher(DMSVR01) switch to SWITCHOVER status
[monitor] 2023-07-03 17:43:02: Dmwatcher process DMSVR01 status switching [OPEN-->SWITCHOVER]
[monitor] 2023-07-03 17:43:03: Switch dmwatcher DMSVR01 to SWITCHOVER status success
[monitor] 2023-07-03 17:43:03: Instance DMSVR02 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 6)
[monitor] 2023-07-03 17:43:03: Instance DMSVR02 execute sql SP_SET_GLOBAL_DW_STATUS(0, 6) success
[monitor] 2023-07-03 17:43:03: Instance DMSVR01 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 6)
[monitor] 2023-07-03 17:43:03: Instance DMSVR01 execute sql SP_SET_GLOBAL_DW_STATUS(0, 6) success
[monitor] 2023-07-03 17:43:03: Instance DMSVR02 start to execute sql ALTER DATABASE MOUNT
[monitor] 2023-07-03 17:43:03: Instance DMSVR02 execute sql ALTER DATABASE MOUNT success
[monitor] 2023-07-03 17:43:03: Instance DMSVR01 start to execute sql SP_APPLY_KEEP_PKG()
[monitor] 2023-07-03 17:43:04: Instance DMSVR01 execute sql SP_APPLY_KEEP_PKG() success
[monitor] 2023-07-03 17:43:04: Instance DMSVR01 start to execute sql ALTER DATABASE MOUNT
[monitor] 2023-07-03 17:43:04: Instance DMSVR01 execute sql ALTER DATABASE MOUNT success
[monitor] 2023-07-03 17:43:04: Instance DMSVR02 start to execute sql ALTER DATABASE STANDBY
[monitor] 2023-07-03 17:43:05: Instance DMSVR02 execute sql ALTER DATABASE STANDBY success
[monitor] 2023-07-03 17:43:05: Instance DMSVR01 start to execute sql ALTER DATABASE PRIMARY
[monitor] 2023-07-03 17:43:05: Instance DMSVR01 execute sql ALTER DATABASE PRIMARY success
[monitor] 2023-07-03 17:43:05: Notify instance DMSVR01 to change all arch status to be invalid
[monitor] 2023-07-03 17:43:05: Succeed to change all instances arch status to be invalid
[monitor] 2023-07-03 17:43:05: Instance DMSVR02 start to execute sql ALTER DATABASE OPEN FORCE
[monitor] 2023-07-03 17:43:05: Instance DMSVR02 execute sql ALTER DATABASE OPEN FORCE success
[monitor] 2023-07-03 17:43:05: Instance DMSVR01 start to execute sql ALTER DATABASE OPEN FORCE
[monitor] 2023-07-03 17:43:06: Instance DMSVR01 execute sql ALTER DATABASE OPEN FORCE success
[monitor] 2023-07-03 17:43:06: Instance DMSVR02 start to execute sql SP_SET_GLOBAL_DW_STATUS(6, 0)
[monitor] 2023-07-03 17:43:06: Instance DMSVR02 execute sql SP_SET_GLOBAL_DW_STATUS(6, 0) success
[monitor] 2023-07-03 17:43:06: Instance DMSVR01 start to execute sql SP_SET_GLOBAL_DW_STATUS(6, 0)
[monitor] 2023-07-03 17:43:06: Instance DMSVR01 execute sql SP_SET_GLOBAL_DW_STATUS(6, 0) success
[monitor] 2023-07-03 17:43:06: Notify dmwatcher(DMSVR02) switch to OPEN status
[monitor] 2023-07-03 17:43:06: Dmwatcher process DMSVR02 status switching [SWITCHOVER-->OPEN]
[monitor] 2023-07-03 17:43:08: Switch dmwatcher DMSVR02 to OPEN status success
[monitor] 2023-07-03 17:43:08: Notify dmwatcher(DMSVR01) switch to OPEN status
[monitor] 2023-07-03 17:43:09: Dmwatcher process DMSVR01 status switching [SWITCHOVER-->OPEN]
[monitor] 2023-07-03 17:43:09: Switch dmwatcher DMSVR01 to OPEN status success
[monitor] 2023-07-03 17:43:09: Notify group(GRP1)'s dmwatcher to do clear
[monitor] 2023-07-03 17:43:09: Clean request of dmwatcher processer DMSVR01 success
2023-07-03 17:43:09
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453331 FALSE MANUAL FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
10.168.100.6 65101 2023-07-03 17:43:09 GLOBAL VALID OPEN DMSVR01 OK 1 1 OPEN PRIMARY DSC_OPEN REALTIME VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
10.168.100.6 5236 OK DMSVR01 OPEN PRIMARY 0 0 REALTIME VALID 26703 238216 26703 238217 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
10.168.100.8 65121 2023-07-03 17:35:20 GLOBAL VALID OPEN DMSVR02 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME INVALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
10.168.100.8 5236 OK DMSVR02 OPEN STANDBY 0 0 REALTIME INVALID 26696 237626 26696 237626 NONE
DATABASE(DMSVR02) APPLY INFO FROM (DMSVR01), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[26696, 26696, 26696], (RLSN, SLSN, KLSN)[237626, 237626, 237626], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (237626)
#================================================================================#
[monitor] 2023-07-03 17:43:09: Clean request of dmwatcher processer DMSVR02 success
[monitor] 2023-07-03 17:43:09: Switchover instance DMSVR01 success