# **练习-2 : 创建 `TiDB` 数据库到 `MySQL` 数据库的同步任务，并检查任务状态** ## 概述 + 在本练习中，将为源端(上游) TiDB 数据库到目标端(下游) MySQL 数据库创建同步任务，开启数据同步，并检查任务状态｡ ## 实验环境要求 + 1. 注意，您的实验环境（包括IP，端口号，用户名，密码和目录）可能和手册不同。 + 2. 可以连接到实验用虚拟机。 + 3. TiDB 数据库提前部署完毕(空)。 + 4. 清理掉之前的实验数据。 + 5. 已经部署好一套 MySQL 数据库｡ + 6. 需要完成练习-1。 ## 步骤 ----------------------------------------------- #### 1. 准备源端（上游）TiDB 数据库和目标端（下游）MySQL 数据库 + **为目标端（下游） MySQL 数据库( 端口号为 3306 )加入时区信息，如下:** ``` # mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -pmysql mysql ``` ``` mysql: [Warning] Using a password on the command line interface can be insecure. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it. ``` + **登录到目标端（下游） MySQL 数据库，创建 test 数据库和空表 T1，如下:** ``` # mysql -uroot -pmysql ``` ```sql mysql> drop database test; Query OK, 1 row affected (0.02 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table T1(id int primary key, name varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> select * from T1; Empty set (0.00 sec) ``` + **登录到源端（上游） TiDB 数据库，进入 test 数据库，创建空表 T1，如下:** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> use test; Database changed mysql> create table T1(id int primary key, name varchar(20)); Query OK, 0 rows affected (0.09 sec) mysql> select * from T1; Empty set (0.00 sec) ``` ----------------------------------------------- #### 2. 创建并开启 TiCDC 数据同步任务 + **使用 cdc cli 工具控制刚部署的 TiCDC 节点 172.31.0.135:8300，开启数据同步任务，如下:**： ``` # cd /tidb-deploy/cdc-8300/bin/ ``` ``` # ./cdc cli changefeed create --pd=http://172.31.0.232:2379 --sink-uri="mysql://root:mysql@172.31.0.135:3306/" --changefeed-id="replication-task-1" --sort-engine="unified" ``` ``` Create changefeed successfully! ID: replication-task-1 Info: {"upstream-id":0,"sink-uri":"mysql://root:mysql@172.31.0.135:3306/","opts":{},"create-time":"2022-06-26T13:15:43.353373862+08:00","start-ts":434168278109650945,"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":["*.*"],"ignore-txn-start-ts":null},"mounter":{"worker-num":16},"sink":{"dispatchers":null,"protocol":"","column-selectors":null,"schema-registry":""},"cyclic-replication":{"enable":false,"replica-id":0,"filter-replica-ids":null,"id-buckets":0,"sync-ddl":false},"consistent":{"level":"none","max-log-size":64,"flush-interval":1000,"storage":""}},"state":"normal","error":null,"sync-point-enabled":false,"sync-point-interval":600000000000,"creator-version":"v6.1.0"} ``` 参数解释如下: --pd=http://172.31.0.232:2379 :为任意一个 PD 节点｡ --sink-uri="mysql://root:mysql@172.31.0.135:3306/" : 目标库(下游) MySQL 数据库的 IP 地址为:172.31.0.135，端口号为:3306｡ --changefeed-id="replication-task-1" --sort-engine="unified" :开启的数据同步任务 ID 是 replication-task-1，不定义引擎｡ + **对于刚创建的同步任务进行查询，如下:**： ``` # ./cdc cli changefeed list --pd=http://172.31.0.232:2379 ``` ``` [ { "id": "replication-task-1", "summary": { "state": "normal", "tso": 434168409456902146, "checkpoint": "2022-06-26 13:24:04.376", "error": null } } ] ``` 注意: "state": "normal" : 表示任务状态正常｡ "tso": 434168409456902146 : 表示同步任务的时间戳信息｡ "checkpoint": "2022-06-26 13:24:04.376" :表示上一次同步任务的时间｡ + **详细查询复制任务信息，如下:** ``` # ./cdc cli changefeed query --pd=http://172.31.0.232:2379 --changefeed-id=replication-task-1 ``` ----------------------------------------------- #### 3.对同步任务进行验证 + **登录源端（上游） TiDB 数据库，查询刚刚创建的 test 数据库下面的表 T1，并且插入一行数据，如下:** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select * from test.T1; Empty set (0.00 sec) mysql> insert into test.T1 values(1,'Tom'); Query OK, 1 row affected (0.00 sec) mysql> select * from test.T1; +----+------+ | id | name | +----+------+ | 1 | Tom | +----+------+ 1 row in set (0.00 sec) mysql> exit Bye ``` + **登录目标端（下游） MySQL 数据库，查询刚刚创建的 test 数据库下面的表 T1，发现数据库已经同步过去，如下:** ``` # mysql -u root -pmysql ``` ```sql mysql> select * from test.T1; +----+------+ | id | name | +----+------+ | 1 | Tom | +----+------+ 1 row in set (0.00 sec) mysql> exit Bye ``` + **从练习的结果，我们看到源端（上游）TiDB 数据库和目标端（下游）MySQL 数据库的数据复制已经通过 TiCDC 建立起来了。** ----------------------------------------------- ## 输出样例 ----------------------------------------------- #### 步骤 2-详细查询复制任务信息输出参考： ``` # ./cdc cli changefeed query --pd=http://172.31.0.232:2379 --changefeed-id=replication-task-1 ``` ``` { "info": { "upstream-id": 0, "sink-uri": "mysql://root:mysql@172.31.0.135:3306/", "opts": {}, "create-time": "2022-06-26T13:15:43.353373862+08:00", "start-ts": 434168278109650945, "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": [ "*.*" ], "ignore-txn-start-ts": null }, "mounter": { "worker-num": 16 }, "sink": { "dispatchers": null, "protocol": "", "column-selectors": null, "schema-registry": "" }, "cyclic-replication": { "enable": false, "replica-id": 0, "filter-replica-ids": null, "id-buckets": 0, "sync-ddl": false }, "consistent": { "level": "none", "max-log-size": 64, "flush-interval": 1000, "storage": "" } }, "state": "normal", "error": null, "sync-point-enabled": false, "sync-point-interval": 600000000000, "creator-version": "v6.1.0" }, "status": { "resolved-ts": 434168458268114945, "checkpoint-ts": 434168458006233089, "admin-job-type": 0 }, "count": 0, "task-status": [] } ``` -----------------------------------------------