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.1.2. 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.

  1. 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
  2. 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).

  3. Give each data source a name consisting only of UPPERCASE letters and numbers.

  4. Make a note of the data source names; you will need them when configuring replication.

  5. 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

Before starting the agent, ensure the warehouse dedicated for connector operations (<ops_wh>) is resumed, as the agent cannot resume the warehouse on its own.

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:

  1. Prepare downloaded mariadb driver.

  2. Prepare json file with snowflake connection configuration downloaded from wizard.

  3. Prepare json file with data sources configuration.

  4. 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

Next steps

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