Synchronizing TiDB to MySQL Cluster

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

Original topic: tidb同步至mysql集群

| username: 烂番薯0

Hello everyone, I have a question. We are currently synchronizing MySQL 5.7 to TiDB and plan to use DM for synchronization. Since the data volume is only a few tens of GB, using DM feels reliable. However, the leadership is concerned about potential issues with the business after switching to TiDB, so they are considering using CDC for reverse synchronization to MySQL. But we have a table that uses auto-increment and not TiDB’s auto_random. In this case, how can we use CDC for reverse synchronization to MySQL? If we switch the auto-increment to auto_random, how can we synchronize it to MySQL?

| username: WalterWj | Original post link

Why not find a test environment and try migrating directly?

| username: 有猫万事足 | Original post link

Without using auto_random, use non-clustered index tables + SHARD_ROW_ID_BITS + PRE_SPLIT_REGIONS to eliminate write hotspots.

This approach may result in some performance loss, but if you need to be cautious, you can temporarily set aside performance considerations.

| username: Kongdom | Original post link

:thinking: Can’t we just replace MySQL with TiDB directly? It won’t have stored procedures, right?

| username: 烂番薯0 | Original post link

Some data is inconsistent in the test environment, making it impossible to test certain features.

| username: 烂番薯0 | Original post link

No, I do plan to switch, but for safety reasons, I still need to synchronize back to MySQL.

| username: cassblanca | Original post link

Feel free to switch. We directly switched from MySQL 5.7 to TiDB 6.5 without any issues.

| username: WalterWj | Original post link

Testing is the guarantee; technical analysis is not as reliable as testing.

| username: 像风一样的男子 | Original post link

Your leader lacks confidence. Set up a complete test environment and conduct thorough testing to gain assurance.

| username: ShawnYan | Original post link

Your requirement is mysql → tidb → mysql?
Why not consider using ticdc?

| username: zhanggame1 | Original post link

My understanding is that it needs to be bidirectional.

| username: 烂番薯0 | Original post link

The main leaders want to synchronize TiDB and then have TiDB reverse synchronize with MySQL. However, in our test environment, we only have one machine, so the auto-increment is always 1. But in production, it’s a cluster, so we can’t test it in the test environment.

| username: cassblanca | Original post link

Tidb also supports auto-increment AUTO_INCREMENT | PingCAP Documentation Center. Synchronizing to MySQL through Ticdc is also no problem at all.

| username: 啦啦啦啦啦 | Original post link

After the application is stopped, stop DM, then start ticdc to synchronize to MySQL. In case of any issues, you can roll back to MySQL. auto_random is not a mandatory option, having an auto-increment column does not affect synchronization.

| username: redgame | Original post link

Sure, you can use TiCDC.

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

Using auto-increment instead of auto_random doesn’t affect synchronization through CDC, and switching to auto_random also doesn’t affect synchronization…

| username: zhanggame1 | Original post link

The auto_random feature in TiDB also does not affect CDC synchronization; synchronization only requires a primary key.

As for auto_random itself, it is not necessary. Using auto-increment in TiDB is also fine. In TiDB version 6.5 and above, enabling AUTO_ID_CACHE 1 can also improve auto-increment performance.

| username: 烂番薯0 | Original post link

If you use auto_increment, TiDB cannot guarantee the sequence. For example, instead of 1, 2, 3, 4, 5, TiDB might generate 1, 2, 3, 4, 5, 1001, 1002. If you then use CDC, will MySQL also become 1, 2, 3, 4, 1001, 1002?

| username: 烂番薯0 | Original post link

The issue is that I am using DM for synchronization. After creating this table, there is no AUTO_ID_CACHE 1, and there is no way to modify the table.

| username: longzhuquan | Original post link

You need to check what your DML statements are. Are the so-called 12345, 1001, 1002 modified through DML statements (generally, auto-increment columns are automatically filled)? If both sides are automatically filled, there will be a state of inconsistency in the auto-increment columns. It is more recommended to use AUTO_ID_CACHE 1 from the beginning in TiDB. After DM synchronizes the table structure, it can be manually modified.