from_mysql

Reads events from a MySQL database.

from_mysql [uri=string], [table=string], [sql=string], [show=string],
           [live=bool], [tracking_column=string],
           [host=string], [port=int], [user=string], [password=string],
           [database=string], [tls=bool|record]

Description

The from_mysql operator reads data from a MySQL database. You can query data using a table name, raw SQL, or retrieve database metadata.

The operator supports three query modes that are mutually exclusive:

1. Table mode: Read all rows from a table using the table parameter
2. SQL mode: Execute a custom SQL query using the sql parameter
3. Show mode: List tables or columns using the show parameter

uri = string (optional)

A MySQL connection URI in the format:

mysql://[user[:password]@]host[:port][/database]

When provided, the URI takes precedence over individual connection parameters. Credentials in the URI can be overridden by explicit user and password parameters.

table = string (optional)

The name of the table to read from. This is mutually exclusive with sql and show.

sql = string (optional)

A raw SQL query to execute. This is mutually exclusive with table and show.

Use raw strings for complex queries:

from_mysql sql=r"SELECT id, name FROM users WHERE active = 1"

show = string (optional)

Retrieve database metadata. This is mutually exclusive with table and sql.

Supported values:

• "tables": List all tables in the database
• "columns": List all columns for the table specified in table

live = bool (optional)

Enables continuous polling for new rows from a table. The operator tracks progress using a watermark on an integer column and polls every second for rows above the last-seen value. Mutually exclusive with sql and show. Requires table.

Defaults to false.

tracking_column = string (optional)

The integer column to use for watermark tracking in live mode. The operator queries for rows where this column exceeds the last-seen watermark.

When omitted, the tracking column is auto-detected from the table’s auto-increment primary key. Requires live=true.

host = string (optional)

The hostname or IP address of the MySQL server.

Defaults to "localhost".

port = int (optional)

The port number of the MySQL server.

Defaults to 3306.

user = string (optional)

The username for authentication. Supports the secret function for secure credential management.

Defaults to "root".

password = string (optional)

The password for authentication. Supports the secret function for secure credential management.

Defaults to "".

database = string (optional)

The database to connect to.

tls = bool|record (optional)

TLS configuration for the MySQL connection. Defaults to false (no TLS).

Use tls=true to enable TLS with default settings and certificate verification, or provide a record to customize specific options:

{
  skip_peer_verification: bool, // skip certificate verification.
  cacert: string,               // CA bundle to verify peers.
  certfile: string,             // client certificate to present.
  keyfile: string,              // private key for the client certificate.
}

Types

The operator maps MySQL types to Tenzir types as follows:

MySQL Type	Tenzir Type	Notes
TINYINT(1)	bool	Boolean representation
TINYINT, SMALLINT, INT	int64
BIGINT	int64
BIGINT UNSIGNED	uint64
FLOAT, DOUBLE	double
DECIMAL, NUMERIC	double	May lose precision
DATE, DATETIME	time
TIMESTAMP	time
TIME	duration
CHAR, VARCHAR, TEXT	string
BINARY, VARBINARY, BLOB	blob
JSON	string
ENUM	enumeration

Examples

Read all rows from a table

from_mysql table="users", host="db.example.com", database="mydb"

Use a connection URI

from_mysql uri="mysql://admin:secret@db.example.com:3306/production", table="events"

Execute a custom SQL query

from_mysql sql=r"SELECT id, name, created_at FROM users WHERE active = 1 LIMIT 100",
           host="localhost", database="app"

Use secure credentials

from_mysql table="orders",
           host="db.example.com",
           user=secret("mysql-user"),
           password=secret("mysql-password"),
           database="shop"

List all tables in a database

from_mysql show="tables", host="localhost", database="mydb"

List columns for a specific table

from_mysql show="columns", table="users", host="localhost", database="mydb"

Enable TLS with defaults

from_mysql table="events",
           host="db.example.com",
           database="production",
           tls=true

Connect with TLS but skip peer verification

from_mysql table="events",
           host="db.example.com",
           database="production",
           tls={skip_peer_verification: true}

Connect with TLS using a CA certificate

from_mysql table="events",
           host="db.example.com",
           database="production",
           tls={cacert: "/path/to/ca.pem"}

Stream new rows from a table

from_mysql table="events", live=true,
           host="db.example.com", database="mydb"

Stream with an explicit tracking column

from_mysql table="events", live=true, tracking_column="event_id",
           host="db.example.com", database="mydb"

See Also

• to_clickhouse
• MySQL