Mysql

Calabi Connect's certified MySQL connector offers the following features:

• Multiple methods of keeping your data fresh, including Change Data Capture (CDC) using the binlog.
• All available sync modes, providing flexibility in how data is delivered to your destination.
• Reliable replication at any table size with checkpointing and chunking of database reads.

The contents below include a 'Quick Start' guide, advanced setup steps, and reference information (data type mapping and changelogs).

Please note the minimum required platform version is v0.58.0 to run source-mysql 3.4.0.

Quick Start

Here is an outline of the minimum required steps to configure a MySQL connector:

1. Create a dedicated read-only MySQL user with permissions for replicating data
2. Create a new MySQL source in the Calabi Connect UI using CDC logical replication
3. (Calabi Connect Only) Allow inbound traffic from Calabi Connect IPs

Once this is complete, you will be able to select MySQL as a source for replicating data.

MySQL Replication Modes

Change Data Capture (CDC)

Calabi Connect uses logical replication of the MySQL binlog to incrementally capture deletes in addition to new and updated records. To learn more how Calabi Connect implements CDC, refer to Change Data Capture (CDC). We generally recommend configure your MySQL source with CDC whenever possible, as it provides:

• A record of deletions, if needed.
• Scalable replication to large tables (1 TB and more).
• A reliable cursor not reliant on the nature of your data. For example, if your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (i.e. updated_at), CDC allows you to sync your table incrementally.

Connecting with SSL or SSH Tunneling

Connection via SSH Tunnel

You can connect to a MySQL server via an SSH tunnel.

When using an SSH tunnel, you are configuring Calabi Connect to connect to an intermediate server (also called a bastion or a jump server) that has direct access to the database. Calabi Connect connects to the bastion and then asks the bastion to connect directly to the server.

To connect to a MySQL server via an SSH tunnel:

1. While setting up the MySQL source connector, from the SSH tunnel dropdown, select:
   • SSH Key Authentication to use a private as your secret for establishing the SSH tunnel
   • Password Authentication to use a password as your secret for establishing the SSH Tunnel
2. For SSH Tunnel Jump Server Host, enter the hostname or IP address for the intermediate (bastion) server that Calabi Connect will connect to.
3. For SSH Connection Port, enter the port on the bastion server. The default port for SSH connections is 22.
4. For SSH Login Username, enter the username to use when connecting to the bastion server. Note: This is the operating system username and not the MySQL username.
5. For authentication:
   • If you selected SSH Key Authentication, set the SSH Private Key to the private Key that you are using to create the SSH connection.
   • If you selected Password Authentication, enter the password for the operating system user to connect to the bastion server. Note: This is the operating system password and not the MySQL password.

Generating a private key for SSH Tunneling

The connector expects an RSA key in PEM format. To generate this key:

ssh-keygen -t rsa -m PEM -f myuser_rsa

This produces the private key in pem format, and the public key remains in the standard format used by the authorized_keys file on your bastion host. The public key should be added to your bastion host to whichever user you want to use with Calabi Connect. The private key is provided via copy-and-paste to the Calabi Connect connector configuration screen, so it may log in to the bastion.

Limitations & Troubleshooting

To see connector limitations, or troubleshoot your MySQL connector, see more in our MySQL troubleshooting guide.

Data Type Mapping

MySQL data types are mapped to the following data types when synchronizing data. You can check test example values here. If you can't find the data type you are looking for, feel free to add a new test. If you do not see a type in this list, assume that it is coerced into a string. We are happy to take feedback on preferred mappings.

Any database or table encoding combination of charset and collation is supported. Charset setting however will not be carried over to destination and data will be encoded with whatever is configured by the destination. Please note that byte arrays are not yet supported.

MySQL Data Type Mapping

MySQL Type	Resulting Type	Notes
bit(1)	boolean
bit(>1)	base64 binary string
boolean	boolean
tinyint(1)	boolean
tinyint(>1)	integer
tinyint(>=1) unsigned	integer
smallint	integer
mediumint	integer
int	integer
bigint	integer
float	number
double	number
decimal	number
binary	string
blob	string
date	string	ISO 8601 date string. ZERO-DATE value will be converted to NULL. If column is mandatory, convert to EPOCH.
datetime, timestamp	string	ISO 8601 datetime string. ZERO-DATE value will be converted to NULL. If column is mandatory, convert to EPOCH.
time	string	ISO 8601 time string. Values are in range between 00:00:00 and 23:59:59.
year	integer	Doc
char, varchar with non-binary charset	string
tinyblob	base64 binary string
blob	base64 binary string
mediumblob	base64 binary string
longblob	base64 binary string
binary	base64 binary string
varbinary	base64 binary string
tinytext	string
text	string
mediumtext	string
longtext	string
json	serialized json string	E.g. {"a": 10, "b": 15}
enum	string
set	string	E.g. blue,green,yellow
geometry	base64 binary string