Configuring Cloud SQL to replicate from an external server

This page describes how to configure your external server for replication to Cloud SQL, create a source representation instance on Cloud SQL, and the options for replicating to Cloud SQL. You need to go through all the steps on this page before proceeding to the replication steps for the option you choose.

An alternative to the steps described on this page is the Database Migration Service, which offers continuous replication or one-time database migration from an external server to Cloud SQL.

Before you begin

Terminology

  • External server. The MySQL server external to Cloud SQL that you want to replicate data from. Also known as the source database or external database server.

  • Source representation instance. A mock of a Cloud SQL instance that represents the external server to the Cloud SQL replica. It is visible in the Google Cloud Console and appears the same as a regular Cloud SQL instance, but it contains no data, requires no configuration or maintenance, and does not affect billing. Contains the external server configuration.

  • Cloud SQL replica. The Cloud SQL instance that replicates from the external server. Also known as the external primary read replica.

  • Replication user account. The MySQL user account on the external server with sufficient permissions to allow replication between the external server and Cloud SQL replica.

  • Managed import. The process of importing data directly from the external server to the Cloud SQL replica. In this situation, Cloud SQL connects to the external server using the replication user account and runs mysqldump directly on the external server to import data to the Cloud SQL replica.

Setting up a Google Cloud project

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. Enable the Cloud SQL Admin API.

    Enable the API

  5. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  6. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  7. Enable the Cloud SQL Admin API.

    Enable the API

  8. Make sure you have the Cloud SQL Admin, Storage Admin, and Compute Viewer roles on your user account.

    Go to the IAM page

  9. Enable the Cloud SQL Admin API.

    Enable the API

Installing the Google Cloud SDK

To configure replication, install Google Cloud SDK for your external server. You may want to install this on your external server if it is not already installed elsewhere.

Setting up the external server for replication

External server checklist

  • Ensure that MySQL 5.5, 5.6, 5.7, or 8.0 is installed on your external server. MySQL Community Edition, Cloud SQL for MySQL, and MySQL on Amazon Relational Database Service (RDS) are supported.

    • You must use a major version of MySQL on your Cloud SQL replica that is the same as or greater than the version of MySQL running on your external server.
    • If MySQL 5.5 is installed on the external server, GTID is not supported.
  • Ensure that binary logs are enabled and that:

  • Take note of whether your external server has GTID enabled. This affects the steps you need to take to set up replication.

  • Take note of whether your external server has global read-lock permissions. This affects the steps you need to take to set up replication.

  • If the Cloud SQL replica is going to use a private IP, then the external server's firewall must be configured to allow the entire internal IP range allocated for the private services access of the VPC network that the Cloud SQL replica is going to use as the privateNetwork field of its ipConfiguration settings.

    • To find the internal IP range in the console:
      • Go to the VPC networks page in the Google Cloud Console.
      • Select the VPC network that you want to use.
      • Select the Private service connection tab.
  • If your external server contains DEFINER clauses (views, events, triggers, or stored procedures), depending on the ordering of when these statements are executed, replication might fail. Learn more about DEFINER usage and potential workarounds in Cloud SQL.

Configuring binary log retention

You should configure your external server to retain binary logs for at least 24 hours.

MySQL Community

If your external server is using the MySQL Community Edition, you can use these MySQL commands to update your binary log retention settings.

Cloud SQL for MySQL

If your external server is using Cloud SQL for MySQL, by default, your binary logs are retained for seven days. You can change this setting.

Amazon RDS

If your external server is an Amazon RDS instance, you can use this command to update your binary log retention settings:

call mysql.rds_set_configuration('binlog retention ', HOURS);
Property Description
HOURS The total number of hours of binary logs for the RDS instance to retain.

For more information, see mysql.rds_set_configuration.

Creating a replication user account

A MySQL user account dedicated to managing replication to the Cloud SQL replica must exist on your external server. This user account should only be used to manage replication. Depending on the migration approach you use, you might need to grant permissions to the user later.

To add a replication user account, open a terminal on the external server and enter these MySQL commands:

MySQL

      CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD';
      GRANT SELECT, SHOW VIEW ON *.* TO 'USERNAME'@'HOST';
      FLUSH PRIVILEGES;

example

CREATE USER 'replicationUser'@'%' IDENTIFIED BY '8*&fwwd';
GRANT SELECT, SHOW VIEW ON *.* TO 'replicationUser'@'%';
FLUSH PRIVILEGES;
Property Description
USERNAME The replication user account on the external server.
PASSWORD The password for the replication user account.
HOST Set this to % to accept all connections from any host. In a later step, this is changed to only accept connections from the Cloud SQL replica.

Setting up a source representation instance

The source representation instance references the external server. It only contains the request data from the external server. In this step, you create the request data and use it in a curl command that creates the source representation instance in Cloud SQL.

Creating the request data

The request data contains basic information about your external server in JSON format, The request data can be configured for a Cloud SQL replica on a public or private network and should contain this information:

source.json

    {
      "name": "SOURCE_NAME",
      "region": "REGION",
      "databaseVersion": "MYSQL_VERSION",
      "onPremisesConfiguration": {
        "hostPort": "SOURCE_HOST",
        "username": "USERNAME",
        "password": "PASSWORD",
        "dumpFilePath": "gs://BUCKET/DUMP_FILE",
        "caCertificate": "SOURCE_CERT",
        "clientCertificate": "CLIENT_CERT",
        "clientKey": "CLIENT_KEY"
      }
    }

managed dump example

// example of source.json for external server that
// - initiates replication from a Cloud SQL managed dump
// - does not use SSL/TSL

{
  "name": "cloudsql-source-instance",
  "region": "us-central1",
  "databaseVersion": "MYSQL_5_7",
  "onPremisesConfiguration": {
    "hostPort": "192.0.2.0:3306",
    "username": "replicationUser",
    "password": "486#@%*@"
  }
}

dump file example

// example of source.json for external server that
// - initiates replication from a file
// - uses SSL/TSL

{
  "name": "cloudsql-source-instance",
  "region": "us-central1",
  "databaseVersion": "MYSQL_5_7",
  "onPremisesConfiguration": {
    "hostPort": "192.0.2.0:3306",
    "username": "replicationUser",
    "password": "486#@%*@",
    "dumpFilePath": "gs://replica-bucket/source-database.sql.gz",
    "caCertificate": "content of your certificate",
    "clientCertificate": "content of your certificate",
    "clientKey": "content of your client key"
  }
}

custom import example

// example of source.json for external server that
// - initiates replication from a Cloud SQL custom import
// - does not use SSL/TSL

{
  "name": "cloudsql-source-instance",
  "region": "us-central1",
  "databaseVersion": "MYSQL_5_7",
  "onPremisesConfiguration": {
    "hostPort": "192.0.2.0:3306",
    "username": "replicationUser",
    "password": "486#@%*@"
  }
}
Optional Fields Notes
dumpFilePath Only include if you are setting up replication with a dump file that exists in a Cloud SQL bucket.
caCertificate Only include if SSL/TLS is used on the external server.
clientCertificate Only include if SSL/TLS is used on the external server.
clientKey Only include if SSL/TLS is used on the external server.
Property Description
SOURCE_NAME The name of the source representation instance to create.
REGION The region where you want the source representation instance to reside.
MYSQL_VERSION The MySQL version running on your external server. The choices are MYSQL_5_5, MYSQL_5_6, MYSQL_5_7 or MYSQL_8_0.
SOURCE HOST The IPv4 address and port for the external server, or the the DNS address for the external server. If you use a DNS address, it can contain up to 60 characters. If the external server is hosted on Cloud SQL, the port is 3306.
USERNAME The replication user account on the external server.
PASSWORD The password for the replication user account.
BUCKET The name of the bucket that contains the dump file.
DUMP_FILE A file in the bucket that contains the data from the external server.
CLIENT_CA_CERT The CA certificate on the external server.
CLIENT_CERT The client certificate on the external server. Required only for server-client authentication.
CLIENT_KEY The private key file for the client certificate on the external server. Required only for server-client authentication.

Creating a source representation instance

Before you complete this step, you should have a JSON file that contains your source request data.

Open a terminal and use the following commands to create the source representation instance in Cloud SQL:

curl

    gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data @JSON_PATH \
         -X POST \
         https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances

example

gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data @./source.json \
     -X POST \
     https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances
Property Description
PROJECT_ID The ID for your project in Google Cloud.
JSON_PATH The path to the JSON file that contains the request data for the external server.

Setting up a Cloud SQL replica

The Cloud SQL replica eventually contains the data from the external server. In this step, you create the request data and use it in a curl command that creates the Cloud SQL replica in Cloud SQL.

Creating the request data

The request data contains basic information about your external server and Cloud SQL replica in JSON format. The request data can be configured for a Cloud SQL replica on a public or private network and should contain this information:

replica.json

    {
        "settings": {
            "tier": "TIER",
            "dataDiskSizeGb": "DISK_SIZE",
            "ipConfiguration": {
                "privateNetwork": "projects/PROJECT_ID/global/networks/NETWORK_NAME"
             }
        },
        "masterInstanceName": "SOURCE_REPRESENTATION_INSTANCE_NAME",
        "region": "SOURCE_REGION",
        "databaseVersion": "MYSQL_VERSION",
        "name": "REPLICA_NAME"
    }

example

    {
        "settings": {
            "tier": "db-custom-4-15360",
            "dataDiskSizeGb": "100"
        },
        "masterInstanceName": "source-instance",
        "region": "us-central1",
        "databaseVersion": "MYSQL_5_7",
        "name": "replica-instance"
    }
Optional Fields Notes
ipConfiguration Include this only if the Cloud SQL replica is on a private network.
Property Description
TIER The type of machine to host your replica instance. If you do not know which machine type to use, start with db-custom-2-7680. You can change its size and other supported values later if needed.
DISK_SIZE The storage size for the Cloud SQL replica, in GB.
PROJECT_ID The ID of your project in Google Cloud.
NETWORK_NAME The name of the private network to use with the Cloud SQL replica.
SOURCE_REPRESENTATION_INSTANCE_NAME The name of the source representation instance.
SOURCE_REGION The region assigned to the source representation instance.
MYSQL_VERSION The MySQL version to use with the Cloud SQL replica. Your choices are MYSQL_5_6, MYSQL_5_7, and MYSQL_8_0. This can match the MySQL version running on your external server or it can be at most one version higher. For example, if you're using MySQL 5.5 on your external server, you can use MySQL 5.6 with the Cloud SQL replica.
REPLICA_NAME The name of the Cloud SQL replica to create.

Creating the Cloud SQL replica

Before you complete this step, you should create a JSON file that contains your replica request data. Then, open a Cloud Shell terminal and use these commands to create a Cloud SQL replica in Cloud SQL:

curl

    gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data @JSON_PATH \
         -X POST \
         https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances

example

    gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data @./replica.json \
         -X POST \
Property Description
JSON_PATH The path to the JSON file that contains the request data for the Cloud SQL replica.
PROJECT_ID The ID of your project in Google Cloud.

What you should see when you finish

To ensure your instances were set up correctly, go to the Cloud SQL Instances page.

You should see your source representation instance and Cloud SQL replica. They look similar to the following:

Instance ID Type Public IP
(-) source-representation-instance MySQL external primary 10.68.48.3:3306
     replica-instance MySQL read replica 34.66.48.59

Adding users to the Cloud SQL replica

MySQL user accounts cannot be imported from the external server, but you can create them on a Cloud SQL replica. Do this before you replicate from the external server.

Getting the Cloud SQL replica's outgoing IP address

You can use the outgoing IP address of the Cloud SQL replica to create a secure connection between the external server and the Cloud SQL replica.

To get the outgoing IP address for the replica:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Next to the Cloud SQL replica's public IP address, hold the pointer over the More info tooltip and retrieve the outgoing IP address. Note that the outgoing IP address is not the IP address displayed in the main listing for the replica in the Cloud Console.

gcloud

gcloud sql instances describe REPLICA_NAME --format="default(ipAddresses)"
Property Description
REPLICA_NAME The name of the Cloud SQL replica whose
outgoing public IP address you want to
retrieve.

Allowing incoming connections on the external server

The Cloud SQL replica needs to connect to the external server for replication to succeed. You must configure the network firewall for your external server to accept connections from the Cloud SQL replica's outgoing IP address if the following conditions apply:

  • The external server is behind a firewall or some other network restriction.
  • Your Cloud SQL replica is using a public IP.

To connect to the Cloud SQL replica, you use the replica's primary IP address. This IP address is displayed in the Cloud Console.

Updating the source representation instance to allow replication to the Cloud SQL replica

After you set up the source representation instance for the Cloud SQL replica, you might need to update the source representation instance. For example, these scenarios require an update to your configurations:

  • The host, port, or IP of the external server changes.
  • You want to use a different MySQL replication user.
  • The password of the MySQL replication user changes.
  • The SSL certificates used to securely connect to the external server changes.

Setting up the initial data load and replication to Cloud SQL

There are three options for performing the initial load of data from the external server into the Cloud SQL replica:

  • A managed import uses a service that extracts data from the external server and directly imports it into the Cloud SQL instance. This option is recommended for global transaction identifier (GTID) replication. If your external server is on RDS, and RDS uses GTID or binlog replication, this is the recommended option. For more information, see Using a managed import to set up replication from external databases.
  • A dump file requires you to create a dump file of your external server, move it to a Cloud Storage bucket, and import it into Cloud SQL. If your server is on RDS and GTID is disabled, you can't use this option. For more information, see Using a dump file to set up replication from external databases.
  • A custom import requires you to make a Cloud SQL primary instance, perform a custom data import using your preferred tools, demote it to a Cloud SQL replica, then set up external server replication. This works best for very large databases. For more information, see Using a custom import to set up replication from large external databases.

Monitoring replication

Confirming the replication status

When the Cloud SQL replica finishes the initial data load, it connects to the external server and applies all the updates that were made after the export operation. Follow these steps to confirm your replication status.

It's important to check the replication status before promoting the replica to a stand-alone instance. If the replication process is not complete and successful, a promoted replica doesn't have all the changes from your external server.

If replication delay is not trending toward 0, take steps to address it.

Completing a migration

Promoting the replica and adding high availability and read replicas

These steps prepare your instance for production.

  1. Promote the replica to a primary instance. Note that GTID and binary logs are turned on and the scheduled backups are enabled on the promoted replica.
  2. Add read replicas to your instance.
  3. Configure your instance for high availability.

What's next