MySQL Integration

Overview

The MySQL node in Lamatic connects to your MySQL database for both scheduled replication and on-demand queries. As a Batch Trigger, it syncs data from a selected table on a schedule using incremental or full-refresh modes—ideal for keeping embeddings or downstream systems up to date. As an Action node, it lets you run custom SQL queries from within your flows.

• Batch Trigger: Sync data from a MySQL table on a schedule. Use Full Refresh to clear and re-embed all content, or Incremental to re-embed only updated records (deleted rows are not removed from the destination).
• Action: Run SQL queries against your MySQL database from any flow.

Features

Key Functionalities

1. Batch Trigger: Sync a table on a configurable schedule (e.g., every 24 hours, every 3 days, weekly) with incremental or full-refresh modes.
2. Action: Execute custom SQL queries (SELECT, and other read/write operations as allowed by your credentials).
3. Connection Security: Support for SSL (multiple modes) and SSH tunneling for connecting through a bastion host.
4. Cursor-based Incremental Sync: Use a cursor column (e.g., updated_at or an auto-increment ID) to replicate only new or changed rows.

Benefits

1. Scheduled Data Sync: Keep vector stores or other systems in sync with MySQL without manual exports.
2. Flexible Sync Modes: Full refresh for complete reloads or incremental for efficient updates.
3. Secure Connectivity: SSL and SSH tunnel options for locked-down or cloud-hosted MySQL instances.
4. Query from Flows: Run arbitrary queries as part of your automation.

Prerequisites

Before using the MySQL node, ensure the following:

• MySQL server accessible from Lamatic (or via SSH bastion).
• Database user with appropriate permissions (see Quick Start for a dedicated read-only user).
• Binary logging enabled if you use CDC-based replication (see Enable binary logging).
• For SSH tunnel: A bastion host and SSH key or password.

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. (Optional) Enable binary logging if using CDC-based replication.
3. Create a new MySQL source in Lamatic using your credentials and desired sync mode.
4. (Lamatic Cloud only) Allow inbound traffic from Lamatic IPs if required.

Once this is complete, you can select MySQL as a source for syncing data or as an action for running queries.

Step 1: Create a dedicated read-only MySQL user

These steps create a dedicated read-only user for replicating data. Alternatively, you can use an existing MySQL user with sufficient privileges.

Create a new user:

CREATE USER <user_name> IDENTIFIED BY 'your_password_here';

Grant read-only and replication-related permissions:

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

If you use standard replication (non-CDC), only the SELECT permission is required.

Step 2: Enable binary logging on your MySQL server

You must enable binary logging for MySQL replication when using CDC. Many cloud providers (AWS RDS, GCP Cloud SQL, etc.) offer one-click options for enabling the binlog.

If you are self-hosting MySQL, add the following to your server configuration:

Example configuration:

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

Setup

Step 1: Configure MySQL credentials in Lamatic

In the Integrate MySQL dialog, provide the following:

SSL Modes

You can connect with SSL or use an SSH tunnel for an extra layer of security.

Connection via SSH tunnel

You can connect to MySQL through an SSH tunnel (bastion/jump server). Lamatic connects to the bastion, and the bastion connects to the MySQL server.

1. In the MySQL source configuration, open the SSH tunnel dropdown and choose:

   • SSH Key Authentication — use a private key for the SSH tunnel.
   • Password Authentication — use a password for the SSH tunnel.

2. SSH Tunnel Jump Server Host: Hostname or IP of the bastion server.

3. SSH Connection Port: Port on the bastion (default 22).

4. SSH Login Username: Operating system username on the bastion (not the MySQL username).

5. Authentication:

   • SSH Key: Paste the SSH Private Key used to connect to the bastion.
   • Password: Enter the OS user password for the bastion (not the MySQL password).

Generating a private key for SSH tunneling

The connector expects an RSA key in PEM format. Generate it with:

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

This creates a private key in PEM format. Add the corresponding public key to the bastion’s authorized_keys for the user Lamatic will use. Paste the private key into the Lamatic connector configuration.

Configuration Reference

Batch Trigger configuration

What is a Cursor?

A cursor is a tracker used during incremental synchronization so that only newly updated or inserted records are sent from the source to the destination in each sync run.

How incremental sync works

• Incremental sync runs on a schedule like a loop. Each run replicates only records that were inserted or updated in the source since the previous run; already-replicated records are skipped.
• This is more efficient than full refresh, which copies the entire table on every run.

How the cursor works

• The system must track what has already been replicated. The cursor is that pointer: it stores the latest value seen in the previous sync.
• On the next run, the connector includes this cursor value in the query so that only rows after that value are fetched (e.g., WHERE cursor_column > last_cursor_value).

Example

• If your table has an updated_at column, you can use it as the cursor field. After a sync, the cursor keeps the largest updated_at among replicated rows. On the next run, the connector selects only rows where updated_at is greater than (or in some cases greater than or equal to) that stored value.

Choosing a cursor field

• The cursor field does not have to be a timestamp. It should increase over time (be monotonically increasing)—for example, an auto-incrementing ID also works.

For more on this pattern, see Incremental data synchronization between Postgres databases.

Action node configuration

As an Action, the node runs the query you provide and returns the result set (or affected rows) as output.

Low-Code Examples

Batch Trigger example

triggerNode:
  nodeId: triggerNode_1
  nodeType: mysqlNode
  nodeName: MySQL
  values:
    credentials: MySQL
    table: ''
    syncMode: incremental
    cursorField: ''
    cronExpression: 0 0 00 1/1 * ? * UTC
  modes: {}
  allConfigs:
    Config A:
      nodeName: MySQL
      credentials: MySQL
      table: ''
      syncMode: incremental
      cursorField: ''
      cronExpression: 0 0 00 1/1 * ? * UTC
      id: triggerNode_1
  schema:
    queryResult: object
    status: string

Action node example

nodes:
  - nodeId: mysqlNode_749
    nodeType: mysqlNode
    nodeName: MySQL
    values:
      query: select * from employees
      action: runQuery
      credentials: MySQL
    modes: {}
    needs:
      - triggerNode_1
    allConfigs:
      Config A:
        id: mysqlNode_749
        query: select * from employees
        action: runQuery
        nodeName: MySQL
        credentials: MySQL
    schema:
      queryResult: object
      status: string
    logic: []

Output

Batch Trigger output

• Records: Array of row objects from the synced table (or cursor-defined subset for incremental).
• Metadata: Sync metadata (e.g., last cursor value, row count) when available.

Action node output

• Rows: Result set of the executed query (for SELECT).
• Affected rows / result: For non-SELECT statements, depends on the connector implementation (e.g., row count or success flag).

Troubleshooting

Common issues

Additional resources

• MySQL documentation (opens in a new tab)
• MySQL binary logging (opens in a new tab)