Data transfer | How to use DTLE to synchronize DDL with pt-osc

Author: Liu An

A member of the test team of Aikesen, mainly responsible for the related testing tasks of DTLE open source projects, and good at Python automated test development.

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.

---

How to use DTLE to synchronize DDL with pt-osc

Background: Some students in the community group asked, the source library uses pt-osc to make table DDL changes, does DTLE support it?

1. The principle of pt-osc

1). Create an empty table with the same structure as the original table, the table name is _原表名_new
2). Modify the table structure of the empty table created in step 1
3). Add three triggers on the original table: delete/update/insert, which are used to synchronize the data changes in the original table to the _原表名_new table during the process of copying data
4). Copy the original table data to the _原表名_new table in the form of data blocks
5). 原表 to _原表名_old table, and put _原表名_new table to rename 原表 , then remove _原表名_old surface
6). Delete trigger

2. DTLE support for DDL

According to the description in DTLE's documentation https://actiontech.github.io/dtle-docs-cn/3/3.6_DDL.html :

1). DTLE support create/altert/drop table statement
2). Although DTLE does not support DDL related to synchronous triggers, the data generated by the triggers can be synchronized to the target.
3). DTLE support rename statement

It seems that DTLE should support pt-osc to make table DDL changes.

3. Operation steps

1). Deploy the DTLE cluster, here is the dtle-ce-4.22.01.0 version

2). Prepare some data at the source MySQL

 mysql> CREATE DATABASE action_db;

shell> sysbench /usr/share/sysbench/oltp_common.lua --mysql-host=172.100.9.1 --mysql-port=3306 --mysql-user=test --mysql-password=test --create_secondary=off --mysql-db=action_db --tables=1 --table_size=100000 prepare

3). Create a DTLE task

Note that both the _原表名_old table and _原表名_new need to be added to the synchronization scope of the DTLE task

 job "test_pt_osc" {
  datacenters = ["dc1"]

  group "Src" {
    task "src" {
      driver = "dtle"
      config {
        ReplicateDoDb = [{
          TableSchema = "action_db"
          Tables = [{
            TableName = "sbtest1"
          },
          {
              TableName = "_sbtest1_new"
          },
          {
              TableName = "_sbtest1_old"
          }]
        }]
        ConnectionConfig = {
          Host = "172.100.9.1"
          Port = 3306
          User = "test_src"
          Password = "test_src"
        }
      }
    }
  }
  group "Dest" {
    task "dest" {
      driver = "dtle"
      config {
        ConnectionConfig = {
          Host = "172.100.9.2"
          Port = 3306
          User = "test_dest"
          Password = "test_dest"
        }
      }
    }
  }
}

4). View the table structure of the databases at both ends

5). The source MySQL continues to add data

 shell> sysbench /usr/share/sysbench/oltp_write_only.lua --mysql-host=172.100.9.1 --mysql-port=3306 --mysql-user=test --mysql-password=test --report-interval=3 --mysql-db=action_db --tables=1 --table_size=100000 --time=10 --rate=100 run

6). Execute the pt-osc command while the source has data traffic

 shell> pt-online-schema-change --print --statistics --progress time,30 --user=test --password=test --alter 'modify c varchar(200) not null default ""' --chunk-size=10000 --nocheck-replication-filters --host=172.100.9.1 --port=3306 D=action_db,t=sbtest1 --execute

During the execution of the pt-ost command, there will be an error when trying to connect to the source DTLE. This is because DTLE pretends to be MySQL to get the binlog from the instance, and pt-ost checks the delay between master and slave. This error will not affect the execution of pt-osc.

7). Check that DDL is properly synchronized and data consistency

4. Summary

1). Using the pt-osc tool to make table DDL changes to DTLE is supported

2). Although the table to be synchronized is specified in this example, in fact, directly creating a Database level task can achieve the same effect

3). It is necessary to plan the table to be synchronized when creating a DTLE task, and then create another task to synchronize later _old table and _new table will cause data inconsistency

---

If you find any problems when using DTLE, please contact us in time.

DTLE repo: https://github.com/actiontech/dtle

DTLE docs: https://actiontech.github.io/dtle-docs-cn/

QQ discussion group: 852990221