Setting up the Snowflake Connector for MySQL Agent container¶

Note

The Snowflake Connector for MySQL is subject to the Connector Terms.

The process of configuring the Snowflake Connector for MySQL agent includes the following steps:

After installing the Snowflake Connector for MySQL, you must configure and run the Docker container with the agent.

Prerequisites¶

Before installing the connector, you must set up the environment where the agent runs.

Configuring your firewall to access to Snowflake¶

Please see Configuring network requirements.

Downloading the MariaDB JDBC connector¶

On the system where you plan to run the agent, download the MariaDB JDBC Connector 3.4.1. The agent uses the MariaDB JDBC Connector to connect to MySQL.

Installing an orchestration tool¶

The agent is distributed as a Docker image that you can run using orchestration tools and services like Docker, Kubernetes, or OpenShift.

To run the agent, you must have one of these tools installed. Your environment must have:

At least 6 GB of RAM available to the container running the agent. The agent is a memory-intensive application.
4 CPUs available to handle the throughput requirements of the agent. Decreasing the number of CPUs decreases the throughput linearly. Having additional CPUs does not provide significant gains.

The connector requires exactly one instance of the agent application to be running at all times.

Preparing data sources configuration file¶

To set up the data sources on agent side, configuration file will be needed.

Copy the following json template for data sources configuration and paste it to file:

{
    "<data_source_name_1>": {
        "url": "jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]",
        "username": "<mysql_db_username>",
        "password": "<mysql_db_password>"
    },
    "<data_source_name_2>": {
        "url": "jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]",
        "username": "<mysql_db_username>",
        "password": "<mysql_db_password>"
    },
    "<data_source_name_3>": {
        "url": "jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]",
        "username": "<mysql_db_username>",
        "password": "<mysql_db_password>"
    }
}

Copy

Fill the necessary parameters for each data source you want to be available for replication (Template contains randomly selected number of data sources for example purposes).
Give each data source a name consisting only of UPPERCASE letters and numbers.
Make a note of the data source names; you will need them when configuring replication.

File is mounted by the following parameter of docker run command:

--volume </path/to/datasources/json/file>:/home/agent/datasources.json

Copy

Data source parameters¶

url

Specifies the MySQL connection URL in the following syntax:

jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]

Copy

If you want to use an SSL connection you need to specify it in the URL by adding useSSL=true. For additional parameters see Connector reference. For example, you can specify in url:

jdbc:mariadb://<host>:<port>/?verifyServerCertificate=true&useSSL=true&requireSSL=true&allowPublicKeyRetrieval=true

Copy

Keep in mind that enabling an SSL connection requires Adding a server SSL certificate to the agent trust store

For more information, see About MariaDB Connector/J.

username

Specifies the user name for the MySQL connection. This MySQL user should have the following permissions:

REPLICATION SLAVE and REPLICATION CLIENT to be able to read from binlog.

For example:

GRANT REPLICATION SLAVE ON *.* TO '<username>'@'%'
GRANT REPLICATION CLIENT ON *.* TO '<username>'@'%'

Copy

SELECT permission to all tables that are replicated.

For example:
GRANT SELECT ON <schema>.* TO '<username>'@'%' GRANT SELECT ON <schema>.<table> TO '<username>'@'%'
Copy
Where <schema>.<table> is the unique identifier of a table to be replicated.

password

Specifies the password of the MySQL database user.

Running the Docker container of the agent¶

Note

When using Docker to manage the image for the agent application, consider using the --restart flag with the appropriate restart policy. This is important to ensure that the agent application is always running.

The following are the general procedures for running the agent using Docker:

Prepare downloaded mariadb driver.
Prepare json file with snowflake connection configuration downloaded from wizard.
Prepare json file with data sources configuration.

Run the container:

docker run -d \
   --restart unless-stopped \
   --name agent-app-instance \
   --volume </path/to/ssh/keys/directory>:/home/agent/.ssh \
   --volume </path/to/mariadb/jdbc/jar>:/home/agent/libs/mariadb-jdbc-driver \
   --volume </path/to/snowflake/json/file>:/home/agent/snowflake.json \
   --volume </path/to/datasources/json/file>:/home/agent/datasources.json \
   -m 6g \
   snowflakedb/database-connector-agent:latest

Copy

Attention

If you already pulled the latest version, it is possible that you’ll need to pull it again by typing docker pull snowflakedb/database-connector-agent:latest to be sure you’re running the the latest version from dockerhub rather than the cached one.

Managing Key Generation and User Assignment¶

Whenever you download a configuration json file from the Snowflake wizard, the user is provided with a newly generated temporary key pair. On the initial agent run, a different set of keys is generated into the directory mounted using the -- volume parameter:

--volume </path/to/ssh/keys/directory>:/home/agent/.ssh

Copy

The Agent alters its own authentication method and sets it to the generated public key. On subsequent agent runs, the generated keys should be reused by the agent container by mounting the same directory specified as the prior --volume.

Optional agent container environment variables¶

Agent provides additional options that can be controlled via environment variables. They are all optional.

SNOWFLAKE_PRIVATEKEYPASSWORD

Specifies the password to the private key file. It can be added if you want the private key be generated with a password or if you are reusing keys that were generated with a password.

SNOWFLAKE_ENFORCEDURL

Specifies the URL to connect to Snowflake, which overrides the automatically specified URL. which can be used to connect the agent to Snowflake via privatelink.

JAVA_OPTS

Specifies the Java options or system properties passed to the container. Separate each property with a space.

Note the following:

Use the -Xmx option to set the maximum Java heap size. Snowflake recommends setting this value to the amount of memory available to the container minus 1GB.

For example, if the container has 6GB available, set the following:
```
JAVA_OPTS=-Xmx5g
```
Copy
To configure the proxy that allows the agent to connect to Snowflake, set the following system properties:
```
JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
```
Copy
To bypass the proxy for one or more IP addresses or hosts, set the http.nonProxyHosts system property to the list of hosts:
- Use a pipe symbol (|) to separate the host names.
- To specify hostnames that match a pattern, use an asterisk (*) as a wildcard character.
  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port> -Dhttp.nonProxyHosts='*.my_company.com|localhost|myorganization-myaccount.snowflakecomputing.com|192.168.91.*'
  Copy

To pass credentials to the proxy server, set the http.proxyUser and http.proxyPassword system properties.

JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
-Dhttp.proxyUser=<proxy-user> -Dhttp.proxyPassword=<proxy-pass>

Copy

Adding server SSL certificate to the agent trust store¶

If you want use SSL you need to import server certs to the Java Truststore in the Docker container. Java Truststore is located in

/opt/java/openjdk/lib/security/cacerts

Copy

The easiest way to provide such certs is to add all required server certificates to the existing cacerts file on your machine:

openssl x509 -outform der -in ca-root.pem -out ca-root.der
keytool -import -alias server-root -keystore $JAVA_HOME/jre/lib/security/cacerrts -file ca-root.der

Copy

And then mount this file while running the Docker image:

docker run --volume $JAVA_HOME/jre/lib/security/cacerrts:/opt/java/openjdk/lib/security/cacerts

Copy

Using a privatelink connection to Snowflake¶

By default, the agent does not use privatelink connections. In order to connect the agent with Snowflake via privatelink, the SNOWFLAKE_ENFORCEDURL environment variable must be passed to the agent container. This environment variable must contain the privatelink url. For a list of all optional environment variables, see Optional agent container environment variables.

Next steps¶

After completing these procedures, follow the steps in Configuring replication for the Snowflake Connector for MySQL.