[SOP Series 30] TiCDC Migration - TiDB to MySQL Testing

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【SOP 系列 30】TiCDC迁移-TiDB到MySQL测试

| username: 18515065291

1. Introduction

Due to recent business needs for TiDB to MySQL migration, it is necessary to test the migration process from TiDB to MySQL. This time, dumpling is used for export and TiCDC for initial data and real-time synchronization migration.

2. Information

TiDB Cluster Version: 5.2.2

Source TiDB Cluster: 666-1

Target MySQL Cluster: 888-1

Export Tool: dumpling

Import Tool: Execute SQL file

Real-time Synchronization: TiCDC

MySQL Main Instance IP: 10.5.5.5

TiDB Control Machine: 10.4.4.4

3. Deploy TiCDC

Cluster 666-1

[Generate TiCDC Expansion Configuration]:

vim add.yml
cdc_servers:
- host: 10.1.1.1
  port: 777
  deploy_dir: /opt/tidb666/deploy/cdc-777
  data_dir: /opt/tidb666/data/cdc-777
  log_dir: /opt/tidb666/log/cdc-777
- host: 10.1.1.2
  port: 777
  deploy_dir: /opt/tidb666/deploy/cdc-777
  data_dir: /opt/tidb666/data/cdc-777
  log_dir: /opt/tidb666/log/cdc-777
- host: 10.1.1.3
  port: 777
  deploy_dir: /opt/tidb666/deploy/cdc-777
  data_dir: /opt/tidb666/data/cdc-777
  log_dir: /opt/tidb666/log/cdc-777

[Execute Deployment]:

tiup cluster scale-out 666_TEST add.yml

[View Topology]:

qtidb -c 666-1

4. Export Data

cd tidb-toolkit-v5.2.2-linux-amd64/bin
./dumpling -udba -pxxx -h10.x.x.x -P666 --status-addr 999 -F 64MiB -t 2 -o 666_dump -B dba_test >> 666_dump_log

[View Backup Files]:

[tidb() bin]$ ll 666_dump/
total 24
-rw-rw-r-- 1 tidb tidb 146 Jul 24 11:34 metadata
-rw-rw-r-- 1 tidb tidb 109 Jul 24 11:34 dba_test-schema-create.sql
-rw-rw-r-- 1 tidb tidb 112 Jul 24 11:34 dba_test.test.000000000.sql
-rw-rw-r-- 1 tidb tidb  66 Jul 24 11:34 dba_test.test2.000000000.sql
-rw-rw-r-- 1 tidb tidb 266 Jul 24 11:34 dba_test.test2-schema.sql
-rw-rw-r-- 1 tidb tidb 265 Jul 24 11:34 dba_test.test-schema.sql

[View Backup Point]:

cat metadata 
Started dump at: 2022-07-24 11:34:31
SHOW MASTER STATUS:
        Log: tidb-binlog
        Pos: 434800865229668357
        GTID:

Finished dump at: 2022-07-24 11:34:31

[Simulate New Data Insertion in TiDB666]:

(dba:666)@[(none)]>use dba_test
Database changed
(dba:666)@[dba_test]>show tables;
+------------------------------+
| Tables_in_dba_test           |
+------------------------------+
| test                         |
| test2                        |
+------------------------------+
2 rows in set (0.00 sec)

(dba:666)@[dba_test]>select * from test;
+----+------+
| id | name |
+----+------+
|  1  | aa   |
| 33  | ccc  |
| 44 | ddd  |
| 55  | eee  |
| 66 | ff   |
+----+------+
5 rows in set (0.00 sec)

(dba:666)@[dba_test]>insert into test values (77,'gg');
Query OK, 1 row affected (0.01 sec)

(dba:666)@[dba_test]>insert into test values (88,'re');
Query OK, 1 row affected (0.00 sec)

(dba:666)@[dba_test]>select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
| 33  | ccc  |
| 44 | ddd  |
| 55  | eee  |
| 66 | ff   |
| 77  | gg   |
| 88 | re   |
+----+------+
7 rows in set (0.00 sec)

5. Import Data into MySQL

5.1. Copy the Exported Backup to the MySQL Main Instance Machine

scp -r 666_dump 10.5.5.5:/data/

5.2. Execute Import

ssh main instance machine
login to mysql
(root@(none))>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sys                |
| performance_schema |
+--------------------+
9 rows in set (0.00 sec)

(root@(none))>source /data/666_dump/dba_test-schema-create.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

(root@(none))>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| dba_test           |
+--------------------+
10 rows in set (0.00 sec)

(root@(none))>use dba_test
Database changed
(root@dba_test)>source /data/666_dump/dba_test.test2-schema.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

(root@dba_test)>source /data/666_dump/dba_test.test-schema.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

(root@dba_test)>show tables;
+------------------------------+
| Tables_in_dba_test |
+------------------------------+
| test                         |
| test2                        |
+------------------------------+
2 rows in set (0.00 sec)

(root@dba_test)>select * from test;
Empty set (0.00 sec)

(root@dba_test)>select * from test2;
Empty set (0.00 sec)

(root@dba_test)>source /data/666_dump/dba_test.test.000000000.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(root@dba_test)>source /data/666_dump/dba_test.test2.000000000.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

(root@dba_test)>select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
| 33 | ccc  |
| 44 | ddd  |
| 55 | eee  |
| 66 | ff   |
+----+------+
5 rows in set (0.00 sec)

(root@dba_test)>select * from test2;
+----+------+
| id | name |
+----+------+
|  2 | bb   |
+----+------+
1 row in set (0.00 sec)

6. Real-time Synchronization

6.1. View TiCDC


ssh 10.4.4.4
[root()@name-2-2 ~]# su - tidb
Last login: Sun Jul 24 11:28:38 CST 2022 on pts/0
[tidb()@name-2-2 ~]$ tiup ctl:v5.2.2 cdc capture list --pd=http://10.3.3.3:678
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc capture list --pd=http://10.3.3.3:678
[
  {
    "id": "42492be0-dd2b-49da-9562-86ba5feff288",
    "is-owner": false,
    "address": "10.1.1.1:567"
  },
  {
    "id": "5543f93e-e0c8-4e91-a468-88362454b958",
    "is-owner": false,
    "address": "10.1.1.2:567"
  },
  {
    "id": "6688a5c7-0779-487e-86f6-46b068743652",
    "is-owner": true,
    "address": "10.1.1.3:567"
  }
]

6.2. Create Synchronization Task:
[Configuration File]:


[tidb()@name-2-2 666_ticdc]$ cd /data/tidb/666_ticdc
[tidb()@name-2-2 666_ticdc]$ cat 666_888_ticdc_config.toml
case-sensitive = true
enable-old-value = true
[filter]
rules = ['dba_test.*']

[mounter]
worker-num = 8

[Create Synchronization Task]:


[tidb()@name-2-2 666_ticdc]$ tiup ctl:v5.2.2 cdc changefeed create --pd=http://10.3.3.3:678 --sink-uri="mysql://dba:xxx@10.5.5.5:888/?worker-count=16&max-txn-row=5000&time-zone=SYSTEM" --changefeed-id="666-888-20220724-task" --sort-engine="unified" --start-ts=434800865229668357 --config 666_888_ticdc_config.toml
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc changefeed create --pd=http://10.3.3.3:678 --sink-uri=mysql://dba:xxx@10.5.5.5:888/?worker-count=16\u0026max-txn-row=5000\u0026time-zone=SYSTEM --changefeed-id=666-888-20220724-task --sort-engine=unified --start-ts=434800865229668357 --config 666_888_ticdc_config.toml
Create changefeed successfully!
ID: 666-888-20220724-task
Info: {"sink-uri":"mysql://dba:xxx@10.5.5.5:888/?worker-count=16\u0026max-txn-row=5000\u0026time-zone=SYSTEM","opts":{"_changefeed_id":"cli-verify"},"create-time":"2022-07-24T12:20:45.606052447+08:00","start-ts":434800865229668357,"target-ts":0,"admin-job-type":0,"sort-engine":"unified","sort-dir":"","config":{"case-sensitive":true,"enable-old-value":true,"force-replicate":false,"check-gc-safe-point":true,"filter":{"rules":["dba_test.*"],"ignore-txn-start-ts":null},"mounter":{"worker-num":8},"sink":{"dispatchers":null,"protocol":"default"},"cyclic-replication":{"enable":false,"replica-id":0,"filter-replica-ids":null,"id-buckets":0,"sync-ddl":false},"scheduler":{"type":"table-number","polling-time":-1}},"state":"normal","history":null,"error":null,"sync-point-enabled":false,"sync-point-interval":600000000000,"creator-version":"v5.2.2"}

[View All Tasks]:


tiup ctl:v5.2.2 cdc changefeed list --pd=http://10.3.3.3:678 
[tidb()@name-2-2 666_ticdc]$ tiup ctl:v5.2.2 cdc changefeed list --pd=http://10.3.3.3:678
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc changefeed list --pd=http://10.3.3.3:678
[
  {
    "id": "666-888-20220724-task",
    "summary": {
      "state": "normal",
      "tso": 434801605865111553,
      "checkpoint": "2022-07-24 12:21:36.983",
      "error": null
    }
  }
]

[View Specific Task]:


[tidb()@name-2-2 666_ticdc]$ tiup ctl:v5.2.2 cdc changefeed query -s --pd=http://10.3.3.3:678 --changefeed-id=666-888-20220724-task
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc changefeed query -s --pd=http://10.3.3.3:678 --changefeed-id=666-888-20220724-task
{
 "state": "normal",
 "tso": 434801616101834753,
 "checkpoint": "2022-07-24 12:22:16.033",
 "error": null
}

[View Task Details]:

[tidb()@name-2-2 666_ticdc]$ tiup ctl:v5.2.2 cdc changefeed query --pd=http://10.3.3.3:678 --changefeed-id=666-888-20220724-task
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc changefeed query --pd=http://10.3.3.3:678 --changefeed-id=666-888-20220724-task
{
  "info": {
    "sink-uri": "mysql://dba:xxx@10.5.5.5:888/?worker-count=16\u0026max-txn-row=5000\u0026time-zone=SYSTEM",
    "opts": {
      "_changefeed_id": "cli-verify"
    },
    "create-time": "2022-07-24T12:20:45.606052447+08:00",
    "start-ts": 434800865229668357,
    "target-ts": 0,
    "admin-job-type": 0,
    "sort-engine": "unified",
    "sort-dir": "",
    "config": {
      "case-sensitive": true,
      "enable-old-value": true,
      "force-replicate": false,
      "check-gc-safe-point": true,
      "filter": {
        "rules": [
          "dba_test.*"
        ],
        "ignore-txn-start-ts": null
      },
      "mounter": {
        "worker-num": 8
      },
      "sink": {
        "dispatchers": null,
        "protocol": "default"
      },
      "cyclic-replication": {
        "enable": false,
        "replica-id": 0,
        "filter-replica-ids": null,
        "id-buckets": 0,
        "sync-ddl": false
      },
      "scheduler": {
        "type": "table-number",
        "polling-time": -1
      }
    },
    "state": "normal",
    "history": null,
    "error": null,
    "sync-point-enabled": false,
    "sync-point-interval": 600000000000,
    "creator-version": "v5.2.2"
  },
  "status": {
    "resolved-ts": 434801631581437953,
    "checkpoint-ts": 434801631581437953,
    "admin-job-type": 0
  },
  "count": 0,
  "task-status": [
    {
      "capture-id": "42492be0-dd2b-49da-9562-86ba5feff288",
      "status": {
        "tables": null,
        "operation": null,
        "admin-job-type": 0
      }
    },
    {
      "capture-id": "5543f93e-e0c8-4e91-a468-88362454b958",
      "status": {
        "tables": {
          "878": {
            "start-ts": 434800865229668357,
            "mark-table-id": 0
          }
        },
        "operation": {},
        "admin-job-type": 0
      }
    },
    {
      "capture-id": "6688a5c7-0779-487e-86f6-46b068743652",
      "status": {
        "tables": {
          "880": {
            "start-ts": 434800865229668357,
            "mark-table-id": 0
          }
        },
        "operation": {},
        "admin-job-type": 0
      }
    }
  ]
}

6.3. Verify Synchronization in MySQL

[View MySQL Data]:


(root@dba_test)>select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
| 33 | ccc  |
| 44 | ddd  |
| 55 | eee  |
| 66 | ff   |
| 77 | gg   |
| 88 | re   |
+----+------+
7 rows in set (0.00 sec)

[Simulate Insertion Again]:

[TiDB666-1]: Insert Data
(dba:666)@[dba_test]>insert into test values (99,'we');
Query OK, 1 row affected (0.00 sec)

(dba:666)@[dba_test]>
2
| username: tidb狂热爱好者 | Original post link

DDL can also have significant delays.