How to use Dumpling to backup TiDB and import it into MySQL 8.0, as MyLoader does not support 8.0

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb如何通过dumpling备份导入到mysql8.0，myloader不支持8.0

| username: 路在何chu

[TiDB Usage Environment] Production Environment

[TiDB Version]
4013
[Reproduction Path] Operations performed that led to the issue
Backup TiDB using dumpling, then import using myloader
[Encountered Issue: Problem Symptoms and Impact]

  • (myloader:6137): CRITICAL **: 16:16:55.011: Error connection to database: SSL connection error: error:00000001:lib(0):func(0):reason(1)
2
| username: 路在何chu | Original post link

mylosder does not have the skip-ssl parameter.

3
| username: Miracle | Original post link

Can I temporarily disable MySQL’s SSL?

4
| username: 路在何chu | Original post link

Cannot be closed.

5
| username: Miracle | Original post link

Is it possible to move all the backup files to the MySQL server, and then write a script to import them using the source method? Alternatively, if Navicat can be used, data transfer with Navicat is also quite convenient.

6
| username: dba远航 | Original post link

Try another tool, lightning.

7
| username: tidb菜鸟一只 | Original post link

Are you using an SQL file or a CSV file?

8
| username: 这里介绍不了我 | Original post link

Why not use Lightning?

9
| username: 路在何chu | Original post link

SQL file

10
| username: 路在何chu | Original post link

Can Lingting be imported into MySQL?

11
| username: 路在何chu | Original post link

There are too many main files, thousands of them.

12
| username: 路在何chu | Original post link

img_v3_028a_dfd2c609-36dd-4495-a30c-b34bda356a6h
img_v3_028a_dfd2c609-36dd-4495-a30c-b34bda356a6h1380×698 107 KB

13
| username: Billmay表妹 | Original post link

Version 4.0.13?

You can use version 7.5.0, which is already compatible with MySQL 8.0.

14
| username: 路在何chu | Original post link

The loader import error occurs when importing data exported by dumpling. How do I import it into version 8.0?

15
| username: 这里介绍不了我 | Original post link

You can test it with a small table. We have used Lightning to import from TiDB to MySQL 5.7.

16
| username: 这里介绍不了我 | Original post link

You can try 8.0 yourself. If it works, the efficiency is very high.

17
| username: xingzhenxiang | Original post link

Just write a script to handle it, source.

18
| username: 路在何chu | Original post link

Unable to import, [ERROR] [main.go:90] [“tidb lightning encountered error stack info”] [error=“Error 1193: Unknown system variable ‘tidb_opt_write_row_id’”] [errorVerbose=“Error 1193: Unknown system variable ‘tidb_opt_write_row_id’\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngithub.com/pingcap/errors.Trace\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/juju_adaptor.go:15\ngithub.com/pingcap/br/pkg/lightning/common.(*MySQLConnectParam).Connect\n\tgithub.com/pingcap/br@/pkg/lightning/common/util.go:81\ngithub.com/pingcap/br/pkg/lightning/restore.DBFromConfig\n\tgithub.com/pingcap/br@/pkg/lightning/restore/tidb.go:108\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).run\n\tgithub.com/pingcap/br@/pkg/lightning/lightning.go:265\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).RunOnce\n\tgithub.com/pingcap/br@/pkg/lightning/lightning.go:191\nmain.main.func2\n\tgithub.com/pingcap/br@/cmd/tidb-lightning/main.go:86\nmain.main\n\tgithub.com/pingcap/br@/cmd/tidb-lightning/main.go:87\nruntime.main\n\truntime/proc.go:203\nruntime.goexit\n\truntime/asm_amd64.s:1357”]
[2024/02/22 10:40:26.290 +00:00] [ERROR] [main.go:91] [“tidb lightning encountered error”] [error=“Error 1193: Unknown system variable ‘tidb_opt_write_row_id’”]

19
| username: 路在何chu | Original post link

It can only be like this, there’s no other way.

20
| username: DBAER | Original post link

Change it to a CSV file, or try using DataX.