# MySQL ## 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**](https://www.w3schools.com/tags/ref_urlencode.ASP) **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:password@db.host: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. * `use_adbc` (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See [ADBC](/connections/database-connections/adbc.md) for setup and details. (*v1.5.2+*) * `adbc_uri` (optional) -> Override the automatically constructed ADBC connection URI when using `use_adbc=true`. ### 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 `my-sa@my-project.iam.gserviceaccount.com`) * MySQL has a 32-character username limit * The service account must have the `Cloud SQL Client` role Additional Parameters. See [here](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#parameters) 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: {% code overflow="wrap" %} ```bash $ sling conns set MYSQL type=mysql host= user= database= password= port= # OR use url (especially for special characters in the password) $ sling conns set MYSQL url="mysql://myuser:mypass%24@host.ip: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 ``` {% endcode %} ### Environment Variable See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#dot-env-file-.env.sling) to learn more about the `.env.sling` file. {% code overflow="wrap" %} ```bash export MYSQL='mysql://myuser:mypass@host.ip:3306/mydatabase?tls=skip-verify' export MYSQL='{ type: mysql, user: "myuser", password: "mypass", host: "host.ip", port: 3306, database: "mydatabase", tls: "skip-verify" }' ``` {% endcode %} ### Sling Env File YAML See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file. ```yaml connections: MYSQL: type: mysql host: user: port: database: schema: password: # use url (especially for special characters in the password) MYSQL_URL: url: "mysql://myuser:mypass%24@host.ip: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 : ```sql CREATE USER 'sling'@'%' IDENTIFIED BY ''; ``` 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*: ```sql 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. ```sql -- To give read access to all tables in a specific schema GRANT SELECT ON .* TO 'sling'@'%'; ``` If you are facing issues connecting, please reach out to us at , on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues). --- # 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.slingdata.io/connections/database-connections/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.