Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 怎么把一个库里的表复制一份到相同集群的另一个库里？
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
How to copy tables from one database to another within the same cluster?
For example, copy tables from the dbtest01 database to the dbtest02 database within the same cluster.
There are many tables, other than using insert into select.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
How about exporting and then importing the logic?
When using loader to import data, you cannot specify which database to import into; it can only be imported into the original database name.
After exporting, rename the file to the new database, and also change the schema file to the new database.
If the data does not move, first export the tables from the source database, then replace them with the new database tables, and then import them again.
Export all the data you want to import using Dumpling, either in SQL or CSV format. Then, batch rename the files, changing dbtest01 to dbtest02, and use Lightning to import them.
For small amounts of data, you can use Navicat directly. For more complex scenarios, you can use tools like DataX.
How can I ensure data consistency when exporting data with mydump? Adding the --single-transaction --master-data=2 parameters results in an error:
mysqldump: Couldn’t execute ‘FLUSH TABLES WITH READ LOCK’: FLUSH TABLES WITH READ LOCK is not supported. Please use @@tidb_snapshot (1105)
Use Dumpling, it will export consistent data by default.
If the data volume is not large, use mydumper.
Lightning can map during import.
I generally use ETL tools like Kettle for replication.
Export the data file and then import it.
Is kettle a table or a job?
Export and then re-import.
Lightning cannot be imported into other databases, right? I couldn’t find it in the documentation.
Yes, I usually make one table per job.
Just change the name. Lightning identifies the database and table names based on the source file’s filename.
|
username: TiDBer_yUoxD0vR
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: