Data Synchronization from MySQL to TiDB, and Then Writing Data Back from TiDB to MySQL

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

Original topic: mysql到tidb数据同步,之后tidb反向写入数据到mysql

| username: 烂番薯0

Hello everyone, I would like to ask a question. In MySQL, the table is set to auto-increment with auto_increment. After using DM to synchronize to TiDB, once the data is caught up and DM is stopped, TiCDC is used for reverse synchronization. At this point, if data is inserted into TiDB, will the auto-increment behave in a jumping manner rather than following auto_increment? When reverse synchronizing to MySQL, will there be errors due to the primary key values jumping too much? If using TiDB’s compatibility mode with auto_cache=1, what issues might arise? Is auto_cache=1 set during DM synchronization?

| username: 烂番薯0 | Original post link

Will this cause a write hotspot? If there is a write hotspot or read hotspot, how should it be resolved? What impact will the hotspot issue have on the business?

| username: Billmay表妹 | Original post link

Why do you need to sync back to MySQL again? You can just install another TiDB for backup.

| username: Fly-bird | Original post link

There will be no hotspots. auto_increment is incremental and will not repeat, so it has no impact on the business.

| username: 烂番薯0 | Original post link

Because the leader needs to back up the MySQL data to facilitate switching back to the business when needed, cousin.

| username: 芮芮是产品 | Original post link

It’s okay, it can be synchronized.

| username: 烂番薯0 | Original post link

Boss, regarding the question above, will TiDB have significant jumps if using auto_increment? Because if we switch to auto_random, it won’t be possible to sync back to MySQL.

| username: Jolyne | Original post link

The implementation principle of AUTO_INCREMENT:
Each auto-increment column uses a globally visible key-value pair to record the currently allocated maximum ID.
To reduce the network overhead of allocating auto-increment IDs in a distributed system, each TiDB node caches a unique ID segment.
When the currently pre-allocated ID segment is exhausted or TiDB restarts, a new ID segment will be requested again.

| username: Kongdom | Original post link

  1. In TiDB, auto-increment is jumpy in a cluster and incremental on a node.
  2. I haven’t used auto_cache=1, but based on the description, it should be consistent with MySQL.
  3. The hotspot issue is clearly pointed out in the official documentation, and corresponding solutions are provided.

Using AUTO_INCREMENT may cause hotspot issues in a production environment, so it is recommended to use AUTO_RANDOM instead. For details, please refer to TiDB Hotspot Issue Handling.