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

DM7 data replication table-level replication is how, 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.

Dameng data replication (DATA REPLICATION) is a solution to share the system access pressure, speed up the response speed of remote access, and improve the reliability of data. Copy data changes on one server instance to another server instance. It can be used to solve the problems of overload operation of database server, network congestion and slow data response of remote users caused by different regions, different departments and different types of data access requests in large and medium-sized applications.

Important concept

1. Database status

The state of the server, in different states, has different restrictions on what can be done. See the data guardianship section for details.

two。 Primary server

The server that initiates the replication operation is called the primary server.

3. From the server

The server that receives and replicates the data sent by the master server is called the slave server.

4. Copy node

A general term for replicated servers, master services, and slave services. A node can be either a master server or a slave server.

5. Replication server (RPS)

In a data replication environment, the server that is responsible for configuring the replication environment and defining replication relationships. There is one and only one RPS, which is only responsible for configuration and monitoring and does not participate in the replication process.

6. Replication relationship

The replication relationship indicates how the master and slave servers replicate. According to the way of replication, the replication relationship is divided into synchronous replication and asynchronous replication.

7. Synchronous replication relationship

The master server data update is immediately copied to the slave server.

8. Asynchronous replication relationship

The data of the master server and the slave server may be different for a certain period of time, and the master server data update will not be synchronized to the slave server immediately, but will be replicated after a period of time. The timing of synchronization for asynchronous replication is determined by the specified timer.

9. Logical log

A log of logical operations that produce changes in data. The recorded logical operations include INSERT, UPDATE, DELETE, TRUNCATE, ROLLBACK, and COMMIT.

10. Copy source object

Objects on the primary server that serve as replication data sources, which can be libraries, schemas, or tables. All operations on this object are recorded as logical logs and sent to the server for replication.

11. Copy target object

An object from the server that is the target of replication data. After receiving the logical log from the server, copy the changes in the replication source object to the replication target object.

twelve。 Copy object Mappin

A pair of replication source objects and replication destination objects form a replication object mapping. The source and target objects that make up the mapping must be of the same type. According to the type of object, replication mapping is divided into three levels: library level, schema level and table level. Among them, the table level requires that the structure of the source table and the target table are exactly the same, and there are no requirements at the library level and the schema level. The library-level and schema-level replication mappings replicate their DDL as well. Replication mappings include read-only mode and non-read-only mode. For the mapping of read-only mode, the destination table of the mapping prohibits the user from updating.

13. Replication group

A set of logically related replication relationships can be constructed as replication groups. Through replication groups, we can construct complex logical replication environments such as one-to-many replication, many-to-one replication, cascade replication, symmetrical replication, circular replication and so on.

System framework

A data replication system consists of the following components:

1. Replication server

two。 Copy node

3. The MAL system that communicates between the instance sites.

Except that the system administrator defines replication and handling exceptions through the replication server, the handling of other parts and replication operations between master and slave servers are transparent to users. The configuration of the entire replication environment is shown in the following figure.

There is one and only one replication server (RPS) in the entire environment. Users define replication and replication environment through RPS, but RPS is not involved in the replication process.

In DM7, the archiving of replication logical logs to the specified directory of this site according to configuration is called local archive, and the sending of logs is called remote archiving of logs.

On the replication node, the configuration files related to replication are dmtimer.ini, dmllog.ini, dmrep.ini. The functions of each profile in replication are shown in the following table

These files are automatically generated or modified by RPS through the MAL system according to user configuration, and administrators do not need to manage them manually.

In the process of data replication, in addition to configuration files, other important files are shown in the following table.

These files are streaming files. During the execution of the master server, the logical log is first recorded in the logical log file, and according to the configuration information, the logical log is distributed and archived to different logical log archive files, and the contents of the logical log archive file are sent to the slave server at the appropriate time.

When a batch of data is received from the server, a copy data file is generated and the data is stored in it to prevent the loss of replication data caused by the slow replication speed from the server.

The logical log file size is limited to 32m, and the two files are used alternately.

The size of each logical log archive file in the archive path is limited to 32m, and new files are added to store new records when the files are full. In dmllog.ini, the total space limit for all files in the archive path is configured. If the space limit is reached, the previous files will be deleted; if configured with no space limit, the administrator can delete them according to the replication situation.

The replication data file is a temporary file of replicated data received from the server at one time, and its size will not exceed 32K; it will be deleted automatically after the end of the data replication.

Configuration data replication takes place on RPS, and the procedures related to data replication are as follows:

1. SP_INIT_REP_SYS*

Definition:

SP_INIT_REP_SYS (

CREATE_FLAG INT

);

Function description:

Create or delete system tables required for data replication

Parameter description:

CREATE_FLAG: 1 means to create system tables required for replication; 0 means to delete these system tables

Return value:

None

Examples are as follows:

Create the system tables required for replication

SP_INIT_REP_SYS (1)

2. SP_RPS_ADD_GROUP

Definition:

SP_RPS_ADD_GROUP (

GROUP_NAME VARCHAR (128)

GROUP_DESC VARCHAR (1000)

);

Function description:

Create a replication group

Parameter description:

GROUP_NAME: name of the replication group created

GROUP_DESC: copy group description

Return value:

None

Note:

Instructs RPS to create a new replication group. An error is reported if a replication group with the same name already exists.

Examples are as follows:

Create replication group REP_GRP_B2C

SP_RPS_ADD_GROUP ('REP_GRP_B2C',' master-slave synchronous replication')

3. SP_RPS_DROP_GROUP

Definition:

SP_RPS_DROP_GROUP (

GROUP_NAME VARCHAR (128)

);

Function description:

Delete replication Group

Parameter description:

GROUP_NAME: replication group name

Return value:

None

Examples are as follows:

Delete replication group REP_GRP_B2C

SP_RPS_DROP_GROUP ('REP_GRP_B2C')

4. SP_RPS_ADD_REPLICATION

Definition:

SP_RPS_ADD_REPLICATION (

GRP_NAME VARCHAR (128)

REP_NAME VARCHAR (128)

REP_DESC VARCHAR (1000)

MINSTANCE VARCHAR (128)

SINSTANCE VARCHAR (128)

REP_TIMER VARCHAR (128)

ARCH_PATH VARCHAR (256)

);

Function description:

Create a replication relationship

Parameter description:

GRP_NAME: replication group name

REP_NAME: copy name, which must be unique on RPS

REP_DESC: copy description

MINSTANCE: primary node instance name, which must be configured in the MAL of RPS

SINSTANCE: slave node instance name, which must be configured in the MAL of RPS

REP_TIMER: copy the timer name. With the help of a timer, you can set the timing of synchronization of replicated data. NULL if it is synchronous replication

ARCH_PATH: the full archive path of the logical logs on the primary server.

Return value:

None

Examples are as follows:

Create a replication relationship

SP_RPS_ADD_REPLICATION (synchronous replication of 'REP_GRP_B2C',' REPB2C','B to C', 'DEFARCHPATH,' NULL,'{DEFARCHPATH}\ REPB2C')

5. SP_RPS_DROP_REPLICATION

Definition:

SP_RPS_DROP_REPLICATION (

REP_NAME VARCHAR (128)

);

Function description:

Delete replication relationship

Parameter description:

REP_NAME: copy name

Return value:

None

Examples are as follows:

Delete replication relationship

SP_RPS_DROP_REPLICATION ('REPB2C')

6. SP_RPS_SET_ROUTE_FAULT_TIMEOUT

Definition:

SP_RPS_SET_ROUTE_FAULT_TIMEOUT (

REP_NAME VARCHAR (128)

TIMEOUTS INT

);

Function description:

Set replication path failure timeout

Parameter description:

REP_NAME: copy the relationship name.

TIMEOUTS: the fault timeout value in seconds. 0 means immediate timeout;-1 means there is no timeout limit return value:

None

Note:

This interface is used to set the replication path failure handling policy. After setting, RPS needs to cancel the failed replication relationship if it detects that there is a failure in the replication path and the fault persists beyond the set timeout value.

Examples are as follows:

Set replication path failure timeout

SP_RPS_SET_ROUTE_FAULT_TIMEOUT ('REPB2C',10)

7. SP_RPS_SET_INST_FAULT_TIMEOUT

Definition:

SP_RPS_SET_INST_FAULT_TIMEOUT (

INST_NAME VARCHAR (128)

TIMEOUTS INT

);

Function description:

Set replication node failure timeout

Parameter description:

INST_NAME: copy node instance name

TIMEOUTS: the fault timeout value in seconds. 0 means immediate timeout;-1 means no timeout limit

Return value:

None

Examples are as follows:

Set replication node failure timeout

SP_RPS_SET_INST_FAULT_TIMEOUT ('Blossom 10)

8. SP_RPS_ADD_TIMER

Definition:

SP_RPS_ADD_TIMER (

TIMER_NAME VARCHAR (128)

TIMER_DESC VARCHAR (1000)

TYPE$ INT

FERQ_INTERVAL INT

FREQ_SUB_INTERVAL INT

FREQ_MINUTE_INTERVAL INT

START_TIME TIME

END_TIME TIME

DURING_START_DATE DATETIME

DURING_END_DATE DATETIME

NO_END_DATA_FLAG INT

);

Function description:

Set the timer for replication relationships

Parameter description:

TIMER_NAME: timer name

TIMER_DESC: timer description

TYPE$: timer type. The values are as follows:

1: execute once

2: daily execution

3: weekly execution

4: the day ordinal of the monthly execution

5: the first week of monthly execution

6: the second week of monthly implementation

7: the third week of monthly implementation

8: the fourth week of monthly implementation

9: the last week of monthly execution

FREQ_INTERVAL: the number of months / weeks between (determined by scheduling type)

FREQ_SUB_INTERVAL: number of days between

FREQ_MINUTE_INTERVAL: number of minutes between

START_TIME: start Tim

END_TIME: end Tim

DURING_START_DATE: the start date time of the valid date time period

DURING_END_DATE: effective date time period end date time

NO_END_DATA_FLAG: whether the end date is invalid. 0 means the end date is valid, 1 means it is invalid.

TYPE$, FERQ_INTERVAL, FREQ_SUB_INTERVAL, FREQ_MINUTE_INTERVAL, START_TIME, END_TIME, DURING_START_DATE, DURING_END_DATE and NO_END_DATA_FLAG of the process correspond to the parameters TYPE, FREQ_MONTH_WEEK_INTERVAL, FREQ_SUB_INTERVAL, FREQ_MINUTE_INTERVAL, START_TIME, END_TIME, DURING_START_DATE, DURING_END_DATE and NO_END_DATE_FLAG of the process SP_ADD_TIMER, respectively. For specific instructions, please refer to the description of the process SP_ADD_TIMER.

Return value:

None

Examples are as follows:

Set the timer for replication relationships

SP_RPS_ADD_TIMER ('TIMER1',' by Day', 1, 1, 0, 1, CURTIME,'23 CURTIME 59 NOW, NULL, 1)

9. SP_RPS_REP_RESET_TIMER

Definition:

SP_RPS_REP_RESET_TIMER (

REP_NAME VARCHAR (128)

TIMER_NAME VARCHAR (128)

);

Function description:

Reset the timer for replication relationships

Parameter description:

REP_NAME: copy name

TIMER_NAME: new timer name

Return value:

None

Examples are as follows:

Reset the timer for replication relationships

SP_RPS_REP_RESET_TIMER ('REPB2C',' TIMER1')

10. SP_RPS_ADD_TAB_MAP

Definition:

SP_RPS_ADD_TAB_MAP (

REP_NAME VARCHAR (128)

MTAB_SCHEMA VARCHAR (128)

MTAB_NAME VARCHAR (128)

STAB_SCHEMA VARCHAR (128)

STAB_NAME VARCHAR (128)

READ_ONLY_MODE INT

);

Function description:

Add a table-level replication mapping

Parameter description:

REP_NAME: copy relationship name

MTAB_SCHEMA: main table schema name

MTAB_NAME: main table name

STAB_SCHEMA: from the table schema name

STAB_NAME: from table name

READ_ONLY_MODE: read-only replication mode, 1 indicates read-only mode, only replicated updates are accepted from the table, and 0 indicates non-read-only mode

Return value:

None

Examples are as follows:

Add replication Mappin

SP_RPS_ADD_TAB_MAP ('REPB2C',' USER1','T1, 'USER2',' T2, 0)

11. SP_RPS_DROP_TAB_MAP

Definition:

SP_RPS_DROP_TAB_MAP (

REP_NAME VARCHAR (128)

MTAB_SCHEMA VARCHAR (128)

MTAB_NAME VARCHAR (128)

STAB_SCHEMA VARCHAR (128)

STAB_NAME VARCHAR (128)

);

Function description:

Delete table-level replication mapping

Parameter description:

REP_NAME: copy relationship name

MTAB_SCHEMA: main table schema name

MTAB_NAME: main table name

STAB_SCHEMA: from the table schema name

STAB_NAME: from table name

Return value:

None

Examples are as follows:

Delete table-level replication mapping

SP_RPS_DROP_TAB_MAP ('REPB2C',' USER1','T1, 'USER2',' T2')

12. SP_RPS_ADD_SCH_MAP

Definition:

SP_RPS_ADD_SCH_MAP (

REP_NAME VARCHAR (128)

MSCH VARCHAR (128)

SSCH VARCHAR (128)

READ_ONLY_MODE INT

);

Function description:

Add a pattern-level replication mapping

Parameter description:

REP_NAME: copy relationship name

MSCH: main schema name

SSCH: from the table schema name

READ_ONLY_MODE: read-only replication mode, 1 indicates read-only mode, only replicated updates are accepted from the table, and 0 indicates non-read-only mode

Return value:

None

Examples are as follows:

Add replication Mappin

SP_RPS_ADD_SCH_MAP ('REPB2C',' USER1', 'USER2', 0)

13. SP_RPS_DROP_SCH_MAP

Definition:

SP_RPS_DROP_SCH_MAP (

REP_NAME VARCHAR (128)

MSCH VARCHAR (128)

SSCH VARCHAR (128)

);

Function description:

Delete pattern-level replication mapping

Parameter description:

REP_NAME: copy relationship name

MSCH: main schema name

SSCH: from schema name

Return value:

None

Examples are as follows:

Delete pattern-level replication mapping

SP_RPS_DROP_SCH_MAP ('REPB2C',' USER1', 'USER2')

14. SP_RPS_ADD_DB_MAP

Definition:

SP_RPS_ADD_DB_MAP (

REP_NAME VARCHAR (128)

READ_ONLY_MODE INT

);

Function description:

Add a library-level replication mapping

Parameter description:

REP_NAME: copy relationship name

READ_ONLY_MODE: read-only replication mode, 1 indicates read-only mode, only replicated updates are accepted from the table, and 0 indicates non-read-only mode

Return value:

None

Examples are as follows:

Add a library-level replication mapping

SP_RPS_ADD_DB_MAP ('REPB2C', 0)

15. SP_RPS_DROP_DB_MAP

Definition:

SP_RPS_DROP_DB_MAP (

REP_NAME VARCHAR (128)

);

Function description:

Delete a library-level replication mapping

Parameter description:

REP_NAME: copy relationship name

Return value:

None

Examples are as follows:

Delete a library-level replication mapping

SP_RPS_DROP_DB_MAP ('REPB2C')

16. SP_RPS_SET_BEGIN

Definition:

SP_RPS_SET_BEGIN (

GRP_NAME VARCHAR (128)

);

Function description:

Start copying settin

Parameter description:

GRP_NAME: replication group name

Return value:

None

Note:

Starts setting properties for the specified replication group. Interfaces such as creating / deleting replication relationships and creating / deleting replication mappings must be executed after this interface is called, otherwise "wrong replication setting sequence" will be reported. You cannot start multiple replication settings at the same time in the same session.

Examples are as follows:

Replication group REPB2C starts replication

SP_RPS_SET_BEGIN ('REP_GRP_B2C')

17. SP_RPS_SET_APPLY

Definition:

SP_RPS_SET_APPLY ()

Function description:

Submit replication settings, save and submit all operations for this setting. If you need to continue with the setup, you must call SP_RPS_SET_BEGIN again

Parameter description:

None

Return value:

None

Examples are as follows:

Submit replication settings

SP_RPS_SET_APPLY ()

18. SP_RPS_SET_CANCEL

Definition:

SP_RPS_SET_CANCEL ()

Function description:

Discard replication settings and all operations for this setting. If you need to reset, you must call SP_RPS_SET_BEGIN again

Parameter description:

None

Return value:

None

Examples are as follows:

Discard replication settin

SP_RPS_SET_CANCEL ()

DM data replication can be configured at the table level, schema level and library level. In this article, we look at the configuration process of data replication at the table level.

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 is a hy.t1 table on the master database and a wy.t1 table with the same structure as the hy.t1 table in the master database, it is necessary to create a synchronous replication relationship between the hy.t1 table on the master database and the wy.t1 table on the slave database, which is named 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 table-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: 16.792 (ms). Execute id is 3.SQL > select * from SYSREP.RPS_GROUPS;LINEID NAME ID DESC$-1 REP_GRP_hy2wy 1 master-slave synchronous replication used time: 1.599 (ms). Execute id is 4.

Start setup

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

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: 3.807 (ms). Execute id is 7.

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_TAB_MAP ('REPhy2wy',' HY','T 1, 'WY',' T 1, 0); DMSQL executed successfullyused time: 4.495 (ms). Execute id is 10.

Submit Settin

SQL > SP_RPS_SET_APPLY (); DMSQL executed successfullyused time: 05.038. Execute id is 12.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 11 12 / dm_home/dmdba/dmdbms/dmreplog-10 NULL 1 NULL hy to wy synchronous replication used time: 0.534 (ms). Execute id is 13.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 1 1-1 0 NULL 0 NULL2 wy 12-10 NULL 0 NULLused time: 0.499 (ms). Execute id is 14.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- 1 1 HY T1 150995951 1297 WY T1 150995951 1296 0used time: 0.523 (ms). Execute id is 15.SQL > select * from SYSREP.RPS_FAULT_HISTORY;no rowsused time: 1.136 (ms). Execute id is 16.SQL > select * from SYSREP.RPS_CONFLICTS;no rowsused time: 1.336 (ms). Execute id is 17.SQL > select * from SYSREP.RPS_TIMERS;no rowsused time: 0.987 (ms). Execute id is 18.

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

Logical log of the main library:

[root@jydm2 dmreplog] # ls-lrt Total consumption 4murRwMurRMurRMI-1 dmdba dinstall 48 October 2108: 24 llog_arch_file_20141021082409.log

The main library inserts data into the T1 table:

[dmdba@jydm2 bin] $. / disql hy/hyServer [LOCALHOST:5236]: mode is normal, state is openlogin used time: 6.047 (ms) disql V7.1.6.46-Build (2018.02.08-89107) ENT Connected to: DM 7.1.6.46SQL > select * from T1 politics no rowsused time: 0.998 (ms). Execute id is 4.s SQL > insert into T1 values (2) affect rows 1used time: 0.777 (ms). Execute id is 8.SQL > commit;executed successfullyused time: 1.141 (ms). Execute id is 9.SQL > select * from T1 * LineID ID USERNAME- 1 1 hy2 2 hy2used time: 0.403 (ms). Execute id is 10.

Query from the slave database: the synchronization is successful. The data before the master database cannot be synchronized. It is only valid for the data operated later.

[dmdba@jydm3 bin] $. / disql wy/wyServer [LOCALHOST:5236]: mode is normal, state is openlogin used time: 5.844 (ms) disql V7.1.6.46-Build (2.08 / 89107) ENT Connected to: DM 7.1.6.46SQL > select * from T1; LINEID ID USERNAME- 1 1 hy2 2 hy2used time: 0.618 (ms). Execute id is 9.

Update records in the main library

SQL > update T1 set username='hy1' where id=1;affect rows 1used time: 1.427 (ms). Execute id is 11.SQL > commit;executed successfullyused time: 0.869 (ms). Execute id is 12.SQL > select * from T1 * LineID ID USERNAME- 1 1 hy12 2 hy2used time: 0.339 (ms). Execute id is 13.

View slave records

SQL > select * from T1; LINEID ID USERNAME--1 1 hy12 2 hy2used time: 1.272 (ms). Execute id is 12.

The main library deletes all data from the T1 table:

SQL > truncate table T1 executed successfullyused time: 3.283 (ms). Execute id is 14.

All data from the library has also been deleted.

SQL > select * from T1: no rowsused time: 0.793 (ms). Execute id is 15.

Throughout the replicated environment, logical logs exist only in the main library.

We configure synchronous replication. If you want to change it to asynchronous synchronization, you can add a timer, such as:

SP_RPS_ADD_TIMER ('hy2wy_TIMER',' replicates regularly from hy to wy', 1JEOJOPER0ZOPERIMENTOPERATION 23JOREX 18PULZZONE NULLLJEI)

SP_RPS_REP_RESET_TIMER ('REPhy2wy','hy2wy_TIMER')

If you need to delete a replication group, the replication group cannot be in the configuration stage, that is, the configuration of the group has been submitted or undone.

Monitoring data replication

Although the replication server RPS does not participate in the specific execution of replication after the completion of configuration, it is recommended to keep RPS running to monitor replication during the replication process.

In the replication system, the state is divided into the state of the replication node and the state of the replication relationship. Details are shown in the table below.

The system status of the replication node is represented in the VALID_FLAG field in SYSREP.RPS_INSTANCES, and the network status is represented in the NET_VALID_FLAG field in the SYSREP.RPS_ instances table.

The status of the replication relationship is indicated in the VALID_FLAG field in SYSREP.RPS_REPLICATIONS.

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 11 12 / dm_home/dmdba/dmdbms/dmreplog-10 NULL 1 NULL hy to wy synchronous replication used time: 1.339 (ms). Execute id is 23.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 1 1-1 0 NULL 0 NULL2 wy 12-10 NULL 0 NULLused time: 0.574 (ms). Execute id is 24.

The configuration information of the replication system can be achieved by querying the system table on the replication RPS. On RPS, replication-related information is all in SYSREP mode.

Replication monitoring is carried out in replication groups. RPS polls all replication groups every 1 minute. Within each replication group, each replication node is detected in turn, requiring it to return the status of the replication node itself and the status of the replication involved.

What needs to be noted here is that the system abnormal state of the node refers to the state of the node in the replication environment, which is an inferred value. When RPS cannot get the return message of the node, and all the replication relationships related to that node in the return message of other nodes are abnormal, RPS considers that the node system is abnormal.

If an exception occurs in the replication node or replication relationship, these exceptions are recorded in the SYSREP.RPS_FAULT_ history table. If the END_TIME field is NULL, it means that the fault is not over, and the administrator needs to check the status of the replication node and its network connection as soon as possible.

SQL > select * from SYSREP.RPS_CONFLICTS;no rowsused time: 1.336 (ms). Execute id is 17.

DM7 does not specifically set up the management interface for error history. Administrators can manage error records by deleting and querying the SYSREP.RPS_FAULT_ HISTORY table.

At this point, the replication environment configuration is complete. The above example is just the simplest replication environment. The configuration of replication is flexible. Within the same replication group, a master server can have multiple slave servers, and a replication node can be both a master server and a slave server. Administrators can configure a symmetrical, one-to-many, many-to-one, cascaded, circular replication environment according to their actual needs.

The replicated configuration can be modified during the configuration process or after the configuration is complete. Modifications include replication groups, replication relationships, deletion of replication objects, and modification of replication relationship properties. These modifications must be made between starting the replication SP_RPS_SET_BEGIN and committing the replication SP_RPS_SET_APPLY. If you need to delete a replication group, the replication group cannot be in the configuration stage, that is, the configuration of the group has been submitted or undone.

Add a timer to change synchronous replication to asynchronous replication

SQL > SP_RPS_ADD_TIMER ('hy2wy_TIMER',' replicates regularly from hy to wy', 2019-12-18 23); SP_RPS_ADD_TIMER ('hy2wy_TIMER',' replicates regularly from hy to wy', 1JEO-0J-23-18-18-18) [- 8702]: group not set begin.used time: 33.874 (ms) Execute id is 0.

Prompt that the replication group settings do not start and cannot be modified.

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

Set the start time to 2019-12-18 23:38:33

SQL > SP_RPS_ADD_TIMER ('hy2wy_TIMER',' is copied regularly from hy to wy', DMSQL executed successfullyused time: 13.217 (ms). Execute id is 29.SQL > SP_RPS_REP_RESET_TIMER ('REPhy2wy','hy2wy_TIMER'); DMSQL executed successfullyused time: 0.790 (ms). Execute id is 30.SQL > SP_RPS_SET_APPLY (); DMSQL executed successfullyused time: 24.494 (ms). Execute id is 31.

Now insert data into the main library

SQL > select * from T1 * LINEID ID USERNAME- 11 hyused time: 2.296 (ms). Execute id is 4.SQL > insert into T1 values (2 affect rows 1used time: 1.519 (ms). Execute id is 5.SQL > commit;executed successfullyused time: 1.430 (ms). Execute id is 6.SQL > select * from T1 * LineID ID USERNAME- 1 1 hy2 2 hy2used time: 0.512 (ms). Execute id is 8.

Query records from the library

SQL > select * from T1 * LINEID ID USERNAME- 11 hyused time: 0.337 (ms). Execute id is 8.SQL > select sysdate;LINEID SYSDATE-1 2019-12-18 23:38:37.570259used time: 0.263 (ms). Execute id is 9.

You can see that the table data has not been synchronized to the slave database at the time 2019-12-18 23

SQL > select sysdate;LINEID SYSDATE-1 2019-12-18 23:39:05.629576used time: 0.432 (ms). Execute id is 12.SQL > select * from T1 * LineID ID USERNAME- 1 1 hy2 2 hy2used time: 0.458 (ms). Execute id is 13.

You can see that the table data has been synchronized to the slave database at the time 2019-12-18 2019-12-18 2315 / 39 / 05.629576

Delete replication mapping

SQL > SP_RPS_DROP_TAB_MAP ('REPhy2wy',' HY','T 1, 'WY',' T 1'); DMSQL executed successfullyused time: 1.485 (ms). Execute id is 36.

Modify the error timeout for replication, which is measured in seconds

SQL > SP_RPS_SET_ROUTE_FAULT_TIMEOUT ('REPhy2wy', 60); DMSQL executed successfullyused time: 0.791 (ms). Execute id is 37.

Delete replication relationship

SQL > SP_RPS_DROP_REPLICATION ('REPhy2wy'); DMSQL executed successfullyused time: 1.807 (ms). Execute id is 38.

Delete the entire replication group

SQL > SP_RPS_DROP_GROUP ('REP_GRP_hy2wy'); DMSQL executed successfullyused time: 1.807 (ms). Execute id is 39.

Delete user and system tables used for data replication

SQL > SP_INIT_REP_SYS (0); DMSQL executed successfullyused time: 160.803 (ms). Execute id is 51.SQL > select * from SYSREP.RPS_INSTANCES;select * from SYSREP.RPS_INSTANCES; [- 2103]: Error in line: 1Invalid schema name [SYSREP] .used time: 0.735 (ms). Execute id is 0.

In addition, if you want to cancel the operation when configuring or modifying the configuration, you can use the following system procedure to end the configuration.

SQL > SP_RPS_SET_CANCEL (); DMSQL executed successfullyused time: 0.749 (ms). Execute id is 40. 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.