# 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. {% hint style="info" %} The MySQL plugin is available on the Snowflake Marketplace with a 30-day free trial. Please [contact us](https://omnata.com/contact-us) to discuss any extra POC requirements. {% endhint %} ### 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](https://omnata.com/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](https://docs.snowflake.com/en/user-guide/private-connectivity-outbound): 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](https://ngrok.com/). 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. {% hint style="info" %} Support for MySQL Privatelink connections is coming soon. If you have further questions please [contact us](https://omnata.com/contact-us). {% endhint %} #### 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. {% hint style="info" %} You do not need to sign up for an ngrok account. We provide ngrok as an embedded OEM product. {% endhint %} 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](https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/integrations/ngrok) 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. {% hint style="info" %} Omnata recommends using the High Watermark method whenever possible as it is more efficient than full refresh and reduces load on your MySQL database. {% endhint %} 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 {% hint style="info" %} The following should be viewed as general guidance. Performance varies significantly between deployments. {% endhint %} #### 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](https://docs.snowflake.com/en/user-guide/warehouses-multicluster), 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 ```sql select * from table(OMNATA_MYSQL_PLUGIN.UDFS.SQL_QUERY( 'my-mysql-connection', 'my_database', 'select * from customers' )) ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/mysql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.