> ## Documentation Index
> Fetch the complete documentation index at: https://hoopdev-update-to-meta-tags.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

> Connect to a private mysql instance from your favorite IDE

## Prerequisites

To get the most out of this guide, you will need to:

* Either [create an account in our managed instance](https://use.hoop.dev) or [deploy your own hoop.dev instance](/getting-started/installation/overview)
* You must be your account administrator to perform the following commands

## Features

The table below explains which features are available for this kind of connection.

* **Native** - This refers to when a database client connects through a specific protocol, such as an IDE or client libraries through `hoop connect <connection-name>`.
* **One Off -** This term refers to accessing this connection from hoop web panel.

| Feature                    | Native                          | One Off               | Description                                                                                                             |
| -------------------------- | ------------------------------- | --------------------- | ----------------------------------------------------------------------------------------------------------------------- |
| TLS Termination Proxy      | <Icon icon="check" /> \*partial | <Icon icon="check" /> | The local proxy terminates the connection with TLS, enabling the connection with the remote server to be TLS encrypted. |
| Audit                      | <Icon icon="check" />           | <Icon icon="check" /> | The gateway stores and audits the queries being issued by the client.                                                   |
| Data Masking (Google DLP)  | <Icon icon="xmark" />           | <Icon icon="check" /> | A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.                   |
| Data Masking (MS Presidio) | <Icon icon="xmark" />           | <Icon icon="check" /> | A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.                   |
| Credentials Offload        | <Icon icon="check" />           | <Icon icon="check" /> | The user authenticates via SSO instead of using database credentials.                                                   |
| Interactive Access         | <Icon icon="check" />           | <Icon icon="check" /> | Interactive access is available when using an IDE or connecting via a terminal to perform analysis exploration.         |

<Info>
  **partial:** the connection uses TLS offloading until it reaches the agent. However, once it reaches the agent, the network connection to the remote server is established without encryption. It is crucial to ensure that the network hosting the agent is secure and reliable.
</Info>

## Configuration

| Name                   | Type       | Required | Description                                                                                    |
| ---------------------- | ---------- | -------- | ---------------------------------------------------------------------------------------------- |
| HOST                   | env-var    | yes      | The IP or Host of the MySQL server                                                             |
| PORT                   | env-var    | yes      | The port of the MySQL server                                                                   |
| USER                   | env-var    | yes      | The user to connect in the MySQL server                                                        |
| PASS                   | env-var    | yes      | The password to connect to the MySQL server                                                    |
| DB                     | env-var    | yes      | The name of the database to connect (Required when using the connection via the command line). |
| PRE\_SQL\_SCRIPT\_FILE | filesystem | no       | Append a custom SQL script at the beginning of the execution.                                  |

### Client Charset Configuration

By default, your client will use the character set provided by the MySQL server.
However, you may need to specify a different charset for your specific requirements when interacting with the database.

<Note>
  The following configuration options are only applicable for **One-Off executions**.
</Note>

#### 1. Session-Level Charset Configuration

You can change the charset for your current session directly in the Webapp by adding a `SET NAMES` statement before your query:

```sql
SET NAMES 'utf8mb4';
SELECT * FROM mytable LIMIT 5;
```

#### 2. Client Default Charset Configuration

To set a default charset for the connection, use the `--default-character-set` option when creating the connection.
This requires updating the default command attribute:

```sh
hoop admin create conn mysql -a <agent> --type database/mysql \
    -e HOST= \
    -e PORT= \
    -e USER= \
    -e PASS= \
    -e DB= \
    -- mysql -h'$HOST' -u'$USER' --port='$PORT' -D'$DB' \
        --default-character-set='<your-collation>'
```

#### 3. Using the Pre-SQL Script File

The `PRE_SQL_SCRIPT_FILE` environment variable allows you to automatically execute SQL commands at the beginning of each session:

```sh
hoop admin create conn mysql -a <agent> --type database/mysql \
    -e HOST= \
    -e PORT= \
    -e USER= \
    -e PASS= \
    -e DB= \
    -e filesystem:PRE_SQL_SCRIPT_FILE="SET NAMES '<your-collation>';"
```

<Note>
  This method is only available when using an image with the **MySQL Wrapper Script**, such as the `hoophq/hoopdev` image
</Note>

### Common Charset Options

* `utf8mb4` - Full UTF-8 support (recommended for most applications)
* `latin1` - Default charset in older MySQL versions
* `ascii` - Basic ASCII character set

For a complete list of supported character sets, refer to the MySQL documentation.

## Connection Setup

Create a new Database connection in hoop's web panel.

<Frame>
  <img src="https://mintlify.s3.us-west-1.amazonaws.com/hoopdev-update-to-meta-tags/images/quickstarts/create-database-connection-screenshot.png" alt="create tcp connection screenshot" />
</Frame>

## Connection Usage

You can access it through hoop CLI or hoop web panel.

### CLI

```bash
hoop connect <connection-name>

connection: mysqldb | session: 0ebc7492-94bf-498e-ac9c-430f2d5e89b0

---------------------mysql-credentials----------------------
      host=127.0.0.1 port=5433 user=noop password=noop
------------------------------------------------------------
ready to accept connections!
```

### Web panel

<Frame>
  <img src="https://mintlify.s3.us-west-1.amazonaws.com/hoopdev-update-to-meta-tags/images/quickstarts/client-pg.png" alt="create tcp connection screenshot" />
</Frame>