MySQL

The MySQL source connector:

• Keeps your destination up to date in several ways, including Change Data Capture (CDC) from the binary log.
• Supports every sync mode, so you choose how data lands in your destination.
• Handles tables of any size: reads are split into chunks and progress is checkpointed, so an interrupted sync resumes instead of starting over.

This page has a quick start, reference material such as the data type mapping, and limitations and troubleshooting.

Quick Start

The minimum setup for a MySQL source:

1. Create a read-only MySQL user that can replicate data.
2. Turn on binary logging on your MySQL server.
3. Create the MySQL source in Pipeloom, using CDC.

After that, MySQL is ready to use as a source in your syncs.

Step 1: Create a read-only MySQL user

We recommend a dedicated read-only user for Pipeloom. You can use an existing MySQL user instead.

Create the user:

CREATE USER <user_name> IDENTIFIED BY 'your_password_here';

Then give it read-only access, including the permissions CDC needs:

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <user_name>;

If you use the STANDARD replication method instead of CDC (not recommended), the user only needs SELECT.

Step 2: Turn on binary logging

CDC reads from MySQL's binary log (binlog), so it has to be on. Most cloud providers (AWS, GCP and so on) have a one-click option for it.

If you run MySQL yourself, set these properties in the server's configuration file:

server-id                  = 223344
log_bin                    = mysql-bin
binlog_format              = ROW
binlog_row_image           = FULL
binlog_expire_logs_seconds  = 864000

Step 3: Create the MySQL source in Pipeloom

Create a new source and choose MySQL. You can do this from the Sources page, or from a Source step on a pipeline canvas.

Fill in the form:

1. Enter your MySQL database's host name, port and database name.
2. Enter the user and password you created in Step 1.
3. Choose an encryption (SSL) mode. Most setups use required or verify_ca. Both always encrypt the connection; verify_ca also checks your database's certificate. See SSL modes for the others, and for SSH tunnels.
4. Under Update Method, choose Read Changes using Change Data Capture (CDC).

Save the source. Pipeloom tests the connection to your database, and the source is ready once the test passes.

Replication methods

Change Data Capture (CDC)

CDC uses logical replication of the MySQL binlog to pick up deleted rows as well as new and changed ones. See change data capture for how it works. We recommend CDC wherever you can use it, because it gives you:

• A record of deleted rows, if you need one.
• Replication that scales to large tables (1 TB and more).
• A reliable cursor that doesn't depend on your data. For example, a table with a primary key but no good cursor column (such as updated_at) can still be synced incrementally.

CDC delivers every change at least once. For incremental CDC syncs, a table needs at least one primary key; tables without one can still be copied with CDC, but only in full refresh mode.

Advanced CDC settings

• Initial Load Timeout in Hours: how long the first copy of the data may run before the connector switches to catching up on the binlog. Between 4 and 24 hours; the default is 8.
• Invalid CDC Position Behavior: what to do if the saved binlog position is no longer valid, for example because the binlog files it points to have been removed. Fail sync (the default) stops, and you have to clear the sync's data before it can continue. Re-sync data starts a full copy automatically, which costs more and can lose changes made in the meantime.
• Configured server timezone: only needed when your server's time zone isn't a valid IANA time zone. Enter the IANA equivalent (for example, Europe/Berlin for CEST).

Standard

Standard replication reads new and changed rows using a cursor column of your choice (for example updated_at). We generally recommend against it, but it suits these cases:

• Your MySQL server doesn't expose the binlog.
• Your data set is small, and you just want a snapshot of each table in the destination.

Connecting with SSL or an SSH tunnel

SSL modes

• preferred: encrypt unless the database doesn't support it.
• required: always encrypt. The connection fails if the database doesn't support encryption. This is the default.
• verify_ca: always encrypt, and check that the database has a valid SSL certificate.
• verify_identity: always encrypt, and check the database's identity.

For verify_ca and verify_identity, paste the CA certificate. A Client certificate and Client Key are optional, but if you use one you need the other too. The Client key password is optional and generated for you if you leave it empty.

SSH tunnel

With an SSH tunnel, Pipeloom connects to an intermediate server (a bastion or jump server) that can reach your database, and the bastion then connects to the database for it.

To set it up:

1. While creating the MySQL source, choose one of these under SSH tunnel:
   • SSH Key Authentication, to use a private key
   • Password Authentication, to use a password
2. For SSH Tunnel Jump Server Host, enter the host name or IP address of the bastion server.
3. For SSH Connection Port, enter the bastion's SSH port. The default is 22.
4. For SSH Login Username, enter the user to sign in to the bastion as. Note: this is an operating system user, not a MySQL user.
5. Then:
   • For SSH Key Authentication, paste the private key for that user into SSH Private Key.
   • For Password Authentication, enter that operating system user's password. Note: this is the operating system password, not the MySQL password.

Generate a private key for the SSH tunnel

The connector expects an RSA key in PEM format. To create one:

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

This writes the private key in PEM format, and the public key in the usual authorized_keys format. Add the public key to the bastion, for the user Pipeloom signs in as. Paste the private key into the source's settings so Pipeloom can sign in to the bastion.

Optional settings

Table Filters

Copy only some tables from the database. Each filter names a database (it should match the Database field) and one or more table name patterns, written as SQL LIKE patterns (for example orders_%).

JDBC URL Params

Extra JDBC connection parameters, as key=value pairs separated by &, for example key1=value1&key2=value2&key3=value3. See Troubleshooting for parameters that fix common connection problems.

Checkpoint Target Time Interval

How often, in seconds, a stream saves its progress when it can. The default is 300 (5 minutes). See Query timeout if your server limits how long a query may run.

Max Concurrent Queries to Database

The most queries the connector runs against the database at once. Leave it empty to let Pipeloom choose.

Check Table and Column Access Privileges

On by default. While discovering the schema, the connector checks access to each table and view one by one, and leaves out any table, view or column the user can't read. With a very large schema this can make discovery slow; turn it off if so.

Treat TINYINT(1) Columns as Integers

Off by default, so TINYINT(1) columns are copied as booleans. Turn it on to copy them as integers instead, both in regular and CDC reads.

Data type mapping

MySQL types are copied as the types below. A type not listed here is copied as a string.

Every combination of character set and collation is supported. The character set itself isn't carried over: the destination stores the text in whatever encoding it's configured with. Byte arrays aren't supported yet.

Limitations

• Supported MySQL server versions: 8.4, 8.0, 5.7 and 5.6.
• We recommend turning SSL on.

Specific MySQL services

Not every MySQL deployment behaves the same. These are known issues that depend on how or where MySQL runs.

• DigitalOcean managed MySQL clears binary logs periodically by default, regardless of MySQL's own replication settings. To use CDC, ask DigitalOcean support to turn this off.
• PlanetScale limits a query to 100K rows by default. The connector normally reads in batches of up to 500K rows for speed.
• MariaDB is a fork of MySQL and works with this connector, but CDC may have limitations. If CDC fails against MariaDB, switch to the standard replication method.

Troubleshooting

Common configuration errors

• Zero dates in datetime columns. MySQL allows zero values for dates and times instead of NULL, and other databases may reject them. To work around this, add zerodatetimebehavior=Converttonull to JDBC URL Params.
• Amazon RDS MySQL or MariaDB: Cannot create a PoolableConnectionFactory. Add enabledTLSProtocols=TLSv1.2 to JDBC URL Params.
• Amazon RDS MySQL: Error: HikariPool-1 - Connection is not available, request timed out after 30001ms. This is often a VPC that doesn't allow public traffic. Work through AWS's connection troubleshooting checklist to make sure Pipeloom is allowed to connect.

Query timeout

Error: MySQL Query Timeout: The sync was aborted because the query took too long to return results, will retry.

What happened: a query took longer than the server allows, usually because of MySQL's execution time limit (for example max_execution_time), so the sync was interrupted.

What to do: the error is temporary and the sync retries on its own, but repeated timeouts can make it fail. To prevent them:

• Open the source's settings and, under optional fields, find Checkpoint Target Time Interval. It's how often the connector saves its progress, in seconds; the default is 5 minutes.
• Set MySQL's max_execution_time (in milliseconds) higher than that interval. For example, with an interval of 300 seconds (5 minutes), max_execution_time should be at least 300000.

Full copies keep happening in CDC mode

In CDC mode, the first sync copies all existing data, and later syncs only read changes from the binlog. Occasionally a later sync copies everything again, and the sync logs say Saved offset no longer present on the server.

This means MySQL removed binlog files before the connector read them. It usually happens when a lot of changes make binlog files expire before the next sync runs. To prevent it:

• Sync more often.
• On standard MySQL, raise binlog_expire_logs_seconds. We recommend 7 days. See the MySQL binary log docs.
• On Amazon RDS for MySQL, set binlog retention hours to at least 24. It defaults to 0, which removes binlogs immediately. Use the RDS procedure in the AWS documentation, for example:

  call mysql.rds_set_configuration('binlog retention hours', 24);

  Longer retention uses more disk space.

EventDataDeserializationException during the first CDC sync

The first CDC sync takes a consistent snapshot of your database. It doesn't lock tables, so other clients can keep writing (MyISAM tables are still locked). It does assume the schema doesn't change while the snapshot runs.

If you see intermittent EventDataDeserializationException errors caused by EOFException or SocketException, raise these MySQL server timeouts:

MySQL 8.0.26 and later:

SET GLOBAL replica_net_timeout = 120;
SET GLOBAL thread_pool_idle_timeout = 120;

MySQL before 8.0.26:

SET GLOBAL slave_net_timeout = 120;
SET GLOBAL thread_pool_idle_timeout = 120;

:::note MySQL 8.0.26 renamed slave_net_timeout to replica_net_timeout. Use the one that matches your MySQL version. :::

(Advanced) Turn on GTIDs

Global transaction identifiers (GTIDs) uniquely identify each transaction on a server in a cluster. The connector doesn't need them, but they make replication simpler and make it easier to confirm that primary and replica servers are consistent. See the MySQL docs.

• gtid_mode: whether GTID mode is on. Turn it on with mysql> gtid_mode=ON.
• enforce_gtid_consistency: whether the server only allows statements that can be logged in a transactionally safe way. Required with GTIDs. Turn it on with mysql> enforce_gtid_consistency=ON.

(Advanced) Server time zone

In CDC mode, the connector may need a time zone if your MySQL server uses a system time zone that isn't in the IANA time zone database. Set Configured server timezone to the IANA equivalent (for example, CEST becomes Europe/Berlin).