anchor.diff:  ----
   
   == Conclusion
  -Debezium is an excellent change data capture tool under active development, and it’s a pleasure to be a part of its community. We’re excited to use incremental snapshots in production here at Shopify. If you have similar database usage restrictions, check out the read-only incremental snapshots feature. Many thanks to my team and the Debezium team without whom this project wouldn't happen.+Debezium is an excellent change data capture tool under active development, and it's a pleasure to be a part of its community. We're excited to use incremental snapshots in production here at Shopify. If you have similar database usage restrictions, check out the read-only incremental snapshots feature. Many thanks to my team and the Debezium team without whom this project wouldn't happen.
  
positive.code: 
  If you were to modify any record in the `orders` table while the snapshot is running, this would be either emitted as a `read` event or as an `update` event, depending on the exact timing and sequence of things.
  
  As the last step, let’s terminate the deployed systems and close all terminals:
  [source,bash]
  ----
  # Shut down the cluster
  
negative.code: ---
  layout: post
  title:  Read-only Incremental Snapshots for MySQL
  date:   2022-03-25
  tags: [ mysql, snapshots ]
  author: kgalieva
  ---
  
  The engineering team at Shopify recently improved the Debezium MySQL connector so that it supports incremental snapshotting for databases without write access by the connector, which is required when pointing Debezium to read-only replicas. In addition, the Debezium MySQL connector now also allows schema changes during an incremental snapshot. This blog post explains the implementation details of those features.
  
  +++<!-- more -->+++
  
  == Why read-only?
  
  Debezium added the link:/documentation/reference/stable/connectors/mysql.html#mysql-incremental-snapshots[incremental snapshotting feature] in the 1.6 release, after Netflix had announced https://arxiv.org/pdf/2010.12597v1.pdf[their change data capture framework]. At Shopify, https://shopify.engineering/capturing-every-change-shopify-sharded-monolith[we use Debezium for change data capture (CDC)], and we were looking forward to being the early adopters. Besides, we wished to have a solution that is writes and locks-free.
  
  The no writes solution allows to capture changes from read-replicas and provides the highest guarantee that CDC won't cause data corruption on the database side.
  
  We've had to coordinate the snapshotting with migrations in the past since schema migrations blockades have affected other projects' development. The solution was to run snapshots only on weekends and as a result, we tried to snapshot as rarely as possible. We saw the opportunity to improve this part of the process as well.
  
  This blog post dives into technical details of the read-only incremental snapshots implementation including lock-free schema changes handling during the incremental snapshot in MySQL connector.
  
  == Incremental snapshots
  
  The link:/blog/2021/10/07/incremental-snapshots/[Incremental Snapshots in Debezium] blog post covers the default implementation in detail. The algorithm utilizes a signaling table for two types of signals:
  
  1. `snapshot-window-open/snapshot-window-close` as watermarks
  2. `execute-snapshot` as a way to trigger an incremental snapshot
  
  For the read-only scenario, we needed to replace both types of signals with alternatives.
  
  === SHOW MASTER STATUS for high and low watermarks
  
  The solution is specific to MySQL and relies on https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html[global transaction identifiers (GTIDs)]. Therefore, you need to set `gtid_mode` to `ON` and configure the database to preserve GTID ordering if you're reading from the read replica.
  
  Prerequisites:
  
  [source]
  ----
  gtid_mode = ON
  enforce_gtid_consistency = ON
  if replica_parallel_workers > 0 set replica_preserve_commit_order = ON
  ----
  
  The algorithm runs a https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html[SHOW MASTER STATUS] query to get the executed GTID set before and after the chunk selection:
  
  [source]
  ----
  low watermark = executed_gtid_set
  high watermark = executed_gtid_set - low watermark
  ----
  
  In the read-only implementation, the watermarks have a form of GTID sets, e.g. like this:
  `2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19`
  
  Such watermarks do not appear in the binlog stream. Instead, the algorithm compares each event's GTID against the in-memory watermarks.
  The implementation ensures there are no stale reads and that a chunk only has changes that are not older than events up to low watermark.
  
  === Deduplication algorithm with read-only watermarks
  
  In pseudo-code, the algorithm for deduplicating events read from the binlog and events retrieved via snapshot chunks looks like this:
  
  [source]
  ----
  