对于mysqldump 出来的数据怎样导入到TIDB中?

1.目前从hotdb 使用myqldump出来数据,如何导入到TIDB中;
备份命令:
mysqldump -hip -P3306 -utest -ptest --single-transaction test>test.sql

2.尝试使用tidb-lightning
[mydumper]

数据源目录

data-source-dir = “/data/ccmit3_bakup/abc”

[tidb]

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

host = “172.10.102.21”
port = 4000
user = “root”
password = “test"

[tikv-importer]
backend = “tidb”

执行:nohup tidb-lightning --config ./tidb-lightning.toml >nohup.out &

日志:
Verbose debug logs will be written to /tmp/lightning.log.2021-03-05T19.22.48+0800

tidb lightning encountered error: no schema create sql files found. Please either set mydumper.no-schema to true or add schema sql file for each database.

[2021/03/05 19:22:48.561 +08:00] [INFO] [version.go:53] [“Welcome to lightning”] [“Release Version”=v4.0.11] [“Git Commit Hash”=19f2048adea4fb6280f77d75b16efd756da7c104] [“Git Branch”=heads/refs/tags/v4.0.11] [“UTC Build Time”=“2021-02-25 04:37:34”] [“Go Version”=“go version go1.13 linux/amd64”]
[2021/03/05 19:22:48.562 +08:00] [INFO] [lightning.go:220] [cfg] [cfg="{“id”:1614943368561830238,“lightning”:{“table-concurrency”:16,“index-concurrency”:16,“region-concurrency”:16,“io-concurrency”:5,“check-requirements”:true},“tidb”:{“host”:“172.10.102.21”,“port”:4000,“user”:“root”,“status-port”:10080,“pd-addr”:"",“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},“checkpoint”:{“enable”:true,“schema”:“tidb_lightning_checkpoint”,“driver”:“file”,“keep-after-success”:false},“mydumper”:{“read-block-size”:65536,“batch-size”:107374182400,“batch-import-ratio”:0,“data-source-dir”:“file:///data/ccmit3_bakup/abc”,“no-schema”:false,“character-set”:“auto”,“csv”:{“separator”:",",“delimiter”:"\"",“header”:true,“trim-last-separator”:false,“not-null”:false,“null”:"\\N",“backslash-escape”:true},“case-sensitive”:false,“strict-format”:false,“max-region-size”:268435456,“filter”:[".","!mysql.","!sys.","!INFORMATION_SCHEMA.","!PERFORMANCE_SCHEMA.","!METRICS_SCHEMA.","!INSPECTION_SCHEMA."],“files”:null,“default-file-rules”:true},“tikv-importer”:{“addr”:"",“backend”:“tidb”,“on-duplicate”:“replace”,“max-kv-pairs”:32,“send-kv-pairs”:32768,“region-split-size”:100663296,“sorted-kv-dir”:"",“range-concurrency”:0},“post-restore”:{“level-1-compact”:false,“compact”:false,“checksum”:“required”,“analyze”:“optional”,“post-process-at-last”:true},“cron”:{“switch-mode”:“5m0s”,“log-progress”:“5m0s”},“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}}"]
[2021/03/05 19:22:48.574 +08:00] [INFO] [lightning.go:282] [“load data source start”]
[2021/03/05 19:22:48.575 +08:00] [ERROR] [lightning.go:285] [“load data source failed”] [takeTime=482.981µs] [error=“no schema create sql files found. Please either set mydumper.no-schema to true or add schema sql file for each database.”]
[2021/03/05 19:22:48.575 +08:00] [ERROR] [main.go:91] [“tidb lightning encountered error stack info”] [error=“no schema create sql files found. Please either set mydumper.no-schema to true or add schema sql file for each database.”] [errorVerbose=“no schema create sql files found. Please either set mydumper.no-schema to true or add schema sql file for each database.\ngithub.com/pingcap/tidb-lightning/lightning/mydump.(*mdLoaderSetup).setup\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/lightning/mydump/loader.go:222\ngithub.com/pingcap/tidb-lightning/lightning/mydump.NewMyDumpLoaderWithStore\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/lightning/mydump/loader.go:158\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:284\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).RunOnce\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:193\nmain.main.func2\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/cmd/tidb-lightning/main.go:86\nmain.main\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-lightning/cmd/tidb-lightning/main.go:88\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:203\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357”]
[2021/03/05 19:22:48.575 +08:00] [ERROR] [main.go:92] [“tidb lightning encountered error”] [error=“no schema create sql files found. Please either set mydumper.no-schema to true or add schema sql file for each database.”]

3.修改配置增加
[mydumper]

数据源目录

data-source-dir = “/data/ccmit3_bakup/abc”
no-schema=true

执行脚本:后

Verbose debug logs will be written to /tmp/lightning.log.2021-03-05T19.26.07+0800

tidb lightning exit

[2021/03/05 19:26:07.615 +08:00] [INFO] [version.go:53] [“Welcome to lightning”] [“Release Version”=v4.0.11] [“Git Commit Hash”=19f2048adea4fb6280f77d75b16efd756da7c104] [“Git Branch”=heads/refs/tags/v4.0.11] [“UTC Build Time”=“2021-02-25 04:37:34”] [“Go Version”=“go version go1.13 linux/amd64”]
[2021/03/05 19:26:07.615 +08:00] [INFO] [lightning.go:220] [cfg] [cfg="{“id”:1614943567615572983,“lightning”:{“table-concurrency”:16,“index-concurrency”:16,“region-concurrency”:16,“io-concurrency”:5,“check-requirements”:true},“tidb”:{“host”:“172.10.102.21”,“port”:4000,“user”:“root”,“status-port”:10080,“pd-addr”:"",“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},“checkpoint”:{“enable”:true,“schema”:“tidb_lightning_checkpoint”,“driver”:“file”,“keep-after-success”:false},“mydumper”:{“read-block-size”:65536,“batch-size”:107374182400,“batch-import-ratio”:0,“data-source-dir”:“file:///data/ccmit3_bakup/abc”,“no-schema”:true,“character-set”:“auto”,“csv”:{“separator”:",",“delimiter”:"\"",“header”:true,“trim-last-separator”:false,“not-null”:false,“null”:"\\N",“backslash-escape”:true},“case-sensitive”:false,“strict-format”:false,“max-region-size”:268435456,“filter”:[".","!mysql.","!sys.","!INFORMATION_SCHEMA.","!PERFORMANCE_SCHEMA.","!METRICS_SCHEMA.","!INSPECTION_SCHEMA."],“files”:null,“default-file-rules”:true},“tikv-importer”:{“addr”:"",“backend”:“tidb”,“on-duplicate”:“replace”,“max-kv-pairs”:32,“send-kv-pairs”:32768,“region-split-size”:100663296,“sorted-kv-dir”:"",“range-concurrency”:0},“post-restore”:{“level-1-compact”:false,“compact”:false,“checksum”:“required”,“analyze”:“optional”,“post-process-at-last”:true},“cron”:{“switch-mode”:“5m0s”,“log-progress”:“5m0s”},“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}}"]
[2021/03/05 19:26:07.626 +08:00] [INFO] [lightning.go:282] [“load data source start”]
[2021/03/05 19:26:07.626 +08:00] [INFO] [lightning.go:285] [“load data source completed”] [takeTime=218.363µs] []
[2021/03/05 19:26:07.626 +08:00] [INFO] [checkpoints.go:939] [“open checkpoint file failed, going to create a new one”] [path=/tmp/tidb_lightning_checkpoint.pb] [error=“open /tmp/tidb_lightning_checkpoint.pb: no such file or directory”]
[2021/03/05 19:26:07.630 +08:00] [INFO] [restore.go:276] [“the whole procedure start”]
[2021/03/05 19:26:07.630 +08:00] [INFO] [tidb.go:320] [“skipping check requirements for tidb backend”]
[2021/03/05 19:26:08.042 +08:00] [INFO] [restore.go:958] [“restore all tables data start”]
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:1164] [“restore all tables data completed”] [takeTime=129.242µs] []
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:1658] [“skip full compaction”]
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:856] [“everything imported, stopping periodic actions”]
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:1752] [“clean checkpoints start”] [keepAfterSuccess=false] [taskID=1614943567615572983]
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:1759] [“clean checkpoints completed”] [keepAfterSuccess=false] [taskID=1614943567615572983] [takeTime=88.502µs] []
[2021/03/05 19:26:08.043 +08:00] [INFO] [restore.go:306] [“the whole procedure completed”] [takeTime=412.753ms] []
[2021/03/05 19:26:08.043 +08:00] [INFO] [main.go:95] [“tidb lightning exit”]

如果无法用 mydumper 再次导出 mysql 数据的话
可以试试,source 将 sql 文件导入进去,因为 mydump 导出仅又一个文件,和 tidb-lightning 识别的文件有区别。

导入方式同mysql一样