DM7搭建读写分离集群

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

机器名     IP地址                    初始状态                          操作系统    18c1     10.13.13.171(对外)         主库 JY1                         redhat 6.7         10.13.13.171(mal对内)18c2     10.13.13.172(对外)         10.13.13.171(mal对内)      备库 JY2                         redhat 6.7dmks     10.13.13.187               确认监视器                       redhat 6.7实例名       port_num             dw_port    mal_host                mal_port        mal_dw_portjy1          5236                 5239       10.13.13.171          5237            5238jy2          5236                 5239       10.13.13.172          5237            5238

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

[dmdba@18c1 bin]$ ./dminit path=/dm7/data db_name=jy instance_name=jy1 port_num=5236 page_size=8 charset=0initdb V7.1.6.46-Build(2018.02.08-89107)ENT db version: 0x7000afile dm.key not found, use default license!License will expire in 14 day(s) on 2020-06-17 log file path: /dm7/data/jy/jy01.log log file path: /dm7/data/jy/jy02.logwrite to dir [/dm7/data/jy].create dm database success. 2020-06-03 16:01:44

注册服务用于启动数据库

[root@18c1 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy1 -t dmserverln -s '/usr/lib/systemd/system/DmServicejy1.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy1.service'Finished to create the service (DmServicejy1)

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

[root@18c1 root]# service DmServicejy1 startRedirecting to /bin/systemctl start  DmServicejy1.service[root@18c1 root]# ps -ef | grep dmserverdmdba    29989     1 13 16:04 ?        00:00:04 /dm7/bin/dmserver /dm7/data/jy/dm.ini -noconsoleroot     30292  3890  0 16:05 pts/1    00:00:00 grep --color=auto dmserver[dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBAServer[LOCALHOST:5236]:mode is normal, state is openlogin used time: 8.010(ms)disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46SQL> exit[root@18c1 root]# service DmServicejy1 stopRedirecting to /bin/systemctl stop  DmServicejy1.service

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

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

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

[dmdba@18c1 jy]$ vi dmmal.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@18c1 jy]$ vi dmarch.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@18c1 jy]$ vi dmwatcher.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@18c1 bin]$ ./dmctlcvt TYPE=3 SRC=/dm7/data/jy/dmwatcher.ini DEST=/dm7/dataDMCTLCVT V7.1.6.46-Build(2018.02.08-89107)ENT convert txt to ctl success!

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

[dmdba@18c1 data]$ ls -lrttotal 4drwxr-xr-x 6 dmdba dinstall 4096 Jun  3 16:23 jydrwxr-xr-x 2 dmdba dinstall   26 Jun  3 16:23 GRP1[dmdba@18c1 data]$ cd GRP1/[dmdba@18c1 GRP1]$ ls -lrttotal 4-rw-r--r-- 1 dmdba dinstall 512 Jun  3 16:23 dmwatcher.ctl[dmdba@18c1 GRP1]$ cp  dmwatcher.ctl /dm7/data/jy/

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

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

[dmdba@18c1 dm7]$ scp -r data/ dmdba@10.13.13.172:/dm7/The authenticity of host '10.13.13.172 (10.13.13.172)' can't be established.ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '10.13.13.172' (ECDSA) to the list of known hosts.dmdba@10.13.13.172's password: dminit20200603160057.log                                                                                                                                                                                  100%  727     0.7KB/s   00:00    sqllog.ini                                                                                                                                                                                                100%  479     0.5KB/s   00:00    dm.ctl                                                                                                                                                                                                    100% 5120     5.0KB/s   00:00    jy01.log                                                                                                                                                                                                  100%  256MB 128.0MB/s   00:02    jy02.log                                                                                                                                                                                                  100%  256MB  85.3MB/s   00:03    dm_20200603160143_364345.ctl                                                                                                                                                                              100% 5120     5.0KB/s   00:00    dm_20200603160450_367099.ctl                                                                                                                                                                              100% 5120     5.0KB/s   00:00    SYSTEM.DBF                                                                                                                                                                                                100%   21MB  21.0MB/s   00:00    dm_service.prikey                                                                                                                                                                                         100%  633     0.6KB/s   00:00    MAIN.DBF                                                                                                                                                                                                  100%  128MB 128.0MB/s   00:01    ROLL.DBF                                                                                                                                                                                                  100%  128MB 128.0MB/s   00:01    dminst.sys                                                                                                                                                                                                100%  220     0.2KB/s   00:00    TEMP.DBF                                                                                                                                                                                                  100%   10MB  10.0MB/s   00:00    rep_conflict.log                                                                                                                                                                                          100%   12     0.0KB/s   00:00    dm.ini                                                                                                                                                                                                    100%   40KB  39.8KB/s   00:00    dmmal.ini                                                                                                                                                                                                 100%  558     0.5KB/s   00:00    dmarch.ini                                                                                                                                                                                                100%  340     0.3KB/s   00:00    dmwatcher.ini                                                                                                                                                                                             100%  665     0.7KB/s   00:00    dmwatcher.ctl                                                                                                                                                                                             100%  512     0.5KB/s   00:00    dmwatcher.ctl                                                                                                                                                                                             100%  512     0.5KB/s   00:00    [dmdba@18c1 dm7]$

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

INSTANCE_NAME = JY2

修改dmarch.ini

ARCH_DEST = JY1

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

[dmdba@18c1 bin]$ ./dmserver /dm7/data/jy/dm.ini mountfile dm.key not found, use default license!version info: developUse normal os_malloc instead of HugeTLBUse normal os_malloc instead of HugeTLBDM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT  startup...License will expire in 14 day(s) on 2020-06-17ckpt lsn: 32981SYSTEM IS READY.[dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBAServer[LOCALHOST:5236]:mode is normal, state is mountlogin used time: 5.995(ms)disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46SQL> sp_set_oguid(453332);DMSQL executed successfullyused time: 68.576(ms). Execute id is 1.

备库

[dmdba@18c2 bin]$ ./dmserver /dm7/data/jy/dm.ini mountfile dm.key not found, use default license!version info: developUse normal os_malloc instead of HugeTLBUse normal os_malloc instead of HugeTLBDM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT  startup...License will expire in 14 day(s) on 2020-06-17ckpt lsn: 32981SYSTEM IS READY.[dmdba@18c2 bin]$ ./disql SYSDBA/SYSDBAServer[LOCALHOST:5236]:mode is normal, state is mountlogin used time: 6.344(ms)disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46SQL> sp_set_oguid(453332);DMSQL executed successfullyused time: 32.329(ms). Execute id is 1.

注册服务用于启动数据库

[root@18c2 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy2 -t dmserverln -s '/usr/lib/systemd/system/DmServicejy2.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy2.service'Finished to create the service (DmServicejy2)

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

SQL> alter database primary;executed successfullyused time: 43.384(ms). Execute id is 0.

备库以standby 打开

SQL> alter database standby;executed successfullyused time: 100.645(ms). Execute id is 0.

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

[dmdba@18c1 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.iniDMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT DMWATCHER[2.1] IS READYshow2020-06-03 16:43:10---------------------------------------------------------------------------GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART LOCAL_DW_STATUS GRP1             GLOBAL    AUTO      453332      FALSE     TRUE         OPEN            INST_OK   NAME             SVR_MODE  SYS_STATUS   OPEN_CNT        RTYPE     N_TASK    TASK_MEM_USED   FLSN            CLSN            SLSN            SSLSN           OK        JY1              PRIMARY   OPEN         2               TIMELY    0         0               34412           34412           34412           34412           ---------------------------------------------------------------------------

备库

[dmdba@18c2 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.iniDMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT DMWATCHER[2.1] IS READYshow2020-06-03 16:43:05---------------------------------------------------------------------------GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART LOCAL_DW_STATUS GRP1             GLOBAL    AUTO      453332      FALSE     TRUE         OPEN            INST_OK   NAME             SVR_MODE  SYS_STATUS   OPEN_CNT        RTYPE     N_TASK    TASK_MEM_USED   FLSN            CLSN            SLSN            SSLSN           OK        JY2              STANDBY   OPEN         1               TIMELY    0         0               32981           32981           32981           32981           ---------------------------------------------------------------------------

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

SQL> select file_LSN, cur_LSN from v$rlog;LINEID     FILE_LSN             CUR_LSN             ---------- -------------------- --------------------1          34412                34412used time: 1.203(ms). Execute id is 6.

备库

SQL> select file_LSN, cur_LSN from v$rlog;LINEID     FILE_LSN             CUR_LSN             ---------- -------------------- --------------------1          34412                34412used time: 1.228(ms). Execute id is 3.

测试数据同步
主库：

SQL> create table t1(id int);executed successfullyused time: 23.402(ms). Execute id is 4.SQL> insert into t1 values(1);affect rows 1used time: 1.303(ms). Execute id is 5.SQL> commit;executed successfullyused time: 4.034(ms). Execute id is 6.SQL> select file_LSN, cur_LSN from v$rlog;LINEID     FILE_LSN             CUR_LSN             ---------- -------------------- --------------------1          34443                34443used time: 0.555(ms). Execute id is 7.

备库:

SQL> select file_LSN, cur_LSN from v$rlog;LINEID     FILE_LSN             CUR_LSN             ---------- -------------------- --------------------1          34443                34443used time: 0.325(ms). Execute id is 2.SQL> select * from t1;LINEID     ID         ---------- -----------1          1used time: 1.373(ms). Execute id is 3.

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

[dmdba@ora19c data]$ vi dmmonitor.ini[dmdba@dmks dmdbms]$ vi dmmonitor.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@dmks bin]$ ./dmmonitor /dm_home/dmdbms/dmmonitor.ini[monitor]         2020-06-03 10:54:59: DMMONITOR[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT [monitor]         2020-06-03 10:54:59: DMMONITOR[2.1] IS READY.[monitor]         2020-06-03 10:54:59: Received message from(JY1)                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN           CLSN           SSLSN          SLSN                             2020-06-03 16:47:46  OPEN           OK        JY1              OPEN        PRIMARY   VALID    2        34443          34443          34443          34443          [monitor]         2020-06-03 10:54:59: Received message from(JY2)                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN           CLSN           SSLSN          SLSN                             2020-06-03 16:47:47  OPEN           OK        JY2              OPEN        STANDBY   VALID    2        34443          34443          34443          34443

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

package cs;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;public class testrw {        // 驱动    public static String driver = "dm.jdbc.driver.DmDriver";    // 连接URL    public static String url = "jdbc:dm://10.13.13.171:5236?rwSeparate=1&rwPercent=10";     // 数据库用户    public static String username = "SYSDBA";    // 数据库密码    public static String password = "SYSDBA";     /**     * @param driver     * @param url     * @param username     * @param password     * @return     */    public static Connection createConnection(String driver, String url,            String username, String password) {        Connection connection = null;        try {            // 加载JDBC驱动程序            Class.forName(driver);            connection = DriverManager.getConnection(url, username, password);        } catch (Exception ex) {            ex.printStackTrace();            System.err.println("Load JDBC Driver Error : " + ex.getMessage());        }        return connection;    }     /**     * @param args     */    public static void main(String[] args){        Connection connection = createConnection(driver,url,username,password);        System.out.println(connection);        try        {  PreparedStatement ps1=connection.prepareStatement("select * from t2;");           ResultSet rs = ps1.executeQuery();           Statement ps=connection.createStatement();           ps.addBatch("insert into t2 values(2)");           ps.executeBatch();           String name = "";           while (rs.next())           {             name = rs.getString("ID");             System.out.println("ID is:"+name);           }           rs.close();           ps.close();        }        catch (Exception ex)        {                ex.printStackTrace();            System.err.println("Run SQL Error : " + ex.getMessage());        }    }}

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

SQL> select * from v$sessions;LINEID     SESS_ID              SESS_SEQ    SQL_TEXT                  STATE  N_STMT      N_USED_STMT SEQ_NO      CURR_SCH USER_NAME TRX_ID               CREATE_TIME                 CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL   CLNT_HOST       APPNAME CLNT_IP               OSNAME                 CONN_TYPE    VPOOLADDR            RUN_STATUS MSG_STATUS LAST_RECV_TIME              LAST_SEND_TIME              DCP_FLAG THRD_ID     CONNECTED   PORT_TYPE   SRC_SITE    MAL_ID               CONCURRENT_FLAG---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- ---------------1          139663411057416      3           select * from v$sessions; ACTIVE 64          1           16          SYSDBA   SYSDBA    140737488355329      2020-06-03 16:43:58.000000  SQL3      +08:00    N        N         N      Y        N            N        Y           N          N       1           18c2            disql   ::1                   Linux                   HOMOGENEOUS 139663410989816      RUNNING    RECIEVE    2020-06-10 22:18:39.000000  2020-06-10 22:16:07.000000  N        4982        1           0           65535       NULL                 02          139663412173640      11          select * from t2;         IDLE   64          2           5           SYSDBA   SYSDBA    0                    2020-06-10 22:18:14.000000  JDBC      +08:00    N        N         N      Y        N            Y        Y           N          N       1           WIN-ROUOJ6ERFO3         ::ffff:10.13.13.242 Windows Server 2008 R2  HOMOGENEOUS 139663412106040      IDLE       SEND       2020-06-10 22:18:21.000000  2020-06-10 22:18:21.000000  N        12355       1           0           65535       NULL                 0used time: 0.833(ms). Execute id is 25.

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

SQL> select * from v$sessions;LINEID     SESS_ID              SESS_SEQ    SQL_TEXT                  STATE  N_STMT      N_USED_STMT SEQ_NO      CURR_SCH USER_NAME TRX_ID               CREATE_TIME                 CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL   CLNT_HOST       APPNAME CLNT_IP               OSNAME                 CONN_TYPE    VPOOLADDR            RUN_STATUS MSG_STATUS LAST_RECV_TIME              LAST_SEND_TIME              DCP_FLAG THRD_ID     CONNECTED   PORT_TYPE   SRC_SITE    MAL_ID               CONCURRENT_FLAG---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- ---------------1          139880043352296      1           select * from v$sessions; ACTIVE 64          1           42          SYSDBA   SYSDBA    1124                 2020-06-03 16:41:00.000000  SQL3      +08:00    N        N         N      Y        N            N        Y           N          N       1           18c1            disql   ::1                   Linux                   HOMOGENEOUS 139878427790072      RUNNING    RECIEVE    2020-06-10 22:18:50.000000  2020-06-10 22:15:59.000000  N        17205       1           0           65535       NULL                 02          139878562075400      12          insert into t2 values(2)  IDLE   64          2           4           SYSDBA   SYSDBA    0                    2020-06-10 22:18:13.000000  JDBC      +08:00    N        N         N      Y        N            Y        Y           N          N       1           WIN-ROUOJ6ERFO3         ::ffff:10.13.13.242 Windows Server 2008 R2  HOMOGENEOUS 139878562007800      IDLE       SEND       2020-06-10 22:18:26.000000  2020-06-10 22:18:26.000000  N        21802       1           0           65535       NULL                 0used time: 1.602(ms). Execute id is 41.