写点什么

TiCDC 迁移 -TiDB 到 MySQL 测试

  • 2022 年 7 月 29 日
  • 本文字数:7590 字

    阅读完需:约 25 分钟

作者: 18515065291 原文来源:https://tidb.net/blog/1bd64e9e

1、前言

因最近有 TiDB 回迁 MySQL 的业务需求,需要测试 TiDB 到 MySQL 的迁移过程,本次利用 dumpling 导出 +TiCDC 进行初始数据 + 实时同步迁移。

2、信息

TiDB 集群版本:5.2.2


来源 TiDB 集群:666-1


目标 MySQL 集群:888-1


导出工具:dumpling


导入工具:执行 SQL 文件


实时同步:TiCDC


MySQL 主实例 IP:10.5.5.5


TiDB 中控机:10.4.4.4

3、部署 TiCDC

集群 666-1


【生成 TiCDC 扩容配置】:


vim add.ymlcdc_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
复制代码


【执行部署】:


tiup cluster scale-out 666_TEST add.yml
复制代码


【查看拓扑】:


qtidb -c 666-1

4、导出数据

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
复制代码


【查看备份的文件】:


[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
复制代码


【查看备份的点位】:


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


【TiDB666 模拟新写入数据】:


(dba:666)@[(none)]>use dba_testDatabase 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、MySQL 导入数据

5.1、拷贝导出的备份到 MySQL 主实例的机器

scp -r 666_dump 10.5.5.5:/data/
复制代码

5.2、执行导入

ssh 主实例机器登录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.sqlQuery 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_testDatabase changed(root@dba_test)>source /data/666_dump/dba_test.test2-schema.sqlQuery 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.sqlQuery 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.sqlQuery 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.sqlQuery 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、实时同步

6.1、查看 TiCDC



ssh 10.4.4.4[root()@name-2-2 ~]# su - tidbLast 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:678Starting 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、创建同步任务: 【配置文件】:



[tidb()@name-2-2 666_ticdc]$ cd /data/tidb/666_ticdc[tidb()@name-2-2 666_ticdc]$ cat 666_888_ticdc_config.tomlcase-sensitive = trueenable-old-value = true[filter]rules = ['dba_test.*']
[mounter]worker-num = 8
复制代码


【创建同步任务】:



[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.tomlStarting 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&max-txn-row=5000&time-zone=SYSTEM --changefeed-id=666-888-20220724-task --sort-engine=unified --start-ts=434800865229668357 --config 666_888_ticdc_config.tomlCreate changefeed successfully!ID: 666-888-20220724-taskInfo: {"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"}
复制代码


【查看所有任务】:



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:678Starting 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 } }]
复制代码


【查看指定的任务】:



[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-taskStarting 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}
复制代码


【查看任务详细信息】:


[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-taskStarting 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、MySQL 校验同步情况


【查看 MySQL 数据】:



(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)
复制代码


【再次模拟写入】:


【TiDB666-1】:写入数据(dba:666)@[dba_test]>insert into test values (99,'we');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 || 99 | we |+----+------+8 rows in set (0.00 sec)
【MySQL888-1】:查看数据(root@dba_test)>select * from test;+----+------+| id | name |+----+------+| 1 | aa || 33 | ccc || 44 | ddd || 55 | eee || 66 | ff || 77 | gg || 88 | re || 99 | we |+----+------+8 rows in set (0.00 sec)
综上:同步正常
复制代码


【测试过滤是否生效】:



【TiDB666-1】:dba_test2 库写入数据(dba:666)@[dba_test]>use dba_test2Database changed(dba:666)@[dba_test2]>show tables;+-------------------------+| Tables_in_dba_test2 |+-------------------------+| tb_test |+-------------------------+1 row in set (0.00 sec)
(dba:666)@[dba_test2]>select * from tb_test;+----+------+------------+| id | age | statDate |+----+------+------------+| 1 | 2 | 2021-12-22 || 2 | 2 | 2021-12-22 |+----+------+------------+2 rows in set (0.00 sec)
(dba:666)@[dba_test2]>insert into tb_test values (3,1,'2022-07-24');Query OK, 1 row affected (0.00 sec)
(dba:666)@[dba_test2]>select * from tb_test;+----+------+------------+| id | age | statDate |+----+------+------------+| 1 | 2 | 2021-12-22 || 2 | 2 | 2021-12-22 || 3 | 1 | 2022-07-24 |+----+------+------------+3 rows in set (0.00 sec)
(dba:666)@[dba_test2]>use dba_testDatabase changed(dba:666)@[dba_test]>insert into test values (100,'ee');Query OK, 1 row affected (0.00 sec)
【MySQL888-1】:查看同步(root@dba_test)>select * from test;+-----+------+| id | name |+-----+------+| 1 | aa || 33 | ccc || 44 | ddd || 55 | eee || 66 | ff || 77 | gg || 88 | re || 99 | we || 100 | ee |+-----+------+9 rows in set (0.00 sec)
综上:说明库过滤ok
复制代码


6.4、关闭任务


【停止任务】:


[tidb()@name-2-2 666_ticdc]$ tiup ctl:v5.2.2 cdc changefeed remove --pd=http://10.3.3.3:678 --changefeed-id 666-888-20220724-taskStarting component `ctl`: /home/tidb/.tiup/components/ctl/v5.2.2/ctl cdc changefeed remove --pd=http://10.3.3.3:678 --changefeed-id 666-888-20220724-task
复制代码


【查看任务】:



[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-taskStarting 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[2022/07/24 12:29:30.408 +08:00] [WARN] [cli_changefeed_query.go:100] ["This changefeed has been deleted, the residual meta data will be completely deleted within 24 hours."] [changgefeed=666-888-20220724-task][2022/07/24 12:29:30.409 +08:00] [ERROR] [cli_changefeed_query.go:109] ["This changefeed does not exist"] [changefeed=666-888-20220724-task]Error: [CDC:ErrChangeFeedNotExists]changefeed not exists, key: /tidb/cdc/job/666-888-20220724-taskUsage: cdc cli changefeed query [flags]
Flags: -c, --changefeed-id string Replication task (changefeed) ID -h, --help help for query -s, --simple Output simplified replication status
Global Flags: --ca string CA certificate path for TLS connection --cert string Certificate path for TLS connection -i, --interact Run cdc cli with readline --key string Private key path for TLS connection --log-level string log level (etc: debug|info|warn|error) (default "warn") --pd string PD address, use ',' to separate multiple PDs (default "http://127.0.0.1:2379")
[CDC:ErrChangeFeedNotExists]changefeed not exists, key: /tidb/cdc/job/666-888-20220724-taskError: exit status 1Error: run `/home/tidb/.tiup/components/ctl/v5.2.2/ctl` (wd:/home/tidb/.tiup/data/TCTXh8X) failed: exit status 1
复制代码


【查看任务详细】:


[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-taskStarting 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": "", "tso": 0, "checkpoint": "", "error": null}
复制代码


发布于: 3 小时前阅读数: 15
用户头像

TiDB 社区官网:https://tidb.net/ 2021.12.15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
TiCDC迁移-TiDB到MySQL测试_管理与运维_TiDB 社区干货传送门_InfoQ写作社区