# 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](https://docs.slingdata.io/connections/database-connections/adbc) 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=<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%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/sling-cli/environment#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/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
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%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 '<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*:

   ```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 <schema_name>.* TO  'sling'@'%';
   ```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).