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 or deploy your own hoop.dev instance
• 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	*partial		The local proxy terminates the connection with TLS, enabling the connection with the remote server to be TLS encrypted.
Audit			The gateway stores and audits the queries being issued by the client.
Data Masking (Google DLP)			A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.
Data Masking (MS Presidio)			A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.
Credentials Offload			The user authenticates via SSO instead of using database credentials.
Interactive Access			Interactive access is available when using an IDE or connecting via a terminal to perform analysis exploration.

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.

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.

The following configuration options are only applicable for One-Off executions.

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:

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:

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:

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>';"

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

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.

Connection Usage

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

CLI

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