Description:
The oracle database can create materialized view log for a table to capture the data change, I think MySQL can also have this features.
The materialized view log is an extra table which record the row changed as following.
MLOG$_<base_table_name> (seqno bigint auto_increment, dmltype {I|U|D}, primary columns of the base table).
For insert operation, insert into MLOG$ table by values (seqno, 'I', primary key value list);
For delete operation, insert into MLOG$ table by values (seqno, 'D', primary key values list);
For non-primary key update, insert into MLOG$ table by values (seqno, 'U', primary key values list);
For Primary key update, insert into MLOG$ table with values (seqno, 'D', old primary key values list), (seqno, 'I', new primary key values list);
We can implement this by triggers, but it's not so efficient. This is a possible solution for changed data capture without binary logs.
How to repeat:
N/A
Suggested fix:
N/A
Description: The oracle database can create materialized view log for a table to capture the data change, I think MySQL can also have this features. The materialized view log is an extra table which record the row changed as following. MLOG$_<base_table_name> (seqno bigint auto_increment, dmltype {I|U|D}, primary columns of the base table). For insert operation, insert into MLOG$ table by values (seqno, 'I', primary key value list); For delete operation, insert into MLOG$ table by values (seqno, 'D', primary key values list); For non-primary key update, insert into MLOG$ table by values (seqno, 'U', primary key values list); For Primary key update, insert into MLOG$ table with values (seqno, 'D', old primary key values list), (seqno, 'I', new primary key values list); We can implement this by triggers, but it's not so efficient. This is a possible solution for changed data capture without binary logs. How to repeat: N/A Suggested fix: N/A