达梦安装部署MPP

环境:
OS:Centos 7
DB:DM8

节点1:192.168.1.134 instance_name:slnngk01
节点2:192.168.1.135 instance_name:slnngk02

说明:
数据库软件安装、用户和用户组以及系统内核参数配置这里省略,可以参考单机的部署

1.初始化数据库

##节点1:192.168.1.134
su - dmdba
dminit path=/dmdbms/data charset=1 db_name=slnngk instance_name=slnngk01 SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"

##节点2:192.168.1.135
su - dmdba
dminit path=/dmdbms/data charset=1 db_name=slnngk instance_name=slnngk02 SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"

2.注册服务
需要用操作系统root账号登陆注册,2个节点的命令一致
节点1:
[root@host134 root]# cd /dmdbms/product/script/root
[root@localhost root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/slnngk/dm.ini -p slnngk

节点2:
[root@host135 root]# cd /dmdbms/product/script/root
[root@localhost root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/slnngk/dm.ini -p slnngk

注册了服务就可以使用操作系统的systemctl命令停启数据库了
systemctl status DmServiceslnngk.service
systemctl start DmServiceslnngk.service

3.尝试启动数据库
节点1和节点2都执行如下命令,root账号下执行
[root@host134 ~]#systemctl status DmServiceslnngk.service

这个时候可以看下数据库默认端口是否启动

节点1:
[root@host134 ~]# ss -nlp|grep 5236
tcp    LISTEN     0      128      :::5236                 :::*                   users:(("dmserver",pid=30859,fd=4))

节点2:
[root@host135 root]# ss -nlp|grep 5236
tcp    LISTEN     0      128      :::5236                 :::*                   users:(("dmserver",pid=30504,fd=4))

4.修改dm.ini参数
开启如下配置文件开关
# 节点1
su - dmdba
vi /dmdbms/data/slnngk/dm.ini
MAL_INI = 1
MPP_INI = 1

# 节点2
vi /dmdbms/data/slnngk/dm.ini
MAL_INI = 1
MPP_INI = 1

5.配置MAL参数
# 节点1和节点2的该配置文件保持一致
vi /dmdbms/data/slnngk/dmmal.ini

MAL_CHECK_INTERVAL   = 5  #MAL 链路检测时间间隔
MAL_CONN_FAIL_INTERVAL  = 5  #判定 MAL 链路断开的时间

[MAL_INST1]
MAL_INST_NAME = slnngk01
MAL_HOST = 192.168.1.134
MAL_PORT = 61141
MAL_INST_HOST = 192.168.1.134
MAL_INST_PORT = 5236

[MAL_INST2]
MAL_INST_NAME = slnngk02
MAL_HOST = 192.168.1.135
MAL_PORT = 61141
MAL_INST_HOST = 192.168.1.135
MAL_INST_PORT = 5236

6.mpp控制文件
该配置文件两个节点都一样

vi /dmdbms/data/slnngk/dmmpp.ini

[SERVICE_NAME1]
MPP_SEQ_NO = 0
MPP_INST_NAME = slnngk01

[SERVICE_NAME2]
MPP_SEQ_NO = 1
MPP_INST_NAME = slnngk02

7.mpp控制文件转化
使用 dmctlcvt 工具将 dmmpp.ini 转化为 ctl 控制文件
# 节点1
su - dmdba
dmctlcvt type=2 SRC=/dmdbms/data/slnngk/dmmpp.ini DEST=/dmdbms/data/slnngk/dmmpp.ctl

# 节点2
su - dmdba
dmctlcvt type=2 SRC=/dmdbms/data/slnngk/dmmpp.ini DEST=/dmdbms/data/slnngk/dmmpp.ctl

7.配置归档(可选)

每个节点都一样,该步骤在mpp里不是强制，但是线上建议需要配置归档模式

disql sysdba/dameng123
alter database mount;
alter database add archivelog 'dest=/dmdbms/arch,TYPE=local,FILE_SIZE=1024';
alter database archivelog;
alter database open;

或是直接生成配置文件,2个节点配置都一样

vi /dmdbms/data/slnngk/dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

ARCH_WAIT_APPLY      = 0

[ARCHIVE_LOCAL1]
        ARCH_TYPE            = LOCAL
        ARCH_DEST            = /dmdbms/arch
        ARCH_FILE_SIZE       = 1024
        ARCH_FLUSH_BUF_SIZE  = 0
        ARCH_HANG_FLAG       = 1

8.重启动服务
节点1和节点2执行的命令一致(root账号下执行):

[root@host134 root]#systemctl restart DmServiceslnngk.service
[root@host135 root]#systemctl restart DmServiceslnngk.service

9.验证集群
其中一个节点登陆
[dmdba@host134 slnngk]$ disql sysdba/dameng123

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.866(ms)
disql V8
SQL> select instance_name from v$instance;

LINEID INSTANCE_NAME
---------- -------------
1 SLNNGK01
2 SLNNGK02

used time: 7.887(ms). Execute id is 367470.

10.创建schema(与oracle一样)
我这里登陆到节点1上执行创建
创建表空间

[dmdba@host134 slnngk]$ disql sysdba/dameng123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.418(ms)
disql V8
SQL> create tablespace tps_hxl datafile '/dmdbms/data/slnngk/tps_hxl01.DBF' size 1024 autoextend on;
executed successfully
used time: 00:00:01.035. Execute id is 1100


这个时候节点2也会自动创建该表空间的
节点1:
SQL> select tablespace_name from dba_tablespaces;

LINEID     TABLESPACE_NAME
---------- ---------------
1          SYSTEM
2          ROLL
3          TEMP
4          MAIN
5          TPS_HXL
6          MAIN

6 rows got

used time: 11.110(ms). Execute id is 902037.


节点2:
[dmdba@host135 slnngk]$ disql sysdba/dameng123

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.280(ms)
disql V8
SQL> select tablespace_name from dba_tablespaces;

LINEID     TABLESPACE_NAME
---------- ---------------
1          SYSTEM
2          ROLL
3          TEMP
4          MAIN
5          TPS_HXL
6          MAIN

6 rows got

used time: 13.577(ms). Execute id is 760038.

##创建用户,在其中一个节点上执行:
create user hxl identified by dameng123 default tablespace tps_hxl default index tablespace tps_hxl;
grant dba to hxl;

设置密码永不过期
alter user hxl limit password_life_time unlimited;

创建用户也是一样的,在一个节点上执行,会自动同步到另外的节点

11.连接方式

本地连接,本地连接指定mpp_type=local,查询到的是本地的信息
节点1:

[dmdba@host135 slnngk]$ disql SYSDBA/dameng123@192.168.1.134:5236#"{mpp_type=local}"

Server[192.168.1.134:5236]:mode is normal, state is open
login used time : 3.111(ms)
disql V8
SQL> select instance_name from v$instance;

LINEID     INSTANCE_NAME
---------- -------------
1          SLNNGK01

used time: 3.651(ms). Execute id is 1400.}"

节点2:

[dmdba@host135 slnngk]$ disql SYSDBA/dameng123@192.168.1.135:5236#"{mpp_type=local}"

Server[192.168.1.135:5236]:mode is normal, state is open
login used time : 1.852(ms)
disql V8
SQL> select instance_name from v$instance;

LINEID INSTANCE_NAME
---------- -------------
1 SLNNGK02

used time: 2.461(ms). Execute id is 1400.

若是不指定mpp_type,那么默认的就是全局连接
连接任何一个EP:

[dmdba@host135 slnngk]$ disql SYSDBA/dameng123@192.168.1.135:5236

Server[192.168.1.135:5236]:mode is normal, state is open
login used time : 3.046(ms)
disql V8
SQL> select instance_name from v$instance;

LINEID     INSTANCE_NAME
---------- -------------
1          SLNNGK02
2          SLNNGK01

used time: 8.528(ms). Execute id is 137261.

或是指定:mpp_type=global

[dmdba@host135 slnngk]$ disql SYSDBA/dameng123@192.168.1.135:5236#"{mpp_type=global}"

Server[192.168.1.135:5236]:mode is normal, state is open
login used time : 2.592(ms)
disql V8
SQL> select instance_name from v$instance;

LINEID     INSTANCE_NAME
---------- -------------
1          SLNNGK02
2          SLNNGK01

used time: 6.534(ms). Execute id is 144858.

12.数据验证
登陆任何一个EP创建表并写入数据,我们这里登陆节点1(使用刚才创建的用户)
创建哈希分布表T_HASH，分布列为C1.

disql hxl/dameng123
create table t_hash(c1 int, c2 char(10)) distributed by hash (c1);

insert into t_hash values(1,'name1');
insert into t_hash values(2,'name2');
insert into t_hash values(3,'name3');
insert into t_hash values(4,'name4');
insert into t_hash values(5,'name5');
insert into t_hash values(6,'name6');
insert into t_hash values(7,'name7');
insert into t_hash values(8,'name8');
insert into t_hash values(9,'name9');
insert into t_hash values(10,'name10');
commit;

集群中看到所有的数据
SQL> select * from t_hash;

LINEID     C1          C2        
---------- ----------- ----------
1          1           name1     
2          3           name3     
3          5           name5     
4          7           name7     
5          9           name9     
6          2           name2     
7          4           name4     
8          6           name6     
9          8           name8     
10         10          name10    

10 rows got

used time: 5.221(ms). Execute id is 854054.

尝试通过本地登陆查看

disql hxl/dameng123@192.168.1.134:5236#"{mpp_type=local}"
SQL> select * from t_hash;

LINEID     C1          C2        
---------- ----------- ----------
1          2           name2     
2          4           name4     
3          6           name6     
4          8           name8     
5          10          name10    

used time: 1.581(ms). Execute id is 1800.

可以看到只能查到本地的数据.