【DM数据守护集群搭建-读写分离】
DM数据守护集群搭建-读写分离
读写分离集群由一个主库以及一个或者多个配置了即时(Timely)归档或实时(Realtime)归档的备库组成,其主要目标是在保障数据库可用性基础上,实现读、写操作的自动分离,进一步提升数据库的业务支撑能力。读写分离集群通过配置事务一致模式保证主、备库数据一致性,并配合达梦数据库管理系统的各种接口(JDBC、DPI等),将只读操作自动分流到备库,有效降低主库的负载,提升系统吞吐量。
一、环境规划
配置项 | A机器(主节点) | B机器(从节点) | C机器(从节点) |
---|---|---|---|
心跳IP | 192.168.148.128 | 192.168.148.130 | 192.168.148.133 |
实例名 | DM_01 | DM_02 | DM_03 |
实例端口 | 5236 | 5236 | 5236 |
MAL端口 | 5336 | 5336 | 5336 |
MAL守护进程端口 | 5436 | 5436 | 5436 |
守护进程端口 | 5536 | 5536 | 5536 |
OGUID | 45331 | 45331 | 45331 |
守护组 | GRP1 | GRP1 | GRP1 |
安装目录 | /home/dmdba/dmdbms | /home/dmdba/dmdbms | /home/dmdba/dmdbms |
实例归档 | /home/dmdba/dmdbms/data | /home/dmdba/dmdbms/data | /home/dmdba/dmdbms/data |
归档上限 | 51200 | 51200 | 51200 |
二、环境搭建
初始化主库实例DM_01
su - dmdba
cd /home/dmdba/dmdbms/bin
./dminit path=/home/dmdba/dmdbms/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=Y DB_NAME=DM INSTANCE_NAME=DM_01 PORT_NUM=5236 BUFFER=2048 SYSDBA_PWD=DaMeng123 SYSAUDITOR_PWD=DaMeng123
初始化从库实例DM_02
su - dmdba
cd /home/dmdba/dmdbms/bin
./dminit path=/home/dmdba/dmdbms/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=Y DB_NAME=DM INSTANCE_NAME=DM_02 PORT_NUM=5236 BUFFER=2048 SYSDBA_PWD=DaMeng123 SYSAUDITOR_PWD=DaMeng123
初始化从库实例DM_03
su - dmdba
cd /home/dmdba/dmdbms/bin
./dminit path=/home/dmdba/dmdbms/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=Y DB_NAME=DM INSTANCE_NAME=DM_03 PORT_NUM=5236 BUFFER=2048 SYSDBA_PWD=DaMeng123 SYSAUDITOR_PWD=DaMeng123
主节点注册服务
su root
cd /home/dmdba/dmdbms/script/root
./dm_service_installer.sh -t dmserver -dm_ini /home/dmdba/dmdbms/data/DM/dm.ini -p DMSERVER
创建成功后启动服务
systemctl start DmServiceDMSERVER.service
注意:三台机器关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
1、配置DM_01主库
修改dm.ini
#configuration fileMAL_INI = 1 #dmmal.iniARCH_INI = 1 #dmarch.ini
#data watchALTER_MODE_STATUS = 0ENABLE_OFFLINE_TS = 2
创建dmarch.ini
vim /home/dmdba/dmdbms/data/DM/dmarch.ini
#DaMeng Database Archive Configuration file
#0:高性能,1:事务一致
ARCH_WAIT_APPLY = 0
[ARCHIVE_LOCAL]
#本地归档类型
ARCH_TYPE = LOCAL
#本地归档存放路径
ARCH_DEST = /home/dmdba/dmdbms/data/DM/arch
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 51200
[ARCHIVE_TIMELY1]
#实时归档类型
ARCH_TYPE = TIMELY
#实时归档目标实例名
ARCH_DEST = DM_02
[ARCHIVE_TIMELY2]
#实时归档类型
ARCH_TYPE = TIMELY
#实时归档目标实例名
ARCH_DEST = DM_03
创建dmmal.ini
vim /home/dmdba/dmdbms/data/DM/dmmal.ini
MAL_CHECK_INTERVAL = 10 #MAL 链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 10 #判定 MAL 链路断开的时间
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/malpath/ #临时文件目录
MAL_BUF_SIZE = 512 #单个 MAL 缓存大小,单位 MB
MAL_SYS_BUF_SIZE = 2048 #MAL 总大小限制,单位 MB
MAL_COMPRESS_LEVEL = 0 #MAL 消息压缩等级,0 表示不压缩[MAL_INST1]
MAL_INST_NAME = DM_01 #实例名,和 dm.ini 的 INSTANCE_NAME 一致
MAL_HOST = 192.168.148.128 #MAL 系统监听 TCP 连接的 IP 地址
MAL_PORT = 5336 #MAL 系统监听 TCP 连接的端口
MAL_INST_HOST = 192.168.148.128 #实例的对外服务 IP 地址
MAL_INST_PORT = 5236 #实例对外服务端口,和 dm.ini 的 PORT_NUM 一致
MAL_DW_PORT = 5436 #实例对应的守护进程监听 TCP 连接的端口
MAL_INST_DW_PORT = 5536 #实例监听守护进程 TCP 连接的端口[MAL_INST2]
MAL_INST_NAME = DM_02
MAL_HOST = 192.168.148.130
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.130
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536[MAL_INST3]
MAL_INST_NAME = DM_03
MAL_HOST = 192.168.148.133
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.133
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536
创建dmwatcher.ini
vim /home/dmdba/dmdbms/data/DM/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #MANUAL:故障手切 AUTO:故障自切
DW_ERROR_TIME = 20 #远程守护进程故障认定时间
INST_ERROR_TIME = 20 #本地实例故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_OGUID = 45331 #守护系统唯一 OGUID 值
INST_INI = /home/dmdba/dmdbms/data/DM/dm.ini #dm.ini 文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值,默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值,默认关闭
2、配置DM_02从库参数
修改 dm.ini
#configuration file
MAL_INI = 1 #dmmal.ini
ARCH_INI = 1 #dmarch.ini
#data watch
ALTER_MODE_STATUS = 0 #Whether to permit database user to alter database mode
ENABLE_OFFLINE_TS = 2 #Whether tablespace can be offline
创建dmarch.ini
vi /home/dmdba/dmdbms/data/DM/dmarch.ini
#DaMeng Database Archive Configuration file
#0:高性能,1:事务一致
ARCH_WAIT_APPLY = 0
[ARCHIVE_LOCAL]
#本地归档类型ARCH_TYPE = LOCAL
#本地归档存放路径 ARCH_DEST = /home/dmdba/dmdbms/data/DM/arch ARCH_FILE_SIZE = 1024ARCH_SPACE_LIMIT = 51200
[ARCHIVE_TIMELY1]
#实时归档类型ARCH_TYPE = TIMELY
#实时归档目标实例名ARCH_DEST = DM_01
[ARCHIVE_TIMELY2]
#实时归档类型ARCH_TYPE = TIMELY
#实时归档目标实例名ARCH_DEST = DM_03
创建dmmal.ini
vi /home/dmdba/dmdbms/data/DM/dmmal.ini
MAL_CHECK_INTERVAL = 10 #MAL 链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 10 #判定 MAL 链路断开的时间
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/malpath/ #临时文件目录
MAL_BUF_SIZE = 512 #单个 MAL 缓存大小,单位 MB
MAL_SYS_BUF_SIZE = 2048 #MAL 总大小限制,单位 MB
MAL_COMPRESS_LEVEL = 0 #MAL 消息压缩等级,0 表示不压缩[MAL_INST1]
MAL_INST_NAME = DM_01 #实例名,和 dm.ini 的 INSTANCE_NAME 一致
MAL_HOST = 192.168.148.128 #MAL 系统监听 TCP 连接的 IP 地址
MAL_PORT = 5336 #MAL 系统监听 TCP 连接的端口
MAL_INST_HOST = 192.168.148.128 #实例的对外服务 IP 地址
MAL_INST_PORT = 5236 #实例对外服务端口,和 dm.ini 的 PORT_NUM 一致
MAL_DW_PORT = 5436 #实例对应的守护进程监听 TCP 连接的端口
MAL_INST_DW_PORT = 5536 #实例监听守护进程 TCP 连接的端口[MAL_INST2]
MAL_INST_NAME = DM_02
MAL_HOST = 192.168.148.130
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.130
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536[MAL_INST3]
MAL_INST_NAME = DM_03
MAL_HOST = 192.168.148.133
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.133
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536
创建 dmwatcher.ini
vi /home/dmdba/dmdbms/data/DM/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #MANUAL:故障手切 AUTO:故障自切
DW_ERROR_TIME = 20 #远程守护进程故障认定时间
INST_ERROR_TIME = 20 #本地实例故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_OGUID = 45331 #守护系统唯一 OGUID 值
INST_INI = /home/dmdba/dmdbms/data/DM/dm.ini #dm.ini 文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值,默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值,默认关闭
3、配置DM_03从库参数
修改 dm.ini
#configuration file
MAL_INI = 1 #dmmal.ini
ARCH_INI = 1 #dmarch.ini
#data watch
ALTER_MODE_STATUS = 0 #Whether to permit database user to alter database mode
ENABLE_OFFLINE_TS = 2 #Whether tablespace can be offline
创建dmarch.ini
vi /home/dmdba/dmdbms/data/DM/dmarch.ini
#DaMeng Database Archive Configuration file
#0:高性能,1:事务一致
ARCH_WAIT_APPLY = 0
[ARCHIVE_LOCAL]
#本地归档类型
ARCH_TYPE = LOCAL
#本地归档存放路径
ARCH_DEST = /home/dmdba/dmdbms/data/DM/arch
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 51200
[ARCHIVE_TIMELY1]
#实时归档类型
ARCH_TYPE = TIMELY
#实时归档目标实例名
ARCH_DEST = DM_01
[ARCHIVE_TIMELY2]
#实时归档类型
ARCH_TYPE = TIMELY
#实时归档目标实例名
ARCH_DEST = DM_02
创建dmmal.ini
vi /home/dmdba/dmdbms/data/DM/dmmal.ini
MAL_CHECK_INTERVAL = 10 #MAL 链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 10 #判定 MAL 链路断开的时间
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/malpath/ #临时文件目录
MAL_BUF_SIZE = 512 #单个 MAL 缓存大小,单位 MB
MAL_SYS_BUF_SIZE = 2048 #MAL 总大小限制,单位 MB
MAL_COMPRESS_LEVEL = 0 #MAL 消息压缩等级,0 表示不压缩[MAL_INST1]
MAL_INST_NAME = DM_01 #实例名,和 dm.ini 的 INSTANCE_NAME 一致
MAL_HOST = 192.168.148.128 #MAL 系统监听 TCP 连接的 IP 地址
MAL_PORT = 5336 #MAL 系统监听 TCP 连接的端口
MAL_INST_HOST = 192.168.148.128 #实例的对外服务 IP 地址
MAL_INST_PORT = 5236 #实例对外服务端口,和 dm.ini 的 PORT_NUM 一致
MAL_DW_PORT = 5436 #实例对应的守护进程监听 TCP 连接的端口
MAL_INST_DW_PORT = 5536 #实例监听守护进程 TCP 连接的端口[MAL_INST2]
MAL_INST_NAME = DM_02
MAL_HOST = 192.168.148.130
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.130
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536[MAL_INST3]
MAL_INST_NAME = DM_03
MAL_HOST = 192.168.148.133
MAL_PORT = 5336
MAL_INST_HOST = 192.168.148.133
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536
创建 dmwatcher.ini
vi /home/dmdba/dmdbms/data/DM/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #MANUAL:故障手切 AUTO:故障自切
DW_ERROR_TIME = 20 #远程守护进程故障认定时间
INST_ERROR_TIME = 20 #本地实例故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_OGUID = 45331 #守护系统唯一 OGUID 值
INST_INI = /home/dmdba/dmdbms/data/DM/dm.ini #dm.ini 文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值,默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值,默认关闭
4、主节点备份,从节点恢复
主节点停止服务
systemctl stop DmServiceDMSERVER.service
主从库均启动AP服务
cd /home/dmdba/dmdbms/bin/service_template/
./DmAPService start
备份主节点
cd /home/dmdba/dmdbms/bin
./dmrman
backup database '/home/dmdba/dmdbms/data/DM/dm.ini' backupset '/home/dmdba/dmdbms/data/DM/bak/dmbackup';
备份文件拷贝到从库
scp -r /home/dmdba/dmdbms/data/DM/bak/dmbackup root@192.168.148.130:/home/dmdba/dmdbms/data/DM/bak
scp -r /home/dmdba/dmdbms/data/DM/bak/dmbackup root@192.168.148.133:/home/dmdba/dmdbms/data/DM/bak
恢复从节点(两节点均要执行)
su dmdba
cd /home/dmdba/dmdbms/bin
./dmrman
restore database '/home/dmdba/dmdbms/data/DM/dm.ini' from backupset '/home/dmdba/dmdbms/data/DM/bak/dmbackup';
recover database '/home/dmdba/dmdbms/data/DM/dm.ini' from backupset '/home/dmdba/dmdbms/data/DM/bak/dmbackup';
recover database '/home/dmdba/dmdbms/data/DM/dm.ini' update db_magic;
注册服务
#主节点DM_01执行
su root
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p DM_01 -dm_ini /home/dmdba/dmdbms/data/DM/dm.ini -m mount
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DM/dmwatcher.ini
#从库DM_02执行
su root
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p DM_02 -dm_ini /home/dmdba/dmdbms/data/DM/dm.ini -m mount
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DM/dmwatcher.ini
#从库DM_03执行
su root
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p DM_03 -dm_ini /home/dmdba/dmdbms/data/DM/dm.ini -m mount
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DM/dmwatcher.ini
5、启动主节点和从节点服务并修改参数
主库执行
/home/dmdba/dmdbms/bin/DmServiceDM_01 start
/home/dmdba/dmdbms/bin/disql SYSDBA/DaMeng123:5236
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SP_SET_OGUID(45331);
ALTER DATABASE PRIMARY;
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
从节点DM_02执行
/home/dmdba/dmdbms/bin/DmServiceDM_02 start
/home/dmdba/dmdbms/bin/disql SYSDBA/Dameng:5236
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SP_SET_OGUID(45331);
ALTER DATABASE STANDBY;
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
从节点DM_03执行
/home/dmdba/dmdbms/bin/DmServiceDM_03 start
/home/dmdba/dmdbms/bin/disql SYSDBA/Dameng:5236
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SP_SET_OGUID(45331);
ALTER DATABASE STANDBY;
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
启动守护进程(三台机器均需要启动)
/home/dmdba/dmdbms/bin/DmWatcherServiceWatcher start
配置监视器
可以使用备节点创建监视器,生产环境还是需要独立部署监视器。
创建监视器配置文件
vi /home/dmdba/dmdbms/data/DM/dmmonitor.ini
MON_DW_CONFIRM = 1 #确认监视器模式(0非确认监视器)
MON_LOG_PATH = ../log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔60秒定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 512 #日志文件最大512M
MON_LOG_SPACE_LIMIT = 2048 #不限定日志文件总占用空间
[GRP1]
MON_INST_OGUID = 45331 #组GRP1的唯一OGUID值
MON_DW_IP = 192.168.148.128:5436 #集群内部节点1
MON_DW_IP = 192.168.148.130:5436 #集群内部节点2
MON_DW_IP = 192.168.148.133:5436 #集群内部节点3
创建dmmonitor_manual.ini
vi /home/dmdba/dmdbms/data/DM/dmmonitor_manual.ini
MON_DW_CONFIRM = 0 #确认监视器模式(0非确认监视器)
MON_LOG_PATH = ../log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔60秒定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 512 #日志文件最大512M
MON_LOG_SPACE_LIMIT = 2048 #不限定日志文件总占用空间
[GRP1]
MON_INST_OGUID = 45331 #组GRP1的唯一OGUID值
MON_DW_IP = 192.168.148.128:5436 #集群内部节点1
MON_DW_IP = 192.168.148.130:5436 #集群内部节点2
MON_DW_IP = 192.168.148.133:5436 #集群内部节点3
注册监视器服务
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmmonitor -p Monitor -monitor_ini /home/dmdba/dmdbms/data/DM/dmmonitor.ini
启动监视器
#后台启动
/home/dmdba/dmdbms/bin/DmMonitorServiceMonitor start#手动启动
cd /home/dmdba/dmdbms/bin
./dmmonitor /home/dmdba/dmdbms/data/DM/dmmonitor_manual.ini
三、环境验证及读写分离配置
启动监视器验证
cd /home/dmdba/dmdbms/bin
./dmmonitor /home/dmdba/dmdbms/data/DM_02/dmmonitor_manual.ini
#使用show global info
show global info
读写分离配置
#修改dm_svc.conf(文件具体位置可使用 find / -name dm_svc.conf进行查找)
vi /home/dmdba/dmdbms/bin/dm_svc.conf
#文件修改为
TIME_ZONE=(480)
LANGUAGE=(cn)
DMRW=(192.168.220.130:5236,192.168.220.131:5236,192.168.220.132:5236)
[DMRW]
#优先登录的服务器模式
LOGIN_MODE=(1)
#表示是否启用读写分离。0:不启用;1:启用;
RW_SEPARATE=(1)
#表示读写分离分发比例,有效值范围 0~100,主库占所有事物数的比例。可以根据主备库的实际压力来设定。
RW_PERCENT=(30)
#未找到符合条件的库成功建立连接,将尝试遍历服务名中库列表的次数
SWITCH_TIMES=(60)
#服务器之间切换的时间间隔,单位为毫秒
SWITCH_INTERVAL=(1000)