mysql.md

type	title	linkTitle	description	aliases
docs	MySQL & MariaDB binding spec	MySQL & MariaDB	Detailed documentation on the MySQL binding component	/operations/components/setup-bindings/supported-bindings/mysql/

Component format

The MySQL binding allows connecting to both MySQL and MariaDB databases. In this document, we refer to "MySQL" to indicate both databases.

To setup a MySQL binding create a component of type bindings.mysql. See [this guide]({{< ref "howto-bindings.md#1-create-a-binding" >}}) on how to create and apply a binding configuration.

The MySQL binding uses Go-MySQL-Driver internally.

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <NAME>
spec:
  type: bindings.mysql
  version: v1
  metadata:
    - name: url # Required, define DB connection in DSN format
      value: "<CONNECTION_STRING>"
    - name: pemPath # Optional
      value: "<PEM PATH>"
    - name: pemContents # Optional, supersedes pemPath
      value: "<PEM CONTENTS>"
    - name: maxIdleConns
      value: "<MAX_IDLE_CONNECTIONS>"
    - name: maxOpenConns
      value: "<MAX_OPEN_CONNECTIONS>"
    - name: connMaxLifetime
      value: "<CONNECTION_MAX_LIFE_TIME>"
    - name: connMaxIdleTime
      value: "<CONNECTION_MAX_IDLE_TIME>"

{{% alert title="Warning" color="warning" %}} The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described [here]({{< ref component-secrets.md >}}). Note that you can not use secret just for username/password. If you use secret, it has to be for the complete connection string. {{% /alert %}}

Spec metadata fields

Field	Required	Binding support	Details	Example
url	Y	Output	Represent DB connection in Data Source Name (DNS) format. See here SSL details	"user:password@tcp(localhost:3306)/dbname"
pemPath	N	Output	Path to the PEM file. Used with SSL connection	"path/to/pem/file"
pemContents	N	Output	PEM file contents. Used with SSL connection. Supersedes pemPath	"-----BEGIN CERTIFICATE-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwJw7z4z\n...\n-----END CERTIFICATE-----"
maxIdleConns	N	Output	The max idle connections. Integer greater than 0	"10"
maxOpenConns	N	Output	The max open connections. Integer greater than 0	"10"
connMaxLifetime	N	Output	The max connection lifetime. Duration string	"12s"
connMaxIdleTime	N	Output	The max connection idle time. Duration string	"12s"

SSL connection

If your server requires SSL your connection string must end of &tls=custom for example:

"<user>:<password>@tcp(<server>:3306)/<database>?allowNativePasswords=true&tls=custom"

You must replace the <PEM PATH> with a full path to the PEM file. If you are using Azure Database for MySQL see the Azure documentation on SSL database connections, for information on how to download the required certificate. The connection to MySQL requires a minimum TLS version of 1.2.

Multiple statements

By default, the MySQL Go driver only supports one SQL statement per query/command.

To allow multiple statements in one query you need to add multiStatements=true to a query string, for example:

"<user>:<password>@tcp(<server>:3306)/<database>?multiStatements=true"

While this allows batch queries, it also greatly increases the risk of SQL injections. Only the result of the first query is returned, all other results are silently discarded.

Binding support

This component supports output binding with the following operations:

• exec
• query
• close

Parametrized queries

This binding supports parametrized queries, which allow separating the SQL query itself from user-supplied values. The usage of parametrized queries is strongly recommended for security reasons, as they prevent SQL Injection attacks.

For example:

-- ❌ WRONG! Includes values in the query and is vulnerable to SQL Injection attacks.
SELECT * FROM mytable WHERE user_key = 'something';

-- ✅ GOOD! Uses parametrized queries.
-- This will be executed with parameters ["something"]
SELECT * FROM mytable WHERE user_key = ?;

exec

The exec operation can be used for DDL operations (like table creation), as well as INSERT, UPDATE, DELETE operations which return only metadata (e.g. number of affected rows).

The params property is a string containing a JSON-encoded array of parameters.

Request

{
  "operation": "exec",
  "metadata": {
    "sql": "INSERT INTO foo (id, c1, ts) VALUES (?, ?, ?)",
    "params": "[1, \"demo\", \"2020-09-24T11:45:05Z07:00\"]"
  }
}

Response

{
  "metadata": {
    "operation": "exec",
    "duration": "294µs",
    "start-time": "2020-09-24T11:13:46.405097Z",
    "end-time": "2020-09-24T11:13:46.414519Z",
    "rows-affected": "1",
    "sql": "INSERT INTO foo (id, c1, ts) VALUES (?, ?, ?)"
  }
}

query

The query operation is used for SELECT statements, which returns the metadata along with data in a form of an array of row values.

The params property is a string containing a JSON-encoded array of parameters.

Request

{
  "operation": "query",
  "metadata": {
    "sql": "SELECT * FROM foo WHERE id < $1",
    "params": "[3]"
  }
}

Response

{
  "metadata": {
    "operation": "query",
    "duration": "432µs",
    "start-time": "2020-09-24T11:13:46.405097Z",
    "end-time": "2020-09-24T11:13:46.420566Z",
    "sql": "SELECT * FROM foo WHERE id < ?"
  },
  "data": [
    {column_name: value, column_name: value, ...},
    {column_name: value, column_name: value, ...},
    {column_name: value, column_name: value, ...},
  ]
}

Here column_name is the name of the column returned by query, and value is a value of this column. Note that values are returned as string or numbers (language specific data type)

close

The close operation can be used to explicitly close the DB connection and return it to the pool. This operation doesn't have any response.

Request

{
  "operation": "close"
}

Related links

• [Basic schema for a Dapr component]({{< ref component-schema >}})
• [Bindings building block]({{< ref bindings >}})
• [How-To: Use bindings to interface with external resources]({{< ref howto-bindings.md >}})
• [Bindings API reference]({{< ref bindings_api.md >}})