Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: BR恢复增量数据表自增ID没有变化。
[TiDB Usage Environment]
Testing
[TiDB Version]
v5.3.1
[Reproduction Path]
- Three-node cluster/single-node cluster, single database single table;
- Perform a full backup for the first time, then perform an incremental backup every ten minutes;
- Store backup data in S3 storage;
- During data recovery, both full recovery and incremental recovery are successful, and the data volume is consistent;
- Error occurs when writing data. After checking, it is found that the AUTO_INCREMENT value of the table is inconsistent. Further inspection reveals that the AUTO_INCREMENT value remains at the value during the full recovery and does not change during the incremental recovery.
[Encountered Problem: Problem Phenomenon and Impact]
After recovering incremental data, the AUTO_INCREMENT value of the table is inconsistent, causing subsequent data writing errors, primary key duplication, and inability to write new data.
[Resource Configuration]
3 machines with 2*4 CPUs, 50G data disk, 30G system disk
The reasons might be as follows:
It is recommended to manually adjust the offset to ensure continuity, as shown below:
Thank you for the guidance, I will try again today~
Yesterday, I referred to the official documentation and set the value of another configuration AUTO_ID_CACHE to 1, but the same issue still exists. It doesn’t seem to be much related to the cached auto-increment ID range.
After I modified the configuration of this value, I observed that the auto-increment ID is now continuous.
Moreover, I did not perform a restart. I used two methods for recovery: one was to back up all the data, then delete the database and restore it within the same cluster; the other was to deploy two clusters one after the other using the same method, with identical system and default cluster configurations. After backing up the data from cluster one, I directly restored it to cluster two. The same issues were present in both cases.
There will be conflicts in handling auto-increment with explicit and implicit inserts, as clearly stated in the official documentation.
Therefore, the best practice is to involve manual intervention to ensure timely handling of offsets and avoid conflicts.
Oh, I see, thank you very much~
I have two more questions:
First, could you provide the official documentation link for implicit and explicit insertion handling of auto-increment? I would like to study it.
Second, regarding incremental backups, is it currently impossible to avoid the aforementioned issues without manual intervention?
Thank you very much~
Additionally, the official documentation currently states that BR’s incremental backup is experimental and not recommended for use in production environments. Are there other issues with incremental backups that could lead to unreliable or problematic backup data?
Confirmed, we are all implicitly inserting
It is not recommended for use in a production environment, indicating that there are still uncontrollable factors. You can test and verify it yourself.
For data backup, it is better to follow the official recommendations to avoid uncontrollable situations (you can set different backup strategies according to your own scenario requirements).
Remember to select the “Best Answer”~
I see that the officially recommended backup method for version 5.3 is BR 
For version 5.3, are there any other recommended methods that combine full and incremental backups?
Physical backup is the only valuable method…
See if logical backup and tiCDC can meet your needs…
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: TiDBer_YEqOV3Hn
|
username: 
|
username: 
|
username: