Shulou(Shulou.com)05/31 Report--

DM7 data replication in the configuration and usage of mode-level replication, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

DM data replication can be configured at table level, schema level and library level. Because table level replication cannot synchronize DDL, the structure of master-slave table must be completely consistent. In this article, we take a look at the configuration process of schema-level data replication.

Here is a simple example to illustrate the configuration of data replication.

1. Preparatory work

The information for the replication instances participating in the replication is shown in the following table.

Database instance name IP address database port number MAL port number file directory

Copy database jydm 10.10.10.180 5236 5241 / dm_home/dmdba/dmdbms/data/

Main database hy 10.10.10.184 5236 5242 / dm_home/dmdba/dmdbms/data/

From the database wy 10.10.10.185 5236 5243 / dm_home/dmdba/dmdbms/data/

Assuming that there are hy users on the master database and wy users on the slave database, you now need to create a synchronous replication relationship between the hy user on the master database and the wy user on the slave database, whose name is REPHY2WY.

two。 Parameter setting

Modify the dm.ini files of the 3 DM databases by adding the following:

Database dm.ini Settings

Copy database INSTANCE_NAME = jydm

PORT_NUM = 5236

MAL_INI = 1

Master database INSTANCE_NAME = hy

PORT_NUM = 5236

MAL_INI = 1

From the database INSTANCE_NAME = wy

PORT_NUM = 5236

MAL_INI = 1

Configure the dmmal.ini file

Modify the dmmal.ini file to add the following. The Dmmal configuration file is not available by default and can be copied and modified from the sample directory.

[root@shard1 ini_script] # pwd/dm_home/dmdba/dmdbms/samples/ini_ script [root @ shard1 ini_script] # ls-lrt Total amount 48-rwxr-xr-x 1 dmdba dinstall 890 February 14 2019 dmarch_example.ini-rwxr-xr-x 1 dmdba dinstall 1966 February 14 2019 dmdcr_cfg_example.ini-rwxr-xr-x 1 dmdba dinstall 631 February 14 2019 dmdcr_example.ini-rwxr-xr-x 1 dmdba dinstall 1537 February 14 2019 dminit_example .ini-rwxr-xr-x 1 dmdba dinstall 2070 February 14 2019 dmmal_example.ini-rwxr-xr-x 1 dmdba dinstall 1277 February 14 2019 dmmonitor_example.ini-rwxr-xr-x 1 dmdba dinstall 288 February 14 2019 dmmpp_example.ini-rwxr-xr-x 1 dmdba dinstall 1679 February 14 2019 dmtimer_example.ini-rwxr-xr-x 1 dmdba dinstall 1241 February 14 2019 dmwatch_example.ini-rwxr-xr-x 1 dmdba dinstall 2146 February 14 2019 dmwatcher_example .ini-rwxr-xr-x 1 dmdba dinstall 522 February 14 2019 dmwmon_example.ini-rwxr-xr-x 1 dmdba dinstall 636 February 14 2019 sqllog_example.ini

Create a dmmal.ini file on each database server, and each dmmal.ini configuration must be consistent:

MAL_CHECK_INTERVAL = 5MAL_CONN_FAIL_INTERVAL = 5 [MAL_jydm] MAL_INST_NAME = jydmMAL_HOST = 10.10.10.180MAL_PORT = 5241MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.180 [Mal _ hy] MAL_INST_NAME = hyMAL_HOST = 10.10.10.184MAL_PORT = 5242MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.184 [Mal _ wy] MAL_INST_NAME = wyMAL_HOST = 10 .10.10.185Mal _ PORT = 5243MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.185 [dmdba@shard1 jydm] $cat dmmal.iniMAL_CHECK_INTERVAL = 5MAL_CONN_FAIL_INTERVAL = 5 [MAL_jydm] MAL_INST_NAME = jydmMAL_HOST = 10.10.10.180MAL_PORT = 5241MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.180 [Mal _ hy] MAL_INST_NAME = hyMAL_HOST = 10.10.10.184MAL _ PORT = 5242MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.184 [Mal _ wy] MAL_INST_NAME = wyMAL_HOST = 10.10.10.185MAL_PORT = 5243MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.185 [dmdba@jydm2 hy] $cat dmmal.iniMAL_CHECK_INTERVAL = 5MAL_CONN_FAIL_INTERVAL = 5 [MAL_jydm] MAL_INST_NAME = jydmMAL_HOST = 10.10.10.180MAL_PORT = 5241Mal PORT = 5236 MAL_INST_HOST = 10.10.10.180 [Mal _ hy] MAL_INST_NAME = hyMAL_HOST = 10.10.10.184MAL_PORT = 5242MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.184 [Mal _ wy] MAL_INST_NAME = wyMAL_HOST = 10.10.10.185MAL_PORT = 5243MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.185 [dmdba@jydm3 wy] $cat dmmal.iniMAL_CHECK_INTERVAL = 5MAL_CONN_FAIL_INTERVAL = 5 [MAL_jydm] MAL_INST_NAME = jydmMAL_HOST = 10.10.10.180MAL_PORT = 5241MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.180 [Mal _ hy] MAL_INST_NAME = hyMAL_HOST = 10.10.10.184MAL_PORT = 5242MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.184 [Mal _ wy] MAL_INST_NAME = wyMAL_HOST = 10.10.10.185MAL _ PORT = 5243MAL_INST_PORT = 5236 MAL_INST_HOST = 10.10.10.185

Enable the mal_ini parameter for all three databases

SQL > sp_set_para_value (2) DMSQL executed successfullyused time: 7.465 (ms). Execute id is 23.

Replication database initialization

If you are using a replication database for the first time, you need to initialize the replication database. Initialize the replication database by executing the system function SP_INIT_REP_SYS (create_flag). Its main role is to create a replication user (SYSREP/SYSREP) and to create the system tables needed on the replication database. The parameter create_flag of SP_INIT_REP_SYS is 1 to create user and system tables, and 0 to delete user and system tables.

Initiate initialization:

SQL > SP_INIT_REP_SYS (1); DMSQL executed successfullyused time: 115.960 (ms). Execute id is 30.SQL > conn SYSREP/SYSREPServer [LOCALHOST:5236]: mode is normal, state is openlogin used time: 9.684 (ms) SQL > select table_name from user_tables LINEID TABLE_NAME-1 RPS_GROUPS2 RPS_INSTANCES3 RPS_REPLICATIONS4 RPS_TABMAPS5 RPS_FAULT_HISTORY6 RPS_CONFLICTS7 RPS_TIMERS7 rows gotused time: 170.443 (ms). Execute id is 31.

The seven replication system tables created are as follows:

1) copy the group table

CREATE TABLE SYSREP.RPS_GROUPS (

NAME VARCHAR (128), / / copy group name

ID INT, / / replication group ID

DESC$ VARCHAR (1000), / / description

CLUSTER PRIMARY (NAME)

);

2) copy node instance table

CREATE TABLE SYSREP.RPS_INSTANCES (

INST_NAME VARCHAR (128), / / copy node instance name

GRP_ID INT, / / replication group ID

INST_ID INT, / / instance is numbered in the replication group

FAULT_TIMEOUT INT, / / fault timeout value, in seconds. 0 is the immediate timeout.

VALID_FLAG CHAR (1), / / Node system status

FAULT_TIME DATETIME, / / start time of node failure

NET_VALID_FLAG CHAR (1), / / Network status

NET_FAULT_TIME DATETIME, / / Network failure start time

CLUSTER PRIMAY KEY (GRP_ID, INST_NAME)

);

3) copy the relational table

CREATE TABLE SYSREP.RPS_REPLICATIONS (

REP_NAME VARCHAR (128), / / copy name

GRP_ID INT, / / replication group ID

REP_ID INT, / / copy ID, globally unique

MINST_ID INT, / / Primary Node instance number

SINST_ID INT, / / Slave node instance number

ARCH_DIR VARCHAR (256), / / Archive log path on the primary node

FAULT_TIMEOUT INT, / / fault timeout value, in seconds. 0 is the immediate timeout.

VALID_FLAG CHAR (1), / / replication relationship status

FAULT_TIME DATETIME, / / start time of failure

SYNC_FLAG INTEGER / / specify synchronous or asynchronous replication

TIMER_NAME VARCHAR / / specifies the timer for asynchronous replication (synchronous replication does not have this item)

DESC$ VARCHAR (1000), / / copy description

CLUSTER PRIMARY KEY (GRP_ID, REP_NAME)

);

4) copy the mapping table

CREATE TABLE SYSREP.RPS_TABMAPS (

REP_ID INT, / / copy ID

MSCH_NAME VARCHAR (128), / / main table schema name

MTAB_NAME VARCHAR (128), / / main table name

MSCH_ID INT, / / main table mode ID

MTAB_ID INT, / / main table ID

SSCH_NAME VARCHAR, / / from the table schema name

STAB_NAME VARCHAR (128), / / from the table name

SSCH_ID INT, / / ID from the table mode

STAB_ID INT, / / ID from the table

READONLY_MODE INT, / / Mapping mode 1: read-only mode, 0: non-read-only mode

CLUSTER PRIMARY KEY (REP_ID, MTAB_ID, STAB_ID)

);

5) copy the failure history table

CREATE TABLE SYSREP.REP_FAULT_HISTORY (

GRP_NAME VARCHAR (128), / / replication group

OBJ_NAME VARCHAR (128), / / failed node or relationship

FAULT_TYPE VARCHAR (128), / / description of fault type

START_TIME DATETIME, / / start time of failure

END_TIME DATETIME / / failure end time

);

6) copy the conflict history table

CREATE TABLE SYSREP.RPS_CONFLICTS

(

SEQ_NO BIGINT / / conflict serial number

REP_ID INT, / / corresponding copy number

INST_ID INT, / / Node number of the conflict

TABLE_ID INT, / / conflict table ID

TYPE TINYINT / / operation type

OCC_TIME DATETIME, / / time of conflict

KEY_DATA VARCHAR (8000), / / PK values of conflicting data. If they contain multiple key values, they are separated by commas. Truncate if the key value is too long

);

7) copy timer table

CREATE TABLE SYSREP.RPS_TIMERS (

NAME VARCHAR (128), / / timer name

TYPE$ INT, / / timing type. For more information, please see SP_RPS_CREATE_TIMER

FERQ_INTERVAL INT, / / interval days

FREQ_SUB_INTERVAL INT, / / number of months / weeks between (schedule type decision)

FREQ_MINUTE_INTERVAL INT, / / minutes between

START_TIME TIME, / / start time

END_TIME TIME, / / end time

DURING_START_DATE DATETIME / / start time point

DURING_END_DATE DATETIME, / / end point in time

NO_END_DATA_FLAG INTEGER / / whether there is an end date (0: has an end date; 1: no end date)

DESC$ VARCHAR (1000), / / timer description

CLUSTER PRIMARY KEY (REP_NAME)

);

Configure pattern-level replication

Start 3 servers in no particular order.

[root@shard1 jydm] # systemctl stop DmServicejydm.service [root@shard1 jydm] # systemctl start DmServicejydm.service [root@jydm2 ~] # systemctl stop DmServicehy.service [root@jydm2] # systemctl start DmServicehy.service [root@jydm3 ~] # systemctl stop DmServicewy.service [root@jydm3 ~] # systemctl start DmServicewy.service

Log in to the replication database (RPS jydm), make sure the server status is OPEN, and start replicating the configuration.

[dmdba@shard1 bin] $. / disql sysdba/abcdServer [LOCALHOST:5236]: mode is normal, state is openlogin used time: 9.851 (ms) disql V7.1.6.46-Build (2018.02.08-89107) ENT Connected to: DM 7.1.6.46

Create replication group REP_GRP_hy2wy

SQL > SP_RPS_ADD_GROUP ('REP_GRP_hy2wy',' master-slave synchronous replication'); DMSQL executed successfullyused time: 6.109 (ms). Execute id is 55.SQL > select * from SYSREP.RPS_GROUPS;LINEID NAME ID DESC$-1 REP_GRP_hy2wy 2 master-slave synchronous replication used time: 1.182 (ms). Execute id is 56.

Start setup

SQL > SP_RPS_SET_BEGIN ('REP_GRP_hy2wy'); DMSQL executed successfullyused time: 0.530 (ms). Execute id is 57.

Add replication relationship

SQL > SP_RPS_ADD_REPLICATION (synchronous replication from 'REP_GRP_hy2wy',' REPhy2wy','hy to wy', 'hy',' wy', NULL,'/ dm_home/dmdba/dmdbms/dmreplog'); DMSQL executed successfullyused time: 2.623 (ms). Execute id is 58.

Add pattern-level replication

Add replication mapping. Note that the user name and table name should be capitalized here, otherwise it will prompt that the object does not exist.

SQL > SP_RPS_ADD_SCH_MAP ('REPhy2wy','hy','wy',0); SP_RPS_ADD_SCH_MAP (' REPhy2wy','hy','wy',0); [- 8713]: source table is not exists.used time: 1.738 (ms). Execute id is 0.SQL > SP_RPS_ADD_SCH_MAP ('REPhy2wy','HY','WY',0); DMSQL executed successfullyused time: 3.126 (ms). Execute id is 6.

Submit Settin

SQL > SP_RPS_SET_APPLY (); DMSQL executed successfullyused time: 05.041. Execute id is 7.SQL > select * from SYSREP.RPS_REPLICATIONS LINEID REP_NAME GRP_ID REP_ID MINST_ID SINST_ID ARCH_DIR FAULT_TIMEOUT VALID_FLAG FAULT_TIME SYNC_FLAG TIMER_NAME DESC$- -1 REPhy2wy 2356 / dm_home/dmdba/dmdbms/dmreplog-10 NULL 1 NULL hy to wy synchronous replication used time: 1.362 (ms). Execute id is 8.SQL > select * from SYSREP.RPS_INSTANCES LINEID INST_NAME GRP_ID INST_ID FAULT_TIMEOUT VALID_FLAG FAULT_TIME NET_VALID_FLAG NET_FAULT_TIME-- -1 hy 25-1 0 NULL 0 NULL2 wy 26-10 NULL 0 NULLused time: 0.981 (ms). Execute id is 9.SQL > select * from SYSREP.RPS_TABMAPS LINEID REP_ID MSCH_NAME MTAB_NAME MSCH_ID MTAB_ID SSCH_NAME STAB_NAME SSCH_ID STAB_ID READONLY_MODE- -- 13 HY NULL 150995951-1 WY NULL 150995951-1 0used time: 1.178 (ms). Execute id is 10.SQL >

At this point, the pattern-level replication configuration is complete. Note that the replication configuration of DM must perform SP_RPS_SET_BEGIN before starting the replication configuration and SP_RPS_SET_APPLY commit after the configuration is complete.

Verify synchronization

First test the DML operation: insert the data in the main library:

SQL > insert into T1 values (1 affect rows 1used time: 1.565 (ms). Execute id is 6.SQL > commit;executed successfullyused time: 1.442 (ms). Execute id is 7.SQL > select * from T1 * LINEID ID USERNAME- 11 hy1used time: 0.548 (ms). Execute id is 8.

Query from the library, synchronization is successful:

SQL > select * from T1 * LINEID ID USERNAME- 11 hy1used time: 2.538 (ms). Execute id is 5.

Create a test table T2 under the hy user on the main library

SQL > create table T2 as select * from sysobjects;executed successfullyused time: 12.763 (ms). Execute id is 6.SQL > select count (*) from T2 / LineID COUNT (*)-- 1 1480used time: 1.266 (ms). Execute id is 7.

Query this table T2 from the wy user on the library

SQL > select count (*) from T2 / LineID COUNT (*)-- 1 1480used time: 0.463 (ms). Execute id is 8.

You can see that the DDL statement that created the table has been synchronized to the wy user.

Because the replication slave library here is level 0, which is not read-only, add level 1 again after deletion, that is, the read-only mode is being tested:

SQL > SP_RPS_SET_BEGIN ('REP_GRP_hy2wy'); DMSQL executed successfullyused time: 0.408 (ms). Execute id is 12.SQL > SP_RPS_DROP_SCH_MAP ('REPhy2wy','HY','WY'); DMSQL executed successfullyused time: 1.456 (ms). Execute id is 13.SQL > SP_RPS_ADD_SCH_MAP ('REPhy2wy','HY','WY',1); DMSQL executed successfullyused time: 2.820 (ms). Execute id is 14.SQL > SP_RPS_SET_APPLY (); DMSQL executed successfullyused time: 05.039. Execute id is 15.

Retest

Create table T3 from the hy user of the main library

SQL > create table T3 as select * from sysobjects;executed successfullyused time: 9.925 (ms). Execute id is 9.SQL > select count (*) from T3 / LINEID COUNT (*)-- 1 1480used time: 0.861 (ms). Execute id is 10.

Query table T3 does not exist under the wy user on the slave library

SQL > select count (*) from T3: select count (*) from T3; [- 2106]: Error in line: 1Invalid table or view name [T3]. Used time: 0.774 (ms). Execute id is 0.

From the test results, we can see that schema-level replication in read-only mode cannot synchronize DDL operations.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.