Change Data Capture in MySQL

Question

In my database I want to track the value of a column whenever it is changed .In a way such that every time value of a field changes we can know what was the previous value .It would be awesome to assign timestamps to each change.I am using MySQL as the database server.

Answer 1

I recommend you take a look at Debezium an open-source CDC platform which, amongst others, also supports MySQL.

You can use Debezium to stream changes into Apache Kafka, but by means of its embedded mode you can also use the Debezium connectors as a library in your Java applications and easily propagate data changes to other streaming APIs such as Kinesis etc.

Disclaimer: I'm the Debezium project lead.

Answer 2

To do that you need to create a trigger, which is an event that is fired when an INSERT, UPDATE, DELETE occurs on a given table.

Look at the documentation for CREATE TRIGGER.

For what you want to do, you probably need to have an auxiliary table that has a row inserted for each INSERT/UPDATE on the main table, AFTER it happens containing the ID of the row changed, the old value the new value and a timestamp.

Answer 3

Sorry for the very late answer, I would like to mention an other tool that hasn't been invoked in the other answer maxwell it's a CDC that reads the binlogs and sends messages on Kafka. The big advantage to this solution is the fact that it doesn't require any change to MySQL servers (no triggers to create ..).

Answer 4

Triggers are not always the best way to record change events in database. As triggers can get invalidated once the schema of table changes, and it would in turn cause the actual table operation to fail.

For mysql, you need to capture the change events from binary log file. Mysql provides API to read binary log ( post version 5.6).

You may also want to look into Flexviews , which provides Oracle like Materialized View logs to record the change events.