Using pt-osc for Table DDL Changes with DTLE: Procedure and Support

Background : In a community discussion a user asked whether DTLE supports table DDL changes performed with the pt‑osc tool.

1. pt‑osc Principle

pt‑osc works by creating a temporary table with the same structure as the original, e.g., _originalTable_new , modifying its schema, adding triggers on the original table to copy changes, copying data in chunks, and finally renaming tables:

Create empty table _originalTable_new

Alter the new table's structure

Add delete/update/insert triggers on the original table to sync changes to _originalTable_new

Copy data from the original table to _originalTable_new

Rename the original table to _originalTable_old and rename _originalTable_new to the original name

Drop the triggers

2. DTLE Support for DDL

According to the DTLE documentation, DTLE supports the following DDL statements:

create/alter/drop table

Rename statements via rename

DTLE does not synchronize trigger‑related DDL, but data generated by triggers can still be replicated to the target.

3. Operation Steps

1) Deploy a DTLE cluster (using dtle‑ce‑4.22.01.0).

2) Prepare source MySQL data:

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 job:

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) Verify table structures on both ends (screenshots omitted).

5) Continuously insert data on the source:

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) While data is flowing, execute the pt‑online‑schema‑change command:

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 execution, DTLE may report a connection error because it pretends to be a MySQL replica; this does not affect pt‑osc.

7) Check that the DDL was correctly synchronized and that data remains consistent (screenshots omitted).

4. Summary

DTLE supports using pt‑osc for table DDL changes.

A database‑level DTLE task can achieve the same effect without explicitly listing tables.

When creating a DTLE job, plan the tables to be synchronized in advance; adding _old and _new tables later may cause data inconsistency.

If you encounter issues with DTLE, contact the community. DTLE repository: https://github.com/actiontech/dtle . Documentation: https://actiontech.github.io/dtle-docs-cn/ . QQ group: 852990221.