MYSQL - CDC Capturing

[Akshath Hegde]

In oracle I can track the CDC on table level by using ORA_ROWSCN field.

In oracle by default table is created with NOROWDEPENDENCIES attribute i.e, it disables row level tracking and we can track till block level. i.e, we can track data is changed in which block.

Example :


SQL> create table test(id int);
SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403623165 1
8445403623165 2

In the above output we can see both rows have same value.. because they belong to same data block in oracle.

Now iam updating only one row as below

SQL> update test set id=3 where id = 1;

1 row updated.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403629328 3
8445403629328 2


It is updated ROW SCN value value for both rows because they belongs same data block.
But to extract only the row which got updated i can do row level tracking in oracle by using ROWDEPENDENCIES attribute and it is as below.

SQL> create table test(id int) ROWDEPENDENCIES;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403646389 1
8445403646389 2

SQL> update test set id=3 where id = 1;

1 row updated.

SQL> commit;


SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403647584 3
8445403646389 2

Now in the above output i can see ROW SCN value is updated only for thr row which is updated but not for whole block.

How we can achieve in MYSQL ???


Please suggest solution for above.