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

What is the principle and usage of database-level replication in DM7 data replication? I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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 look at the configuration process of database-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: 32.545 (ms). Execute id is 26.SQL > select * from SYSREP.RPS_GROUPS;LINEID NAME ID DESC$-1 REP_GRP_hy2wy 3 master-slave synchronous replication used time: 1.073 (ms). Execute id is 27.

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.529 (ms). Execute id is 29.

Add database-level replication

SQL > SP_RPS_ADD_DB_MAP ('REPhy2wy', 0); DMSQL executed successfullyused time: 0.597 (ms). Execute id is 30.

Submit Settin

SQL > SP_RPS_SET_APPLY (); DMSQL executed successfullyused time: 05.035. Execute id is 31.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 3 4 7 8 / dm_home/dmdba/dmdbms/dmreplog-1 0 NULL 1 NULL hy to wy synchronous replication used time: 1.277 (ms). Execute id is 32.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 3 7-1 0 NULL 0 NULL2 wy 38-10 NULL 0 NULLused time: 1.014 (ms). Execute id is 33.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- -14 NULL NULL-1-1 NULL NULL-1-1 0used time: 1.129 (ms). Execute id is 34.

At this point, the database 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

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

SQL > create table T4 as select * from sysobjects;executed successfullyused time: 12.280 (ms). Execute id is 12.SQL > select count (*) from t4umbent COUNT (*)-- 1 1480used time: 1.256 (ms). Execute id is 13.

Query this table T4 from the wy user on the library

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

You can see that the DDL statement that created the table is not synchronized to the wy user.

Create a user jy on the main library

SQL > create user jy identified by "abcd"; executed successfullyused time: 4.940 (ms). Execute id is 5.SQL > grant dba,resource to jy;executed successfullyused time: 2.929 (ms). Execute id is 6.

Log in to user jy on the slave library

[dmdba@jydm3 bin] $. / disql jy/abcd [- 70028]: Create SOCKET connection failure.disql V7.1.6.46-Build (2018.02.08-89107) ENT username:

You can see the fault information from the log on the library

2019-12-20 00:15:08 [WARNING] database P0000029732 main_thread rps sys init failed, code:-8748

View replication failure information from the replication database

SQL > select * from SYSREP.RPS_FAULT_HISTORY LINEID GRP_NAME OBJ_NAME FAULT_TYPE START_TIME END_TIME-- -1 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-12-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 1423.366210 2019-12-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 1524.0420322 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-12-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 1423.366210 2019-12-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 2019-20 00 REP_GRP_hy2wy REPhy2wy REPLICATION FAULT 20 12-20 00 NULL4 REP_GRP_hy2wy wy INSTANCE SYS FAULT 16 ms 23.386842 NULLused time: 1.179 (ms). Execute id is 4.

From the test results, we can see that the database-level data replication does not support DDL, and the database data replication does not handle the operation of the creation user, which will lead to a failure from the database.

After reading the above, have you mastered the principle and usage of database-level replication in DM7 data replication? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.