黄东旭解析 TiDB 的核心优势
548
2024-02-25
因最近有TiDB回迁MySQL的业务需求,需要测试TiDB 到MySQL的迁移过程,本次利用dumpling导出+TiCDC进行初始数据+实时同步迁移。
TiDB集群版本:5.2.2
来源TiDB集群:666-1
目标MySQL集群:888-1
导出工具:dumpling
导入工具:执行SQL文件
实时同步:TiCDC
MySQL主实例IP:10.5.5.5
TiDB中控机:10.4.4.4
集群666-1
【生成TiCDC扩容配置】:
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【执行部署】:
tiup cluster scale-out 666_TEST add.yml【查看拓扑】:
qtidb -c 666-1
【查看备份的文件】:
[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:31 SHOW MASTER STATUS: Log: tidb-binlog Pos: 434800865229668357 GTID: Finished dump at: 2022-07-24 11:34:31【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)6.1、查看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、创建同步任务:【配置文件】:
[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【创建同步任务】:
ow=5000&time-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: {"g":{"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: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 } } ]【查看指定的任务】:
[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 }【查看任务详细信息】:
[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 Star=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_test2 Database 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_test Database 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) 综上:说明库过滤ok6.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-task Starting 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-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 [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-task Usage: 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-task Error: exit status 1 Error: 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-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": "", "tso": 0, "checkpoint": "", "error": null }版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。