BUILDING Parts of this page describe what is not built yet, and each says so.

MySQL change capture

MySQL change capture is the source type mysql_cdc. It is meant to read every insert, update and delete of the tables you name from a MySQL server’s binary log and land them as a changelog in bronze. What you can do with it today is declare it and prepare the server for it. Reading the binary log is not built yet. Sources says what works and how to declare one; PostgreSQL change capture is the same kind of source for PostgreSQL.

Declare a MySQL source

Save this as sources/crm-db.yaml:

apiVersion: runninglake.io/v1alpha1
kind: Source
metadata:
  name: crm-db
spec:
  type: mysql_cdc
  host: crm-db.internal.example.com
  port: 3306
  database: crm
  credentials:
    user: rl_replication
    secretRef: crm-db-reader
  read:
    tables:
      - crm.orders
      - crm.customers
  land:
    schema: bronze.crm_db
  mode: continuous

• type is mysql_cdc, and mode is continuous, the only mode this type takes. A continuous source takes no schedule.
• host, port and database are required for this type. host is the server’s host name or its IPv4 address on its own, with no colon: an IPv6 address, or a host written with its port, is not accepted. The port goes in port.
• credentials.user is the MySQL user the source is meant to be read with. It is held only to the shape of a user name. credentials.secretRef names a SecretReference, as Credentials on Sources says.
• read.tables lists the tables to read, each written database.table, such as crm.orders. A pattern such as crm.* is refused: every name is one you typed.
• land.schema is the declared schema the tables land in. Land it in the bronze layer: rl diff does not check which layer it is in. What lands, and what you declare says what to declare beside the source.

rl diff judges the file offline with the rules the control plane runs when it plans a change. For a MySQL source it refuses, or warns about, the following. The Add a source form in the console plans the same file.

What the server needs

rl source prereqs writes seven steps for a MySQL source, in the order you do them. Each names the check that verifies it:

The user is the one the file names in credentials.user, and a source without one is refused in a sentence of its own, because the SQL names that user. The steps make it with CREATE USER IF NOT EXISTS and IDENTIFIED BY RANDOM PASSWORD, which needs MySQL 8.0.18 or later: MySQL shows the random password once, to whoever runs the statement, and the step says the password is chosen in your own store and set there. No step carries a password. The user is made for any host, and the step says to name the plane’s address instead if you restrict hosts. The steps grant REPLICATION SLAVE and REPLICATION CLIENT on all databases, and SELECT on exactly the tables the file names. The log settings are SET PERSIST statements on a server you run yourself, and the retention they set is three days.

On a server you run yourself, the last step grants FLUSH_TABLES on all databases, because the first copy is meant to take a global read lock on the whole server. In a measurement against MySQL 8.4.11, a user with only the grants above connected, read the table names, and then the first copy stopped; granting FLUSH_TABLES on all databases was enough for it to run. FLUSH_TABLES is a privilege of MySQL 8.0.23 and later. On MySQL 8.0.0 to 8.0.22 the statement is refused (that was not run), and the step says to grant RELOAD instead. The steps for RDS, Aurora and Cloud SQL, which rl source prereqs writes with -flavour, list the statement as a comment, because none of the grants was tried on those services. Those steps cite the providers’ documentation: AWS lists RELOAD among the privileges of the master user of RDS for MySQL and does not list FLUSH_TABLES; for Aurora MySQL it lists FLUSH_TABLES as well from version 3.09.0; Google lists FLUSH_TABLES among the cloudsqlsuperuser privileges starting with Cloud SQL for MySQL 8.0.26. The pages cited here do not say whether that user may grant these privileges to another user, except that the Aurora MySQL version 2 page lists GRANT OPTION for its master user. When the server’s own settings say it is RDS, Aurora or Cloud SQL and the login lacks the privilege, the connection test is meant to give a warning and not a failure. On any other server, including one whose settings do not say, it is meant to fail.

In the measurement, the first copy briefly paused writes on the whole server. The connector is set to take a global read lock to read the tables consistently. On an idle server the measurement held it for about half a second (0.49 seconds, over three tables, on one laptop). If a long statement is running, the lock waits behind it, and every later write to any database on the server waits behind the lock for as long as the connector is set to wait, which is 3 seconds: the measurement blocked writes for 3.5 seconds. When the wait ran out, the first copy stopped and its error named the LOCK TABLES privilege, which was not the cause. A restart of the first copy repeats the pause. Run the first copy in a quiet window. Skipping the lock loses rows: with the lock skipped, rows written while the first copy started were missing from both the copy and the changes after it, 432 to 524 of them in three runs at about 13,000 inserts a second, on one laptop.

If the server purges log that the source has not read yet, the connector is meant to stop and say so, and not copy the tables again by itself: a silent second copy would never see what was deleted in the gap. In the measurement the connector stopped, named the position that was gone and sent nothing. Holding a source under a name that says its log was purged is not built yet. Starting a new copy by hand is not built yet.

Flavours

The steps differ with the kind of server you run. Nothing detects the flavour of your server yet. You say which with -flavour, as in rl source prereqs -flavour rds crm-db, and without it the steps are written for a server you run yourself.

The user and the grants are the same in every flavour. The steps for RDS, Aurora and Cloud SQL were not run against those services.

The connection

A plane is meant to read the value from your store when it connects, and nothing connects yet. The connection is meant to be encrypted with TLS and not to fall back to plain text when the server offers none. In this default the server’s certificate is not meant to be checked: in the measurement, with a self-signed certificate, this mode connected and the certificate-checking mode did not. Choosing plain text is meant to be an explicit step. The Source file has no field for the TLS mode yet, so nothing in a file can choose plain text, and a server that offers no TLS is meant to be refused.

Table names

A table is written database.table, as the server spells it. Each lands in a table of the schema land.schema names, called by the last part of its name in lower case, so crm.orders lands in bronze.crm_db.orders. The last part of a name must be a name a table can have: it starts with a letter and holds only letters, digits, _ and -, at most 63 characters. In a repository that declares layers rl diff refuses a name that cannot be one, and two names that end alike, such as crm.orders and shop.orders, with source-landing-name.

Write database and table names in lower case. A MySQL server decides whether names are case sensitive with its lower_case_table_names setting. With the setting at 2, a table created with capital letters is spelled one way in the first copy and another in the changes after it, and the measurement found no spelling in the file that worked for such a table. The settings 0 and 1 were not measured. The connection test is meant to read the setting, and once it exists a capital letter in a declared database or table is meant to be refused on a server that folds case. Today nothing refuses a capital letter when you plan a change. The code that receives the changes already refuses a batch that holds a table whose name differs from a declared one only in capital letters, and it stops the source until you act; no plane runs that code yet.

Tables with no key

Copying the rows a table already holds is not built yet, and the plan is that it will land them in batches. If a table has no key, a batch of rows that are identical to each other can be mistaken for a batch it has already landed, and then those rows are lost from the first copy. It takes two or more whole batches whose rows for the table are the same, so a table with only a few identical rows is not affected. A table with a key is not affected, and nor are changes after the first copy. The connection test is meant to warn about each declared table that has no primary key and no unique index over columns that cannot be null; the warning does not fail the test. No connection test can run for a MySQL source yet, so nothing shows that warning yet. To clear it, add a primary key to the table, or a unique index over columns that cannot be null. If the table already holds identical rows, tell them apart first, for example with a new id column.

Values the connector changes

The part that receives the changes is meant to land every value the connector sends as its column says, or, when a value does not fit its column, as NULL with the value kept in the _rl_rescued column. It cannot know about a value the connector changed before sending it. Each of these was measured once, on MySQL 8.4.11, through the connector this build pins, by reading the same cell in both ways:

None of these is rescued: the value arrives already changed. Do not compare JSON text, or a FLOAT from the first copy with one from the changes after it, because the same cell has two spellings. The connection test is meant to warn about each column that can hold a zero or a partial date, on a server whose sql_mode lacks NO_ZERO_DATE or NO_ZERO_IN_DATE.

A TIME column is meant to land as a number of microseconds in a 64-bit integer column, and not as a time of day, because a MySQL TIME is an interval from -838:59:59 to 838:59:59. Every value is kept and the column’s type does not depend on its value: 838:59:59 lands as 3020399000000, and -01:02:03.456 as -3723456000. A BIGINT UNSIGNED is meant to land exactly, as a decimal of 20 digits, so 18446744073709551615 is not read as -1.

Changes the connector does not send

In the measurement, a TRUNCATE TABLE of a declared table produced no event, because this build leaves the connector at its default of skipping truncate events, so its rows vanish with no delete in the changelog. A RENAME TABLE of a declared table did not stop anything either: later inserts into the renamed table produced no event. After either, the table has to be copied again from the start. Starting a new copy by hand is not built yet.

What was measured

Every measurement on this page was made on one laptop, against MySQL 8.4.11 and Debezium Server 3.7.0 (the connector this build pins), with lower_case_table_names at 2, the binary log in row format with full images and GTID mode on. The steps for RDS, Aurora and Cloud SQL were not run against those services. Not measured: MySQL 5.7 and MariaDB, a replica or a failover, a server with GTID mode off, a server with lower_case_table_names at 0 or 1, a server that offers no TLS, and a managed service whose administrator cannot grant FLUSH_TABLES. On MySQL before 8.0.23, 5.7 included, and on such a service the privilege may be RELOAD instead. The statements of the self-managed steps were run on that one server only.

Remove a source

Remove a source as you remove any file: delete sources/crm-db.yaml, then plan and apply the change. Today that retires the source and removes the state the control plane recorded for it. It does not touch your server: the user, the grants and the settings you made from the steps stay until you remove them yourself. The tables declared for the source are files of their own, and they stay unless the same change deletes them.

Not built yet

• Reading the binary log is not built yet.
• Copying the rows a table already holds, before changes are followed, is not built yet.
• Landing changes in bronze as a changelog is not built yet.
• A connection test for MySQL is not built yet: it is meant to read your server’s settings, its case setting, its grants and the keys of your tables.
• A choice of TLS mode is not in the Source file yet.