MySQL

Before you start

You need three things on the MySQL side: a server sanda can reach, a user that can only read, and sanda's address allowed through your firewall.

• A reachable server. MySQL 8.0 or newer, or MariaDB 10.5 or newer. sanda connects from the internet, so the host name must resolve on the public internet. A private address, or a name that only works inside your network, is refused before sanda tries.
• The connection details. The host name, the port (3306 unless you changed it) and the name of the database.
• A read-only user. sanda only ever reads. Give it a user of its own, so its access is easy to see and easy to revoke:

create user 'becca_reader'@'%' identified by 'use-a-long-random-password';
grant select, show view on acme.* to 'becca_reader'@'%';

Replace acme with your database name. The % lets the user sign in from any address. To accept logins only from sanda, replace it with sanda's fixed address from the allowlist page.

• sanda's address allowed. Add sanda's address to your provider's network rules or security group. See Allowlist sanda's address.

Connect MySQL

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See Add a connection.

What syncs

sanda reads the tables in the database you name. Each table you choose becomes a stream, and lands in your warehouse as its own table.

To limit which tables sanda can see, add a filter under Table Filters in Advanced settings. The field takes JSON: the database name, which must match Database, and a list of SQL like patterns for the table names to include.

[{"database_name": "acme", "table_name_patterns": ["orders%", "customers"]}]

Check Table and Column Access Privileges is on by default. During schema discovery sanda tests each table individually and leaves out any the user cannot read. A table missing from the stream list usually means the user lacks select on it.

How changes are found

Update Method, under Advanced settings, decides how sanda can tell what has changed since the last run.

The form opens on the cursor option. A table left on a full refresh mode is read whole on every run and replaced, so it does reflect deletes at the source, at the cost of reading everything each time. See sync modes.

Tips

• Read the big tables incrementally. The first run reads every table you choose in full. For a large table, switch to an incremental mode and choose a cursor column that only moves forward, such as updated_at. See choose what to sync.
• Watch TINYINT(1) columns. They arrive as booleans. If you use them for small counts, turn on Treat TINYINT(1) Columns as Integers under Advanced settings.
• Prefer a read replica. If you have one, point sanda at it so syncs never compete with your application. A replica that lags hands sanda older data.
• Reach a private database through a tunnel. SSH Tunnel Method sits under Advanced settings, with SSH Key Authentication and Password Authentication options. The jump server has to be reachable from the internet, and it needs sanda's address allowed, exactly as a database would. See Allowlist sanda's address.

Set up change data capture

Only do this if you need deletes captured and can change server settings. The server must write its binary log in row format, with full row images (binlog_format = ROW and binlog_row_image = FULL). The user needs reload, show databases and the replication privileges on top of select. These are global privileges, so they are granted on *.*:

grant reload, show databases, replication slave, replication client on *.* to 'becca_reader'@'%';

Choose Read Changes using Change Data Capture (CDC) under Update Method. Its own options sit under it:

• Configured server timezone for the MySQL source (Advanced). Set this only if the server's timezone is not a standard one.
• Invalid CDC Position Behavior (Advanced). MySQL removes old binary logs on its own schedule. If sanda's saved position has been removed by the time it next runs, Fail sync stops the sync until the connection is reset, which sanda support does for you, and Re-sync data starts a fresh full load automatically, which costs more and can lose data. It opens on Fail sync.

Keep binary log retention longer than the longest gap between syncs, so sanda's position is still there when the next run starts.

Configuration fields

What the connection form asks for. Required fields are marked; the rest are optional.

Encryption

The encryption method which is used when communicating with the database.

Choose one of the following. Each asks for its own fields.

preferred

No further fields.

required

No further fields.

verify_ca

verify_identity

SSH Tunnel Method

Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use.

Choose one of the following. Each asks for its own fields.

No Tunnel

No further fields.

SSH Key Authentication

Password Authentication

Update Method

Configures how data is extracted from the database.

Choose one of the following. Each asks for its own fields.

Scan Changes with User Defined Cursor

No further fields.

Read Changes using Change Data Capture (CDC)

Related