Google Cloud MySQL Setup Guide

Note: Google Cloud MySQL automatically manages its binary log retention period. By default, binary logs are purged after 7 days. If your connector is paused for more than 7 days, you may be required to re-sync your data.

Follow these instructions to replicate your Google Cloud MySQL database to your destination via Fivetran.

Prerequisiteslink

To connect your MySQL database to Fivetran, you need:

• MySQL version 5.6 or above.
• Your database host’s IP (for example, 1.2.3.4) or domain (your.server.com)
• Your database’s port (usually 3306)
• TLS enabled on your database (if you want to connect to Fivetran directly)

Additionally, if you will be using binary log as your incremental sync mechanism:

• If you upgrade your database version to 8.0.23 or above, you must set the binlog_row_metadata value to MINIMAL before you do the upgrade. If you set binlog_row_metadata to MINIMAL after the upgrade, your existing Google Cloud MySQL connectors will fail and you may need re-sync them.
• A unique replica ID for Fivetran. We need this ID because we connect to your database as a replica. We provide a random replica ID in your setup form, but you can provide your own if you’d prefer or if the form’s replica ID conflicts with one of your existing replica IDs.

  NOTE: The replica ID is a unique ID within the MySQL replica set. By default, the replica ID is a random integer greater than 1000.

For the prerequisites for connecting via an SSH tunnel, see Fivetran’s Connection Options page.

Choose incremental sync mechanismlink

To keep your data up to date after the initial sync, we use one of the following incremental sync methods:

• Binary log
• Fivetran Teleport Sync PRIVATE PREVIEW

Each of these methods keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.

To learn the differences between the two mechanisms, see our incremental update documentation.

Choose your connection optionlink

Google Cloud MySQL products require client certificates to connect to a database that is configured to require SSL. Fivetran only supports Server Certificate authentication.

To connect Fivetran to your Google Cloud MySQL product, you must perform either of these two actions:

1. Select the Allow Unsecured Connections option from the Connections tab of your Cloud SQL instance.

   IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran.

2. Create a Google Cloud Virtual Machine to act as a proxy to connect using one of these options:
   • SSH tunneling with client certificates configured
   • Google Cloud SQL Proxy
   • Certificate forwarding using stunnel

Allow access to master or replicalink

Your Google Cloud MySQL master or replica server needs to be made accessible to Fivetran’s data processing server.

1. In your MySQL Dashboard, select the “Master” instance you would like to use.

   

   IMPORTANT: If you’re using a replica, select Replicas from the left menu, then choose the Replica instance that you want to connect to Fivetran.

2. Note the Master or Replica IP address and port number (3306 by default).

   

3. Add Fivetran’s IP addresses to access the port number of your master or replica node unless you already have 0.0.0.0/0 as an allowed network.

   

Check that binary logging is enabled (optional, binary log only)link

By default, Google enables binary logging through point-in-time recovery for MySQL master databases. To confirm that binary logging is enabled on your database, follow the steps below.

NOTE: If you are using a read replica, you must enable binary logging in your read replica. Read Google Cloud’s Replication in Cloud SQL documentation to see which databases support enabling binary logging on read replicas and the Point-in-time recovery documentation to learn how to enable it.

1. In your MySQL Dashboard, click Backups in the left menu.

   

2. In the Settings section, confirm that Enable point-in-time recovery is set to Enabled.

   

3. If point-in-time recovery is disabled, you must enable it. Click Edit.

   

4. In the Edit backups settings window, select the Enable point-in-time recovery box to enable binary logging.

5. Click Save.

   NOTE: Your Cloud SQL instance will restart when you enable point-in-time recovery.

Create userlink

Next, you must create a Fivetran user in your Google Cloud MySQL database. Make sure to do this on the master node because replicas are read-only.

How you create a user depends on which incremental update mechanism you are using. Follow the instructions below for your incremental update mechanism.

WARNING: This user must be reserved for Fivetran use only and must be unique to your connector. For more information, see our MySQL documentation.

Binary loglink

1. Open a connection to your Google Cloud MySQL database using your favorite SQL tool (for example, MySQL Workbench or the “mysql” command in your operating system’s terminal window).

2. Create a Fivetran user and grant replication permissions by running the following SQL commands. Replace <username> and password with a username and password of your choice…

CREATE USER '<username>'@'%' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<username>'@'%';

NOTE: You must grant the Fivetran user SELECT permissions for all of the columns in the tables that you want to sync. When we do not have SELECT access to all columns in a table, we trigger a re-sync for that table, which slows down your syncs. If you don’t want to sync certain columns, you can exclude them from your syncs in the Fivetran dashboard.

Fivetran Teleport Sync PRIVATE PREVIEWlink

1. Open a connection to your MySQL primary database using your favorite SQL tool (for example, MySQL Workbench or the mysql command in your operating system’s terminal window).

2. Create a Fivetran user and grant SELECT permissions by running the following SQL commands. Replace <username> and password with a username and password of your choice.

   CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password';
   
   -- Option 1: Grant user SELECT permission on all tables and columns
   GRANT SELECT ON *.* TO <username>@'%';
   
   -- Option 2: Grant user SELECT permission on only specified table and columns 
   GRANT SELECT ON <tables/columns> TO <username>@'%';
   

Choose schema prefixlink

Each schema from your source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas “foo” and “bar” and if you choose the prefix “pre”, then you will get schemas “pre_foo” and “pre_bar” in the output.

Setup testslink

Fivetran performs the following tests to ensure that we can connect to your Google Cloud MySQL database and that it is properly configured:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It generates a pop-up window where you must verify the SSH fingerprint. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you are connecting directly.)
• The Connecting to Host Test verifies that the database host is not private and checks that we can connect to the host.
• The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you are connecting using an SSH tunnel and did not choose to require TLS.)
• The Validating Database User Test validates the database credentials you provided in the setup form.
• (Binary log only) The Checking Database Configuration Test verifies that we can find your database’s server ID. It then checks your binary log configuration and confirms that we can connect to the binary log.
• The Validating Database Type Test checks that your database type matches the connector type. For example, this test will fail if you try to set up a Google Cloud MySQL connector with a MySQL RDS database. To perform this test, the Fivetran user needs permission to access the mysql system database, though we do not sync any data from that DB.
• The Validating Speed Setup test checks how quickly Fivetran can fetch data from your source database. During this test, we measure our ability to download sample data from your source database to Fivetran, but we do not perform a full sync. We start a timer, then download the sample data in memory. We then calculate the connector speed based on how much data we downloaded and how long it took to download. The test shows a warning if the download speed is less than 5MB/sec.

NOTE: The tests may take a few minutes to finish running.

Related Contentlink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home