dumpling整个database为sql文件时，误用了--output-filename-template参数，导致lighting时无法识别加载导出的sql.gz数据

TiDBer_g9JOVuLf · 2023 年3 月 24 日 06:50

【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.4.0
【复现路径】做过哪些操作出现的问题
单机部署1tidb server 3tikv 1pd 实例，tikv数据实例达到磁盘100%,需要扩容。由于磁盘规划格式化问题，需要协助集群，重装。先利用tiup dumpling导出已有的数据到本地，后来发现参考网上导出命令时，误用了output-filename-template参数。运行命令如下（ip等信息已隐）：
tiup dumpling -u root -P 4000 -h 10.xx.xx.xxx -o /opt/ccc/data/export/ -r 200000 -t 16 --filetype sql --compress gzip --database db1,test -F 256MiB --output-filename-template ‘tidb-test.{{.Index}}’

导出完之后，便将tidb集群数据清理和卸载了，然后调整拓扑文件之后，重新部署了集群，现使用tiup lighting工具导入数据时，没报错，但是没导入数据。命令如下：
nohup tiup tidb-lightning -no-schema -config tidb-lightning.toml > nohup.out &

运行结果日志：
[2023/03/24 12:53:34.938 +08:00] [WARN] [config.go:827] [“currently only per-task configuration can be applied, global configuration changes can only be made on startup”] [“global config changes”=“[lightning.level,lightning.file]”]
[2023/03/24 12:53:34.939 +08:00] [INFO] [lightning.go:382] [cfg] [cfg=“{"id":1679633614939008092,"lightning":{"table-concurrency":6,"index-concurrency":2,"region-concurrency":88,"io-concurrency":5,"check-requirements":true,"meta-schema-name":"lightning_metadata","max-error":{"type":0},"task-info-schema-name":"lightning_task_info"},"tidb":{"host":"10.xx.xx.xx","port":4000,"user":"root","status-port":10080,"pd-addr":"10.xx.xx.xx:2379","sql-mode":"ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER","tls":"false","security":{"ca-path":"","cert-path":"","key-path":"","redact-info-log":false},"max-allowed-packet":67108864,"distsql-scan-concurrency":15,"build-stats-concurrency":20,"index-serial-scan-concurrency":20,"checksum-table-concurrency":2,"vars":null},"checkpoint":{"schema":"tidb_lightning_checkpoint","driver":"file","enable":true,"keep-after-success":"remove"},"mydumper":{"read-block-size":65536,"batch-size":0,"batch-import-ratio":0,"source-id":"","data-source-dir":"file:///opt/ccc/data1/export","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."],"files":null,"no-schema":true,"case-sensitive":false,"strict-format":false,"default-file-rules":true,"ignore-data-columns":null,"data-character-set":"binary","data-invalid-char-replace":"�"},"tikv-importer":{"addr":"","backend":"local","on-duplicate":"replace","max-kv-pairs":4096,"send-kv-pairs":32768,"region-split-size":0,"region-split-keys":0,"sorted-kv-dir":"/opt/ccc/data/sorted-kv-dir","disk-quota":9223372036854775807,"range-concurrency":16,"duplicate-resolution":"none","incremental-import":false,"engine-mem-cache-size":536870912,"local-writer-mem-cache-size":134217728,"store-write-bwlimit":0},"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-disk-quota":"1m0s"},"routes":null,"security":{"ca-path":"","cert-path":"","key-path":"","redact-info-log":false},"black-white-list":{"do-tables":null,"do-dbs":null,"ignore-tables":null,"ignore-dbs":null}}”]
[2023/03/24 12:53:34.959 +08:00] [INFO] [lightning.go:483] [“load data source start”]
[2023/03/24 12:53:34.974 +08:00] [INFO] [loader.go:450] [“[loader] file is filtered by file router”] [path=metadata]
[2023/03/24 12:53:35.546 +08:00] [INFO] [lightning.go:486] [“load data source completed”] [takeTime=587.32434ms] []
[2023/03/24 12:53:35.546 +08:00] [INFO] [checkpoints.go:1014] [“open checkpoint file failed, going to create a new one”] [path=/tmp/tidb_lightning_checkpoint.pb] []
[2023/03/24 12:53:35.554 +08:00] [INFO] [local.go:578] [“multi ingest support”]
[2023/03/24 12:53:35.554 +08:00] [INFO] [restore.go:448] [“the whole procedure start”]
[2023/03/24 12:53:35.556 +08:00] [INFO] [restore.go:1995] [new_collation_enabled] [enabled=true]
[2023/03/24 12:53:35.556 +08:00] [INFO] [restore.go:756] [“restore all schema start”]
[2023/03/24 12:53:35.556 +08:00] [INFO] [restore.go:772] [“restore all schema completed”] [takeTime=82.701µs] []
[2023/03/24 12:53:35.562 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/24 12:53:35.563 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/24 12:53:35.567 +08:00] [INFO] [restore.go:1424] [“restore all tables data start”]
[2023/03/24 12:53:35.569 +08:00] [INFO] [restore.go:1309] [“cancel periodic actions”] [do=true]
[2023/03/24 12:53:35.569 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Import]
[2023/03/24 12:53:35.569 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Normal]
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:1532] [“restore all tables data completed”] [takeTime=24.970574ms] []
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:1535] [“cleanup task metas”]
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:1829] [“skip full compaction”]
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:1171] [“everything imported, stopping periodic actions”]
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:2018] [“clean checkpoints start”] [keepAfterSuccess=remove] [taskID=1679633614939008092]
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:2026] [“clean checkpoints completed”] [keepAfterSuccess=remove] [taskID=1679633614939008092] [takeTime=41.754µs] []
[2023/03/24 12:53:35.592 +08:00] [INFO] [restore.go:476] [“the whole procedure completed”] [takeTime=37.750328ms] []
[2023/03/24 12:53:35.593 +08:00] [INFO] [main.go:106] [“tidb lightning exit”] [finished=true]

tidb-lightning.toml 内容如下：
[lightning]

日志

level = “info”
file = “tidb-lightning.log”

[tikv-importer]

选择使用的导入模式

backend = “local”

设置排序的键值对的临时存放地址，目标路径需要是一个空目录

sorted-kv-dir = “/opt/ccc/data/sorted-kv-dir”

[mydumper]

源数据目录。

data-source-dir = “/opt/ccc/data1/export/”

配置通配符规则，默认规则会过滤 mysql、sys、INFORMATION_SCHEMA、PERFORMANCE_SCHEMA、METRICS_SCHEMA、INSPECTION_SCHEMA 系统数据库下的所有表

若不配置该项，导入系统表时会出现“找不到 schema”的异常

#filter = [‘.’, ‘!mysql.', '!sys.’, ‘!INFORMATION_SCHEMA.', '!PERFORMANCE_SCHEMA.’, ‘!METRICS_SCHEMA.', '!INSPECTION_SCHEMA.’]
filter = [‘!mysql.', '!sys.’, ‘!INFORMATION_SCHEMA.', '!PERFORMANCE_SCHEMA.’, ‘!METRICS_SCHEMA.', '!INSPECTION_SCHEMA.’]
[tidb]

目标集群的信息

host = “10.xx.xx.xx”
port = 4000
user = “root”
password = “xxx”

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

status-port = 10080

集群 pd 的地址

pd-addr = “10.xx.xx.xx:2379”

【遇到的问题：问题现象及影响】
dumpling语句，误加了参数，导致导出数据格式异常，在lighting阶段无法被识别。请问该如何处理，以正常加载到数据库中？
tiup dumpling -u root -P 4000 -h 10.xx.xx.xxx -o /opt/ccc/data/export/ -r 200000 -t 16 --filetype sql --compress gzip --database db1,test -F 256MiB --output-filename-template ‘tidb-test.{{.Index}}’

【资源配置】
【附件：截图/日志/监控】

胡杨树旁 · 2023 年3 月 24 日 07:06

重装了之后库里面有表吗？看lightning导入的时候加了–no-schema 原来导出的文件还可以区分出来吗？如果可以区分的话是否可以手动改下文件名字？

啦啦啦啦啦 · 2023 年3 月 24 日 07:07

没写{{.Table}}，sql文件的格式肯定识别不了了，看能不能手动修改下导出的文件名，数据太多的话考虑用脚本批量做下。
参考这里
https://docs.pingcap.com/zh/tidb/stable/tidb-lightning-data-source

TiDBer_g9JOVuLf · 2023 年3 月 24 日 07:10

把-no-schema去掉了,还是没识别出来。

TiDBer_g9JOVuLf · 2023 年3 月 24 日 07:15

现在生成的数据都是tidb-test.0000341790000.sql.gz这样的格式的，然后db1下面有多张表，光看文件名，我无法区分哪个tidb-test.000xxx.sql.gz是属于哪个表的数据。。。

啦啦啦啦啦 · 2023 年3 月 24 日 07:17

试试利用脚本解压完获取sql文件的前几行来得到表名，然后循环改文件名

TiDBer_g9JOVuLf · 2023 年3 月 24 日 07:24

机智，我刚分析了下数据文件，也打算这么搞。。

胡杨树旁 · 2023 年3 月 24 日 07:25

文件里面有些库名.表名是不是可以看下前2行，进行一下文件的归类？

TiDBer_g9JOVuLf · 2023 年3 月 24 日 09:48

改了还是不行哎

很快就运行结束，没提示具体错误。没导入成功。
[2023/03/24 17:23:33.383 +08:00] [WARN] [config.go:827] [“currently only per-task configuration can be applied, global configuration changes can only be made on startup”] [“global config changes”=“[lightning.level,lightning.file]”]
[2023/03/24 17:23:33.383 +08:00] [INFO] [lightning.go:382] [cfg] [cfg=“{"id":1679649813383409438,"lightning":{"table-concurrency":6,"index-concurrency":2,"region-concurrency":88,"io-concurrency":5,"check-requirements":true,"meta-schema-name":"lightning_metadata","max-error":{"type":0},"task-info-schema-name":"lightning_task_info"},"tidb":{"host":"10.xx.xx.xx","port":4000,"user":"root","status-port":10080,"pd-addr":"10.xx.xx.xx:2379","sql-mode":"ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER","tls":"false","security":{"ca-path":"","cert-path":"","key-path":"","redact-info-log":false},"max-allowed-packet":67108864,"distsql-scan-concurrency":15,"build-stats-concurrency":20,"index-serial-scan-concurrency":20,"checksum-table-concurrency":2,"vars":null},"checkpoint":{"schema":"tidb_lightning_checkpoint","driver":"file","enable":true,"keep-after-success":"remove"},"mydumper":{"read-block-size":65536,"batch-size":0,"batch-import-ratio":0,"source-id":"","data-source-dir":"file:///opt/ccc/data1/export","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."],"files":null,"no-schema":false,"case-sensitive":false,"strict-format":false,"default-file-rules":true,"ignore-data-columns":null,"data-character-set":"binary","data-invalid-char-replace":"�"},"tikv-importer":{"addr":"","backend":"local","on-duplicate":"replace","max-kv-pairs":4096,"send-kv-pairs":32768,"region-split-size":0,"region-split-keys":0,"sorted-kv-dir":"/opt/ccc/data/sorted-kv-dir","disk-quota":9223372036854775807,"range-concurrency":16,"duplicate-resolution":"none","incremental-import":false,"engine-mem-cache-size":536870912,"local-writer-mem-cache-size":134217728,"store-write-bwlimit":0},"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-disk-quota":"1m0s"},"routes":null,"security":{"ca-path":"","cert-path":"","key-path":"","redact-info-log":false},"black-white-list":{"do-tables":null,"do-dbs":null,"ignore-tables":null,"ignore-dbs":null}}”]
[2023/03/24 17:23:33.406 +08:00] [INFO] [lightning.go:483] [“load data source start”]
[2023/03/24 17:23:33.423 +08:00] [INFO] [loader.go:450] [“[loader] file is filtered by file router”] [path=metadata]
[2023/03/24 17:23:34.252 +08:00] [INFO] [lightning.go:486] [“load data source completed”] [takeTime=846.285355ms] []
[2023/03/24 17:23:34.252 +08:00] [INFO] [checkpoints.go:1014] [“open checkpoint file failed, going to create a new one”] [path=/tmp/tidb_lightning_checkpoint.pb] []
[2023/03/24 17:23:34.261 +08:00] [INFO] [local.go:578] [“multi ingest support”]
[2023/03/24 17:23:34.261 +08:00] [INFO] [restore.go:448] [“the whole procedure start”]
[2023/03/24 17:23:34.262 +08:00] [INFO] [restore.go:1995] [new_collation_enabled] [enabled=true]
[2023/03/24 17:23:34.262 +08:00] [INFO] [restore.go:756] [“restore all schema start”]
[2023/03/24 17:23:34.262 +08:00] [INFO] [restore.go:772] [“restore all schema completed”] [takeTime=49.229µs] []
[2023/03/24 17:23:34.268 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/24 17:23:34.269 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/24 17:23:34.273 +08:00] [INFO] [restore.go:1424] [“restore all tables data start”]
[2023/03/24 17:23:34.276 +08:00] [INFO] [restore.go:1309] [“cancel periodic actions”] [do=true]
[2023/03/24 17:23:34.276 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Import]
[2023/03/24 17:23:34.276 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Normal]
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:1532] [“restore all tables data completed”] [takeTime=21.927336ms] []
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:1535] [“cleanup task metas”]
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:1829] [“skip full compaction”]
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:2018] [“clean checkpoints start”] [keepAfterSuccess=remove] [taskID=1679649813383409438]
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:1171] [“everything imported, stopping periodic actions”]
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:2026] [“clean checkpoints completed”] [keepAfterSuccess=remove] [taskID=1679649813383409438] [takeTime=43.489µs] []
[2023/03/24 17:23:34.295 +08:00] [INFO] [restore.go:476] [“the whole procedure completed”] [takeTime=33.94224ms] []
[2023/03/24 17:23:34.295 +08:00] [INFO] [main.go:106] [“tidb lightning exit”] [finished=true]

TiDBer_g9JOVuLf · 2023 年3 月 24 日 09:57

原先文件是从tidb-test.0000000010000.sql.gz 一直到tidb-test.0000341790000.sql.gz

其中，
tidb-test.0000000010000.sql.gz → db1.tb1.0000000010000.sql.gz
tidb-test.0000000020000.sql.gz → db1.tb1.0000000020000.sql.gz

tidb-test.0000000030000.sql.gz → db1.tb2.0000000030000.sql.gz
…
tidb-test.0000024240000.sql.gz → db1.tb2.0000024240000.sql.gz

tidb-test.0000024250000.sql.gz → db1.tb3.0000024250000.sql.gz
…
tidb-test.0000341790000.sql.gz → db1.tb3.0000341790000.sql.gz

胡杨树旁 · 2023 年3 月 24 日 10:05

导出的是sql 文本，导入的时候可以使用local 模式吗？这个没有测试过，一般导出sql 模式我都是用tidb模式导入，你可以先选择一个小的文件导入试试呢？是否需要解压再导入？

ljluestc · 2023 年4 月 17 日 03:27

# 第 1 步：验证使用 tiup 转储创建的数据转储是否正确且未损坏。
# 将数据转储导入到新的 TiDB 集群中，查看数据是否导入成功。
tiup playground --db 2 --pd 1 --kv 3 --tiflash 0 --monitor false
# 在新的 TiDB 集群中，运行以下命令导入数据转储：
# tiup restore --host <tidb_host> --port <tidb_port> --user <tidb_user> --password <tidb_password> --status-addr <tidb_status_address> --backup <backup_dir> --schema <schema_name>
# 将 <tidb_host>、<tidb_port>、<tidb_user>、<tidb_password>、<tidb_status_address>、<backup_dir> 和 <schema_name> 替换为合适的值。

# Step 2: 检查拓扑文件以确保其设置正确。
# 验证 TiKV 数据目录是否设置正确，数据是否写入了正确的目录。
# 可以查看 TiKV 的日志，看是否有数据写入不正确的错误信息。

# 第三步：检查 tidb-lightning 配置文件，确保设置正确。
# 确保数据转储的路径正确并且导入设置配置正确。
# 也可以查看 tidb-lightning 日志，看是否有可能提示配置不正确的错误信息。
# 以下是 tidb-lightning 配置文件示例：
# tidb-server-addr = "<tidb_host>:<tidb_port>"
# pd-addr = "<pd_address>"
# tikv-importer.backend = "local"
# tikv-importer.sorted-kv-dir = "<sorted_kv_dir>"
# checkpoint.enable = true
# checkpoint.driver = "文件"
# checkpoint.schema = "<checkpoint_schema_name>"
# checkpoint.keep-after-success = "删除"
# 数据源目录 = "<data_dump_dir>"
# 无架构 = true
# 在这里，将 <tidb_host>、<tidb_port>、<pd_address>、<sorted_kv_dir>、<checkpoint_schema_name> 和 <data_dump_dir> 替换为合适的值。

# 第四步：再次运行 tidb-lightning 工具，查看日志是否有报错信息。
nohup tiup tidb-lightning -no-schema -config tidb-lightning.toml > nohup.out &
# 检查 nohup.out 文件是否有任何错误消息。