MySQL binding spec

Detailed documentation on the MySQL binding component

Component format

To setup MySQL binding create a component of type bindings.mysql. See this guide on how to create and apply a binding configuration.

The MySQL binding uses Go-MySQL-Driver internally.

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4.   name: <NAME>
  5.   namespace: <NAMESPACE>
  6. spec:
  7.   type: bindings.mysql
  8.   version: v1
  9.   metadata:
  10.     - name: url # Required, define DB connection in DSN format
  11.       value: <CONNECTION_STRING>
  12.     - name: pemPath # Optional
  13.       value: <PEM PATH>
  14.     - name: maxIdleConns
  15.       value: <MAX_IDLE_CONNECTIONS>
  16.     - name: maxOpenConns
  17.       value: <MAX_OPEN_CONNECTIONS>
  18.     - name: connMaxLifetime
  19.       value: <CONNECTILN_MAX_LIFE_TIME>
  20.     - name: connMaxIdleTime
  21.       value: <CONNECTION_MAX_IDLE_TIME>

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Spec metadata fields

FieldRequiredBinding supportDetailsExample
urlYOutputRepresent DB connection in Data Source Name (DNS) format. See here SSL details“user:password@tcp(localhost:3306)/dbname”
pemPathYOutputPath to the PEM file. Used with SSL connection“path/to/pem/file”
maxIdleConnsNOutputThe max idle connections. Integer greater than 0“10”
maxOpenConnsNOutputThe max open connections. Integer greater than 0“10”
connMaxLifetimeNOutputThe max connection lifetime. Duration string“12s”
connMaxIdleTimeNOutputThe max connection idel time. Duration string“12s”

SSL connection

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

  1. "<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 MySQL on Azure see the Azure documentation on SSL database connections, for information on how to download the required certificate. The connection to MySQL will require a minimum TLS version of 1.2.

Binding support

This component supports output binding with the following operations:

  • exec
  • query
  • close

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).

Request

  1. {
  2.   "operation": "exec",
  3.   "metadata": {
  4.     "sql": "INSERT INTO foo (id, c1, ts) VALUES (1, 'demo', '2020-09-24T11:45:05Z07:00')"
  5.   }
  6. }

Response

  1. {
  2.   "metadata": {
  3.     "operation": "exec",
  4.     "duration": "294µs",
  5.     "start-time": "2020-09-24T11:13:46.405097Z",
  6.     "end-time": "2020-09-24T11:13:46.414519Z",
  7.     "rows-affected": "1",
  8.     "sql": "INSERT INTO foo (id, c1, ts) VALUES (1, 'demo', '2020-09-24T11:45:05Z07:00')"
  9.   }
  10. }

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.

Request

  1. {
  2.   "operation": "query",
  3.   "metadata": {
  4.     "sql": "SELECT * FROM foo WHERE id < 3"
  5.   }
  6. }

Response

  1. {
  2.   "metadata": {
  3.     "operation": "query",
  4.     "duration": "432µs",
  5.     "start-time": "2020-09-24T11:13:46.405097Z",
  6.     "end-time": "2020-09-24T11:13:46.420566Z",
  7.     "sql": "SELECT * FROM foo WHERE id < 3"
  8.   },
  9.   "data": "[
  10.     [0,\"test-0\",\"2020-09-24T04:13:46Z\"],
  11.     [1,\"test-1\",\"2020-09-24T04:13:46Z\"],
  12.     [2,\"test-2\",\"2020-09-24T04:13:46Z\"]
  13.   ]"
  14. }

close

Finally, 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

  1. {
  2.   "operation": "close"
  3. }

Note, the MySQL binding itself doesn’t prevent SQL injection, like with any database application, validate the input before executing query.

Last modified February 18, 2022: Update setup-jetstream.md (#2200) (428d8c2)