MySQL 的单向复制

以下步骤以docker容器的方式快速演示如何搭建MySQL的单向复制环境.

创建网络

docker network create dtle-net

创建源端/目标端 MySQL

docker run --name mysql-src -e MYSQL_ROOT_PASSWORD=pass -p 33061:3306 --network=dtle-net -d mysql:5.7 --gtid-mode=ON --enforce-gtid-consistency=1 --log-bin=bin --server-id=1

docker run --name mysql-dst -e MYSQL_ROOT_PASSWORD=pass -p 33062:3306 --network=dtle-net -d mysql:5.7 --gtid-mode=ON --enforce-gtid-consistency=1 --log-bin=bin --server-id=2

检查是否联通:

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "select @@version\G"
< *************************** 1. row ***************************
@@version: 5.7.23-log

> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "select @@version\G"
< *************************** 1. row ***************************
@@version: 5.7.23-log

创建 dtle

docker run --name dtle-consul -p 8500:8500 --network=dtle-net -d consul:latest
docker run --name dtle -p 4646:4646 --network=dtle-net -d actiontech/dtle
# 如需要使用dtle 2.x HTTP API兼容层，则需要额外映射8190端口：-p 8190:8190

检查是否正常:

> curl -XGET "127.0.0.1:4646/v1/nodes" -s | jq
< [
    {
      "Address": "127.0.0.1",
      "Datacenter": "dc1",
      "Drivers": {
        "dtle": {
          "Attributes": {
            "driver.dtle": "true",
            "driver.dtle.version": "..."
          },
          "Detected": true,
          "Healthy": true,
        }
      },
      "ID": "65ff2f9a-a9fa-997c-cce0-9bc0b4f3396c",
      "Name": "nomad0",
      "Status": "ready",
    }
  ]
# （部分项目省略）

准备作业定义文件

准备文件job.json, 内容如下:

{
  "Job": {
    "ID": "dtle-demo",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
        "Name": "src",
        "Tasks": [{
          "Name": "src",
          "Driver": "dtle",
          "Config": {
            "Gtid": "",
            "ReplicateDoDb": [{
              "TableSchema": "demo",
              "Tables": [{
                "TableName": "demo_tbl"
              }]
            }],
            "SrcConnectionConfig": {
              "Host": "mysql-src",
              "Port": 3306,
              "User": "root",
              "Password": "pass"
            },
            "DestConnectionConfig": {
              "Host": "mysql-dst",
              "Port": 3306,
              "User": "root",
              "Password": "pass"
            }
          }
        }]
      }, {
        "Name": "dest",
        "Tasks": [{
          "Name": "dest",
          "Driver": "dtle",
          "Config": {
            "DestType": "mysql"
          }
        }]
    }]
  }
}

其中定义了:

• 源端/目标端的连接字符串
• 要复制的表为demo.demo_tbl
• GTID点位为空, 表示此复制是 全量+增量 的复制. 如只测试增量复制, 可指定合法的GTID

准备测试数据

可在源端准备提前建表demo.demo_tbl, 并插入数据, 以体验全量复制过程. 也可不提前建表.

创建复制任务

> curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @job.json -s | jq
< {
  "EvalCreateIndex": 50,
  "EvalID": "a5e9c353-5eb9-243e-983d-bc096a93ddca",
  "Index": 50,
  "JobModifyIndex": 49,
  "KnownLeader": false,
  "LastContact": 0,
  "Warnings": ""
}

查看作业状态

> curl -XGET "http://127.0.0.1:4646/v1/job/dtle-demo" -s | jq '.Status'
< "running"

测试

此时可在源端对表demo.demo_tbl进行DDL/DML等各种操作, 查看目标端数据是否一致