DM7搭建读写分离集群

2025-04-11 技术教程

读写分离集群
环境说明
下列机器事先都安装了DM软件，安装路径为/dm7,执行程序保存在/dm7/bin目录中，数据存放路径为/dm7/data

机器名IP地址初始状态操作系统18c110.13.13.171(对外)主库JY1redhat6.710.13.13.171(mal对内)18c210.13.13.172(对外)10.13.13.171(mal对内)备库JY2redhat6.7dmks10.13.13.187确认监视器redhat6.7实例名port_numdw_portmal_hostmal_portmal_dw_portjy15236523910.13.13.17152375238jy25236523910.13.13.17252375238

数据准备
在主库机器上初始化数据库到目录/dm7/data:

[dmdba@18c1bin]$./dminitpath=/dm7/datadb_name=jyinstance_name=jy1port_num=5236page_size=8charset=0initdbV7.1.6.46-Build(2018.02.08-89107)ENTdbversion:0x7000afiledm.keynotfound,usedefaultlicense!Licensewillexpirein14day(s)on2020-06-17logfilepath:/dm7/data/jy/jy01.loglogfilepath:/dm7/data/jy/jy02.logwritetodir[/dm7/data/jy].createdmdatabasesuccess.2020-06-0316:01:44

注册服务用于启动数据库

[root@18c1root]#./dm_service_installer.sh-i/dm7/data/jy/dm.ini-pjy1-tdmserverln-s'/usr/lib/systemd/system/DmServicejy1.service''/etc/systemd/system/multi-user.target.wants/DmServicejy1.service'Finishedtocreatetheservice(DmServicejy1)

正常启动数据库并正常关闭

[root@18c1root]#serviceDmServicejy1startRedirectingto/bin/systemctlstartDmServicejy1.service[root@18c1root]#ps-ef|grepdmserverdmdba2998911316:04?00:00:04/dm7/bin/dmserver/dm7/data/jy/dm.ini-noconsoleroot302923890016:05pts/100:00:00grep--color=autodmserver[dmdba@18c1bin]$./disqlSYSDBA/SYSDBAServer[LOCALHOST:5236]:modeisnormal,stateisopenloginusedtime:8.010(ms)disqlV7.1.6.46-Build(2018.02.08-89107)ENTConnectedto:DM7.1.6.46SQL>exit[root@18c1root]#serviceDmServicejy1stopRedirectingto/bin/systemctlstopDmServicejy1.service

一.配置操作
主库
配置dm.ini文件，配置以下参数

[dmdba@18c1jy]$vidmmal.iniINSTANCE_NAME=JY1PORT_NUM=5236DW_PORT=5239DW_ERROR_TIME=60ALTER_MODE_STATUS=0ENABLE_OFFLINE_TS=2MAL_INI=1ARCH_INI=1HA_INST_CHECK_FLAG=1RLOG_SEND_APPLY_MON=64

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

[dmdba@18c1jy]$vidmmal.iniMAL_CHECK_INTERVAL=5MAL_CONN_FAIL_INTERVAL=5[MAL_INST1]MAL_INST_NAME=JY1MAL_HOST=10.13.13.171MAL_PORT=5237MAL_INST_HOST=10.13.13.171MAL_INST_PORT=5236MAL_DW_PORT=5238[MAL_INST2]MAL_INST_NAME=JY2MAL_HOST=10.13.13.172MAL_PORT=5237MAL_INST_HOST=10.13.13.172MAL_INST_PORT=5236MAL_DW_PORT=5238

配置dmarch.ini
修改dmarch.ini，配置本地归档和实时归档。除了本地归档外，其他归档配置项中的ARCH_DEST表示实例是Primary模式时，需要同步归档数据的目标实例名。当前实例DM1是主库，需要向DM2（实时备库）同步数据，因此实时归档的ARCH_DEST配置为DM2。

[dmdba@18c1jy]$vidmarch.ini[ARCHIVE_TIMELY]ARCH_TYPE=TIMELYARCH_DEST=JY2[ARCHIVE_LOCAL1]ARCH_TYPE=LOCALARCH_DEST=/dm7/data/jy/archARCH_FILE_SIZE=128ARCH_SPACE_LIMIT=0

配置dmwatcher.ini
修改dmwatcher.ini配置守护进程，配置为全局守护类型，使用自动切换模式。

[dmdba@18c1jy]$vidmwatcher.ini[GRP1]DW_TYPE=GLOBALDW_MODE=AUTODW_ERROR_TIME=10INST_RECOVER_TIME=60INST_ERROR_TIME=10INST_OGUID=453332INST_INI=/dm7/data/jy/dm.iniINST_AUTO_RESTART=1INST_STARTUP_CMD=/dm7/bin/dmserverRLOG_SEND_THRESHOLD=0RLOG_APPLY_THRESHOLD=0

配置dmwatcher.ctl
同一个守护进程组，必须使用同一份dmwatcher.ctl。因此，只需要使用dmctlcvt工具生成一份dmwatcher.ctl文件，然后分别拷贝到各个数据库目录下即可。在配置完成dmwatcher.ini后，使用dmctlcvt工具生成dmwatcher.ctl：(特别注意，DEST目录为jy的上一级目录，否则不生成控制文件)

[dmdba@18c1bin]$./dmctlcvtTYPE=3SRC=/dm7/data/jy/dmwatcher.iniDEST=/dm7/dataDMCTLCVTV7.1.6.46-Build(2018.02.08-89107)ENTconverttxttoctlsuccess!

会在/dm7/data目录中生成一个GRP1目录，在GRP1目录中生成了dmwatcher.ctl控制文件

[dmdba@18c1data]$ls-lrttotal4drwxr-xr-x6dmdbadinstall4096Jun316:23jydrwxr-xr-x2dmdbadinstall26Jun316:23GRP1[dmdba@18c1data]$cdGRP1/[dmdba@18c1GRP1]$ls-lrttotal4-rw-r--r--1dmdbadinstall512Jun316:23dmwatcher.ctl[dmdba@18c1GRP1]$cpdmwatcher.ctl/dm7/data/jy/

拷贝生成的dmwatcher.ctl文件到数据文件目录/dm7/data/jy。

将主库相关文件传输到备机:

[dmdba@18c1dm7]$scp-rdata/dmdba@10.13.13.172:/dm7/Theauthenticityofhost'10.13.13.172(10.13.13.172)'can'tbeestablished.ECDSAkeyfingerprintis7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded'10.13.13.172'(ECDSA)tothelistofknownhosts.dmdba@10.13.13.172'spassword:dminit20200603160057.log100%7270.7KB/s00:00sqllog.ini100%4790.5KB/s00:00dm.ctl100%51205.0KB/s00:00jy01.log100%256MB128.0MB/s00:02jy02.log100%256MB85.3MB/s00:03dm_20200603160143_364345.ctl100%51205.0KB/s00:00dm_20200603160450_367099.ctl100%51205.0KB/s00:00SYSTEM.DBF100%21MB21.0MB/s00:00dm_service.prikey100%6330.6KB/s00:00MAIN.DBF100%128MB128.0MB/s00:01ROLL.DBF100%128MB128.0MB/s00:01dminst.sys100%2200.2KB/s00:00TEMP.DBF100%10MB10.0MB/s00:00rep_conflict.log100%120.0KB/s00:00dm.ini100%40KB39.8KB/s00:00dmmal.ini100%5580.5KB/s00:00dmarch.ini100%3400.3KB/s00:00dmwatcher.ini100%6650.7KB/s00:00dmwatcher.ctl100%5120.5KB/s00:00dmwatcher.ctl100%5120.5KB/s00:00[dmdba@18c1dm7]$

备机修改相关配置
修改dm.ini

INSTANCE_NAME=JY2

修改dmarch.ini

ARCH_DEST=JY1

dmwatcher.ini,dmwatcher.ctl,dmmal.ini与主库一致不用修改二:启动到mount状态设置oguid 主库

[dmdba@18c1bin]$./dmserver/dm7/data/jy/dm.inimountfiledm.keynotfound,usedefaultlicense!versioninfo:developUsenormalos_mallocinsteadofHugeTLBUsenormalos_mallocinsteadofHugeTLBDMDatabaseServerx64V7.1.6.46-Build(2018.02.08-89107)ENTstartup...Licensewillexpirein14day(s)on2020-06-17ckptlsn:32981SYSTEMISREADY.[dmdba@18c1bin]$./disqlSYSDBA/SYSDBAServer[LOCALHOST:5236]:modeisnormal,stateismountloginusedtime:5.995(ms)disqlV7.1.6.46-Build(2018.02.08-89107)ENTConnectedto:DM7.1.6.46SQL>sp_set_oguid(453332);DMSQLexecutedsuccessfullyusedtime:68.576(ms).Executeidis1.

备库

[dmdba@18c2bin]$./dmserver/dm7/data/jy/dm.inimountfiledm.keynotfound,usedefaultlicense!versioninfo:developUsenormalos_mallocinsteadofHugeTLBUsenormalos_mallocinsteadofHugeTLBDMDatabaseServerx64V7.1.6.46-Build(2018.02.08-89107)ENTstartup...Licensewillexpirein14day(s)on2020-06-17ckptlsn:32981SYSTEMISREADY.[dmdba@18c2bin]$./disqlSYSDBA/SYSDBAServer[LOCALHOST:5236]:modeisnormal,stateismountloginusedtime:6.344(ms)disqlV7.1.6.46-Build(2018.02.08-89107)ENTConnectedto:DM7.1.6.46SQL>sp_set_oguid(453332);DMSQLexecutedsuccessfullyusedtime:32.329(ms).Executeidis1.

注册服务用于启动数据库

[root@18c2root]#./dm_service_installer.sh-i/dm7/data/jy/dm.ini-pjy2-tdmserverln-s'/usr/lib/systemd/system/DmServicejy2.service''/etc/systemd/system/multi-user.target.wants/DmServicejy2.service'Finishedtocreatetheservice(DmServicejy2)

三:打开数据库
主库以primary打开

SQL>alterdatabaseprimary;executedsuccessfullyusedtime:43.384(ms).Executeidis0.

备库以standby 打开

SQL>alterdatabasestandby;executedsuccessfullyusedtime:100.645(ms).Executeidis0.

四:启动守护进程
启动各个主备库上的守护进程：
主库

[dmdba@18c1bin]$./dmwatcher/dm7/data/jy/dmwatcher.iniDMWATCHER[2.1]V7.1.6.46-Build(2018.02.08-89107)ENTDMWATCHER[2.1]ISREADYshow2020-06-0316:43:10---------------------------------------------------------------------------GROUP_NAMETYPEMODEOGUIDMPP_FLAGAUTO_RESTARTLOCAL_DW_STATUSGRP1GLOBALAUTO453332FALSETRUEOPENINST_OKNAMESVR_MODESYS_STATUSOPEN_CNTRTYPEN_TASKTASK_MEM_USEDFLSNCLSNSLSNSSLSNOKJY1PRIMARYOPEN2TIMELY0034412344123441234412---------------------------------------------------------------------------

备库

[dmdba@18c2bin]$./dmwatcher/dm7/data/jy/dmwatcher.iniDMWATCHER[2.1]V7.1.6.46-Build(2018.02.08-89107)ENTDMWATCHER[2.1]ISREADYshow2020-06-0316:43:05---------------------------------------------------------------------------GROUP_NAMETYPEMODEOGUIDMPP_FLAGAUTO_RESTARTLOCAL_DW_STATUSGRP1GLOBALAUTO453332FALSETRUEOPENINST_OKNAMESVR_MODESYS_STATUSOPEN_CNTRTYPEN_TASKTASK_MEM_USEDFLSNCLSNSLSNSSLSNOKJY2STANDBYOPEN1TIMELY0032981329813298132981---------------------------------------------------------------------------

五:查看file_lsn与cur_lsn主备库是否一致
主库

SQL>selectfile_LSN,cur_LSNfromv$rlog;LINEIDFILE_LSNCUR_LSN--------------------------------------------------13441234412usedtime:1.203(ms).Executeidis6.

备库

SQL>selectfile_LSN,cur_LSNfromv$rlog;LINEIDFILE_LSNCUR_LSN--------------------------------------------------13441234412usedtime:1.228(ms).Executeidis3.

测试数据同步
主库：

SQL>createtablet1(idint);executedsuccessfullyusedtime:23.402(ms).Executeidis4.SQL>insertintot1values(1);affectrows1usedtime:1.303(ms).Executeidis5.SQL>commit;executedsuccessfullyusedtime:4.034(ms).Executeidis6.SQL>selectfile_LSN,cur_LSNfromv$rlog;LINEIDFILE_LSNCUR_LSN--------------------------------------------------13444334443usedtime:0.555(ms).Executeidis7.

备库:

SQL>selectfile_LSN,cur_LSNfromv$rlog;LINEIDFILE_LSNCUR_LSN--------------------------------------------------13444334443usedtime:0.325(ms).Executeidis2.SQL>select*fromt1;LINEIDID---------------------11usedtime:1.373(ms).Executeidis3.

六:配置监视器(基本要求，安装dm7的软件)
由于主库和实时备库的守护进程配置为自动切换模式，因此这里选择配置确认监视器。和普通监视器相比，确认监视器除了相同的命令支持外，在主库发生故障时，能够自动通知实时备库接管为新的主库，具有自动故障处理的功能。修改dmmonitor.ini配置确认监视器，其中MON_DW_IP中的IP和PORT和dmmal.ini中的MAL_HOST和MAL_DW_PORT配置项保持一致。

[dmdba@ora19cdata]$vidmmonitor.ini[dmdba@dmksdmdbms]$vidmmonitor.iniMON_DW_CONFIRM=1MON_LOG_PATH=/dm_home/dmdbms/logMON_LOG_INTERVAL=60MON_LOG_FILE_SIZE=32MON_LOG_SPACE_LIMIT=0[GRP1]MON_INST_OGUID=453332MON_DW_IP=10.13.13.171:5238MON_DW_IP=10.13.13.172:5238

启动监视器：

[dmdba@dmksbin]$./dmmonitor/dm_home/dmdbms/dmmonitor.ini[monitor]2020-06-0310:54:59:DMMONITOR[2.1]V7.1.6.46-Build(2018.02.08-89107)ENT[monitor]2020-06-0310:54:59:DMMONITOR[2.1]ISREADY.[monitor]2020-06-0310:54:59:Receivedmessagefrom(JY1)WTIMEWSTATUSINST_OKINAMEISTATUSIMODERSTATN_OPENFLSNCLSNSSLSNSLSN2020-06-0316:47:46OPENOKJY1OPENPRIMARYVALID234443344433444334443[monitor]2020-06-0310:54:59:Receivedmessagefrom(JY2)WTIMEWSTATUSINST_OKINAMEISTATUSIMODERSTATN_OPENFLSNCLSNSSLSNSLSN2020-06-0316:47:47OPENOKJY2OPENSTANDBYVALID234443344433444334443

在JDBC连接串中增加了两个连接属性：
rwSeparate 是否使用读写分离系统，默认0；取值（0不使用，1使用）。
rwPercent 分发到主库的事务占主备库总事务的百分比，有效值0~100，默认值25。
下面使用jdbc来测试读写分离

packagecs;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.Statement;publicclasstestrw{//驱动publicstaticStringdriver="dm.jdbc.driver.DmDriver";//连接URLpublicstaticStringurl="jdbc:dm://10.13.13.171:5236?rwSeparate=1&rwPercent=10";//数据库用户publicstaticStringusername="SYSDBA";//数据库密码publicstaticStringpassword="SYSDBA";/***@paramdriver*@paramurl*@paramusername*@parampassword*@return*/publicstaticConnectioncreateConnection(Stringdriver,Stringurl,Stringusername,Stringpassword){Connectionconnection=null;try{//加载JDBC驱动程序Class.forName(driver);connection=DriverManager.getConnection(url,username,password);}catch(Exceptionex){ex.printStackTrace();System.err.println("LoadJDBCDriverError:"+ex.getMessage());}returnconnection;}/***@paramargs*/publicstaticvoidmain(String[]args){Connectionconnection=createConnection(driver,url,username,password);System.out.println(connection);try{PreparedStatementps1=connection.prepareStatement("select*fromt2;");ResultSetrs=ps1.executeQuery();Statementps=connection.createStatement();ps.addBatch("insertintot2values(2)");ps.executeBatch();Stringname="";while(rs.next()){name=rs.getString("ID");System.out.println("IDis:"+name);}rs.close();ps.close();}catch(Exceptionex){ex.printStackTrace();System.err.println("RunSQLError:"+ex.getMessage());}}}

备库上执行的是查询语句执行时间是2020-06-10 22:18:14.000000

SQL>select*fromv$sessions;LINEIDSESS_IDSESS_SEQSQL_TEXTSTATEN_STMTN_USED_STMTSEQ_NOCURR_SCHUSER_NAMETRX_IDCREATE_TIMECLNT_TYPETIME_ZONECHK_CONSCHK_IDENTRDONLYINS_NULLCOMPILE_FLAGAUTO_CMTDDL_AUTOCMTRS_FOR_QRYCHK_NETISO_LEVELCLNT_HOSTAPPNAMECLNT_IPOSNAMECONN_TYPEVPOOLADDRRUN_STATUSMSG_STATUSLAST_RECV_TIMELAST_SEND_TIMEDCP_FLAGTHRD_IDCONNECTEDPORT_TYPESRC_SITEMAL_IDCONCURRENT_FLAG-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------11396634110574163select*fromv$sessions;ACTIVE64116SYSDBASYSDBA1407374883553292020-06-0316:43:58.000000SQL3+08:00NNNYNNYNN118c2disql::1LinuxHOMOGENEOUS139663410989816RUNNINGRECIEVE2020-06-1022:18:39.0000002020-06-1022:16:07.000000N49821065535NULL0213966341217364011select*fromt2;IDLE6425SYSDBASYSDBA02020-06-1022:18:14.000000JDBC+08:00NNNYNYYNN1WIN-ROUOJ6ERFO3::ffff:10.13.13.242WindowsServer2008R2HOMOGENEOUS139663412106040IDLESEND2020-06-1022:18:21.0000002020-06-1022:18:21.000000N123551065535NULL0usedtime:0.833(ms).Executeidis25.

主库上执行的是插入语句执行时间是2020-06-10 22:18:13.000000

SQL>select*fromv$sessions;LINEIDSESS_IDSESS_SEQSQL_TEXTSTATEN_STMTN_USED_STMTSEQ_NOCURR_SCHUSER_NAMETRX_IDCREATE_TIMECLNT_TYPETIME_ZONECHK_CONSCHK_IDENTRDONLYINS_NULLCOMPILE_FLAGAUTO_CMTDDL_AUTOCMTRS_FOR_QRYCHK_NETISO_LEVELCLNT_HOSTAPPNAMECLNT_IPOSNAMECONN_TYPEVPOOLADDRRUN_STATUSMSG_STATUSLAST_RECV_TIMELAST_SEND_TIMEDCP_FLAGTHRD_IDCONNECTEDPORT_TYPESRC_SITEMAL_IDCONCURRENT_FLAG-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------11398800433522961select*fromv$sessions;ACTIVE64142SYSDBASYSDBA11242020-06-0316:41:00.000000SQL3+08:00NNNYNNYNN118c1disql::1LinuxHOMOGENEOUS139878427790072RUNNINGRECIEVE2020-06-1022:18:50.0000002020-06-1022:15:59.000000N172051065535NULL0213987856207540012insertintot2values(2)IDLE6424SYSDBASYSDBA02020-06-1022:18:13.000000JDBC+08:00NNNYNYYNN1WIN-ROUOJ6ERFO3::ffff:10.13.13.242WindowsServer2008R2HOMOGENEOUS139878562007800IDLESEND2020-06-1022:18:26.0000002020-06-1022:18:26.000000N218021065535NULL0usedtime:1.602(ms).Executeidis41.