DM一直在同步,为什么tidb里没有数据?

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】v6.1.0
【复现路径】做过哪些操作出现的问题
【遇到的问题:问题现象及影响】
dm同步状态显示在同步,但tidb里没有任何数据表建立
【资源配置】
name: “test”

任务模式,可设为

full:只进行全量数据迁移

incremental: binlog 实时同步

all: 全量 + binlog 迁移

task-mode: “all”

下游 TiDB 配置信息。

target-database:
host: “192.168.106.92” # 例如:172.16.10.83
port: 4000
user: “root”
password: “*********” # 支持但不推荐使用明文密码,建议使用 dmctl encrypt 对明文密码进行加密后使用

当前数据迁移任务需要的全部上游 MySQL 实例配置。

mysql-instances:

上游实例或者复制组 ID。

source-id: “mysql-01”

需要迁移的库名或表名的黑白名单的配置项名称,用于引用全局的黑白名单配置,全局配置见下面的 block-allow-list 的配置。

block-allow-list: “listA”

黑白名单全局配置,各实例通过配置项名引用。

block-allow-list:
listA: # 名称
do-tables: # 需要迁移的上游表的白名单。
- db-name: “nadia." # 需要迁移的表的库名。
tbl-name: "
” # 需要迁移的表的名称。

【附件:截图/日志/监控】

检查下 DM 的日志

tbl-name: " ” # 需要迁移的表的名称。 这改成*

可是复制出错了,实际配置是
block-allow-list:
listA: # 名称
do-tables: # 需要迁移的上游表的白名单。
- db-name: “nadia” # 需要迁移的表的库名。
tbl-name: “*”

问题后面已经解决了是吗?

root@db93:/home/tidb/dm/deploy/dm-worker-8262/log# tail dm-worker_stderr.log -n 20
[mysql] 2022/12/23 07:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:35:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:45:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:55:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:05:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:15:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:35:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:45:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:55:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:05:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:15:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:47:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:57:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:37:18 packets.go:123: closing bad idle connection: EOF

我看了worker上,dumped_data.nadia里的目录,看到还没有全部的表,是不是表明还没有dump完成啊?

没有呢

老是会断开,这是什么原因呢?源库已设了86400的超时时间了
“subTaskStatus”: [
{
“name”: “nadia”,
“stage”: “Paused”,
“unit”: “Dump”,
“result”: {
“isCanceled”: false,
“errors”: [
{
“ErrCode”: 32001,
“ErrClass”: “dump-unit”,
“ErrScope”: “internal”,
“ErrLevel”: “high”,
“Message”: "mydumper/dumpling runs with error, with output (may empty): ",
“RawCause”: “sql: SHOW COLUMNS FROM nadia.goods_store: driver: bad connection”,
“Workaround”: “”
}
],
“detail”: null
},
“unresolvedDDLLockID”: “”,
“dump”: {
“totalTables”: “103”,
“completedTables”: 20,
“finishedBytes”: 19923930333,
“finishedRows”: 106142560,
“estimateTotalRows”: 227656254,
“bps”: “0”,
“progress”: “”
},
“validation”: null
}

下游 tidb 有别的连接吗?看下 show processlist 和 max_connections?

这是连接挂了 ,你想解决问题,得把所有的环境描述清楚才行了

上游是mysql5.7,在阿里云公网的。下游是tidb6.1.3,在私网环境,网络上端口均已开通,能连

worker上都是这样的日志:
[mysql] 2022/12/23 10:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:47:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:57:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:37:18 packets.go:123: closing bad idle connection: EOF

估计得抓包才能知道原因了

参考下:

参考1:http://www.zhangjiee.com/blog/2020/go-mysql-closing-bad-idle-connection.html

参考2:MySQL连接池与超时设定_ATpiu的博客-CSDN博客_3306: i/o timeout


估计还是环境问题导致的…

dm同步状态显示在同步,有同步成功的表或者数据库吗?

没有,还在dump,估计没有表也是因为在dump中,现在的问题是dump过程,一直会报
“Message”: "mydumper/dumpling runs with error, with output (may empty): ",
“RawCause”: “sql: SHOW COLUMNS FROM nadia.goods_store: driver: bad connection”,

看了worker日志,[mysql] 2022/12/23 11:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:47:18 packets.go:123: closing bad idle connection: EOF

这是数据库连接的问题了,连接失败。你需要检查一下“ 同步的配置文件”是否正确?迁移库和目标库是否可以正常连接,然后再执行命令。如果tidb是测试库,没有重要的数据的话,推荐空闲时间重启一下tidb集群。

1 Like

看这日志,都是10分钟断开的

好像是这个 issue https://github.com/pingcap/tidb/issues/36549 , 要不你试试新版本的 DM。以及你应该贴一下 worker 的正常日志,而不仅仅是 dm-worker_stderr.log