为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
tiDB5.0.4
【概述】 场景 + 问题概述
我在自己电脑的虚拟机上用tiDB-CDC想把tiDB的数据同步到mysql当中,目前新增库,新增表都已经能够同步,但是在表中插入数据却同步不了,我的sync-ddl已经设置为true.
【备份和数据迁移策略逻辑】
【背景】 做过哪些操作
tiup ctl:v5.0.4 cdc changefeed create --pd=http://hadoop112:2379 --sink-uri=“mysql://root:123456@hadoop112:3306/” --tz Asia/Shanghai --sort-engine=“unified” --config /opt/module/tidb/local/changefeed.toml
ID: 0dbdcde4-c513-41ed-b117-1b6f7a905cd6
Info: {“sink-uri”:“mysql://root:123456@hadoop112:3306/”,“opts”:{"_changefeed_id":“cli-verify”},“create-time”:“2021-10-15T09:31:53.218057046+08:00”,“start-ts”:428411850117349377,“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”:[".","!test."],“ignore-txn-start-ts”:[1,2]},“mounter”:{“worker-num”:16},“sink”:{“dispatchers”:[{“matcher”:["test1.",“test2."],“dispatcher”:“ts”},{“matcher”:["test3.”,“test4.*”],“dispatcher”:“rowid”}],“protocol”:“default”},“cyclic-replication”:{“enable”:false,“replica-id”:1,“filter-replica-ids”:[2,3],“id-buckets”:0,“sync-ddl”:true},“scheduler”:{“type”:“table-number”,“polling-time”:-1}},“state”:“normal”,“history”:null,“error”:null,“sync-point-enabled”:false,“sync-point-interval”:600000000000,“creator-version”:“v5.0.4”}
mysql> CREATE TABLE mx_jzy_device_data
(
-> id
bigint(20) NOT NULL AUTO_INCREMENT,
-> ent_plt_user_id
bigint(20) NOT NULL COMMENT ‘核心企业平台用户id’,
-> device_mac_flag
varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘设备标识’,
-> product_key
varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘产品唯一Key’,
-> product_name
varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘产品名称’,
-> device_status
int(11) DEFAULT NULL COMMENT ‘设备状态:0:下线,1:上线’,
-> data_statistics_time
varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘数据统计时间’,
-> data_statistics_hour
int(11) DEFAULT NULL COMMENT ‘数据统计按照小时,取值范围:0-23,默认-1’,
-> data_statistics_value
int(11) DEFAULT NULL COMMENT ‘统计值’,
-> data_statistics_type
varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘数据统计类型:取值:status:设备状态,day:天,hour:小时’,
-> add_record_time
bigint(20) DEFAULT NULL COMMENT ‘数据入库创建时间’,
-> event_send_time
bigint(20) DEFAULT NULL COMMENT ‘事件发生时间’,
-> uniqueness_key
varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘保证数据唯一性(幂等处理)’,
-> PRIMARY KEY (id
),
-> UNIQUE KEY uniqueness_key_index
(uniqueness_key
)
-> ) ENGINE=InnoDB AUTO_INCREMENT=30035 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.09 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO mx_jzy_device_data
VALUES (30001, ‘7’, ‘861193046118106’, ‘3472e0d07f2d4435a197e202c9988199’, ‘玻璃厂切割机’, 0, ‘20211006’, -1, 0, ‘status’, 1633948536036, 1633391979, ‘7456b36a25b44c606536dd56202983d0’);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO mx_jzy_device_data
VALUES (30001, ‘7’, ‘861193046118106’, ‘3472e0d07f2d4435a197e202c9988199’, ‘玻璃厂切割机’, 0, ‘20211006’, -1, 0, ‘status’, 1633948536036, 1633391979, ‘7456b36a25b44c606536dd56202983d0’);
ERROR 1062 (23000): Duplicate entry ‘30001’ for key ‘PRIMARY’
mysql> INSERT INTO mx_jzy_device_data
VALUES (30002, ‘7’, ‘861193046114212’, ‘3472e0d07f2d4435a197e202c9988199’, ‘玻璃厂切割机’, 0, ‘20211006’, -1, 0, ‘status’, 1633948536036, 1633311173, ‘0d42b6940aec8b80465ea6b4f8be0266’);
aVALUES (30003, '7', '863293057714724', '5acaa1f064974c20bef2fcbd8fecd6ac', '玻璃厂智能电表', 1, '20211005', 15, 2025, 'hour', 1633948536036, 1633503634, '0c1135a7305f08fe0112c79bfbe7a07e'); INSERT INTO
mx_jzy_device_data` VALUES (30004, ‘7’, '86Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mx_jzy_device_data
VALUES (30003, ‘7’, ‘863293057714724’, ‘5acaa1f064974c20bef2fcbd8fecd6ac’, ‘玻璃厂智能电表’, 1, ‘20211005’, 15, 2025, ‘hour’, 1633948536036, 1633503634, ‘0c1135a7305f08fe0112c79bfbe7a07e’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mx_jzy_device_data
VALUES (30004, ‘7’, ‘861193046114212’, ‘3472e0d07f2d4435a197e202c9988199’, ‘玻璃厂切割机’, 0, ‘20211003’, -1, 0, ‘status’, 1633948536525, 1633311303, ‘c1233180160c3201a4358b8ca21b552d’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mx_jzy_device_data;
±------±----------------±----------------±---------------------------------±----------------------±--------------±---------------------±---------------------±----------------------±---------------------±----------------±----------------±---------------------------------+
| id | ent_plt_user_id | device_mac_flag | product_key | product_name | device_status | data_statistics_time | data_statistics_hour | data_statistics_value | data_statistics_type | add_record_time | event_send_time | uniqueness_key |
±------±----------------±----------------±---------------------------------±----------------------±--------------±---------------------±---------------------±----------------------±---------------------±----------------±----------------±---------------------------------+
| 30001 | 7 | 861193046118106 | 3472e0d07f2d4435a197e202c9988199 | 玻璃厂切割机 | 0 | 20211006 | -1 | 0 | status | 1633948536036 | 1633391979 | 7456b36a25b44c606536dd56202983d0 |
| 30002 | 7 | 861193046114212 | 3472e0d07f2d4435a197e202c9988199 | 玻璃厂切割机 | 0 | 20211006 | -1 | 0 | status | 1633948536036 | 1633311173 | 0d42b6940aec8b80465ea6b4f8be0266 |
| 30003 | 7 | 863293057714724 | 5acaa1f064974c20bef2fcbd8fecd6ac | 玻璃厂智能电表 | 1 | 20211005 | 15 | 2025 | hour | 1633948536036 | 1633503634 | 0c1135a7305f08fe0112c79bfbe7a07e |
| 30004 | 7 | 861193046114212 | 3472e0d07f2d4435a197e202c9988199 | 玻璃厂切割机 | 0 | 20211003 | -1 | 0 | status | 1633948536525 | 1633311303 | c1233180160c3201a4358b8ca21b552d |
±------±----------------±----------------±---------------------------------±----------------------±--------------±------------
【现象】 业务和数据库现象
【问题】 当前遇到的问题
【业务影响】
【TiDB 版本】
【附件】
- 相关日志、配置文件、Grafana 监控(https://metricstool.pingcap.com/)
- TiUP Cluster Display 信息
- TiUP CLuster Edit config 信息
- TiDB-Overview 监控
- 对应模块的 Grafana 监控(如有 BR、TiDB-binlog、TiCDC 等)
- 对应模块日志(包含问题前后 1 小时日志)
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。