TiDB 5.2.1 tidb-lightning导入MySQL8导出的*.sql文件时的有关问题，日志无报错，数据未导入。

[zuoxh (Hacker T3brlp4 R)]

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
12台TiDB集群

【概述】 场景 + 问题概述
MySQL导出文件*.sql，900+w记录，使用tidb-lightning导入时无报错，但是数据未导入。
配置文件如下：
$ vi tidb-lightning.toml

[lightning]

level = “info”

file = “tidb-lightning.log”

[tikv-importer]

backend 设置为 local 模式

backend = “local”

设置本地临时存储路径

sorted-kv-dir = “/home/tidb/data/sorted-kv-dir”

[mydumper]

Mydumper 源数据目录。

data-source-dir = “/home/tidb/data/my_database”

no-schema=false

read-block-size = 65536 # Byte (默认为 64 KB)

[tidb]

目标集群的信息。tidb-server 的监听地址，填一个即可。

host = “************”

port = 4000

user = “root”

password = “****************”

表架构信息在从 TiDB 的“状态端口”获取。

status-port = 10080

pd-server 的地址，填一个即可

pd-addr = “******************:2379”

[checkpoint]

enable = false

存储断点的数据库名称。

schema = “tidb_lightning_checkpoint”

driver = “/home/tidb/Tools/tidb-toolkit-v5.2.1-linux-amd64/bin/tidb_lightning_checkpoint.pb”

【备份和数据迁移策略逻辑】

【背景】 做过哪些操作

【现象】 业务和数据库现象

[2021/11/08 16:40:10.533 +08:00] [WARN] [config.go:529] [“currently only per-task configuration can be applied, global configuration changes can only be made on startup”] [“global conf
ig changes”="[lightning.level,lightning.file]"]
[2021/11/08 16:40:10.537 +08:00] [INFO] [config.go:710] [“adjust scan concurrency success”] [DistSQLScanConcurrency=15]
[2021/11/08 16:40:10.537 +08:00] [INFO] [info.go:49] [“Welcome to TiDB-Lightning”] [release-version=v5.2.1] [git-hash=cd8fb24c5f7ebd9d479ed228bb41848bd5e97445] [git-branch=heads/refs/t
ags/v5.2.1] [go-version=go1.16.4] [utc-build-time=“2021-09-07 16:19:29”] [race-enabled=false]
[2021/11/08 16:40:10.537 +08:00] [INFO] [lightning.go:217] [cfg] [cfg="{“id”:1636360810537761834,“lightning”:{“table-concurrency”:6,“index-concurrency”:2,“region-concurrency”
:80,“io-concurrency”:5,“check-requirements”:false,“meta-schema-name”:“lightning_metadata”},“tidb”:{“host”:“10.110.70.16”,“port”:4000,“user”:“sthj”,“status-port”:1
0080,“pd-addr”:“10.110.70.18:2379”,“sql-mode”:“ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER”,“tls”:“false”,“security”:{“ca-path”:"",“cert-path”:"",“key-path”:"",“r
edact-info-log”:false},“max-allowed-packet”:67108864,“distsql-scan-concurrency”:15,“build-stats-concurrency”:20,“index-serial-scan-concurrency”:20,“checksum-table-concurrency
“:2},“checkpoint”:{“schema”:“tidb_lightning_checkpoint”,“driver”:”/home/tidb/Tools/tidb-toolkit-v5.2.1-linux-amd64/bin/tidb_lightning_checkpoint.pb”,“enable”:false,“keep-
after-success”:false},“mydumper”:{“read-block-size”:65536,“batch-size”:0,“batch-import-ratio”:0,“data-source-dir”:“file:///home/tidb/data/my_database”,“character-set”:"
auto",“csv”:{“separator”:",",“delimiter”:"\"",“terminator”:"",“null”:"\\N",“header”:true,“trim-last-separator”:false,“not-null”:false,“backslash-escape”:
true},“max-region-size”:268435456,“filter”:[".","!mysql.","!sys.","!INFORMATION_SCHEMA.","!PERFORMANCE_SCHEMA.","!METRICS_SCHEMA.","!INSPECTION_SCHEMA."],“fi
les”:null,“no-schema”:true,“case-sensitive”:false,“strict-format”:false,“default-file-rules”:true,“ignore-data-columns”:null},“tikv-importer”:{“addr”:"",“backend”:"
local",“on-duplicate”:“replace”,“max-kv-pairs”:4096,“send-kv-pairs”:32768,“region-split-size”:100663296,“sorted-kv-dir”:"/home/tidb/data/sorted-kv-dir",“disk-quota”:92
23372036854775807,“range-concurrency”:16,“duplicate-detection”:false,“engine-mem-cache-size”:536870912,“local-writer-mem-cache-size”:134217728},“post-restore”:{“checksum”:
“required”,“analyze”:“optional”,“level-1-compact”:false,“post-process-at-last”:true,“compact”:false},“cron”:{“switch-mode”:“5m0s”,“log-progress”:“5m0s”,“check-dis
k-quota”:“1m0s”},“routes”:null,“security”:{“ca-path”:"",“cert-path”:"",“key-path”:"",“redact-info-log”:false},“black-white-list”:{“do-tables”:null,“do-dbs”:n
ull,“ignore-tables”:null,“ignore-dbs”:null}}"]
[2021/11/08 16:40:10.541 +08:00] [INFO] [lightning.go:280] [“load data source start”]
[2021/11/08 16:40:10.541 +08:00] [INFO] [loader.go:288] ["[loader] file is filtered by file router"] [path=t_jzgd_ztcssdwxx.sql]
[2021/11/08 16:40:10.541 +08:00] [INFO] [lightning.go:283] [“load data source completed”] [takeTime=584.298µs] []
[2021/11/08 16:40:10.556 +08:00] [INFO] [local.go:1003] [“multi ingest support”]
[2021/11/08 16:40:10.556 +08:00] [INFO] [restore.go:401] [“the whole procedure start”]
[2021/11/08 16:40:10.559 +08:00] [INFO] [restore.go:705] [“restore all schema start”]
[2021/11/08 16:40:10.559 +08:00] [INFO] [restore.go:724] [“restore all schema completed”] [takeTime=43.106µs] []
[2021/11/08 16:40:12.184 +08:00] [INFO] [restore.go:1183] [“restore all tables data start”]
[2021/11/08 16:40:12.184 +08:00] [INFO] [restore.go:1203] [“removing PD leader&region schedulers”]
[2021/11/08 16:40:12.195 +08:00] [INFO] [pd.go:403] [“pause scheduler successful at beginning”] [name="[balance-region-scheduler,balance-leader-scheduler,balance-hot-region-scheduler]"
]
[2021/11/08 16:40:12.199 +08:00] [INFO] [pd.go:411] [“pause configs successful at beginning”] [cfg="{“enable-location-replacement”:“false”,“leader-schedule-limit”:12,“max-merge-
region-keys”:0,“max-merge-region-size”:0,“max-pending-peer-count”:2147483647,“max-snapshot-count”:9,“region-schedule-limit”:40}"]
[2021/11/08 16:40:12.212 +08:00] [INFO] [restore.go:1371] [“restore all tables data completed”] [takeTime=28.303045ms] []
[2021/11/08 16:40:12.257 +08:00] [INFO] [meta_manager.go:797] [“check all task finish status”] [task_finished=true] [all_finished=true] [switch_back=true]
[2021/11/08 16:40:12.257 +08:00] [INFO] [pd.go:450] [“resume scheduler”] [schedulers="[balance-region-scheduler,balance-leader-scheduler,balance-hot-region-scheduler]"]
[2021/11/08 16:40:12.258 +08:00] [INFO] [pd.go:470] [“resume scheduler successful”] [scheduler=balance-region-scheduler]
[2021/11/08 16:40:12.260 +08:00] [INFO] [pd.go:470] [“resume scheduler successful”] [scheduler=balance-leader-scheduler]
[2021/11/08 16:40:12.261 +08:00] [INFO] [pd.go:470] [“resume scheduler successful”] [scheduler=balance-hot-region-scheduler]
[2021/11/08 16:40:12.261 +08:00] [INFO] [pd.go:561] [“restoring config”] [config="{“enable-location-replacement”:“true”,“leader-schedule-limit”:4,“max-merge-region-keys”:200000
,“max-merge-region-size”:20,“max-pending-peer-count”:16,“max-snapshot-count”:3,“region-schedule-limit”:2048}"]
[2021/11/08 16:40:12.264 +08:00] [INFO] [restore.go:1221] [“add back PD leader&region schedulers”]
[2021/11/08 16:40:12.264 +08:00] [INFO] [restore.go:1224] [“cleanup task metas”]
[2021/11/08 16:40:12.271 +08:00] [INFO] [restore.go:1072] [“everything imported, stopping periodic actions”]
[2021/11/08 16:40:12.782 +08:00] [INFO] [restore.go:1173] [“cancel periodic actions”] [do=true]
[2021/11/08 16:40:12.782 +08:00] [INFO] [restore.go:1038] [“switch to normal mode”]
[2021/11/08 16:40:13.400 +08:00] [INFO] [restore.go:1488] [“skip full compaction”]
[2021/11/08 16:40:13.404 +08:00] [INFO] [restore.go:431] [“the whole procedure completed”] [takeTime=2.847645828s] []
[2021/11/08 16:40:13.404 +08:00] [INFO] [main.go:93] [“tidb lightning exit”]

【问题】 当前遇到的问题
MySQL导出文件*.sql，900+w记录，使用tidb-lightning导入时无报错，但是查询目标表数据为空，数据未导入。

【业务影响】

【TiDB 版本】
v5.2.1

1. 列表条目

【附件】

• 相关日志、配置文件、Grafana 监控（https://metricstool.pingcap.com/)
• TiUP Cluster Display 信息
• TiUP CLuster Edit config 信息
• TiDB-Overview 监控
• 对应模块的 Grafana 监控（如有 BR、TiDB-binlog、TiCDC 等）
• 对应模块日志（包含问题前后 1 小时日志）

---

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

[zuoxh (Hacker T3brlp4 R)]

谢谢，我试一下，我是直接用mysql导出的sql文件导入的，估计问题就出在这里了。

[zuoxh (Hacker T3brlp4 R)]

测试结果：使用dumpling导出的文件可以直接用lightning导入，但是用mysql导出的sql文件，用lightning直接导入不不成功，覆盖文件也导入不成功。暂时还未找到原因。

[zuoxh (Hacker T3brlp4 R)]

dumpling导出的文件头部是：
/!40101 SET NAMES binary/;
INSERT INTO

MySQL导出的文件头部是：
建表语句

导入模式我用的就是tidb模式。

[zuoxh (Hacker T3brlp4 R)]

备份出来的文件名：

MySQL导出的sql文件头部：

tidb导出的sql文件头部：

[zuoxh (Hacker T3brlp4 R)]

这个问题已解决，是里面放着一个.bak文件，删掉就好了。非常感谢你的帮助。

[bitifi2019]

您好，我遇到跟您一样的问题，但是按照这里步骤操作，还是提示失败，能详细帮忙指导下吗
这是MySQL导出的sql文件头部：

这是报错信息：

[bitifi2019]

您好，我按照此贴的操作步骤，操作后还是报错了，能详细描述下您的操作吗，或者帮忙指导下我的操作，谢谢