CDC和DSG等复制软件的原理，表存在主键才能复制的原因？而mysql binlog主从复制却不需要？

李大川的博客

已于 2024-03-29 17:07:43 修改

阅读量575

点赞数 1

文章标签：数据库

于 2023-09-08 17:07:45 首次发布

本文链接：https://blog.csdn.net/liys0811/article/details/132763793

版权

CDC和DSG复制的原理是把页改动的Log转换成逻辑日志去目标端重做。比如源端一条sql update完全一样的两行数据,那CDC会把两行的改动转换成两个update sql，第一条sql执行完就把两行数据都更新了，那么第二条再去执行就会报错找不到该行。所以复制软件要求必须要有主键，不存在完全相同的两行数据。（CDC复制软件是以主键作为复制根本的，如果存在主键，源和目标的主键一样，哪怕其他列不同，CDC也会认为是同一行完全相同的数据。如果不存在主键，则会全行所有的列对比，目标端插入(1，lisi),源端插入(1，zhangsan88),源端更新id=1的行，目标端也更新成功了，说明CDC认为主键一样就是完全一样的行），另外一个原因是没有主键的时候，目标端redo log会非常慢，比如Update,delete一行数据时会全表扫描。还有一个非常重要的原因，如果没有主键CDC和DSG长时间复制后会造成数据不一致，比如源库有2行完全一样的数据，源库只删除其中的1行（limit），但是目标端一下子就会删除2行，而Mysql的binlog里会有隐藏的rowid,所以也会只删除一行，从而保证数据一致。

测试场景1：预定没有失败，原因是两行不是完全相同的行
CDC源端：
$ db2 "insert into test values(88888888,'8888-0001','8888-0001')"
DB20000I The SQL command completed successfully.
$ db2 "insert into t
$ db2 "insert into test values(88888888,'8888-0002','8888-0002')"
DB20000I The SQL command completed successfully.
$ db2 "update test set id=66666666 where id=88888888"
DB20000I The SQL command completed successfully.
$

CDC目标端：

[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test"

ID NAME1 NAME2
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
66666666 8888-0001 8888-0001
66666666 8888-0002 8888-0002

2 record(s) selected.

测试场景2：CDC报如下错误，符合预期。CDC目标端在一个事务里执行两条sql，第一条sql更新了两行，第二条sql再执行的时候发现找不到ID: [88888888] NAME1: [8888-0001] NAME2: [8888-0001]这一行了。然后事务回滚，两条都失败了

无法更新表 DB2INST1.TEST 中的行。ID: [88888888] NAME1: [8888-0001] NAME2: [8888-0001] 。
IBM InfoSphere Data Replication 已应用一批 2 行，JDBC 驱动程序发回报告已对这批中的条目 1 更新 0 行。这可能意味着，无法在数据库中找到这批中的该行。请查看文档，关闭批处理或减小批处理大小，然后重试操作以缩小无法应用的行数。

CDC源端：
$ db2 "delete from test"
DB20000I The SQL command completed successfully.
$ db2 "insert into test values(88888888,'8888-0001','8888-0001')"
DB20000I The SQL command completed successfully.
$ db2 "insert into test values(88888888,'8888-0001','8888-0001')"
DB20000I The SQL command completed successfully.
$ db2 "update test set id=66666666 where id=88888888"
DB20000I The SQL command completed successfully.

CDC目标端：
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test"

ID NAME1 NAME2
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
88888888 8888-0001 8888-0001
88888888 8888-0001 8888-0001

2 record(s) selected.

但是mysql binlog主从复制却不需要一定有主键，这是为什么呢？

主：

mysql> create table test(id int,name1 varchar(100),name2 varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(88888888,'8888-0001','8888-0001')
-> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(88888888,'8888-0001','8888-0001');
Query OK, 1 row affected (0.01 sec)

mysql> update test set id=66666666 where id=88888888;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

从：
[root@localhost:mytest1]>select * from test;
+----------+-----------+-----------+
| id | name1 | name2 |
+----------+-----------+-----------+
| 66666666 | 8888-0001 | 8888-0001 |
| 66666666 | 8888-0001 | 8888-0001 |
+----------+-----------+-----------+
2 rows in set (0.00 sec)

[root@localhost:mytest1]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.153.119.5
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 2874
Relay_Log_File: t3-dtpoc-dtpoc-web05-relay-bin.000002
Relay_Log_Pos: 2386
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

通过mysqlbinlog查看binlog，确实是一条update语句生成了两条update sql,当第一条sql备机执行完后，第二条sql没有执行成功也没有报错？

./mysqlbinlog -v /testdata/mysql/log/bin/mysql-bin.000014
### UPDATE `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### SET
### @1=66666666
### @2='8888-0001'
### @3='8888-0001'
### UPDATE `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### SET
### @1=66666666
### @2='8888-0001'
### @3='8888-0001'
# at 2843

其实第二个sql执行Update，发现这条不存在不会报错，也会执行成功，只不过更新了0行而已。

mysql> select * from test;
+----------+-----------+-----------+
| id | name1 | name2 |
+----------+-----------+-----------+
| 66666666 | 8888-0001 | 8888-0001 |
| 66666666 | 8888-0001 | 8888-0001 |
+----------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set id=88888888 where id=66666666 and name1='8888-0001' and name2='8888-0001';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> update test set id=88888888 where id=66666666 and name1='8888-0001' and name2='8888-0001';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> update test set id=88888888 where id=66666666 and name1='8888-0001' and name2='8888-0001';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

如果我们插入9行数据，然后全部删除，binlog会如何记录，会把一条sql生成9条sql，然后在备端执行9条sql.
mysql> select * from test;
+----------+-----------+-----------+
| id | name1 | name2 |
+----------+-----------+-----------+
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
| 88888888 | 8888-0001 | 8888-0001 |
+----------+-----------+-----------+
9 rows in set (0.00 sec)

mysql> delete from test;
Query OK, 9 rows affected (0.00 sec)

### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
### DELETE FROM `mytest1`.`test`
### WHERE
### @1=88888888
### @2='8888-0001'
### @3='8888-0001'
# at 6462

李大川的博客

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
CDC和DSG等复制软件的原理，表存在主键才能复制的原因？而mysql binlog主从复制却不需要？

比如源端一条sql update完全一样的两行数据,那CDC会把两行的改动转换成两个update sql，第一条sql执行完就把两行数据都更新了，那么第二条再去执行就会报错找不到该行。CDC目标端在一个事务里执行两条sql，第一条sql更新了两行，第二条sql再执行的时候发现找不到ID: [88888888] NAME1: [8888-0001] NAME2: [8888-0001]这一行了。
复制链接

扫一扫