MySQL 的汇聚复制

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

创建网络

docker network create dtle-net

创建源端(2个)和目标端(1个) MySQL

docker run --name mysql-src1 -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-src2 -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

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

检查是否联通:

> 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

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

在源端MySQL中创建表结构, 获取GTID点位, 并插入数据

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "CREATE DATABASE demo; CREATE TABLE demo.demo_tbl(a int primary key)"
< ...

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "show master status\G" | grep "Executed_Gtid_Set"
< Executed_Gtid_Set: f6def853-cbaa-11e8-8aeb-0242ac120003:1-7

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "insert into demo.demo_tbl values(1),(2),(3)"
< ...

---

> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "CREATE DATABASE demo; CREATE TABLE demo.demo_tbl(a int primary key)"
< ...

> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "show master status\G" | grep "Executed_Gtid_Set"
< Executed_Gtid_Set: f74aacb5-cbaa-11e8-bdd1-0242ac120004:1-7


> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "insert into demo.demo_tbl values(4),(5),(6)"
< ...

在目标端MySQL中创建表结构

> mysql -h 127.0.0.1 -P 33063 -uroot -ppass -e "CREATE DATABASE demo; CREATE TABLE demo.demo_tbl(a int primary key)"
< ...

创建 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

检查是否正常:

> curl -XGET "127.0.0.1:4646/v1/nodes" -s | jq
< [{...}]

准备作业定义文件

src1到dst的复制定义文件

准备src1_dst.json, 内容如下:

{
  "Job": {
    "ID": "dtle-demo-src1-dst",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
        "Name": "src",
        "Tasks": [{
          "Name": "src",
          "Driver": "dtle",
          "Config": {
            "Gtid": "f6def853-cbaa-11e8-8aeb-0242ac120003:1-7",
            "ReplicateDoDb": [{
              "TableSchema": "demo",
              "Tables": [{
                "TableName": "demo_tbl"
              }]
            }],
            "SrcConnectionConfig": {
              "Host": "mysql-src1",
              "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点位为 准备数据阶段 插入数据之前的src1上的GTID点位

src2到dst的复制定义文件

准备src2_dst.json, 内容如下:

{
  "Job": {
    "ID": "dtle-demo-src2-dst",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
        "Name": "src",
        "Tasks": [{
          "Name": "src",
          "Driver": "dtle",
          "Config": {
            "Gtid": "f74aacb5-cbaa-11e8-bdd1-0242ac120004:1-7",
            "ReplicateDoDb": [{
              "TableSchema": "demo",
              "Tables": [{
                "TableName": "demo_tbl"
              }]
            }],
            "SrcConnectionConfig": {
              "Host": "mysql-src2",
              "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"
          }
        }]
    }]
  }
}

其中与src1_dst.json不同的是:

• 源端的连接字符串
• GTID点位为 准备数据阶段 插入数据之前的src2上的GTID点位

创建复制任务

> curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @src1_dst.json -s | jq
< {...}

> curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @src2_dst.json -s | jq
< {...}

查看作业ID和状态:

> curl -XGET "127.0.0.1:4646/v1/jobs" -s | jq '.[] | .ID, .Status'
< "dtle-demo-src1-dst"
"running"
"dtle-demo-src2-dst"
"running"

测试

在src1和src2中分别插入数据, 查看dst中的数据, 验证全量和增量的数据均存在

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "insert into demo.demo_tbl values(11)"
< ...

> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "insert into demo.demo_tbl values(12)"
< ...

> mysql -h 127.0.0.1 -P 33063 -uroot -ppass -e "select * from demo.demo_tbl"
< 
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
| 11 |
| 12 |
+----+