Replicating from an External Server

This page describes how to create a configuration that replicates data from a source database server to Cloud SQL Second Generation replicas. This configuration is sometimes referred to as an external master configuration.

The source database server can be any MySQL server that meets all of the server requirements, including Cloud SQL instances. Instances hosted by other cloud providers can also serve as the data source, provided they meet the requirements, including supporting GTID.

For more detailed information about this configuration, see About Replicating from an External Server.

Before you begin

Before you set up replication from an external server, you must complete the following steps:

  • Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  • Select or create a GCP project.

    Go to the Manage resources page

  • Make sure that billing is enabled for your project.

    Learn how to enable billing

  • Enable the Cloud SQL Admin API.

    Enable the API

  • Install and authenticate the gcloud command-line tool.
  • Ensure that your server meets the requirements for the source database server.
  • Determine what level of security you require for the connections between the master and the replicas, and obtain the required certificate files.
  • Decide which GCP region you want to use to contain your Cloud SQL replicas.
  • Assemble the required information about your source database server:

    • External IPv4 address and port number

      By default, MySQL uses port 3306.

    • MySQL replication user account and password
    • MySQL version number
    • Location of all required SSL/TLS certificates and keys, depending on the level of security you selected
  • Be prepared to update the network firewall for your source database server to accept connections from the Cloud SQL replica.

    This step must be completed within 30 minutes of creating the replica.

  • If you are not a project owner, you must have the Storage Admin role.

Requirements for the source database server

Before you can replicate from an external server to a Cloud SQL replica, you must ensure that the source database server meets these configuration requirements:

Configuration process

To set up replication from an external database server, you perform the following steps:

  1. Create a Cloud Storage bucket for your data.
  2. Export your data to Cloud Storage.
  3. Set up the replication configuration.
  4. Configure the source database server to accept connections from the replica.

    Note: this step must be done within 30 minutes of replica creation.

  5. Restrict access to the MySQL replication user.

  6. Finalize the replica configuration.
  7. Confirm replication status.
  8. Clean up your storage.

1. Create a Cloud Storage bucket for your data

Create a bucket to temporarily hold your data during the export process, or use an existing bucket.

For more information about creating a bucket, see Creating Storage Buckets. For more information about the Storage Admin role, see Cloud Storage IAM Roles.

2. Export your data to Cloud Storage

The replica looks for its data in Cloud Storage, so you put a copy of the server's data there. You can continue to accept write operations to your server after the export. After the replica finishes importing the dump file, it starts processing all changes since the export was taken and eventually catches up to the master.

From a machine with the gcloud tool installed and network connectivity to your MySQL server, run the following command:

mysqldump \
    -h [MASTER_IP] -P [MASTER_PORT] -u [USERNAME] -p \
    --databases [DBS]  \
    --hex-blob  --skip-triggers  --master-data=1  \
    --order-by-primary --compact --no-autocommit \
    --default-character-set=utf8mb4 --ignore-table [VIEW] \
    --single-transaction --set-gtid-purged=on | gzip | \
    gsutil cp - gs://[BUCKET]/[PATH_TO_DUMP]

Replace [PROPERTIES_IN_BRACKETS] with the following values:

Property Value
[MASTER_IP] The IPv4 address for the source database server.
[MASTER_PORT] The port for the source database server.
[USERNAME] The MySQL replication user account.
[PASSWORD] The password for the MySQL replication user account.
[DBS] Comma-separated list of all databases on the source database server, except for the system databases (sys, mysql, performance_schema, and information_schema). Use the SHOW DATABASES MySQL command to list your databases.
[VIEW] All views present in the databases you are dumping must be ignored. For multiple views, use this option multiple times.
[BUCKET] The name of the bucket you created to hold the export file.
[PATH_TO_DUMP] The path to the export file.

If you see a warning about partial dumps and GTIDs, that is expected.

This command might look like the following example:

mysqldump \
    -h 192.0.2.1 -P 3306 -u replicationUser \
    --databases guestbook  \
    --hex-blob  --skip-triggers  --master-data=1  \
    --order-by-primary --compact --no-autocommit \
    --default-character-set=utf8mb4 --ignore-table view1 --ignore-table view2 \
    --single-transaction --set-gtid-purged=on | gzip | \
    gsutil cp - gs://export-bucket/hq-master1.sql.gz

3. Create the replication configuration

Console

This step creates the Cloud SQL replica and the source representation instance.

  1. Open the Cloud SQL instance list in the Google Cloud Platform Console.

    Open the instance list

  2. Click Migrate data in the button bar to open the Cloud SQL Migration Assistant.

  3. Click Begin migration.
  4. In the Source database name field, provide a name for the replication configuration in Cloud SQL.

    This name is a reference for the configuration, use any valid Cloud SQL instance name.

  5. Enter the IP address and port number for your source database server.

  6. Provide the user name and password for the MySQL user that will be used for the replication connection.
  7. Select the MySQL version of your source database server.
  8. If you are using SSL/TLS for the connection between the replica and the source database server (recommended), select Enable SSL/TLS security and provide the SSL/TLS certificate information for your source server.

    For more information about SSL/TLS options, see SSL/TLS options.

  9. Click Next, and fill in the details for your replica.

    To ensure that the import happens as efficiently as possible, configure the replica with roughly enough storage to contain the database. Size the machine type to be similar to the source server.

  10. Provide the path to the dump file you uploaded to Cloud Storage earlier.

  11. Click Create to create the replica.
  12. Click Next.

gcloud

  1. Create the source representation instance.

    gcloud beta sql instances create [SOURCE_REPRESENTATION_NAME] \
        --region=[REGION] --database-version=[MYSQL_VERSION] \
        --source-ip-address=[SOURCE_IP] --source-port=[SOURCE_PORT]
    

    Replace [PROPERTIES_IN_BRACKETS] with the following values:

    PropertyValue
    [SOURCE_REPRESENTATION_NAME]The name of the source representation instance. Use any valid Cloud SQL instance name.
    [REGION]The region where you want your Cloud SQL replicas to reside.
    [MYSQL_VERSION]The MySQL version running on your source database server: MYSQL_5_6 or MYSQL_5_7.
    [SOURCE_IP]The externally accessible IPv4 address for the source database server.
    [SOURCE_PORT]The externally accessible port for the source database server.
  2. Create the Cloud SQL replica:

    gcloud beta sql instances create [REPLICA_NAME] \
        --master-instance-name=[SOURCE_REPRESENTATION_NAME] \
        --master-username=[USERNAME] --prompt-for-master-password \
        --master-dump-file-path=gs://[BUCKET]/[PATH_TO_DUMP] \
        --master-ca-certificate-path=[SOURCE_SERVER_CA_PATH] \
        --client-certificate-path=[CLIENT_CERT_PATH] \
        --client-key-path=[PRIVATE_KEY_PATH] \
        --tier=[MACHINE_TYPE] --storage-size=[DISK_SIZE]
    

    Replace [PROPERTIES_IN_BRACKETS] with the following values:

    PropertyValue
    [REPLICA_NAME]The name of the Cloud SQL read replica. Use any valid Cloud SQL instance name.
    [SOURCE_REPRESENTATION_NAME]The name of the source representation instance you created in the previous step.
    [USERNAME]The MySQL replication user account on the source database server.
    [BUCKET]The name of the bucket you created to hold the export file.
    [PATH_TO_DUMP]The path to the export file.
    [SOURCE_SERVER_CA_PATH]The local path to where the CA certificate of the source database server is stored. Required only for SSL/TLS.
    [CLIENT_CERT_PATH]The local path to where the client certificate is stored. Required only for server-client authentication.
    [PRIVATE_KEY_PATH]The local path to where the private key file for the client certificate is stored. Required only for server-client authentication.
    [MACHINE_TYPE]The size of your replica. Generally, the replica should be about the same size as the source database server. If you are unsure what machine type to use, start with db-n1-standard-2. You can change its size later if needed.
    [DISK_SIZE]The storage size for the replica, in GiB. For best performance, create the replica with roughly enough storage to hold the entire import file.

cURL

1. Create the Cloud SQL source representation instance

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{
         "name": "[SOURCE_REPRESENTATION_NAME]",
         "region": "[REGION]",
         "databaseVersion": "[MYSQL_VERSION]",
         "onPremisesConfiguration": {
             "hostPort": "[SOURCE_SERVER_IP]:[SOURCE_SERVER_PORT]"
         }
     }' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances

Replace [PROPERTIES_IN_BRACKETS] with the following values:

PropertyValue
[SOURCE_REPRESENTATION_NAME]The name of the source representation instance. Use any valid Cloud SQL instance name.
[REGION]The region where you want your Cloud SQL replicas to reside.
[MYSQL_VERSION]The MySQL version running on your source database server: MYSQL_5_6 or MYSQL_5_7.
[SOURCE_SERVER_IP]The externally accessible IPv4 address for the source database server.
[SOURCE_SERVER_PORT]The externally accessible port for the source database server.
[PROJECT_ID]The project ID for your GCP project.

For example, to create a source representation instance named hq-master1, for a source database server with the IP address of 192.0.2.0 open on port 3306, and running MySQL 5.7, for Cloud SQL replicas in the MyProject project and the us-central1 region, you would use the following commands:

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{
         "name": "hq-master1",
         "region": "us-central1",
         "databaseVersion": "MYSQL_5_7",
         "onPremisesConfiguration": {
             "hostPort": "192.0.2.0:3306"
         }
     }' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/MyProject/instances

2. Create the Cloud SQL replica

In this step, you create a Cloud SQL read replica, using the source representation server to represent the source database server, and provide the replica with the data you loaded into Cloud Storage in the previous step.

Because this API call requires you to provide sensitive information, you should use a JSON file to provide your data to cURL, rather than providing it on the command line.

Create the data file:

{
    "replicaConfiguration": {
        "mysqlReplicaConfiguration": {
            "username": "[USERNAME]",
            "password": "[PASSWORD]",
            "dumpFilePath": "gs://[BUCKET]/[PATH_TO_DUMP]",
            "caCertificate": "[SOURCE_SERVER_CA]",
            "clientCertificate": "[CLIENT_CERT]",
            "clientKey": "[PRIVATE_KEY]"
        }
     },
     "settings": {
         "tier": "[MACHINE_TYPE]",
         "dataDiskSizeGb": "[DISK_SIZE]"
     },
     "masterInstanceName": "[SOURCE_REPRESENTATION_NAME]",
     "region": "[REGION]",
     "databaseVersion": "[MYSQL_VERSION]",
     "name": "[REPLICA_NAME]"
}

Replace [PROPERTIES_IN_BRACKETS] with the following values:

PropertyValue
[USERNAME]The MySQL replication user account on the source database server.
[PASSWORD]The password for the MySQL replication user account.
[BUCKET]The name of the bucket you created to hold the export file.
[PATH_TO_DUMP]The path to the export file.
[SOURCE_SERVER_CA]The CA certificate of the source database server. Required only for SSL/TLS. Paste the certificate into the request body.
[CLIENT_CERT]The client certificate. Required only for server-client authentication. Paste the certificate into the request body.
[PRIVATE_KEY]The private key file for the client certificate. Required only for server-client authentication. Paste the key into the request body.
[MACHINE_TYPE]The size of your replica. Generally, the replica should be about the same size as the source database server. If you are unsure what machine type to use, start with db-n1-standard-2. You can change its size later if needed.
[DISK_SIZE]The storage size for the replica, in GiB. For best performance, create the replica with enough storage to hold the entire import.
[SOURCE_REPRESENTATION_NAME]The name of the source representation instance you created previously.
[REGION]The region where you created the source representation instance. The read replicas must be in the same region as the source representation instance.
[MYSQL_VERSION]The MySQL version running on your source database server: MYSQL_5_6 or MYSQL_5_7.
[REPLICA_NAME]The name of the Cloud SQL read replica. Use any valid Cloud SQL instance name.

At the command line, call the API:

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

For the source representation instance and bucket we created earlier, the data file, data.json, might look like the following example:

{
    "replicaConfiguration": {
        "mysqlReplicaConfiguration": {
            "username": "replicationUser",
            "password": "486#@%*@",
            "dumpFilePath": "gs://export-bucket/hq-master1.sql.gz",
            "caCertificate": "[SOURCE_SERVER_CA]",
            "clientCertificate": "[CLIENT_CERT]",
            "clientKey": "[PRIVATE_KEY]"
        }
    },
    "settings": {
        "tier": "db-n1-standard-4",
        "dataDiskSizeGb": "100"
    },
    "masterInstanceName": "hq-master1",
    "region": "us-central1",
    "databaseVersion": "MYSQL_5_7",
    "name": "hq-master1-replica1"
}

And the cURL command would like like this:

curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data @./data.json
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/MyProject/instances

When the read replica can access the dump file, it begins the import process. The duration of the import process depends on the dump file size, the replica machine type, and the database schema. As a general guide for estimation, expect the replica to import approximately 25-50 GB per hour.

The replica stays in the PENDING_CREATE state until the import process is complete; at that time, the replica changes to the RUNNABLE state.

4. Configure the source database server to accept connections from the replica

The replica needs to connect to the source database server for replication to succeed. If your source database server is behind a firewall or some other network restriction, you must enable network access for the replica, using the replica's OUTGOING IP address. Note that this is not the IP address displayed in the main listing for the replica in the GCP Console. You can retrieve the OUTGOING IP address by hovering over the More info tool tip for the IP address, or by using the gcloud command below.

  1. Retrieve the read replica's IP addresses:

    gcloud sql instances describe [REPLICA_NAME] --format="default(ipAddresses)"
    
  2. Configure the network firewall for your source database server to accept connections from the OUTGOING IP address.
  3. Confirm that the replica has successfully connected to the source database server.

    1. The replica's icon in the instance listing page should no longer be spinning, and it should be green.
    2. Go to the Logs Viewer in the Google Cloud Platform Console.

      Go to the Logs Viewer

    3. Select the replica from the Instance dropdown.
    4. Select the replication-setup.log log file.

      If the replica is not able to connect to the source database server, confirm the following items:

      • Any firewall on the source database server is configured to allow connections from the replica's OUTGOING IP address.
      • Your SSL/TLS configuration is correct.
      • Your replication user, host, and password are correct.

5. Restrict access to the MySQL replication user

This step is optional but recommended for security.

The MySQL replication user on the source database server is configured to accept connections from any host (%). Update that user account to accept connections only from the replica's OUTGOING IP address:

    UPDATE mysql.user SET Host='[OUTGOING_IP]' WHERE Host='%' AND User='[USERNAME]';
    FLUSH PRIVILEGES;

6. Finalize the replica configuration

  1. Configure a user account on the replica.

    You can do this by using the Google Cloud Platform Console, the gcloud command-line tool, or the Cloud SQL API. However, you cannot use the mysql client. For more information about MySQL users, see MySQL users.

    To create a user using the GCP Console:

    1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
      Go to the Cloud SQL Instances page
    2. Click the instance name to open its Instance details page.
    3. Select the Users tab.
    4. Click Create user account.
    5. In the Create user account dialog, specify the details for your user.
    6. Click Create.
  2. Authorize access to the replica from any client you plan to use to connect to the replica using IP addresses.

    For instructions, see Adding an authorized address or address range. To connect to the replica, you use the replica's PRIMARY IP address. This IP address is displayed in the GCP Console.

    You can also use any other connection method to connect to the replica.

7. Confirm replication status

When the replica finishes importing the dump file, it connects to the on- premises server and applies all the updates that were made after the export was taken. You can confirm its status in the Instance listing page of the GCP Console.

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.
    Go to the Cloud SQL Instances page
  2. Click Show Info Panel and select Replication Delay from the metrics dropdown list.

    Replication delay should be at or trending towards 0. If it is not, you should take steps to address it.

8. Clean up your storage

  1. Delete the export file:

    gsutil rm gs://[BUCKET]/[PATH_TO_DUMP]
    
  2. If you no longer need the bucket, delete the bucket:

    gsutil rm -r gs://[BUCKET]
    

For more information, see the Cloud Storage documentation for Deleting Objects and Deleting Buckets.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud SQL for MySQL