Google Cloud SQL (MySQL)

Create a connection between Faraday and Google Cloud SQL (MySQL) so that your data is always up to date to make predictions, and your predictions can seamlessly sync back to your database.

In this tutorial, we'll show you how to:

  • Connect your Google Cloud SQL (MySQL) account to Faraday using a connection.

Let's dive in.

  1. You'll need a Faraday Enterprise account — talk to sales to get set up.

Prerequisites

You'll need the following details to create your connection to Google Cloud SQL (MySQL):

  • Instance connection name requiredtextInstance connection name, in the form PROJECT:REGION:INSTANCE. Enable the Cloud SQL Admin API in PROJECT and grant this connection's gcp_service_account_email the Cloud SQL Client role there.
  • User requiredtext
  • Password requiredtext
  • Database requiredtext
  • GCP service account email optionaltextGoogle Cloud service account Faraday generated for this connection. Grant this account the Cloud SQL Client role on the project that holds your instance, then force-update the connection. Unique per Faraday Cloud SQL connection.

Granting access

First, you'll need Faraday access to your Google Cloud SQL (MySQL) account.

Google Cloud SQL is a hosted database service. Faraday reaches your instance through the Cloud SQL Auth Proxy as a Google Cloud service account, then signs in to the database with a user and password.

Setup process

  1. Enable the Cloud SQL Admin API in the project that holds your instance.
  2. Create your Faraday connection using the API or UI. Faraday generates a unique Google Cloud service account for this connection.
  3. Copy the service account email from the connection details (gcp_service_account_email).
  4. Grant that account Cloud SQL Client (roles/cloudsql.client) on the project that holds your instance.
  5. Create a database user for Faraday, as described below.
  6. Test the connection with Force Update. The connection will error until the grant is in place.

The same generated account is used for Datasets (send data to Faraday) and Targets (receive data from Faraday).

Connections created before unique identities still use Faraday's shared account, faraday-incoming@production-237317.iam.gserviceaccount.com, for both Datasets and Targets.

Database user

MySQL connections are host-based and require a user and password. We suggest that you create a Faraday-only database to both send and receive data. Within this database, Faraday would have full read and write access. Alternatively, you can give Faraday access to certain tables in a shared database.

Example commands:

  1. CREATE USER 'faraday_user'@'%' IDENTIFIED BY '***'
  2. CREATE DATABASE faraday_database
  3. GRANT ALL PRIVILEGES ON faraday_database.* TO 'faraday_user'@'%'

This connection type can be placed behind an SSH bastion (aka jump server). In that case, specify the user and host of the SSH bastion.

This connection type can also be placed behind a load balancer. In that case, specify the host of the load balancer. Faraday suggests that you use an unguessable string somewhere in the path to your data. This avoids what is called the Confused deputy problem

For example, let's say you were using S3. Instead of naming an S3 bucket s3://faraday-acme/, name it s3://faraday-acme-pwiiprz162ez. This guarantees that malicious actors cannot guess the name and request that Faraday import data from it into their account. The same logic applies to any path that is used to locate data.

Connecting

API via cURL
Dashboard

Use a POST /connections request:

curl https://api.faraday.ai/connections --json '{
  "name": "Google Cloud SQL (MySQL)",
  "options": {
    "type": "gcp-cloud-sql-mysql",
    "instance_connection_name": "...",
    "user": "...",
    "password": "...",
    "database": "..."
  }
}'
  1. Wait briefly while Faraday establishes your connection. It shouldn't take long.

Your new connection is now ready to use.