用tiup dumpling工具导出的sql数据,用tiup tidb-lightning工具导不回tidb-test集群

【 TiDB 使用环境】测试
【 TiDB 版本】v6.4.0
【复现路径】做过哪些操作出现的问题
1、导出数据库mmm的所有数据:
tiup dumpling -u root -P 4000 -h 10.xx.xx.xx -o /opt/ccc/data1/tmp/ -r 200000 -t 16 --filetype sql --compress gzip --database mmm -F 256MiB -p $pss

2、得到以下数据
-rw------- 1 tidb root 146 Mar 24 14:30 metadata
-rw------- 1 tidb root 152 Mar 24 14:30 mmm.mmm_feature_dump.0000000010000.sql.gz
-rw------- 1 tidb root 260 Mar 24 14:30 mmm.mmm_feature_dump-schema.sql.gz
-rw------- 1 tidb root 141 Mar 24 14:30 mmm.rr_hlw_log.0000000010000.sql.gz
-rw------- 1 tidb root 252 Mar 24 14:30 mmm.rr_hlw_log-schema.sql.gz
-rw------- 1 tidb root 127 Mar 24 14:30 mmm-schema-create.sql.gz

3、将tidb-test集群中的mmm数据库drop掉

4、配置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/tmp/”
filter = [‘!mysql.', '!sys.’, ‘!INFORMATION_SCHEMA.', '!PERFORMANCE_SCHEMA.’, ‘!METRICS_SCHEMA.', '!INSPECTION_SCHEMA.’]
[tidb]
host = “10.xx.xx.xx”
port = 4000
user = “root”
password = “xxx”
status-port = 10080
pd-addr = “10.xx.xx.xx:2379”

5、运行导入命令:
nohup tiup tidb-lightning -config mmm.toml > mmm.out &

得到mmm.out日志
Verbose debug logs will be written to tidb-lightning.log

±—±-----------------------------------------------------------------------------------------±------------±-------+
| | CHECK ITEM | TYPE | PASSED |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 1 | Source csv files size is proper | performance | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 2 | the checkpoints are valid | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 3 | table schemas are valid | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 4 | all importing tables on the target are empty | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 5 | Cluster version check passed | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 6 | Lightning has the correct storage permission | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 7 | local source dir and temp-kv dir are in different disks | performance | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 8 | local disk resources are rich, estimate sorted data size 0B, local available is 2.139TiB | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 9 | Cluster available is rich, available is 5.607TiB, we need 0B | performance | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 10 | Cluster doesn’t have too many empty regions | performance | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+
| 11 | Cluster region distribution is balanced | critical | true |
±—±-----------------------------------------------------------------------------------------±------------±-------+

tidb lightning exit successfully

6、tidb-lightning.log打印如下
[2023/03/25 11:08:46.419 +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/25 11:08:46.419 +08:00] [INFO] [lightning.go:382] [cfg] [cfg=“{"id":1679713726419544464,"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/tmp","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/25 11:08:46.422 +08:00] [INFO] [lightning.go:483] [“load data source start”]
[2023/03/25 11:08:46.423 +08:00] [INFO] [loader.go:450] [“[loader] file is filtered by file router”] [path=metadata]
[2023/03/25 11:08:46.423 +08:00] [INFO] [lightning.go:486] [“load data source completed”] [takeTime=362.361µs] []
[2023/03/25 11:08:46.423 +08:00] [INFO] [checkpoints.go:1014] [“open checkpoint file failed, going to create a new one”] [path=/tmp/tidb_lightning_checkpoint.pb] []
[2023/03/25 11:08:46.432 +08:00] [INFO] [local.go:578] [“multi ingest support”]
[2023/03/25 11:08:46.432 +08:00] [INFO] [restore.go:448] [“the whole procedure start”]
[2023/03/25 11:08:46.433 +08:00] [INFO] [restore.go:1995] [new_collation_enabled] [enabled=true]
[2023/03/25 11:08:46.433 +08:00] [INFO] [restore.go:756] [“restore all schema start”]
[2023/03/25 11:08:46.433 +08:00] [INFO] [restore.go:772] [“restore all schema completed”] [takeTime=20.947µs] []
[2023/03/25 11:08:46.438 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/25 11:08:46.439 +08:00] [INFO] [version.go:398] [“detect server version”] [type=TiDB] [version=6.4.0]
[2023/03/25 11:08:46.444 +08:00] [INFO] [restore.go:1424] [“restore all tables data start”]
[2023/03/25 11:08:46.447 +08:00] [INFO] [restore.go:1309] [“cancel periodic actions”] [do=true]
[2023/03/25 11:08:46.447 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Import]
[2023/03/25 11:08:46.447 +08:00] [INFO] [restore.go:1869] [“switch import mode”] [mode=Normal]
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:1532] [“restore all tables data completed”] [takeTime=21.163674ms] []
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:1535] [“cleanup task metas”]
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:1829] [“skip full compaction”]
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:1171] [“everything imported, stopping periodic actions”]
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:2018] [“clean checkpoints start”] [keepAfterSuccess=remove] [taskID=1679713726419544464]
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:2026] [“clean checkpoints completed”] [keepAfterSuccess=remove] [taskID=1679713726419544464] [takeTime=31.575µs] []
[2023/03/25 11:08:46.466 +08:00] [INFO] [restore.go:476] [“the whole procedure completed”] [takeTime=34.262225ms] []
[2023/03/25 11:08:46.466 +08:00] [INFO] [main.go:106] [“tidb lightning exit”] [finished=true]

7、查验tidb-test集群是否生成,发现没有生成对应的mmm数据库
show databases;
±-------------------+
| Database |
±-------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| mysql |
| tidb-test |
±-------------------+

【遇到的问题:问题现象及影响】
【资源配置】
【附件:截图/日志/监控】

简单的测试数据库,导出之后,居然一直导不回去,也没相关有效的说明日志。整个人懵逼了两天了。。急求助。。

以前遇到过类似情况 是 pd ip 或者 tidb ip 配置到另外集群了。

确认过ip,没有配错。我是单机部署:

tiup is checking updates for component cluster …
Starting component cluster: /home/tidb/.tiup/components/cluster/v1.11.0/tiup-cluster display tidb-test
Cluster type: tidb
Cluster name: tidb-test
Cluster version: v6.4.0
Deploy user: tidb
SSH type: builtin
Dashboard URL: http://10.xx.xx.xx:2379/dashboard
Grafana URL: http://10.xx.xx.xx:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir


10.xx.xx.xx:9093 alertmanager 10.xx.xx.xx 9093/9094 linux/x86_64 Up /opt/ccc/data1/tidb-data/alertmanager-9093 /opt/ccc/data1/tidb-deploy/alertmanager-9093
10.xx.xx.xx:3000 grafana 10.xx.xx.xx 3000 linux/x86_64 Up - /opt/ccc/data1/tidb-deploy/grafana-3000
10.xx.xx.xx:2379 pd 10.xx.xx.xx 2379/2380 linux/x86_64 Up|L|UI /opt/ccc/data1/tidb-data/pd-2379 /opt/ccc/data1/tidb-deploy/pd-2379
10.xx.xx.xx:9090 prometheus 10.xx.xx.xx 9090/12020 linux/x86_64 Up /opt/ccc/data1/tidb-data/prometheus-8249 /opt/ccc/data1/tidb-deploy/prometheus-8249
10.xx.xx.xx:4000 tidb 10.xx.xx.xx 4000/10080 linux/x86_64 Up - /opt/ccc/data1/tidb-deploy/tidb-4000
10.xx.xx.xx:9000 tiflash 10.xx.xx.xx 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /opt/ccc/data3/tidb-data/tiflash-9000 /opt/ccc/data3/tidb-deploy/tiflash-9000
10.xx.xx.xx:20160 tikv 10.xx.xx.xx 20160/20180 linux/x86_64 Up /opt/ccc/data2/part0/tidb-data/tikv-20160 /opt/ccc/data2/part0/tidb-deploy/tikv-20160
10.xx.xx.xx:20161 tikv 10.xx.xx.xx 20161/20181 linux/x86_64 Up /opt/ccc/data2/part1/tidb-data/tikv-20161 /opt/ccc/data2/part1/tidb-deploy/tikv-20161
10.xx.xx.xx:20162 tikv 10.xx.xx.xx 20162/20182 linux/x86_64 Up /opt/ccc/data2/part2/tidb-data/tikv-20162 /opt/ccc/data2/part2/tidb-deploy/tikv-20162
10.xx.xx.xx:20163 tikv 10.xx.xx.xx 20163/20183 linux/x86_64 Up /opt/ccc/data3/part0/tidb-data/tikv-20163 /opt/ccc/data3/part0/tidb-deploy/tikv-20163
10.xx.xx.xx:20164 tikv 10.xx.xx.xx 20164/20184 linux/x86_64 Up /opt/ccc/data3/part1/tidb-data/tikv-20164 /opt/ccc/data3/part1/tidb-deploy/tikv-20164
10.xx.xx.xx:20165 tikv 10.xx.xx.xx 20165/20185 linux/x86_64 Up /opt/ccc/data3/part2/tidb-data/tikv-20165 /opt/ccc/data3/part2/tidb-deploy/tikv-20165

filter = [‘.’, ‘!mysql.', '!sys.’, ‘!INFORMATION_SCHEMA.', '!PERFORMANCE_SCHEMA.’, ‘!METRICS_SCHEMA.', '!INSPECTION_SCHEMA.’]
在lightning config file中加上’.'后,有了报错信息了:

[2023/03/25 15:18:28.551 +08:00] [INFO] [local.go:578] [“multi ingest support”]
[2023/03/25 15:18:28.551 +08:00] [INFO] [restore.go:448] [“the whole procedure start”]
[2023/03/25 15:18:28.552 +08:00] [INFO] [restore.go:1995] [new_collation_enabled] [enabled=true]
[2023/03/25 15:18:28.552 +08:00] [INFO] [restore.go:756] [“restore all schema start”]
[2023/03/25 15:18:28.553 +08:00] [INFO] [restore.go:772] [“restore all schema completed”] [takeTime=989.108µs] []
[2023/03/25 15:18:28.553 +08:00] [ERROR] [get_pre_info.go:411] [“parse sql statement error”] [error=“[parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use”] [sql=]
[2023/03/25 15:18:28.553 +08:00] [ERROR] [get_pre_info.go:398] [“generate table info from SQL error”] [error=“[parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use”] [errorVerbose=“[parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/juju_adaptor.go:15\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.newTableInfo\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:412\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).getTableStructuresByFileMeta\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:395\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).GetAllTableStructures.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:354\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.LoadSchemaInfo\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/tidb.go:238\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).GetAllTableStructures\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:353\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*Controller).restoreSchema\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/restore.go:777\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*Controller).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/restore.go:454\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:524\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:372\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1594”] [sql=] [table_name=rr_hlw_log]
[2023/03/25 15:18:28.553 +08:00] [ERROR] [restore.go:466] [“run failed”] [step=1] [error=“generate table info from SQL error: rr_hlw_log: [parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use”]
[2023/03/25 15:18:28.553 +08:00] [ERROR] [restore.go:476] [“the whole procedure failed”] [takeTime=2.36902ms] [error=“generate table info from SQL error: rr_hlw_log: [parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use”]
[2023/03/25 15:18:28.553 +08:00] [ERROR] [main.go:103] [“tidb lightning encountered error stack info”] [error=“generate table info from SQL error: rr_hlw_log: [parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use”] [errorVerbose=“[parser:1149]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/juju_adaptor.go:15\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.newTableInfo\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:412\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).getTableStructuresByFileMeta\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:395\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).GetAllTableStructures.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:354\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.LoadSchemaInfo\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/tidb.go:238\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*PreRestoreInfoGetterImpl).GetAllTableStructures\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/get_pre_info.go:353\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*Controller).restoreSchema\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/restore.go:777\ngithub.com/pingcap/tidb/br/pkg/lightning/restore.(*Controller).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/restore/restore.go:454\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:524\ngithub.com/pingcap/tidb/br/pkg/lightning.(*Lightning).RunOnceWithOptions\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/lightning.go:372\nmain.main.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:94\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/cmd/tidb-lightning/main.go:95\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1594\ngenerate table info from SQL error: rr_hlw_log”]

mmp哦,终于找到原因了。dumpling的时候使用了gzip压缩。lighting不支持压缩数据的处理。需要手动把dumpling的sql.gz数据gunzip成sql文件,就能载入了。。

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。