How to Backup and Restore Historical Partitions of Partitioned Tables in TiDB

Translated
1

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

Original topic: TiDB如何对分区表的历史分区做备份恢复

| username: yeminhua

We have many tables partitioned by day or month, and many historical partitions need to be backed up and deleted, then restored as needed by the application based on the specified date. How is partition backup and recovery generally handled in production for partitioned tables? Is it only possible to do logical exports using Dumpling, and is it true that you can’t specify partition names but have to filter them one by one using the WHERE condition?

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

You can switch data to a non-partitioned table through exchange, and br can back up the non-partitioned table.
Additionally, in dumpling, you can specify the SQL as SELECT * FROM ry.t1_partition PARTITION (p202307).
Wouldn’t that work too?

3
| username: zhanggame1 | Original post link

There’s nothing inconvenient about using “where” in dumpling.

4
| username: redgame | Original post link

Create a script with regular variables.

5
| username: cassblanca | Original post link

Specifying a partition is also feasible: SELECT * FROM [table_name] PARTITION (******)

6
| username: TiDBer_vfJBUcxl | Original post link

You can use scripts for backup.

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

Additional processing with scripts.

8
| username: yeminhua | Original post link

Thank you, I wrote a script to back up using Dumpling with specified partition SQL.

9
| username: yeminhua | Original post link

Got it, thank you.

10
| username: yeminhua | Original post link

Okay, I’ll write the script myself, thanks.

11
| username: yeminhua | Original post link

Yes, that’s how it’s done.

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.