In systems with a large amount of data and database access, it is necessary to spread load across several read-only "slave" databases that are replicated off of a "master" database. In the past, we used a system called "statement" based replication, meaning every statement run on master is then run on the slave instances. We wanted to move to "row" based replication. This means that whenever there is a change to any row of data on master, the state of that row is updated on all the slaves.
Databases can have triggers set to execute whenever certain kinds of statements are run, and we had several of these set on a slave database so as to avoid the load on master. Unfortunately, row based replication does not cause triggers to execute because it simply updates the "state" of the row, rather than executing a statement.
We implemented a java based solution to database triggers. We used a library called Maxwell which is designed to read the binary replication logs of a database, and produce generic application readable JSON. Maxwell is installed on the slave instance itself and reads all changes made on that database, and sends the JSON to a kafka topic. We built a new java app to listen to the kafka topic and determine any actions to take upon receiving a message, such as writing rows to a new table in the database.
This solution had two advantages, 1) Maxwell can read row-based replication logs, so we solve our original problem, and 2) the triggers are asynchronous, meaning that the database can continue as usual without waiting for triggers to finish executing. Our application independently processes the changes at it's own speed.