MySQL

Connect & Ingest data from / to a MySQL database

Setup

The following credentials keys are accepted:

• host (required) -> The hostname / ip of the instance

• user (required) -> The username to access the instance

• database (required) -> The database name of the instance

• schema (optional) -> The default schema to use

• password (optional) -> The password to access the instance. If you're having issues with a special character in the password, try using the url by encoding the special character.

• port (optional) -> The port of the instance. Default is 3306.

• ssh_tunnel (optional) -> The URL of the SSH server you would like to use as a tunnel (example ssh://user:[email protected]:22)

• ssh_private_key (optional) -> The private key to use to access a SSH server (raw string or path to file).

• ssh_passphrase (optional) -> The passphrase to use to access a SSH server.

• innodb_lock_wait_timeout -> The timeout in seconds for InnoDB row lock waits (integer, default: 50). Increase this value if you encounter Lock wait timeout exceeded errors during upsert/merge operations with concurrent writes.

Google Cloud SQL IAM Authentication (v1.4.25+)

For Google Cloud SQL MySQL instances with IAM authentication enabled, you can use the following properties:

• gcp_instance (required) -> The Cloud SQL instance name

• gcp_project (required) -> The GCP project ID

• gcp_region (required) -> The GCP region (e.g., us-central1)

• user (required) -> The IAM user or service account name (short format, e.g., my-sa)

• gcp_key_file (optional) -> Path to the GCP service account credentials JSON file

• gcp_key_body (optional) -> The GCP service account credentials JSON content as a string

• gcp_use_iam_auth (required) -> Must be set to true to enable IAM authentication

• gcp_lazy_refresh (optional) -> Enable lazy refresh for serverless environments (true or false). Default is false.

• gcp_use_private_ip (optional) -> Use private IP for the connection (true or false). Default is false.

Authentication Methods (in priority order):

1. If gcp_key_body is provided, uses the JSON credentials directly

2. If gcp_key_file is provided, uses the credentials file

3. Otherwise, uses Application Default Credentials (ADC)

Important Notes:

• When using IAM authentication, do NOT provide a password - authentication is handled via IAM tokens

• The user field should be the short format (e.g., my-sa, not [email protected])

• MySQL has a 32-character username limit

• The service account must have the Cloud SQL Client role

Additional Parameters. See here for more details:

• allow_all_files -> Allows using LOCAL DATA INFILE without restrictions (true or false)

• allow_cleartext_passwords -> Permits sending passwords in clear text (true or false)

• allow_fallback_to_plaintext -> Allows fallback to unencrypted connection if server doesn't support TLS (true or false)

• allow_native_passwords -> Enables the native password authentication method (true or false)

• allow_old_passwords -> Allows the old insecure password method (true or false)

• charset -> Sets the charset for server-side prepared statements (e.g., utf8mb4)

• check_conn_liveness -> Checks connection liveness before using it (true or false)

• collation -> Sets the collation for server-side prepared statements (e.g., utf8mb4_general_ci)

• client_found_rows -> Returns number of matching rows instead of rows changed (true or false)

• columns_with_alias -> Prepares result columns as if they have an alias (true or false)

• interpolate_params -> Interpolates placeholders instead of using prepared statements (true or false)

• loc -> Sets the location for time.Time values (e.g., Local, UTC, or a time zone name)

• time_truncate -> Truncates time values to the given precision (true or false)

• max_allowed_packet -> Max packet size allowed (integer value in bytes)

• multi_statements -> Allows multiple statements in one query (true or false)

• parse_time -> Converts TIME/DATE/DATETIME to time.Time (true or false)

• read_timeout -> I/O read timeout (duration string, e.g., 30s, 0.5m, 1h)

• reject_read_only -> Rejects read-only connections (true or false)

• server_pub_key -> Server public key name (string)

• timeout -> Timeout for establishing connections (duration string, e.g., 30s, 0.5m, 1h)

• write_timeout -> I/O write timeout (duration string, e.g., 30s, 0.5m, 1h)

• connection_attributes -> Connection attributes to send to MySQL (comma-separated list of key-value pairs)

• tls -> TLS configuration name (true, false, skip-verify, or custom when providing cert_* keys below)

Custom TLS Certificates:

• cert_file (optional) -> the client certificate to use to access the instance via TLS (file path or raw)

• cert_key_file (optional) -> the client key to use to access the instance via TLS (file path or raw)

• cert_ca_file (optional) -> the client CA certificate to use to access the instance via TLS (file path or raw)

Using sling conns

Here are examples of setting a connection named MYSQL. We must provide the type=mysql property:

$ sling conns set MYSQL type=mysql host=<host> user=<user> database=<database> password=<password> port=<port> 

# OR use url (especially for special characters in the password)
$ sling conns set MYSQL url="mysql://myuser:mypass%[email protected]:3306/mydatabase?tls=skip-verify"

# For Google Cloud SQL with IAM authentication
$ sling conns set MYSQL type=mysql \
  gcp_instance=my-instance \
  gcp_project=my-project \
  gcp_region=us-central1 \
  user=my-sa \
  database=mydatabase \
  gcp_use_iam_auth=true \
  gcp_key_file=/path/to/credentials.json

Environment Variable

export MYSQL='mysql://myuser:[email protected]:3306/mydatabase?tls=skip-verify'
export MYSQL='{ type: mysql, user: "myuser", password: "mypass", host: "host.ip", port: 3306, database: "mydatabase", tls: "skip-verify" }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  MYSQL:
    type: mysql
    host: <host>
    user: <user>
    port: <port>
    database: <database>
    schema: <schema>
    password: <password>

  # use url (especially for special characters in the password)
  MYSQL_URL:
    url: "mysql://myuser:mypass%[email protected]:3306/mydatabase?tls=skip-verify"

  MYSQL_CLOUDSQL:
    type: mysql
    gcp_instance: my-instance
    gcp_project: my-project
    gcp_region: us-central1
    user: my-sa  # Short format without domain suffix
    database: mydatabase
    gcp_use_iam_auth: true
    gcp_key_file: /path/to/credentials.json
    # gcp_lazy_refresh: true  # Enable for serverless environments
    # gcp_use_private_ip: true  # Enable for private IP connections

Database user creation

To allow Sling to access your database, we need to create a user with the proper privileges. Please follow the steps below:

1. First you'll need to login as a user with CREATE USER and GRANT OPTION privileges. Create a user sling (or whatever you prefer) by running :

   CREATE USER 'sling'@'%' IDENTIFIED BY '<password>';

2. If you are planning to load data into this connection, you need to grant the following privileges to that user so we can create tables in schema sling:

   CREATE SCHEMA sling;
   GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW ON sling.* TO 'sling'@'%';

3. If you are planning to extract data from this connection, you need to give permission to read the tables you'd like Sling to extract.

   -- To give read access to all tables in a specific schema
   GRANT SELECT ON <schema_name>.* TO  'sling'@'%';

If you are facing issues connecting, please reach out to us at [email protected], on discord or open a Github Issue here.