Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: DM MySQL多个库同步至TIDB DML仅影响本库数据 DDL影响所有数据
|
username: tjdagang1
【TiDB Usage Environment】Production\Test Environment\POC
【TiDB Version】TIDB v5.4.0 DM v2.0.1
【Problem Encountered】Using DM to synchronize MySQL to TiDB. The delete statement executed on the upstream MySQL only affects the synchronized data of the current database, while DDL statements affect all statements. Please help explain the principle or provide official documentation.
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
For example, both MySQL A and MySQL B databases have a table test_dm that synchronizes data to TiDB C database test_dm. The DM synchronization method is all without event filtering.
The result is the same whether the table has a primary key or not.
A Database
create table test_dm
(
id int,
name varchar(20),
port varchar(20) default '3306',
primary key (id)
);
B Database
create table test_dm
(
id int,
name varchar(20),
port varchar(20) default '3307',
primary key (id)
);
C Database
create table test_dm
(
id int,
name varchar(20),
port varchar(20) default '3308')
;
A Database
MySQL [test_db]> insert into test_dm(id,name) values(1,'AA'),(2,'BB');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [test_db]> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 1 | AA | 3306 |
| 2 | BB | 3306 |
+----+------+------+
2 rows in set (0.00 sec)
B Database
mysql> insert into test_dm(id,name) values(1,'AA'),(2,'BB');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 1 | AA | 3307 |
| 2 | BB | 3307 |
+----+------+------+
2 rows in set (0.00 sec)
C Database (all data synchronized)
MySQL [test_db]> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 1 | AA | 3306 |
| 2 | BB | 3306 |
| 2 | BB | 3307 |
| 1 | AA | 3307 |
+----+------+------+
4 rows in set (0.00 sec)
A Database
MySQL [test_db]> delete from test_dm where id=1;
Query OK, 1 row affected (0.00 sec)
MySQL [test_db]> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 2 | BB | 3306 |
+----+------+------+
1 row in set (0.01 sec)
C Database (only data with id=1 from A database is deleted)
MySQL [test_db]> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 2 | BB | 3306 |
| 2 | BB | 3307 |
| 1 | AA | 3307 |
+----+------+------+
3 rows in set (0.00 sec)
B Database
mysql> delete from test_dm where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 1 | AA | 3307 |
+----+------+------+
1 row in set (0.00 sec)
C Database (only data with id=2 from B database is deleted)
MySQL [test_db]> select * from test_dm;
+----+------+------+
| id | name | port |
+----+------+------+
| 2 | BB | 3306 |
| 1 | AA | 3307 |
+----+------+------+
2 rows in set (0.00 sec)
B Database
mysql> truncate table test_dm;
Query OK, 0 rows affected (0.00 sec)
C Database (all data cleared)
mysql> select * from test_dm;
Empty set (0.00 sec)
【Attachments】
- Relevant logs, configuration files, Grafana monitoring (https://metricstool.pingcap.com/)
- TiUP Cluster Display information
- TiUP Cluster Edit config information
- TiDB-Overview monitoring
- Corresponding module Grafana monitoring (if any, such as BR, TiDB-binlog, TiCDC, etc.)
- Corresponding module logs (including logs one hour before and after the issue)
For questions related to performance optimization and fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output.
|
username: 
|
username: