MySQL / MariaDB Connection | DataKnits Knowledge Hub

MySQL / MariaDB

On-premises MySQL (5.7, 8.x) and MariaDB, plus cloud-managed instances (Amazon RDS for MySQL, Google Cloud SQL for MySQL, Azure Database for MySQL). JDBC-based connectivity with metadata browsing, data preview, and read/write pipeline support.

On this page

Connection Fields
Authentication Methods
SSL / TLS
SSH Tunneling
Write Modes
Known Limitations

Connection Fields

Field	Required	Default	Description
Display Name	Yes	—	A human-readable label for this connection, shown across the platform.
Host	Yes	—	Hostname or IP address of the MySQL or MariaDB server. For cloud-managed instances, use the endpoint provided by your cloud provider.
Port	Yes	3306	MySQL/MariaDB server port. Change only if your server uses a non-default port.
Database	Yes	—	The default database (schema) to connect to. Used as the initial catalog for metadata browsing.
Username	Yes	—	Database username.
Password	Yes	—	Database password. Stored encrypted at rest using AES-256.
SSL Mode	No	`PREFERRED`	Controls SSL behaviour. See SSL / TLS section below.
Timezone	No	Server timezone	Sets the session timezone for JDBC operations (e.g., `UTC`). Recommended when the server and application timezones differ to avoid datetime offset issues.
Connection Timeout (s)	No	30	Seconds before a connection attempt times out.
Additional JDBC Parameters	No	—	Optional key=value pairs appended to the JDBC URL (e.g., `characterEncoding=UTF-8&useUnicode=true`). Separate multiple parameters with `&`.

MariaDB note: MariaDB uses a different JDBC driver class (org.mariadb.jdbc.Driver) from MySQL (com.mysql.cj.jdbc.Driver). DataKnits detects the server type automatically from the connection test response and applies the correct driver. If auto-detection fails, you can override the driver class in Additional JDBC Parameters using driverClassName=org.mariadb.jdbc.Driver.

Authentication Methods

Username / Password — Standard MySQL authentication. The most common method for on-premises and cloud-managed instances.
SSH Tunnel + Username/Password — Routes the JDBC connection through an SSH bastion host. Required for databases in private subnets without direct inbound access. See SSH Tunneling.

SSL / TLS

The SSL Mode field controls whether and how SSL is enforced on the JDBC connection.

Mode	Description	When to Use
`DISABLED`	No SSL. All traffic is unencrypted.	Internal development environments only. Not recommended for production.
`PREFERRED`	Use SSL if the server supports it; fall back to unencrypted if not.	Default. Provides encryption where available.
`REQUIRED`	SSL required. Fails if the server does not support SSL. Server certificate is not validated.	Cloud-managed MySQL instances where SSL is always enabled.
`VERIFY_CA`	SSL required. Server certificate validated against a trusted CA. Hostname not verified.	Private cloud or on-premises with an internal CA.
`VERIFY_FULL`	SSL required. Certificate validated and hostname must match the certificate CN/SAN.	Highest security. Required for PCI-DSS and HIPAA deployments.

SSH Tunneling

When a MySQL instance is in a private subnet, DataKnits can route the JDBC connection through an SSH bastion host.

Field	Required	Description
SSH Host	Yes	Hostname or IP of the SSH bastion server.
SSH Port	Yes	SSH port. Default: 22.
SSH Username	Yes	Linux username on the bastion host.
SSH Private Key (PEM)	Yes*	PEM-encoded RSA or Ed25519 private key. Required if not using password authentication for SSH.
SSH Password	Yes*	Bastion host password. Use only if a private key is not available.

When SSH tunnel is enabled, the Host field should be the address of the MySQL instance as reachable from the bastion host (e.g., an internal IP or private DNS name), not a public address.

Write Modes

When MySQL or MariaDB is configured as a pipeline target, the following write modes are available:

Mode	Behaviour
Overwrite	Truncates the target table and writes all incoming rows. Equivalent to `TRUNCATE TABLE` followed by batch `INSERT`.
Append	Inserts all incoming rows without modifying existing data. No deduplication.
Upsert (Merge)	Inserts new rows; updates rows where the key already exists. Generates `INSERT INTO ... ON DUPLICATE KEY UPDATE ...`. Requires the target table to have a PRIMARY KEY or UNIQUE constraint on the merge key column(s).
Create if Not Exists	Creates the target table from the DataFrame schema if it does not exist, then appends data.

Known Limitations

Supported MySQL versions: 5.7 and 8.x. Older 5.6 servers are not tested and may encounter JDBC compatibility issues.
Pre/Post SQL fields that contain multiple statements require allowMultiQueries=true in the Additional JDBC Parameters field, as the MySQL JDBC driver blocks multi-statement execution by default.
MySQL's UNSIGNED BIGINT columns exceed Java's signed long range. DataKnits maps these to DECIMAL(20,0) in the catalog to prevent overflow. Be aware when joining or filtering on these columns in pipeline transforms.
Schema changes on the target table are not applied automatically. The target table schema must match the incoming DataFrame schema, or the pipeline will fail at runtime.
The Timezone field affects session-level datetime parsing only. It does not convert stored values; ensure source and target systems agree on timezone storage conventions.