MySQL

About

Omnata supports replication of your MySQL source database to Snowflake as well as providing direct query capabilities to your MySQL database. At the moment, the connector only support query-based replication strategies incremental high-watermark and full-refresh. We plan to add support log-based replication strategies in future releases.

The MySQL plugin is available on the Snowflake Marketplace with a 30-day free trial. Please contact us to discuss any extra POC requirements.

Supported Services

• Generic MySQL install

• Amazon RDS for MySQL

Compatibility Levels

The MySQL plugin supports version 8.4.x databases. If you're using MySQL 5.7 or need to confirm compatibility with a specific MySQL version, please contact us.

Connectivity options

The Omnata Native App can connect to your database in three ways:

• Snowflake private connectivity endpoints (Privatelink)

• Omnata-ngrok tunnel

• Directly via the internet (if the database network is publicly accessible)

Snowflake private connectivity endpoints (aka Privatelink)

To use Snowflake private endpoints:

1. You must have Snowflake Business Critical Edition

2. Your Snowflake account must be using the same cloud provider as the one you are connecting to. For example, to connect to an AWS Database instance via Privatelink, your Snowflake account must be on AWS.

3. Snowflake supports AWS, Azure and GCP.

Omnata-ngrok tunnels

The Omnata tunnelling uses an SSH Tunnel which is provided by our OEM partner ngrok. Omnata establishes a secure TLS tunnel to the ngrok server and then connects to the MySQL database through a tunnel established from your Snowflake account. This ensures that your database is not exposed to the internet and that your data is secure.

Should I use Privatelink or ngrok?

You will use one or the other, you don't need both.

If you have Snowflake Business Critical and the required cloud infrastructure, then we recommend using Privatelink and you won't need ngrok. If you have any other Snowflake Edition, mis-matched cloud providers and/or on-premise data, then ngrok is the best solution for private connectivity.

Authentication

MySQL Authentication

Omnata supports standard MySQL authentication. When you create a connection, you will need to provide the username and password for the MySQL user you want to use to connect to the database.

Connection Configuration

MySQL Configuration

Generic Installs

No additional configuration is required for MySQL databases for standard replication using Full Refresh or High Watermark methods.

Amazon RDS for MySQL

For direct connections:

• Security Groups - Ensure your RDS security group allows inbound connections from Snowflake

For ngrok and Privatelink connections:

• Private Access - Configure appropriate VPC settings and security groups

• Public accessibility - Not required

Privatelink

You can use Azure, AWS and GCP private link to connect to databases hosted in your cloud VPC, examples are documented below.

It is also possible to connect via Private link to databases hosted on-premise. This uses the same network load balancer approach, shown in the AWS example below, and an existing network bridge between your cloud and on-premise environments.

Support for MySQL Privatelink connections is coming soon. If you have further questions please contact us.

ngrok Tunnel Configuration

If you choose Omnata-ngrok tunnel as the connectivity method, the Omnata UI will guide you through the steps to establish a secure tunnel to your MySQL database using ngrok.

You do not need to sign up for an ngrok account. We provide ngrok as an embedded OEM product.

The connection workflow is as follows:

1. You create an integration to the ngrok service

2. Omnata creates an ngrok endpoint on your behalf

3. You install the ngrok agent on your network, where it can access your MySQL database

4. The agent establishes an outbound TLS connection from your network to the ngrok endpoint, you do not open any ports.

5. Omnata connects through the tunnel to your MySQL database, again using TLS.

Follow this article to understand the configuration steps in detail.

Troubleshooting Connectivity Issues

The following are common issues and solutions when connecting.

Message	Action
Access denied for user 'username'@'host'	Please verify your username and password are correct. Ensure the MySQL user has appropriate permissions to access the database.
Can't connect to MySQL server on 'host'	Please ensure that the ngrok agent is running and that it can access the MySQL database. Verify that MySQL is running and accepting connections on the configured port (typically 3306).
Host 'host' is not allowed to connect to this MySQL server	The MySQL server is restricting connections from certain hosts. You may need to update the user's host permissions in MySQL or configure your firewall rules.

Inbound Syncs

Omnata connects to your MySQL database using standard connection protocols. Omnata uses the following methods to replicate data from your MySQL source database:

• High Watermark (Incremental)

• Full Refresh

Full Refresh / Incremental (High Watermark)

Omnata can use High Watermark to replicate your data from your source database incrementally, or perform full refresh syncs for tables without suitable timestamp or incrementing columns.

The High Watermark method tracks the maximum value of a specified column (typically a timestamp or auto-incrementing ID) and only retrieves records with values greater than the last synced value. This method captures the current state on each run, so the only history available is from the last sync.

Omnata recommends using the High Watermark method whenever possible as it is more efficient than full refresh and reduces load on your MySQL database.

If a table or view doesn't have any of the following field types available: TIMESTAMP, DATETIME, DATE, or numeric auto-incrementing fields, then only a full refresh option will be available.

Composite keys

Omnata supports composite keys for high watermarking.

Managing Performance

The following should be viewed as general guidance. Performance varies significantly between deployments.

MySQL workload

We generally want to optimize our syncs so that they run in the shortest timeframe possible. However, we need to be mindful of the load these routines place on our MySQL instances. When you run a sync for the first time, the Plugin is requesting all the historical data for the Streams (Tables) and at this time the heaviest load will be placed on your MySQL instance. Subsequent loads will be much lighter for High Watermark strategies. Full refresh strategies will place a similar load on the server for each run of the sync, so use full refresh strategy carefully.

Tuning parameters

Coming soon...

Snowflake warehouse settings

We have designed Omnata to be able to run on a single cluster XS (Extra Small) Warehouse.

However, during these initial sync runs, we are requesting, receiving and processing a lot of data rows. With a single cluster XS Warehouse you will likely see query queueing on the Snowflake Warehouse. In order to reduce this queueing, you can temporarily set the Warehouse to be multi-clustered, which will reduce queueing and the overall duration of these initial sync runs.

Outbound Syncs

Omnata currently does not support outbound syncs for MySQL databases.

Functions

To enable you to query your MySQL database directly from your destination database, Omnata provides a set of UDFs that you can use to query your MySQL database. These UDFs are available in the OMNATA_MYSQL_PLUGIN database and the UDFS schema.

SQL_QUERY

Executes a SQL query on the source database and returns the result as a table.

Parameters:

• CONNECTION_SLUG (VARCHAR): The slug of the connection to query

• DATABASE_NAME (VARCHAR): The name of the database to query

• QUERY (VARCHAR): The SQL query

Examples

select *
from table(OMNATA_MYSQL_PLUGIN.UDFS.SQL_QUERY(
    'my-mysql-connection',
    'my_database',
    'select * from customers'
))