Set up the Openflow Connector for MySQL¶

Prerequisites¶

1. Ensure that you have reviewed About Openflow Connector for MySQL.

2. Ensure that you have a MySQL 8 or a later version to synchronize data with Snowflake.

3. Ensure that you have set up Openflow.

4. As a database administrator, perform the following tasks:

   1. Enable binary logs, then save and configure its format as follows:

      log_bin	Set to on. This enables the binary log that records structural and data changes.
      binlog_format	Set to row. The connector supports only row-based replication. MySQL 8.x versions may be the last ones to support this setting, and future versions will only support row-based replication. Not applicable in GCP Cloud SQL, where it is fixed at the right value.
      binlog_row_metadata	Set to full. The connector requires all row metadata to operate, most importantly, column names and primary key information.
      binlog_row_image	Set to full. The connector requires that all columns be written into the binary log. Not applicable in Amazon Aurora, where it is fixed at the right value.
      binlog_row_value_options	Leave empty. This option ony affects JSON columns, where it can be set to include only the modified parts of JSON documents for UPDATE statements. The connector requires that full documents are written into the binary log.
      binlog_expire_logs_seconds	Set to at least a few hours, or longer to ensure that the database agent can continue incremental replication after extended pauses or downtime. Snowflake recommends that you set the binary log expiration period (``binlog_expire_logs_seconds`) <https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds>`_ to at least a few hours to ensure stable working of the connector. After binary log expiration period ends, binary log files might be automatically removed. If the integration is paused for a long period, for example due to maintenance work, and the expired binary log files are deleted during this time, Openflow will not be able to replicate the data from these files. If you’re using scheduled replication, the value needs to be longer than the configured schedule.

      See the following code as a example:

      log_bin = on
      binlog_format = row
      binlog_row_metadata = full
      binlog_row_image = full
      binlog_row_value_options =
      

      Copy

   2. Connect via SSL. If you’re planning to use an SSL connection to MySQL, prepare the root certificate for your database server. It is required during configuration.

   3. Create a user for the connector. The connector requires a user with the REPLICATION_SLAVE and REPLICATION_CLIENT privileges for reading the binary logs. Grant these privileges:

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

      Copy

   4. Grant the SELECT privilege on every replicated table:

      GRANT SELECT ON <schema>.* TO '<username>'@'%'
      GRANT SELECT ON <schema>.<table> TO '<username>'@'%'
      

      Copy

      For more information on replication security, see Binary log.

5. As a Snowflake account administrator, perform the following tasks:

   1. Create a Snowflake user with the type as SERVICE. Create a database to store the replicated data, and set up privileges for the Snowflake user to create objects in that database by granting the USAGE and CREATE SCHEMA privileges.

      CREATE DATABASE <destination_database>;
      CREATE USER <openflow_user> TYPE=SERVICE COMMENT='Service user for automated access of Openflow';
      CREATE ROLE <openflow_role>;
      GRANT ROLE <openflow_role> TO USER <openflow_user>;
      GRANT USAGE ON DATABASE <destination_database> TO ROLE <openflow_role>;
      GRANT CREATE SCHEMA ON DATABASE <destination_database> TO ROLE <openflow_role>;
      CREATE WAREHOUSE <openflow_warehouse>
           WITH
               WAREHOUSE_SIZE = 'MEDIUM'
               AUTO_SUSPEND = 300
               AUTO_RESUME = TRUE;
      GRANT USAGE, OPERATE ON WAREHOUSE <openflow_warehouse> TO ROLE <openflow_role>;
      

      Copy

   2. Create a pair of secure keys (public and private). Store the private key for the user in a file to supply to the connector’s configuration. Assign the public key to the Snowflake service user:

      ALTER USER openflow_user SET RSA_PUBLIC_KEY = 'thekey';
      

      Copy

      For more information, see pair of keys.

   3. Designate a warehouse for the connector to use. Start with the smallest warehouse size, then experiment with size depending on the amount of tables being replicated, and the amount of data transferred. Large table numbers typically scale better with multi-cluster warehouses, rather than the warehouse size.

Import the connector definition into Openflow¶

1. Download the connector definition file.

2. Import the connector definition into Openflow:

   1. Open the Snowflake Openflow canvas.

   2. Add a process group. To do this, drag and drop the Process Group icon from the tool palette at the top of the page onto the canvas. Once you release your pointer, a Create Process Group pop-up appears.

   3. On the Create Process Group pop-up, select the connector definition file to import.

Configure the connector¶

You can configure the connector for the following use cases:

[
    {
        "schema": "<source table schema>",
        "table" : "<source table name>",
        "included": ["<column name>", "<column name>"],
        "excluded": ["<column name>", "<column name>"],
        "includedPattern": "<regular expression>",
        "excludedPattern": "<regular expression>",
    }
]

customers_1234_1
customers_1234_2
orders_5678_1
orders_5678_2

Run the flow¶

1. Right-click on the plane and select Enable all Controller Services.

2. Right-click on the imported process group and select Start. The connector starts the data ingestion.