读写分离集群由一个主库以及一个或者多个配置了即时(Timely)归档的备库组成,其主要目标是在保障数据库可用性基础上,实现读、写操作的自动分离,进一步提升数据库 的业务支撑能力。读写分离集群通过即时归档机制保证主、备库数据一致性,并配合达梦数 据库管理系统的各种接口(JDBC、DPI等),将只读操作自动分流到备库,有效降低主库的 负载,提升系统吞吐量
环境准备
主机 | ip地址 | 实例名 | 主备 | 操作系统 |
hx1 | 192.168.1.10 | RW_01 | 主库 | CentOS |
hx2 | 192.168.1.11 | RW_02 | 备库 | CentOS |
hx3 | 192.168.1.12 | RW_03 | 监视器 | CentOS |
初始化数据库
主库:./dminit path=/home/dmdba/dmdbms/data db_name=RW_1 page_size=32 extent_size=16 case_sensitive=y
备库:./dminit path=/home/dmdba/dmdbms/data db_name=RW_1 page_size=32 extent_size=16 case_sensitive=y
分别启停主备数据库
./dmserver /home/dmdba/dmdbms/data/RW_1/dm.ini
进行备份还原
主库上备份:
./dmrman ctlstmt="BACKUP DATABASE '/home/dmdba/dmdbms/data/RW_1/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/home/dmdba/back/DAMENG'"
发送到备库进行还原
./dmrman ctlstmt="RESTORE DATABASE '/home/dmdba/dmdbms/data/RW_1/dm.ini' FROM BACKUPSET '/home/dmdba/back'"
数据恢复
./dmrman ctlstmt="RECOVER DATABASE '/home/dmdba/dmdbms/data/RW_1/dm.ini' FROM BACKUPSET '/home/dmdba/back'"
配置主库
修改dm.ini文件
INSTANCE_NAME= RW_1
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1
配置dmmal.ini文件
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = RW_1
MAL_HOST = 192.168.1.10
MAL_PORT = 5237
MAL_INST_HOST = 192.168.1.10
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238
MAL_INST_DW_PORT= 5239
[MAL_INST2]
MAL_INST_NAME = RW_1
MAL_HOST = 192.168.1.11
MAL_PORT = 5237
MAL_INST_HOST = 192.168.1.11
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238
MAL_INST_DW_PORT= 5239
配置 dmarch.ini
ARCH_WAIT_APPLY = 1
[ARCHIVE_TIMELY1]
ARCH_TYPE = TIMELY
ARCH_DEST = RW_1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /home/dmdba/dmdbms/data/RW_1/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT= 0
配置dmwatcher.ini文件
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453332
INST_INI = /home/dmdba/dmdbms/data/RW_1/dm.ini
INST_AUTO_restart = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/DmServiceDMSERVER restart
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
生成dmwatcher.ctl并发送到备库
./dmctlcvt TYPE=3 SRC=/home/dmdba/dmdbms/data/RW_1/dmwatcher.ini DEST=/home/dmdba/dmdbms/data/RW_1
启动主库
以 Mount 方式启动主库
./dmserver /home/dmdba/dmdbms/data/RW_1/dm.ini mount
设置 OGUID、修改数据库模式
启动命令行工具 disql
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL>sp_set_oguid(453332);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
配置备库
修改dm.ini文件
INSTANCE_NAME = RW_1
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1
配置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 = RW_1
MAL_HOST = 192.168.1.10
MAL_PORT = 5237
MAL_INST_HOST = 192.168.1.10
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238
MAL_INST_DW_PORT= 5239
[MAL_INST2]
MAL_INST_NAME = RW_1
MAL_HOST = 192.168.1.11
MAL_PORT = 5237
MAL_INST_HOST = 192.168.1.11
MAL_INST_PORT = 5236
MAL_DW_PORT = 5238
MAL_INST_DW_PORT= 5239
配置 dmarch.ini
ARCH_WAIT_APPLY = 1
[ARCHIVE_TIMELY1]
ARCH_TYPE = TIMELY
ARCH_DEST = RW_1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /home/dmdba/dmdbms/data/RW_1/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT= 0
配置dmwatcher.ini文件
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453332
INST_INI = /home/dmdba/dmdbms/data/RW_1/dm.ini
INST_AUTO_restart = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/DmServiceDMSERVER restart
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
启动备库
以 Mount 方式启动备库
./dmserver /dm/data/DAMENG/dm.ini mount
设置 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);
配置监视器
在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 = 453332
MON_DW_IP = 192.168.1.10:5238
MON_DW_IP = 192.168.1.11:5238
启动守护进程
主库 ./dmwatcher /home/dmdba/dmdbms/data/RW_1/dmwatcher.ini
备库 ./dmwatcher /home/dmdba/dmdbms/data/RW_1/dmwatcher.ini
启动监视器
[dmdba@rw3 bin]$ ./dmmonitor dmmonitor.ini
测试
主库插入数据
备库查询
备库查询到数据说明配置正常。
遇到的问题
启动监视器报错:
code=-6010, oguid or group_name mismatch, please check config!!!
定位:监视器中的组名和dmwatcher.ini中不一致导致启动报错,修改成一致后启动正常
备份报错:
未启动dmap导致备份失败,启动dmap即可备份成功