# MySQL 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. ```yaml datasets: - from: mysql:mytable name: my_dataset params: mysql_host: localhost mysql_tcp_port: 3306 mysql_db: my_database mysql_user: my_user mysql_pass: ${secrets:mysql_pass} mysql_pool_min: 10 mysql_pool_max: 100 ``` ## 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. {% hint style="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](/building-blocks/data-connectors.md#identifier-case-sensitivity-and-quoting). {% endhint %} These two examples are identical: ```yaml datasets: - from: mysql:mytable name: my_dataset params: mysql_db: my_database ... ``` ```yaml datasets: - from: mysql:my_database.mytable name: my_dataset params: ... ``` ### `name` The dataset name. This will be used as the table name within Spice. Example: ```yaml datasets: - from: mysql:path.to.my_dataset name: cool_dataset params: ... ``` ```sql SELECT COUNT(*) FROM cool_dataset; ``` ```shell +----------+ | count(*) | +----------+ | 6001215 | +----------+ ``` The dataset name cannot be a reserved keyword or any of the following keywords that are reserved by MySQL: * `PARTITION` ### `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) - requires an SSL connection, `preferred` - tries SSL but connects insecurely if not supported, `disabled` - does not use SSL. | | `mysql_sslrootcert` | Optional parameter specifying the path to a custom PEM certificate that the connector will trust. | | `mysql_time_zone` | Optional. Specifies connection time zone. Default is `UTC`. Accepts fixed offsets (e.g., `+02:00`), IANA time zone names (e.g., `America/Los_Angeles`) if supported by the MySQL server, `system` (MySQL server host's OS time zone), or `local_system` (local runtime OS time zone). | | `mysql_pool_min` | The minimum number of connections to keep open in the pool, lazily created when requested. Default: `10` | | `mysql_pool_max` | The maximum number of connections to allow in the pool. Default: `100` | ### `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` | Counter | Counter of connections that were discarded because they returned an error during cleanup | | `connection_returned_to_pool` | Counter | Counter of connections that were returned to the pool | These metrics are not enabled by default, enable them by setting the `metrics` parameter: ```yaml datasets: - from: mysql:mytable name: my_dataset metrics: - name: connection_count - name: connections_in_pool - name: active_wait_requests - name: create_failed - name: discarded_superfluous_connection - name: discarded_unestablished_connection - name: dirty_connection_return - name: discarded_expired_connection - name: resetting_connection - name: discarded_error_during_cleanup - name: connection_returned_to_pool params: mysql_host: localhost mysql_tcp_port: 3306 mysql_user: my_user mysql_pass: ${secrets:mysql_pass} ``` ## 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` | | `DATETIME` | `Timestamp(Microsecond, None)` | | `TIMESTAMP` | `Timestamp(Microsecond, None)` | | `YEAR` | `Int16` | | `TIME` | `Time64(Nanosecond)` | | `DATE` | `Date32` | | `CHAR` | `Utf8` | | `BINARY` | `Binary` | | `VARCHAR` | `Utf8` | | `VARBINARY` | `Binary` | | `TINYBLOB` | `Binary` | | `TINYTEXT` | `Utf8` | | `BLOB` | `Binary` | | `TEXT` | `Utf8` | | `MEDIUMBLOB` | `Binary` | | `MEDIUMTEXT` | `Utf8` | | `LONGBLOB` | `LargeBinary` | | `LONGTEXT` | `LargeUtf8` | | `SET` | `Utf8` | | `ENUM` | `Dictionary(UInt16, Utf8)` | | `BIT` | `UInt64` | {% hint style="info" %} The MySQL `TIMESTAMP` value is [retrieved as a UTC time value](https://dev.mysql.com/doc/refman/8.4/en/datetime.html) by default. Use the `mysql_time_zone` configuration parameter to specify the desired time zone for interpreting `TIMESTAMP` values during data retrieval. {% endhint %} ## Examples ### Connecting using username and password ```yaml datasets: - from: mysql:path.to.my_dataset name: my_dataset params: mysql_host: localhost mysql_tcp_port: 3306 mysql_db: my_database mysql_user: my_user mysql_pass: ${secrets:mysql_pass} ``` ### Connecting using SSL ```yaml datasets: - from: mysql:path.to.my_dataset name: my_dataset params: mysql_host: localhost mysql_tcp_port: 3306 mysql_db: my_database mysql_user: my_user mysql_pass: ${secrets:mysql_pass} mysql_sslmode: preferred mysql_sslrootcert: ./custom_cert.pem ``` ### Connecting using a Connection String ```yaml datasets: - from: mysql:path.to.my_dataset name: my_dataset params: mysql_connection_string: mysql://${secrets:my_user}:${secrets:my_password}@localhost:3306/my_db ``` ### Connecting to the default database ```yaml datasets: - from: mysql:mytable name: my_dataset params: mysql_host: localhost mysql_tcp_port: 3306 mysql_user: my_user mysql_pass: ${secrets:mysql_pass} ``` ### With custom connection pool settings ```yaml datasets: - from: mysql:path.to.my_dataset name: my_dataset params: mysql_host: localhost mysql_tcp_port: 3306 mysql_db: my_database mysql_user: my_user mysql_pass: ${secrets:mysql_pass} mysql_pool_min: 5 mysql_pool_max: 10 ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.spice.ai/building-blocks/data-connectors/mysql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.