数据同步 - Data Transfer | Using DTLE to synchronize MySQL data to DBLE - MySQL分布式中间件DBLE

Author: Ren Zhongyu
A member of the DBA team of Aikesheng, good at failure analysis and performance optimization, welcome to discuss technical issues related to articles.
Source of this article: original contribution
*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

background

Due to the needs of a customer, there is an online business MySQL database that needs to migrate business data to the DBLE distributed database due to the large amount of data and business read and write pressure, but at the same time, because the business is 7x24h, the time window for downtime is short. , so you need to consider the solution of real-time data synchronization.

In the past, DBLE's business online was basically a new deployment, and real-time data synchronization was rarely implemented. This problem has been improved after the release of DTLE last year. Let's practice today.

Environmental preparation

1. Target DBLE cluster deployment

The process of installing DBLE software and back-end sharding MySQL library is omitted
- DBLE version 3.20.10.8, MySQL version 5.7.25
sharding.xml

  <?xml version="1.0"?>
  <!DOCTYPE dble:sharding SYSTEM "sharding.dtd">
  <dble:sharding xmlns:dble="http://dble.cloud/" version="4.0">
      <schema name="dtle" sqlMaxLimit="-1" shardingNode="dn_01">
          <singleTable name="gtid_executed_v4" shardingNode="dn_01" sqlMaxLimit="-1"></singleTable>
      </schema>
      <schema name="ren" sqlMaxLimit="-1" shardingNode="dn_01">
          <shardingTable name="test" shardingNode="dn_01,dn_02,dn_03,dn_04" sqlMaxLimit="-1" shardingColumn="id" function="func_jumphash"></shardingTable>
      </schema>
      <shardingNode name="dn_03" dbGroup="dh-mysql-cluster02" database="dh_dn_03"></shardingNode>
      <shardingNode name="dn_04" dbGroup="dh-mysql-cluster02" database="dh_dn_04"></shardingNode>
      <shardingNode name="dn_02" dbGroup="dh-mysql-cluster01" database="dh_dn_02"></shardingNode>
      <shardingNode name="dn_01" dbGroup="dh-mysql-cluster01" database="dh_dn_01"></shardingNode>
      <function name="func_jumphash" class="jumpStringHash">
          <property name="partitionCount">4</property>
          <property name="hashSlice">0:-1</property>
      </function>
  </dble:sharding>

db.xml

  <?xml version="1.0"?>
  <!DOCTYPE dble:db SYSTEM "db.dtd">
  <dble:db xmlns:dble="http://dble.cloud/" version="4.0">
      <dbGroup name="dh-mysql-cluster02" rwSplitMode="0" delayThreshold="-1">
          <heartbeat timeout="0" errorRetryCount="0">show slave status</heartbeat>
          <dbInstance name="10.186.61.13-3326-dh-1" url="10.186.61.13:3326" user="dbleuser" password="jpfmxIeMt1vxAJ6zd6Q10PGRRi+Qj023Dl+YXuOr3C4VXTdV5+GJaOIv5iVmWCwpXcucn/zi02HVlT7ADX+m6Q==" maxCon="100" minCon="10" primary="true" readWeight="0" id="mysql-i63009" usingDecrypt="true"></dbInstance>
      </dbGroup>
      <dbGroup name="dh-mysql-cluster01" rwSplitMode="0" delayThreshold="-1">
          <heartbeat timeout="0" errorRetryCount="0">show slave status</heartbeat>
          <dbInstance name="10.186.61.11-3316-dh-1" url="10.186.61.11:3316" user="dbleuser" password="QQWRF80AGNbx4jIAx/b2Ww7Myol1+ntlyzGmA1A3PXVISmRD/i5pgRnLLwYsXoLmH0jiv1qZAkqIBHv6Yg/XAg==" maxCon="100" minCon="10" primary="true" readWeight="0" id="mysql-47vn84" usingDecrypt="true"></dbInstance>
      </dbGroup>
  </dble:db>

user.xml

  <?xml version="1.0"?>
  <!DOCTYPE dble:user SYSTEM "user.dtd">
  <dble:user xmlns:dble="http://dble.cloud/" version="4.0">
      <managerUser name="root" password="CrjpLhvVJkHk0EPW35Y07dUeTimf52zMqClYQkIAN3/dqiG1DVUe9Zr4JLh8Kl+1KH1zd7YTKu5w04QgdyQeDw==" usingDecrypt="true"></managerUser>
      <shardingUser name="ren" schemas="ren,dtle" password="P+C2KazQiS3ZZ6uojBJ91MZIqYqGczspQ/ebyBZOC9xKAAkAFrqEDC9OPn/vObAyO4P8Zu3vHQJ+rljM040Kdg==" usingDecrypt="true" readOnly="false" maxCon="0" blacklist="default_black_list"></shardingUser>
  </dble:user>

2. Source and target test table creation

Source MySQL database software installation
Both the source MySQL and the target DBLE need to create a test table name: test

  use ren;
  CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    `city` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    `dt` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ctiy` (`city`)
  ) ENGINE=InnoDB;

3. Deploy a single-node DTLE

DTLE community version GitHub download address: https://github.com/actiontech/dtle/
After the download is complete, install it directly by rpm (this example uses the internal QA verification version)
- rpm -ivh --prefix=/data/dtle dtle-ee-9.9.9.9.x86_64.rpm
After the installation is complete, confirm that the startup is normal

  # curl -XGET "127.0.0.1:4646/v1/nodes" -s | jq
  [
    {
      "Address": "127.0.0.1",
      "ID": "223c31b4-05cd-a763-b3e7-dbea6d416576",
      "Datacenter": "dc1",
      "Name": "nomad0",
      "NodeClass": "",
      "Version": "1.1.2",
      "Drain": false,
      "SchedulingEligibility": "eligible",
      "Status": "ready",
      "StatusDescription": "",
      "Drivers": {
        "dtle": {
          "Attributes": {
            "driver.dtle.full_version": "9.9.9.9-master-a65ee13",
            "driver.dtle": "true",
            "driver.dtle.version": "9.9.9.9"
          },
          "Detected": true,
          "Healthy": true,     
          "HealthDescription": "Healthy",
          "UpdateTime": "2022-02-28T07:45:15.650289984Z"
  ·········
  ]

Create MySQL-To-DBLE task

1. Full synchronization

1. Prepare the job file

# cat job.json
{
  "Job": {
    "ID": "mysqlToDBLE",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
        "Name": "src",
        "Tasks": [{
          "Name": "src",
          "Driver": "dtle",
          "Config": {
            "Gtid": "",
            "ReplicateDoDb": [{
              "TableSchema": "ren",
              "Tables": [{
                "TableName": "test"
              }]
            }],
            "ConnectionConfig": {
              "Host": "10.186.61.11",
              "Port": 3306,
              "User": "root",
              "Password": "root"
            }
          }
        }]
      }, {
        "Name": "dest",
        "Tasks": [{
          "Name": "dest",
          "Driver": "dtle",
          "Config": {
            "ConnectionConfig": {
              "Host": "10.186.61.10",
              "Port": 8066,
              "User": "ren",
              "Password": "ren"
            }
          }
        }]
    }]
  }
}

2. Prepare full copy data

Source-side MySQL library execution

mysql> insert into test values(1,'ren','sh',now());
mysql> insert into test values(2,'jack','bj',now());
mysql> insert into test values(3,'tom','sz',now());

3. Start the sync task

# curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @job.json -s| jq
{
  "EvalID": "88ab4a42-98b7-696e-0f98-08c1fe3ee4bd",
  "EvalCreateIndex": 12310,
  "JobModifyIndex": 12310,
  "Warnings": "",
  "Index": 12310,
  "LastContact": 0,
  "KnownLeader": false
}

4. Check the synchronization

Confirm that the full data synchronization is complete

# 目标端 DBLE 中执行
mysql> use ren;
Database changed
mysql> show tables;
+------------------+
| Tables_in_ren    |
+------------------+
| test             |
| gtid_executed_v4 |
+------------------+
2 rows in set (0.01 sec)

mysql> select * from test;
+----+------+------+---------------------+
| id | name | city | dt                  |
+----+------+------+---------------------+
|  1 | ren  | sh   | 2022-03-07 06:53:30 |
|  2 | jack | bj   | 2022-03-07 06:53:41 |
|  3 | tom  | sz   | 2022-03-07 06:53:59 |
+----+------+------+---------------------+
3 rows in set (0.01 sec)

# 源端 MySQL 写入增量测试数据
mysql> insert into test select null,'mike','nj',now();
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
········
mysql> insert into test select null,'mike4','nj',now();
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update test set city = 'sh' where name like 'mike%';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from test;
+----+-------+------+---------------------+
| id | name  | city | dt                  |
+----+-------+------+---------------------+
|  1 | ren   | sh   | 2022-03-07 06:53:30 |
|  2 | jack  | bj   | 2022-03-07 06:53:41 |
|  3 | tom   | sz   | 2022-03-07 06:53:59 |
| 45 | mike  | sh   | 2022-03-07 08:03:57 |
| 46 | mike2 | sh   | 2022-03-07 08:04:02 |
| 47 | mike3 | sh   | 2022-03-07 08:04:05 |
| 48 | mike4 | sh   | 2022-03-07 08:04:09 |
+----+-------+------+---------------------+
7 rows in set (0.01 sec)

# 目标端 DBLE 检查增量同步情况
mysql> select * from test;
+----+-------+------+---------------------+
| id | name  | city | dt                  |
+----+-------+------+---------------------+
|  1 | ren   | sh   | 2022-03-07 06:53:30 |
|  2 | jack  | bj   | 2022-03-07 06:53:41 |
|  3 | tom   | sz   | 2022-03-07 06:53:59 |
| 45 | mike  | sh   | 2022-03-07 08:03:57 |
| 46 | mike2 | sh   | 2022-03-07 08:04:02 |
| 47 | mike3 | sh   | 2022-03-07 08:04:05 |
| 48 | mike4 | sh   | 2022-03-07 08:04:09 |
+----+-------+------+---------------------+
7 rows in set (0.04 sec)

mysql> explain select * from test where id  = 1;
+---------------+----------+----------------------------------+
| SHARDING_NODE | TYPE     | SQL/REF                          |
+---------------+----------+----------------------------------+
| dn_01         | BASE SQL | select * from test where id  = 1 |
+---------------+----------+----------------------------------+
1 row in set (0.03 sec)

2. Incremental synchronization based on GTID sites

1. Destroy all synchronization tasks

# cd /data/dtle/usr/bin/
# ll
total 188836
-rwxr-xr-x 1 root root 107811060 Mar 17  2020 consul
-rwxr-xr-x 1 root root  85550512 Jun 22  2021 nomad
# ./nomad job status
ID                 Type     Priority  Status   Submit Date
mysqlToDBLE        service  50        running  2022-03-07T15:47:31+08:00
mysqltoMysql-sync  service  50        running  2022-03-03T16:06:10+08:00
# ./nomad job stop -purge mysqlToDBLE
·······
  ⠙ Deployment "433ed3d4" successful
·······
# ./nomad job status
ID                 Type     Priority  Status   Submit Date
mysqltoMysql-sync  service  50        running  2022-03-03T16:06:10+08:00

2. Record the source GTID site

# 记录源端 MySQL 需要开始的GTID位点
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000178
········
Executed_Gtid_Set: 442dbe92-00c3-11ec-a0cf-02000aba3d0b:1-49705119,
cdc6fb62-00c2-11ec-a259-02000aba3d0a:1-3555
1 row in set (0.01 sec)
# 插入增量数据（模拟业务新增数据）
mysql> insert into test select 88,'sync01','wh',now();
mysql> insert into test select 99,'sync02','wh',now();
# 源端 MySQL 确认数据已插入
mysql> select * from test;
+----+--------+------+---------------------+
| id | name   | city | dt                  |
+----+--------+------+---------------------+
|  1 | ren    | sh   | 2022-03-07 06:53:30 |
········
| 48 | mike4  | sh   | 2022-03-07 08:04:09 |
| 88 | sync01 | wh   | 2022-03-07 08:24:20 |
| 99 | sync02 | wh   | 2022-03-07 08:24:31 |
+----+--------+------+---------------------+
9 rows in set (0.00 sec)
# 目标端 DBLE 数据因同步job已销毁，新插入数据未同步过来
mysql> select * from test;
+----+-------+------+---------------------+
| id | name  | city | dt                  |
+----+-------+------+---------------------+
|  1 | ren   | sh   | 2022-03-07 06:53:30 |
········
| 48 | mike4 | sh   | 2022-03-07 08:04:09 |
+----+-------+------+---------------------+
7 rows in set (0.00 sec)

3. Prepare the incremental synchronization job file

# cat job.json
{
  "Job": {
    "ID": "mysqlToDBLE",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
        "Name": "src",
        "Tasks": [{
          "Name": "src",
          "Driver": "dtle",
          "Config": {
            "Gtid": "442dbe92-00c3-11ec-a0cf-02000aba3d0b:1-49705119,cdc6fb62-00c2-11ec-a259-02000aba3d0a:1-3555",
            "ReplicateDoDb": [{
              "TableSchema": "ren",
              "Tables": [{
                "TableName": "test"
              }]
            }],
            "ConnectionConfig": {
              "Host": "10.186.61.11",
              "Port": 3306,
              "User": "root",
              "Password": "root"
            }
          }
        }]
      }, {
        "Name": "dest",
        "Tasks": [{
          "Name": "dest",
          "Driver": "dtle",
          "Config": {
            "ConnectionConfig": {
              "Host": "10.186.61.10",
              "Port": 8066,
              "User": "ren",
              "Password": "ren"
            }
          }
        }]
    }]
  }
}

4. Start the incremental sync task

# curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @job.json -s |jq
{
  "EvalID": "cad6fb19-62d3-67aa-6f5c-fbb79f8016d2",
  "EvalCreateIndex": 12855,
  "JobModifyIndex": 12855,
  "Warnings": "",
  "Index": 12855,
  "LastContact": 0,
  "KnownLeader": false
}

5. Check synchronization

# 目标端 DBLE 中查看到 GTID 位点之后的数据已同步过来
mysql> select * from test;
+-----+--------+------+---------------------+
| id  | name   | city | dt                  |
+-----+--------+------+---------------------+
|   1 | ren    | sh   | 2022-03-07 06:53:30 |
|  48 | mike4  | sh   | 2022-03-07 08:04:09 |
·········
|  88 | sync01 | wh   | 2022-03-07 08:24:20 |
|  99 | sync02 | wh   | 2022-03-07 08:24:31 |
+-----+--------+------+---------------------+
11 rows in set (0.06 sec)

6. Other DML and DDL synchronization

Verify the synchronization of other update, delete statements and DDL statements

# 源端 MySQL 执行操作
mysql> delete from test where id >= 100;
Query OK, 2 rows affected (0.01 sec)
mysql> delete from test where id > 3;
Query OK, 6 rows affected (0.01 sec)
mysql> update test set name = 'actionsky' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 目标端 DBLE 检查同步情况
mysql> select * from test;
+----+-----------+------+---------------------+
| id | name      | city | dt                  |
+----+-----------+------+---------------------+
|  1 | ren       | sh   | 2022-03-07 06:53:30 |
|  2 | jack      | bj   | 2022-03-07 06:53:41 |
|  3 | actionsky | sz   | 2022-03-07 06:53:59 |
+----+-----------+------+---------------------+
3 rows in set (0.01 sec)

# 源端 MySQL 执行 DDL操作
mysql> alter table test add column info varchar(20) default 'hello';
mysql> update test set info = 'thanks' where id = 3;
mysql> alter table test add index idx_info(`info`);

# 目标端 DBLE 可以进行DDL同步（篇幅所限，实际上DBLE兼容的DDL语句都能同步成功）
mysql> select * from test;
+----+-----------+------+---------------------+--------+
| id | name      | city | dt                  | info   |
+----+-----------+------+---------------------+--------+
|  1 | ren       | sh   | 2022-03-07 06:53:30 | hello  |
|  2 | jack      | bj   | 2022-03-07 06:53:41 | hello  |
|  3 | actionsky | sz   | 2022-03-07 06:53:59 | thanks |
+----+-----------+------+---------------------+--------+
3 rows in set (0.02 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `city` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `info` varchar(20) COLLATE utf8mb4_bin DEFAULT 'hello',
  PRIMARY KEY (`id`),
  KEY `idx_ctiy` (`city`),
  KEY `idx_info` (`info`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

Configuring MySQL-To-DBLE Considerations

1. Detect the connection failure problem

Description of the problem: During the DTLE configuration of the JOB, the "connection detection" failed, and the nomad log reported an error of ERROR 1064 (HY000): java.sql.SQLSyntaxErrorException: illegal value[TRUE]
Reason: The detection client statement set autocommit=true issued by DTLE is not supported in some versions of DBLE
Solution: Upgrade DBLE to version 3.20.10.6 and later

2. After the task is started, the synchronization fails and reports that 'dtle' does not exist

Problem description: An error is reported after the DTLE synchronization task is started, and the nomad log shows Can't create database 'dtle' that doesn't exists.
reason:
- DTLE To MySQL , there will be no such error
- DTLE To DBLE, since the schema creation method in DBLE middleware is inconsistent with ordinary MySQL, the create syntax is not supported
solve:
- Additional Schema/Table configuration is required for DBLE, refer to the related configuration in sharding.xml and user.xml above

3. After the task is started, the synchronization fails and reports 'Data too long'

Problem description: An error is reported after the DTLE synchronization task is started, and the nomad log shows "applier error/restart: insert gno: Error 1406: Data too long for column 'source_uuid' at row 1"
reason:
- In the table gtid_executed_v4 created by DTLE in DBLE, the Binary data type of the field source_uuid is not long enough
- You can also check the DBLE middleware log (core/log/dble.log), and the error message is "execute sql err : errNo:1406 Data too long for column 'source_uuid' at row 1"
solve:
- In DBLE, modify fields
- alter table gtid_executed_v4 modify column source_uuid binary(60);

in conclusion

The current function of DTLE can basically meet the data synchronization requirements between MySQL -> DBLE, but it should be noted that the full synchronization method mentioned in this article is not recommended.
- Production environment implementation Due to the large amount of data in the old MySQL database, you can first logically back up the full amount of data (GTID sites need to be recorded), then split and import through the split tool that comes with DBLE, and then use DTLE based on GTID Data synchronization is performed by means of incremental synchronization of
Before DTLE creates a To-DBLE task, you need to pay attention to the precautions shown above, especially prepare the sharding.xml and user.xml files in advance and create the DTLE metadata table gtid_executed_v4.

Data Transfer | Using DTLE to synchronize MySQL data to DBLE