Which Backup Method Should You Choose: Full, Single Database, or Incremental?

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

Original topic: 全量、单库、增量应该选哪种备份方式

| username: bryanz

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.1.1
[Reproduction Path] None
[Encountered Problem: Problem Phenomenon and Impact] Online requirement, requires full backup, single database backup, incremental backup. How should I choose backup tools and strategies, and also ensure quick recovery? It should not affect online operations.
[Resource Configuration] None
[Attachment: Screenshot/Log/Monitoring] None

| username: TiDBer_小阿飞 | Original post link

Sorry, I can only provide translations for text content. Please provide the text you need translated.

| username: bryanz | Original post link

Thank you, boss. For the entire database, I plan to use BR, for a single database, I’ll use Dumpling, and for incremental backups, I’ll use log backups. The requirement is that a single database should be restorable even when it has data, so I’m using logical backup with Dumpling. I’m using BR for the entire database to have log increments. Is this backup plan feasible?

| username: kelvin | Original post link

This is correct.

| username: bryanz | Original post link

Since it is a central database involving multiple businesses, if a single business issue occurs, only the single business database needs to be rolled back.

| username: zhanggame1 | Original post link

There is no problem using it this way, but there might still be a data loss of one or two minutes in the log increment. If the data is more important, it is recommended to set up a CDC.

| username: bryanz | Original post link

Thank you, expert. Isn’t CDC a way to synchronize to another environment in real-time?

| username: TiDBer_小阿飞 | Original post link

For the entire database, I plan to use BR, and for a single database, I’ll use Dumpling. Both of these are fine. Although BR has incremental functionality, it is not real-time. PITR is not real-time either, so you need to choose a specific point in time when using it.

| username: bryanz | Original post link

Are there any other solutions for incremental backups, sir?

| username: Soysauce520 | Original post link

If it’s a very important database, you can set up a replica using CDC for synchronization.

| username: GreenGuan | Original post link

I feel that the backup method should be chosen based on the continuity of your business and the security of your data, as this affects your operational costs.

High data security, high continuity:
Full backup + Incremental backup + TiCDC

High data security, low continuity:
Full backup + Incremental backup

Low data security, low continuity:
Full backup

Regarding your mention of single database backup, I personally feel it is aimed at multiple databases mixed in the same environment. Single database recovery will improve your speed, and this requires specific analysis for specific issues.

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

For the entire database, use BR snapshots; for incremental backups of the entire database, use BR logs. BR can also be used for a single database, but restoring a single database to a specific point in time is relatively cumbersome.

| username: bryanz | Original post link

Thank you, master. There is no additional environment available for CDC synchronization.

| username: zhanggame1 | Original post link

Yes, it can be considered a type of backup method.

| username: bryanz | Original post link

Thank you, expert. The main considerations are the issues of single database corruption and full database corruption. For single database, we have decided to use dumpling for logical backups and br for full database snapshots. However, we are still unsure about how to handle incremental backups.

| username: zhanggame1 | Original post link

Based on the data volume, it’s generally unnecessary to perform incremental updates.

| username: bryanz | Original post link

Can BR also perform a specified time rollback for a single database?

| username: bryanz | Original post link

Thank you, master. The main reason is to be able to roll back quickly. Full backups have more steps for rollback. Incremental backups can be done with just one command.

| username: GreenGuan | Original post link

The essence of incremental recovery is to improve recovery speed. If incremental recovery is used, it must be based on full recovery. Please test whether the time for full + incremental recovery is acceptable for your business, considering the scale of your actual operations.

| username: DBAER | Original post link

Full backup + flashback is also possible.