达梦读写分离集群搭建

1.介绍

读写分离集群是基于即时归档或实时归档实现的高性能数据库集群，不但提供数据保护、容灾等数据守护基本功能，还具有读写操作自动分离、负载均衡等特性；读写分离集群可以配置为即时归档，也可以配置为实时归档，这两种配置方式仅仅是归档流程上有差别，读写分离集群的特性仍然是一致的。实际实施时一般配置即时归档事务一致性模式。

2.环境准备

主机	ip地址	实例名	主备	操作系统
rw1	192.168.44.51	GRP1_RW_01	主库	Redhat6
rw2	192.168.44.52	GRP1_RW_02	备库	Redhat6
rw3	192.168.44.53	GRP1_RW_03	监视器	Redhat6

[root@rw1 ~]# groupadd dinstall
[root@rw1 ~]# useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
[root@rw1 ~]# passwd dmdba

[root@rw1 ~]# mount -o loop dm.iso /mnt
[root@rw1 ~]# su - dmdba
[dmdba@rw1 ~]$ cd /mnt
[dmdba@rw1 /mnt]$ ./ DMInstall.bin -i

注册Damp服务
[root@rw1 ~]# /home/dmdba/dmdbms/script/root/root_installer.sh

3.初始化数据库

[dmdba@rw1 bin]$ mkdir /home/dmdba/dmdbms/data
[dmdba@rw1 bin]$ cd /home/dmdba/dmdbms/bin
[dmdba@rw1 bin]$ ./dminit path=/home/dmdba/dmdbms/data/

备库类似。

4.启停主备库数据库

使用前台启动的方式，启停主备库数据库一次
./dmserver /home/dmdba/dmdbms/data/GRP1_RW_01/dm.ini
成功启动后输入exit即可退出。

5.备份还原

主库备份

[dmdba@rw1 bin]$ ./dmrman ctlstmt="BACKUP DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/home/dmdba/back/DAMENG'"

备库数据库还原:

./dmrman ctlstmt="RESTORE DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/home/dmdba/DAMENG'"

备库数据库恢复:

./dmrman ctlstmt="RECOVER DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini' FROM BACKUPSET '/home/dmdba/DAMENG'"  

备库更新:

./dmrman ctlstmt="RECOVER DATABASE '/home/dmdba/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC"

6.配置主库

6.1修改dm.ini文件

INSTANCE_NAME = GRP1_RW_01
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1

6.2配置dmmal.ini文件

MAL_CHECK_INTERVAL	= 5
MAL_CONN_FAIL_INTERVAL	= 5

[MAL_INST1]
MAL_INST_NAME	= GRP1_RW_01
MAL_HOST	= 192.168.44.51
MAL_PORT	= 5237
MAL_INST_HOST	= 192.168.44.51
MAL_INST_PORT	= 5236
MAL_DW_PORT	= 5238
MAL_INST_DW_PORT= 5239

[MAL_INST2]
MAL_INST_NAME   = GRP1_RW_02
MAL_HOST        = 192.168.44.52
MAL_PORT        = 5237
MAL_INST_HOST   = 192.168.44.52
MAL_INST_PORT   = 5236
MAL_DW_PORT     = 5238
MAL_INST_DW_PORT= 5239

6.3配置 dmarch.ini

ARCH_WAIT_APPLY = 1
[ARCHIVE_TIMELY1]
ARCH_TYPE       = TIMELY
ARCH_DEST       = GRP1_RW_02

[ARCHIVE_LOCAL1]
ARCH_TYPE       = LOCAL
ARCH_DEST       = /home/dmdba/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE  = 128
ARCH_SPACE_LIMIT= 0

6.4配置dmwatcher.ini文件

[GRP1]
DW_TYPE                 = GLOBAL
DW_MODE                 = MANUAL
DW_ERROR_TIME           = 10
INST_RECOVER_TIME       = 60
INST_ERROR_TIME         = 10
INST_OGUID              = 453331
INST_INI                = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_restart       = 1
INST_STARTUP_CMD    = /home/dmdba/dmdbms/bin/DmServiceDMSERVER restart
RLOG_SEND_THRESHOLD     = 0
RLOG_APPLY_THRESHOLD    = 0

6.5启动主库

以 Mount 方式启动主库
./dmserver /dm/data/DAMENG/dm.ini mount

6.6设置 OGUID、修改数据库模式

启动命令行工具 disql

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(453331);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

7.配置备库

7.1修改dm.ini文件

INSTANCE_NAME = GRP1_RW_02
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1

7.2配置dmmal.ini文件

配置MAL系统，各主备库的 dmmal.ini 配置必须完全一致，MAL_HOST 使用内部网络IP，MAL_PORT与dm.ini中PORT_NUM 使用不同的端口值，MAL_DW_PORT 是各实例对应的守护进程之间，以及守护进程和监视器之间的通信端口。

MAL_CHECK_INTERVAL	= 5
MAL_CONN_FAIL_INTERVAL	= 5

[MAL_INST1]
MAL_INST_NAME	= GRP1_RW_01
MAL_HOST	= 192.168.44.51
MAL_PORT	= 5237
MAL_INST_HOST	= 192.168.44.51
MAL_INST_PORT	= 5236
MAL_DW_PORT	= 5238
MAL_INST_DW_PORT= 5239

[MAL_INST2]
MAL_INST_NAME   = GRP1_RW_02
MAL_HOST        = 192.168.44.52
MAL_PORT        = 5237
MAL_INST_HOST   = 192.168.44.52
MAL_INST_PORT   = 5236
MAL_DW_PORT     = 5238
MAL_INST_DW_PORT= 5239

7.3配置 dmarch.ini

ARCH_WAIT_APPLY = 1
[ARCHIVE_TIMELY1]
ARCH_TYPE       = TIMELY
ARCH_DEST       = GRP1_RW_01

[ARCHIVE_LOCAL1]
ARCH_TYPE       = LOCAL
ARCH_DEST       = /home/dmdba/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE  = 128
ARCH_SPACE_LIMIT= 0

7.4配置dmwatcher.ini文件

[GRP1]
DW_TYPE                 = GLOBAL
DW_MODE                 = MANUAL
DW_ERROR_TIME           = 10
INST_RECOVER_TIME       = 60
INST_ERROR_TIME         = 10
INST_OGUID              = 453331
INST_INI                = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_restart       = 1
INST_STARTUP_CMD    = /home/dmdba/dmdbms/bin/DmServiceDMSERVER restart
RLOG_SEND_THRESHOLD     = 0
RLOG_APPLY_THRESHOLD    = 0

7.5启动主库

以 Mount 方式启动主库
./dmserver /dm/data/DAMENG/dm.ini mount

7.6设置 OGUID、修改数据库模式

启动命令行工具 disql

SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(453331);
SQL>alter database standby;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

8.配置监视器

在rw3上安装达梦数据库，并创建dmmonitor.ini文件，内容如下：

MON_DW_CONFIRM = 1
MON_LOG_PATH = /home/dmdba/dmdbms/mon_log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP_SP]
MON_INST_OGUID = 453331
MON_DW_IP = 192.168.44.51:5238
MON_DW_IP = 192.168.44.52:5238

9.启动守护进程

主库 ./dmwatcher /home/dmdba/dmdbms/data/GRP_SP_1/dmwatcher.ini
备库 ./dmwatcher /home/dmdba/dmdbms/data/GRP_SP_2/dmwatcher.ini

10.启动监视器

[dmdba@rw3 bin]$ ./dmmonitor dmmonitor.ini

从监视器中可以看到集群配置成功。

11.测试

主库插入数据

./disql SYSDBA/SYSDBA
SQL> create table test(name varchar(10), id int);
SQL> insert into test values ('ccc', 1);
SQL> commit;
SQL> exit

备库查询

./disql SYSDBA/SYSDBA
SQL> select * from test;

备库查询到数据说明配置正常。