After importing the target database with tidb-lighting, the data in the table is duplicated, unlike mysqldump which deletes existing tables during export and import

Translated
1

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

Original topic: tidb-lighting导入目标库后表里面数据重复，不是类似于mysqldump导出导入会将存在的表删除

| username: TiDBer_嘎嘣脆

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
6.2.0
[Encountered Problem: Problem Phenomenon and Impact]
When importing the production environment database into another environment, it was found that after using lighting to import, the database instance contained not only the production data but also the original environment data. The desired effect is similar to using mysqldump for export and mysql for import, which drops the existing tables.
[Attachment: Screenshot/Log/Monitoring]
See the configuration of the tidb-lightning.toml file in the image:

WeChat Screenshot_20230828160315
WeChat Screenshot_202308281603151380×446 51.4 KB

The dumpling backup command is as follows:
/root/.tiup/bin/tiup dumpling -u root -P 4000 -h IP -p password --filetype sql -t 8 -o /data/backup/${DATE} -r 20000 -F125MiB

2
| username: 像风一样的男子 | Original post link

You can just drop that table before importing.

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

The table structure statement exported by tidb’s dumpling is roughly like this:
CREATE TABLE emp (
id int(11) DEFAULT NULL,
name varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
It does not have the “DROP TABLE IF EXISTS t;” statement like in mysqldump.
You can batch drop all the tables that need to be imported first.

4
| username: 昵称想不起来了 | Original post link

:rofl: Just wrap a script yourself and delete it.

5
| username: dba-kit | Original post link

When TiDB Lightning encounters conflicts, it only has three options: replace/ignore/error, and it will not proactively drop tables. Therefore, such drop table operations need to be performed manually. Additionally, since you are exporting to an SQL file, you can actually use the myloader tool to import directly, and use the -o option to drop tables.

6
| username: redgame | Original post link

This is the situation, directly using DOP is very risky, it needs to be added by someone.

7
| username: zhanggame1 | Original post link

Dropping data during import is too dangerous, this feature is generally not available.