Skip to main content

MySQL

The mysql connector reads MySQL and MariaDB databases with read-only SELECT queries and table reads, and writes tables from DuckDB and Spark job outputs. It works like the postgres connector; this page lists what differs.

Install​

sw connectors -i mysql

Connect​

Use the connection keys or one DSN secret, not both.

source.yaml
kind: source
name: mysql_demo
spec:
config:
mysql_host: { type: string, default: localhost }
start_date: { type: date, default: "-7d" }
secrets:
mysql_password: { type: string }
auth:
provider: mysql
host: "{{ config.mysql_host }}"
database: app_db
user: reader
password: "{{ secrets.mysql_password }}"
statement_timeout: 5min
KeyWhat it isRequired / default
hostA host name or an IP addressRequired (with keys)
portThe port3306
databaseThe database. Also the schema of a table named without one (a source also accepts dbname)Required (with keys)
userThe user. Use one that can only readRequired (with keys)
passwordThe user's password. SecretOptional
sslmodeThe TLS mode (see TLS)preferred
optionsMore connection parameters: socket and the ssl_* files (ssl_ca, ssl_cert, ssl_key, ...)Optional
statement_timeoutOn streamwright run, stops a statement that runs longer: seconds, or 500ms, 30s, 5minOptional
dsnmysql://user:password@host:port/database, or key=value pairs, instead of the keys above. SecretOptional

TLS​

sslmodeWhat it does
disabledNo TLS
preferredThe default. Uses TLS when the server offers it; ssl_* files in options make it required
requiredEncrypts, without checking the server certificate
verify_caAlso checks the server certificate against ssl_ca
verify_identityAlso checks the server's host name

The same sslmode and ssl_* files apply to every connection the connector makes, including job state stores and Spark merges and deletes.

Read data​

A request uses service: database:

MethodArgumentsWhat it does
queryquery, paramsRuns one read-only SELECT. $name placeholders take their values from params
tabletable, schema (default: the connection's database), columns, whereReads a table: some or all columns, and the rows that match every where condition
streams/users.yaml
incremental:
cursor_field: created_at
start: "{{ config.start_date }}"
window: 1d
requests:
- name: raw_users
operation:
service: database
method: query
arguments:
query: |
SELECT id, username, email, created_at
FROM users
WHERE created_at >= $since
params: { since: "{{ window.start }}" }

transform:
mode: page
steps:
- name: users
select: |
SELECT
(record->>'id')::BIGINT AS user_id,
record->>'username' AS username,
record->>'email' AS email,
(record->>'created_at')::TIMESTAMPTZ AS created_at
FROM raw_users

export:
users:
step: users
primary_key: [user_id]

$since is a bound parameter: its value comes from params and is never pasted into the query text.

In jobs, a DuckDB job uses a read step (read: query with query, or read: table with table and schema), and a Spark job the same operation: as a source stream. Engines take the same arguments as on PostgreSQL. On Spark, $name values become escaped literals (backslashes doubled, as MySQL reads them as escapes).

Write data​

DuckDB and Spark job outputs write a step's rows into a table:

outputs:
- name: save_users # DuckDB job
connection: app_db
step: users
table: users_copy
mode: overwrite
ModeWhat it doesEngines
appendAdds the rowsDuckDB, Spark
overwriteReplaces the tableDuckDB
mergeUpdates the rows that match on primary_key and inserts the rest (INSERT ... ON DUPLICATE KEY UPDATE)Spark
deleteDeletes the rows that match on primary_keySpark

DuckDB's MySQL extension cannot merge, so use a Spark job to merge or delete. On Spark, merge and delete load a staging table (CREATE TABLE ... LIKE the target, or the output's staging_table) and run one statement.

Good to know​

  • Read-only queries. A query is checked before it runs: one statement, no writes, and no system schemas (mysql, performance_schema, sys, information_schema) or files. The database is attached read-only.
  • Connection options. On streamwright run and in DuckDB jobs, options takes only socket and the ssl_* files. Any other parameter is refused by name, never silently dropped.
  • Job state and Spark. A DuckDB job can keep its state in MySQL; install pip install "streamwright-mysql[state]". For Spark jobs, install pip install "streamwright-mysql[spark]".
  • Vendor arguments (Spark). A connection key or database.* argument the connector does not declare becomes a JDBC driver property, unchanged (for example fetchsize, connectTimeout, sessionInitStatement).

See also​