On the use of dtle in data transmission

Author: Ma yingle A member of the research and development team of akesheng, responsible for the testing of mysql middleware and database management platform. Good at finding fault (testing technology enthusiasts), welcome to try dtle~ Source: original contribution *Aikesheng is produced by the open source community. The original content cannot be used without authorization. Please contact the editor for reprint and indicate the source.

dtle is an open-source Data Transmission Middleware of ikosen MySQL. This paper briefly introduces the preliminary use method. Project address: https://github.com/actiontech/dtle

1, Download and install

Environmental description:

  • Prepare three hosts with the names of node4, node5 and node6

  • All three are agent s, and node4 and node5 are manager s

1.1 download

Download dtle's latest release rpm package Address: https://github.com/actiontech/dtle/releases

dtle installation package download:

wget https://github.com/actiontech/dtle/releases/download/v2.19.11.0/dtle-2.19.11.0.x86_64.rpm

1.2 installation

Installation steps https://actiontech.github.io/dtle-docs-cn/4/4.0_installation.html

Install dtle of node4, node5 and node6 respectively.

1.3 start dtle

After dtle installation, it does not start. You need to configure the conf file of dtle before starting.

1.4 configure dtle.conf

Configuration related instructions https://actiontech.github.io/dtle-docs-cn/4/4.1_node_configuration.html

Dtle.conf preparation: the following configuration files replace the original dtle.conf (/ opt/dtle/etc/dtle/dtle.conf)

dtle.conf of node4

# Setup data dir
data_dir = "/opt/dtle/data"
log_level = "DEBUG"
log_file = "/opt/dtle.log"
#log_to_stdout = true

bind_addr = "172.100.9.4"

# Modify our port to avoid a collision with server
ports {
    http = 8190
}

# Enable the manager
manager {
    enabled = true

    # Self-elect, should be 3 or 5 for production,
    # Addresses to attempt to join when the server starts.
    join = [ "172.100.9.4","172.100.9.5" ]
}

# Enable the agent
agent {
    enabled = true
    managers = ["172.100.9.4:8191","172.100.9.5:8191"]
}

metric{
   collection_interval = "15s"
   publish_allocation_metrics = "true"
   publish_node_metrics = "true"
}

addresses {
    http = "172.100.9.4"
    rpc = "172.100.9.4"
    serf = "172.100.9.4"
}
advertise {
    http = "172.100.9.4"
    rpc = "172.100.9.4"
    serf = "172.100.9.4"
}

dtle.conf of node5

# Setup data dir
data_dir = "/opt/dtle/data"
log_level = "DEBUG"
log_file = "/opt/dtle.log"
#log_to_stdout = true

bind_addr = "172.100.9.5"

# Modify our port to avoid a collision with server
ports {
    http = 8190
}

# Enable the manager
manager {
    enabled = true

    # Self-elect, should be 3 or 5 for production,
    # Addresses to attempt to join when the server starts.
    join = [ "172.100.9.4","172.100.9.5" ]
}

# Enable the agent
agent {
    enabled = true
    managers = ["172.100.9.4:8191","172.100.9.5:8191"]
}

metric{
   collection_interval = "15s"
   publish_allocation_metrics = "true"
   publish_node_metrics = "true"
}

addresses {
    http = "172.100.9.5"
    rpc = "172.100.9.5"
    serf = "172.100.9.5"
}
advertise {
    http = "172.100.9.5"
    rpc = "172.100.9.5"
    serf = "172.100.9.5"
}

dtle.conf of node6

# Setup data dir
data_dir = "/opt/dtle/data"
log_level = "DEBUG"
log_file = "/opt/dtle.log"
#log_to_stdout = true

bind_addr = "172.100.9.6"

# Modify our port to avoid a collision with server
ports {
    http = 8190
}

# Enable the manager
manager {
    enabled = false

    # Self-elect, should be 3 or 5 for production,
    # Addresses to attempt to join when the server starts.
    join = [ "172.100.9.4","172.100.9.5" ]
}

# Enable the agent
agent {
    enabled = true
    managers = ["172.100.9.4:8191","172.100.9.5:8191"]
}

metric{
   collection_interval = "15s"
   publish_allocation_metrics = "true"
   publish_node_metrics = "true"
}

addresses {
    http = "172.100.9.6"
    rpc = "172.100.9.6"
    serf = "172.100.9.6"
}
advertise {
    http = "172.100.9.6"
    rpc = "172.100.9.6"
    serf = "172.100.9.6"
}

1.5 start dtle

Start command description https://actiontech.github.io/dtle-docs-cn/4/4.2_command.html

1.6 view three node dtle processes

node 4

node 5

node 6

1.7 try to see if dtle architecture meets expectations

Command specification https://actiontech.github.io/dtle-docs-cn/4/4.2_command.html

Among them, members shows the information of the manager node in the cluster

HTTP API https://actiontech.github.io/dtle-docs-cn/4/4.4_http_api.html

The output content is in json format. You can install jq to format the display.

2, Source / target preparation

MySQL migration user preparation

2.1 create user

Source end user creation

Target end user creation

Description of minimum permissions for source / target users https://actiontech.github.io/dtle-docs-cn/4/4.5_mysql_user_privileges.html

2.2 data preparation

Source data preparation

Database table before target end test

2.3 create job

Create full + incremental, table level migration job s, and use dtle of node5 and node6 nodes as the agent of source and target migration respectively

job Configure related parameters https://actiontech.github.io/dtle-docs-cn/4/4.3_job_configuration.html

2.3.1 prepare job.json

job.json
{
    "Name":"have_a_try",
    "Failover":false,
    "Orders":[],
    "Type":"synchronous",
    "Tasks":[
        {
            "Type":"Src",
            "NodeId":"ee97dc49-85ed-febc-4d3c-cfbfa87f46bd",
            "Config":{
                "Gtid":"",
                "DropTableIfExists":false,
                "SkipCreateDbTable":false,
                "ApproveHeterogeneous":true,
                "ReplChanBufferSize":"600",
                "ChunkSize":"2000",
                "ExpandSyntaxSupport":false,
                "MsgBytesLimit":"20480",
                "MsgsLimit":"65536",
                "BytesLimit":"67108864",
                "GroupMaxSize":"1",
                "GroupTimeout":"100",
                "SqlFilter":[],
                "ReplicateDoDb":[
                    {
                        "TableSchema":"test",
                        "Tables":[
                            {
                                "TableName":"test1"
                            }
                        ]
                    }
                ],
                "ConnectionConfig":{
                    "Host":"172.100.9.1",
                    "Port":"3306",
                    "User":"src_test",
                    "Password":"test"
                }
            }
        },
        {
            "Type":"Dest",
            "NodeId":"e623aedd-5c37-da67-4ddf-1a82ce1ac298",
            "Config":{
                "ParallelWorkers":"1",
                "ConnectionConfig":{
                    "Host":"172.100.9.2",
                    "Port":"3306",
                    "User":"dest_test",
                    "Password":"test"
                }
            }
        }
    ]
}

NodeId can be found by command or HTTP API query.

2.3.2 create job

Establish job On trial HTTP API https://actiontech.github.io/dtle-docs-cn/4/4.4_http_api.html

Query job status as running after creation

job details can be queried using the HTTP API, for example

[
    {
        "CreateIndex":109,
        "ID":"ba13f3e1-0630-f141-4c60-a1f36c61cdb3",
        "JobModifyIndex":2372,
        "JobSummary":{
            "Constraints":null,
            "CreateIndex":109,
            "Datacenters":[
                "dc1"
            ],
            "EnforceIndex":false,
            "Failover":false,
            "ID":"ba13f3e1-0630-f141-4c60-a1f36c61cdb3",
            "JobModifyIndex":2372,
            "ModifyIndex":2372,
            "Name":"have_a_try",
            "Orders":[

            ],
            "Region":"global",
            "Status":"running",
            "StatusDescription":"",
            "Tasks":[
                {
                    "Config":{
                        "GroupMaxSize":"1",
                        "ChunkSize":"2000",
                        "BinlogFile":"1.000002",
                        "MsgsLimit":"65536",
                        "GroupTimeout":"100",
                        "DropTableIfExists":false,
                        "ExpandSyntaxSupport":false,
                        "BytesLimit":"67108864",
                        "MsgBytesLimit":"20480",
                        "ReplChanBufferSize":"600",
                        "NatsAddr":"172.100.9.6:8193",
                        "ApproveHeterogeneous":true,
                        "SqlFilter":[
 
                        ],
                        "BinlogPos":4261,
                        "ConnectionConfig":{
                            "Host":"172.100.9.1",
                            "Port":"3306",
                            "User":"src_test",
                            "Password":"*"
                        },
                        "TrafficAgainstLimits":0,
                        "Gtid":"8868d98f-af5e-11e8-9aa9-0242ac110002:1-16",
                        "ReplicateDoDb":[
                            {
                                "TableSchema":"test",
                                "Tables":[
                                    {
                                        "TableName":"test1"
                                    }
                                ]
                            }
                        ],
                        "SkipCreateDbTable":false
                    },
                    "ConfigLock":{
 
                    },
                    "Constraints":null,
                    "Driver":"MySQL",
                    "Leader":false,
                    "NodeID":"ee97dc49-85ed-febc-4d3c-cfbfa87f46bd",
                    "NodeName":"",
                    "Type":"Src"
                },
                {
                    "Config":{
                        "BinlogFile":"1.000002",
                        "BinlogPos":4261,
                        "ConnectionConfig":{
                            "Host":"172.100.9.2",
                            "Port":"3306",
                            "User":"dest_test",
                            "Password":"*"
                        },
                        "Gtid":"8868d98f-af5e-11e8-9aa9-0242ac110002:1-16",
                        "ParallelWorkers":"1",
                        "NatsAddr":"172.100.9.6:8193"
                    },
                    "ConfigLock":{

                    },
                    "Constraints":null,
                    "Driver":"MySQL",
                    "Leader":true,
                    "NodeID":"e623aedd-5c37-da67-4ddf-1a82ce1ac298",
                    "NodeName":"",
                    "Type":"Dest"
                }
            ],
            "Type":"synchronous"
        },
        "ModifyIndex":2372,
        "Name":"have_a_try",
        "Status":"running",
        "StatusDescription":"",
        "Type":"synchronous"
    }
]

2.4 query target migration

Comparison before and after target end migration:

Add two new libraries, of which the test library is the result of our job migration.

dtle Library related description https://actiontech.github.io/dtle-docs-cn/3/3.3_impact_on_dest.html

2.5 perform common DML operations at the source

2.6 perform normal DDL operation at the source

Summary

For more usage, please refer to the project document. Welcome to try~ https://actiontech.github.io/dtle-docs-cn/

Tags: Database github MySQL JSON

Posted on Mon, 30 Mar 2020 02:53:10 -0700 by Crysma