title: 'MySQL Data Connector'
sidebar_label: 'MySQL Data Connector'
description: 'MySQL Data Connector Documentation'
tags:
- data-connectors
- mysql
- relational
- component-metrics
MySQL is an open-source relational database management system that uses structured query language (SQL) for managing and manipulating databases.
The MySQL Data Connector enables federated/accelerated SQL queries on data stored in MySQL databases.
Configuration
from
The from field takes the form mysql:database_name.table_name where database_name is the fully-qualified table name in the SQL server.
If the database_name is omitted in the from field, the connector will use the database specified in the mysql_db parameter. If the mysql_db parameter is not provided, it will default to the user's default database.
:::info
Unquoted identifiers are normalized to lowercase. To reference a table or database with mixed-case characters, wrap each case-sensitive part in double quotes: mysql:my_database."MixedCaseTable". See Identifier Case Sensitivity.
:::
These two examples are identical:
name
The dataset name. This will be used as the table name within Spice.
Example:
The dataset name cannot be a reserved keyword or any of the following keywords that are reserved by MySQL:
params
The MySQL data connector can be configured by providing the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:my_mysql_conn_string}.
| Parameter Name | Description |
|---|
mysql_connection_string | The connection string to use to connect to the MySQL server. This can be used instead of providing individual connection parameters. |
mysql_host | The hostname of the MySQL server. |
mysql_tcp_port | The port of the MySQL server. |
mysql_db | The name of the database to connect to. |
mysql_user | The MySQL username. |
mysql_pass | The password to connect with. |
mysql_sslmode | Optional. Specifies the SSL/TLS behavior for the connection, supported values: required: (default) Require TLS and verify the server certificate against system root CAs and domain name (equivalent to verify_identity). Set mysql_sslrootcert to verify against a specific CA bundle instead of the system trust store.preferred: Attempt a TLS connection but skip certificate and hostname verification (invalid or self-signed certificates are accepted). The connection is still encrypted and does not fall back to plaintext — if the server does not support TLS, the connection fails. Not recommended for production, as it does not protect against man-in-the-middle attacks.: Do not attempt to use an SSL connection, even if the server supports it. |
metrics
The MySQL data connector supports the following optional component metrics:
| Metric Name | Type | Description |
|---|
connection_count | Gauge | Gauge of active connections to the database server |
connections_in_pool | Gauge | Gauge of active connections that are idling in the pool |
active_wait_requests | Gauge | Gauge of requests that are waiting for a connection to be returned to the pool |
create_failed | Counter | Counter of connections that failed to be created |
discarded_superfluous_connection | Counter | Counter of connections that were closed because there were already enough idle connections in the pool |
discarded_unestablished_connection | Counter | Counter of connections that were closed because they could not be established |
dirty_connection_return | Counter | Counter of connections that were returned to the pool but were dirty (ie. open transactions, pending queries, etc) |
discarded_expired_connection | Counter | Counter of connections that were discarded because they were expired by the pool constraints (i.e. TTL expired) |
resetting_connection | Counter | Counter of connections that were reset |
discarded_error_during_cleanup |
These metrics are not enabled by default, enable them by setting the metrics parameter:
Types
The table below shows the MySQL data types supported, along with the type mapping to Apache Arrow types in Spice.
| MySQL Type | Arrow Type |
|---|
TINYINT | Int8 |
SMALLINT | Int16 |
INT | Int32 |
MEDIUMINT | Int32 |
BIGINT | Int64 |
DECIMAL | Decimal128 / Decimal256 |
FLOAT | Float32 |
DOUBLE | Float64 |
|
:::note
- The MySQL
TIMESTAMP value is retrieved as a UTC time value by default. Use the mysql_time_zone configuration parameter to specify the desired time zone for interpreting TIMESTAMP values during data retrieval.
:::
Limitations
- MySQL has no native nested or array column types (see the MySQL data types reference), so columns containing Arrow
Struct, List, or LargeList values are not supported by this connector.
- MySQL spatial types (
GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION) are not currently supported — only the types listed in the Types table are mapped to Arrow.
- Nested or array-valued data should be stored in a
JSON column, which is read as LargeUtf8 and can be decoded with SQL JSON functions.
Examples
Connecting using username and password
Connecting using SSL
Connecting using a Connection String
Connecting to the default database
With custom connection pool settings
Secrets
Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.
Cookbook
