- 不带主备部署
- 环境准备
两台虚拟机如下:
实例名 | MAL_INST_HOST | MAL_INST_PORT | MAL_HOST | MAL端口 | MPP_SEQNO |
EP01 | 192.168.43.137 | 5236 | 192.168.43.137 | 5347 | 0 |
EP02 | 192.168.43.138 | 5236 | 192.168.43.138 | 5347 | 1 |
2. 初始化及配置dm.ini dmmal.ini
初始化数据库如下
cd /home/dmdba/dmdbms/bin ./dminit path=/home/dmdba/dmdbms instance_name=EP01 db_name=EP01 ./dminit path=/home/dmdba/dmdbms instance_name=EP02 db_name=EP02 |
配置两节点dm.ini参数如下
EP01: INSTANCE_NAME=EP01 PORT_NUM=5236 MAIL_INI=1 MPP_INI=1 EP02: INSTANCE_NAME=EP02 PORT_NUM=5236 MAIL_INI=1 MPP_INI=1 |
配置dmmal.ini如下
[MAL_INST1] MAL_INST_NAME = EP01 MAL_HOST = 192.168.43.137 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.137 MAL_INST_PORT = 5236 [MAL_INST2] MAL_INST_NAME = EP02 MAL_HOST = 192.168.43.138 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.138 MAL_INST_PORT = 5236 |
3.配置dmmpp.ctl并启动实例
配置dmmpp.txt参数
[service_name1] mpp_seq_no=0 mpp_inst_name=EP01 [service_name2] mpp_seq_no=1 mpp_inst_name=EP02 |
通过以下命令转换生成dmmpp.ctl
./dmctlcvt type=2 src=/home/dmdba/dmdbms/EP01/dmmpp.txt dest=/home/dmdba/dmdbms/dmmpp.ctl |
执行结果如下图所示
复制dmmpp.ctl到EP02后启动2个节点实例无主备MPP环境即搭建完成
scp -rp /home/dmdba/dmdbms/EP01/dmmpp.ctl 192.168.43.168:/home/dmdba/dmdbms/EP02 启动2个节点数据库即可 ./dmserver /home/dmdba/dmdbms/EP01/dm.ini ./dmserver /home/dmdba/dmdbms/EP02/dm.ini |
4. 测试
创建LIST分布表T_LIST,分布列为C1并根据分布表规则插入测试数据
CREATE TABLE T_LIST(C1 INT,C2 CHAR(10))DISTRIBUTED BY LIST (C1) (VALUES(3) ON EP01,VALUES(4) ON EP02); insert into T_LIST values (3,'testEP01'); insert into T_LIST values (4,'testEP02'); insert into T_LIST values (3,'test2'); insert into T_LIST values (4,'test2'); |
查询结果确定数据分别在哪个节点上
./disql SYSDBA/SYSDBA@192.168.43.138:5236 #通过SF_GET_EP_SEQNO(ROWID)函数可以查看数据分布在哪个节点 SQL>select SF_GET_EP_SEQNO(ROWID),C1,C2 from t_list; |
5.配置服务名
在客户端配置如下:
vi /etc/dm_svc.conf dmmpp=(192.168.43.137:5236,192.136.48.138:5236) TIME_ZONE=(480) LANGUAGE=(cn) |
登陆验证
./disql SYSDBA/SYSDBA@dmmpp |
根据登陆连接测试可以发现是按顺序遍历所列出的IP进行连接的。
- 带主备部署
- 环境准备
添加两台虚拟服务器EP11、EP12分别为EP01、EP02备库
实例名 | MAL_INST_HOST | MAL_INST_PORT | MAL_HOST | MAL端口 | MPP_SEQNO |
EP01 | 192.168.43.137 | 5236 | 192.168.43.137 | 5347 | 0 |
EP02 | 192.168.43.138 | 5236 | 192.168.43.138 | 5347 | 1 |
EP11 | 192.168.43.139 | 5237 | 192.168.43.139 | 5347 | |
EP12 | 192.168.43.140 | 5237 | 192.168.43.140 | 5347 |
- 关闭EP01、EP02并进行脱机备份
./dmrman CTLSTMT="BACKUP DATABASE '/home/dmdba/dmdbms/EP01/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/home/dmdba/dmdbms/BAK/EP01/'" ./dmrman CTLSTMT="BACKUP DATABASE '/home/dmdba/dmdbms/EP02/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/home/dmdba/dmdbms/BAK/EP02/'" 复制到EP11、EP12两台机器上去 cd /home/dmdba/dmdbms scp -rp BAK/ 192.168.43.139:/home/dmdba/dmdbms/ scp -rp BAK/ 192.168.43.134:/home/dmdba/dmdbms/ |
结果如下:
3.初始化备库并还原与恢复
EP11、EP12上
#初始化 cd /home/dmdba/dmdbms/bin ./dminit path=/home/dmdba/dmdbms instance_name=EP11 db_name=EP01 ./dminit path=/home/dmdba/dmdbms instance_name=EP12 db_name=EP02 #还原 ./dmrman CTLSTMT="RESTORE DATABASE '/home/dmdba/dmdbms/EP01/dm.ini' FROM BACKUPSET '/home/dmdba/dmdbms/BAK/EP01/'" ./dmrman CTLSTMT="RECOVER DATABASE '/home/dmdba/dmdbms/EP01/dm.ini' FROM BACKUPSET '/home/dmdba/dmdbms/BAK/EP01/'" ./dmrman CTLSTMT="RECOVER DATABASE '/home/dmdba/dmdbms/EP01/dm.ini' UPDATE DB_MAGIC" ./dmrman CTLSTMT="RESTORE DATABASE '/home/dmdba/dmdbms/EP02/dm.ini' FROM BACKUPSET '/home/dmdba/dmdbms/BAK/EP02/'" ./dmrman CTLSTMT="RECOVER DATABASE '/home/dmdba/dmdbms/EP02/dm.ini' FROM BACKUPSET '/home/dmdba/dmdbms/BAK/EP02/'" ./dmrman CTLSTMT="RECOVER DATABASE '/home/dmdba/dmdbms/EP02/dm.ini' UPDATE DB_MAGIC" |
4.配置主备库
dm.ini
EP01
INSTANCE_NAME = EP01 PORT_NUM = 5236 #数据库实例监听端口 DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间 ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID ENABLE_OFFLINE_TS = 2 #不允许备库OFFLINE表空间 MAL_INI = 1 #打开MAL系统 ARCH_INI = 1 #打开归档配置 MPP_INI = 1 #启用MPP配置 RLOG_SEND_APPLY_MON = 64 #统计最近64次的日志发送信息 |
EP02
INSTANCE_NAME = EP02 PORT_NUM = 5236 #数据库实例监听端口 DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间 ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID ENABLE_OFFLINE_TS = 2 #不允许备库OFFLINE表空间 MAL_INI = 1 #打开MAL系统 ARCH_INI = 1 #打开归档配置 MPP_INI = 1 #启用MPP配置 RLOG_SEND_APPLY_MON = 64 #统计最近64次的日志发送信息 |
dmmal.ini(4个节点一致)
[MAL_INST1] MAL_INST_NAME = EP01 MAL_HOST = 192.168.43.137 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.137 MAL_INST_PORT = 5236 MAL_DW_PORT = 5253 MAL_INST_DW_PORT = 5243 [MAL_INST2] MAL_INST_NAME = EP02 MAL_HOST = 192.168.43.138 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.138 MAL_INST_PORT = 5236 MAL_DW_PORT = 5253 MAL_INST_DW_PORT = 5243 [MAL_INST3] MAL_INST_NAME = EP11 MAL_HOST = 192.168.43.139 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.139 MAL_INST_PORT = 5236 MAL_DW_PORT = 5253 MAL_INST_DW_PORT = 5243 [MAL_INST4] MAL_INST_NAME = EP12 MAL_HOST = 192.168.43.140 MAL_PORT = 5347 MAL_INST_HOST = 192.168.43.140 MAL_INST_PORT = 5236 MAL_DW_PORT = 5253 MAL_INST_DW_PORT = 5243 |
dmarch.ini
EP01
[ARCHIVE_REALTIME1] ARCH_TYPE = REALTIME #实时归档类型 ARCH_DEST = EP11 #实时归档目标实例名 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL #本地归档类型 ARCH_DEST = /home/dmdba/dmdbms/EP01/arch #本地归档文件存放路径 ARCH_FILE_SIZE = 128 #单位Mb,本地单个归档文件最大值 ARCH_SPACE_LIMIT = 0 #单位Mb,0表示无限制,范围1024~4294967294M |
EP11
[ARCHIVE_REALTIME1] ARCH_TYPE = REALTIME #实时归档类型 ARCH_DEST = EP01 #实时归档目标实例名 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL #本地归档类型 ARCH_DEST = /home/dmdba/dmdbms/EP01/arch #本地归档文件存放路径 ARCH_FILE_SIZE = 128 #单位Mb,本地单个归档文件最大值 ARCH_SPACE_LIMIT = 0 #单位Mb,0表示无限制,范围1024~4294967294M |
EP02
[ARCHIVE_REALTIME1] ARCH_TYPE = REALTIME #实时归档类型 ARCH_DEST = EP12 #实时归档目标实例名 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL #本地归档类型 ARCH_DEST = /home/dmdba/dmdbms/EP02/arch #本地归档文件存放路径 ARCH_FILE_SIZE = 128 #单位Mb,本地单个归档文件最大值 ARCH_SPACE_LIMIT = 0 #单位Mb,0表示无限制,范围1024~4294967294M |
EP12
[ARCHIVE_REALTIME1] ARCH_TYPE = REALTIME #实时归档类型 ARCH_DEST = EP02 #实时归档目标实例名 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL #本地归档类型 ARCH_DEST = /home/dmdba/dmdbms/EP02/arch #本地归档文件存放路径 ARCH_FILE_SIZE = 128 #单位Mb,本地单个归档文件最大值 ARCH_SPACE_LIMIT = 0 #单位Mb,0表示无限制,范围1024~4294967294M |
dmmpp.ctl
直接从EP01节点上copy
dmmpp.ini如下:
[service_name1] mpp_seq_no = 0 mpp_inst_name = EP01 [service_name2] mpp_seq_no = 1 mpp_inst_name = EP02 |
5 . 启动主库并设置OGUID
./dmserver /home/dmdba/dmdbms/EP01/dm.ini mount ./dmserver /home/dmdba/dmdbms/EP02/dm.ini mount ./disql SYSDBA/SYSDBA@192.168.43.137 SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); SQL>sp_set_oguid(1001); SQL>alter database primary; SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); ./disql SYSDBA/SYSDBA@192.168.43.138 SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); SQL>sp_set_oguid(1002); SQL>alter database primary; SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); |
6. 启动备库并设置OGUID
./dmserver /home/dmdba/dmdbms/EP01/dm.ini mount ./dmserver /home/dmdba/dmdbms/EP02/dm.ini mount ./disql SYSDBA/SYSDBA@192.168.43.139 SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); SQL>sp_set_oguid(1001); SQL>alter database STANDBY; SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); ./disql SYSDBA/SYSDBA@192.168.43.140 SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); SQL>sp_set_oguid(1002); SQL>alter database STANDBY; SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); |
7. 配置dmwatcher.ini
分别对应在EP01、EP11上配置如下
[MPP1] DW_TYPE = GLOBAL #全局守护类型 DW_MODE = AUTO #自动切换模式 DW_ERROR_TIME = 10 #远程守护进程故障认定时间 INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间 INST_ERROR_TIME =10#本地实例故障认定时间 INST_OGUID = 1001 #守护系统唯一OGUID值 INST_INI = /home/dmdba/dmdbms/EP01/dm.ini #dm.ini配置文件路径 INST_AUTO_RESTART = 1 #打开实例的自动启动功能 INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动 RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值, |
在EP02、EP12上配置如下
[MPP2] DW_TYPE = GLOBAL #全局守护类型 DW_MODE = AUTO #自动切换模式 DW_ERROR_TIME = 10 #远程守护进程故障认定时间 INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间 INST_ERROR_TIME =10#本地实例故障认定时间 INST_OGUID = 1002 #守护系统唯一OGUID值 INST_INI = /home/dmdba/dmdbms/EP02/dm.ini #dm.ini配置文件路径 INST_AUTO_RESTART = 1 #打开实例的自动启动功能 INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动 RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值, |
8. 配置监视器
在192.168.43.130监视服务器上添加如下:
[MPP1] MON_INST_OGUID = 1001 MON_DW_IP = 192.168.43.137:5253 MON_DW_IP = 192.168.43.139:5253 [MPP2] MON_INST_OGUID = 1002 MON_DW_IP = 192.168.43.138:5253 MON_DW_IP = 192.168.43.140:5253 |
9. 启动守护进程与监视器
4个节点开启守护进程 ./dmwatcher /home/dmdba/dmdbms/EP01/dmwatcher.ini ./dmwatcher /home/dmdba/dmdbms/EP02/dmwatcher.ini 监视服务器上启动监视器 ./dmmonitor /home/dmdba/dmdbms/dmmonitor.ini |
最终监视器上可以看到MPP主备集群状态正常如下:
10.测试故障一个节点情况
直接虚拟机关掉2节点(其中一个主节点)后监视器上收到如下信息:
表明节点2(EP02)与节点4(节点2的备用节点EP12)发生了自动主备切换从而保证DMMPP集群还能够正常提供服务(EP12代替EP02成为MPP2守护组的主库继续提供服务)
继续进行数据读写测试
./disql SYSDBA/SYSDBA@192.168.43.137:5236 SQL>select SF_GET_EP_SEQNO(ROWID),C1,C2 from t_list; SQL>insert into T_LIST values (4,'故障后'); SQL>insert into T_LIST values (4,'故障后'); SQL>insert into T_LIST values (3,'故障后'); SQL>select SF_GET_EP_SEQNO(ROWID),C1,C2 from t_list; |
测试结果如上图依然还能提供dmmpp集群正常功能,数据依然会按照t_list分布表创建时的规则分布在不同的节点上。
更多资讯请上达梦技术社区了解: https://eco.dameng.com