MySQL 的数据分散

以下步骤以docker容器的方式快速演示如何搭建MySQL的数据分散环境. 数据分散复制, 将源表中的数据中, 主键<5的行复制到目标库1, 主键>=5的行复制到目标库2.

创建网络

docker network create dtle-net

创建源端(1个)和目标端(2个) 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-dst1 -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-dst2 -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: 167dd42f-d076-11e8-8104-0242ac120003:1-7

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

在目标端MySQL中创建表结构

> 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 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
< [{...}]

准备作业定义文件

src到dst1的复制定义文件

准备src_dst1.json, 内容如下:

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

其中定义了:

源端/目标端的连接字符串
要复制的表为demo.demo_tbl
demo_tbl的复制数据条件为a<5

src到dst2的复制定义文件

准备src_dst2.json, 内容如下:

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

其中定义了:

源端/目标端的连接字符串
要复制的表为demo.demo_tbl
demo_tbl的复制数据条件为a>=5

其中与src1_dst.json不同的是:

源端的连接字符串
demo_tbl的复制数据条件

创建复制任务

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

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

查看作业ID和状态:

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

测试

在src中插入数据, 查看dst1/dst2中的数据, 验证全量和增量的数据均存在

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


> mysql -h 127.0.0.1 -P 33062 -uroot -ppass -e "select * from demo.demo_tbl"
< 
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
|  4 |
+----+

> mysql -h 127.0.0.1 -P 33063 -uroot -ppass -e "select * from demo.demo_tbl"
< 
+----+
| a  |
+----+
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

2.2.MySQL的数据分散