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>" } }
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
Data source parameters¶
url
Specifies the MySQL connection URL in the following syntax:
jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]
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 inurl
:jdbc:mariadb://<host>:<port>/?verifyServerCertificate=true&useSSL=true&requireSSL=true&allowPublicKeyRetrieval=true
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
andREPLICATION CLIENT
to be able to read frombinlog
.For example:
GRANT REPLICATION SLAVE ON *.* TO '<username>'@'%' GRANT REPLICATION CLIENT ON *.* TO '<username>'@'%'
SELECT
permission to all tables that are replicated.For example:
GRANT SELECT ON <schema>.* TO '<username>'@'%' GRANT SELECT ON <schema>.<table> TO '<username>'@'%'
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
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
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
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>
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.*'
To pass credentials to the proxy server, set the
http.proxyUser
andhttp.proxyPassword
system properties.JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port> -Dhttp.proxyUser=<proxy-user> -Dhttp.proxyPassword=<proxy-pass>
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
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
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
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.