I have a need to maintain a copy of an external database (including some additional derived data). With the same set of hardware, which one of the following solutions would give me faster consistency (low lag) with high availability? Assume updates to external database happen at 1000 records per second.
a) Create local mysql replica of an external db using mysql 5.7 replication (binary log file mechanism).
OR
b) Get real time Kafka events from external system, doing HTTP GET to fetch updated object details and use these details to maintain a local mysql replica.
The first will almost certainly give you lower lag (since there are just two systems and not three). Availability is about same - Kafka is high availability, but you have two databases on both sides anyway.
The second is better if you think you'll want to send the data in real-time to additional system. That is: MySQL1 -> Kafka -> (MySQL2 + Elastic Search + Cassandra + ...)