You can maintain read-only replicas of your Google Cloud SQL instances in other hosting environments, also known as external slaves. For more information, see the MySQL Replication documentation. This page describes how to configure replication with Google Cloud SQL.

Contents

• Overview
• Enabling replication of a Cloud SQL instance
• Setting up an external slave

Overview

Google Cloud SQL enables you to replicate to any MySQL instance that can connect to your Cloud SQL instance using the MySQL wire protocol, supports row-based replication, and is the same (or later) version of the Cloud SQL instance being replicated. This includes most on-premise, or conventionally-hosted databases. However, replicating to a MySQL instance hosted in another cloud platform may not be possible: you will need to check with the other provider's documentation.

Using replication has the following benefits:

• Lower latency connections to on-premises applications.
• Off-cloud analytics that won’t interfere with your transactional database.
• Fully redundant standby on another platform in the event of Google Cloud outages.
• Easy migration to other platforms.

To configure external replication of a Cloud SQL instance, you must enable the binary log for the Cloud SQL instance and set up an external slave. Note that it is not currently possible to replicate between Cloud SQL instances, or from external instances to Cloud SQL instances.

The only extra charge for replicating a Cloud SQL instance is for the outbound network cost. See the pricing page for outbound network pricing.

Enabling replication of a Cloud SQL instance

To enable replication of a Cloud SQL instance, do the following:

1. Enable access to the instance from the slave.

   Specifically, you need to add the slave's IP address to the list of authorized IP ranges that can access the instance. For more information, see Configuring access control for non-App Engine applications .

2. Enable the binary log using the Cloud SQL API.

   Cloud SQL Admin Command Line

   Uses the sql command line tool in the Google Cloud SDK .

   $ gcloud config set project your-project-id
   $ gcloud sql instances patch --enable-bin-log your-instance-name
   

   cURL

   You can do this with cURL by setting the binaryLogEnabled and the startTime properties of the instance's backupConfiguration resource setting. The following command sets the backup configuration in the case where no backup configuration exists. In the command, we set binaryLogEnabled to true and startTime to a time of day in the 24 hour format HH:MM .

   curl --header 'Authorization: Bearer accessToken' \
        --header 'Content-Type: application/json' \
        --data '{"settings" : {"backupConfiguration" : [{ "startTime": "01:00",  "enabled": true, "binaryLogEnabled" : true }]}}' \
        -X PATCH \
        https://www.googleapis.com/sql/v1beta3/projects/your-project-id/instances/your-instance-name
   

   If there is an existing backup configuration for the instance, modify the previous command to include the resource identifier of the configuration. You can get the resource identifier from the response to a GET request for the instance resource.

   curl --header 'Authorization: Bearer accessToken' \
        --header 'Content-Type: application/json' \
        --data '{"settings" : {"backupConfiguration" : [{ "startTime": "01:00",  "enabled": true, "id": "resource-id", "binaryLogEnabled" : true }]}}' \
        -X PATCH \
        https://www.googleapis.com/sql/v1beta3/projects/your-project-id/instances/your-instance-name
   

   For more information the resources of an instance you can access with the API, see instance resource definition .

Notes

You must enable the binary log to support external slaves; however, this has the following impacts:

• Performance overhead
  Cloud SQL uses row-based replication with MySQL flags sync_binlog=1 and innodb_support_xa=true . Hence, an additional disk fsync is required for each write operation, which can cause a significant performance overhead. The degradation is less severe when using Cloud SQL async replication .
  
• Storage overhead
  The storage of binary logs is charged at the same rate as regular data. Cloud SQL retains binary logs from the time when the oldest backup was taken (Cloud SQL currently retains 7 backups). The size of binary logs, and hence the amount charged, will depend on the workload. For example, a write-heavy workload will consume more binary log space than a read-heavy workload. You can see the size of binary logs using the SHOW BINARY LOGS command.
  

Setting up an external slave

After you have enabled replication for your Cloud SQL following the steps above, you are now ready to create an external slave. It should be possible to replicate your Cloud SQL instance to any MySQL instance, version 5.5 or above, in a host that can access the IP address of your Cloud SQL instance. The process for setting up an external slave of a Cloud SQL instance is similar to configuring any other MySQL slave: the external slave is first initialized with a dump of the master database and then it is configured to follow the master so that all writes are copied to the slave. See the MySQL documentation for more details.

To set up the master:

1. On the master instance, create a database dump, either through the Cloud SQL Console , or by using the mysqldump utility .
2. Start replication on the master instance by executing the following two statements using the MySQL command-line tool:

   CREATE USER 'replication-user'@'%' IDENTIFIED BY 'slave-password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication-user'@'%';
   

   The first command creates a new MySQL account that can connect to the master from any host ('%'). The second command grants replication priveleges to the account.

To set up the slave:

1. On the slave instance, create your new external MySQL instance from the dump file.

   For example, the following command loads the dump file called mysqldump.sql .

   mysql --user=root --password < mysqldump.sql
   

2. Set the server ID on the slave.

   The server ID is a system variable that enables master and slave servers to uniquely identify each other. It can be any numeric value (e.g., "3"), that is not in use by the master and any other slave. You can set server ID in a number for ways, for example, by adding it to the my.cnf option file of the external slave as shown in the following example:

   [mysqld]
   server-id=serverID
   

   For more information about replication options including server-id , see Replication and Binary Logging Options .

3. Set the master information on the slave.

   The mysqldump file your create from the master includes a "CHANGE MASTER TO MASTER_LOG_FILE" comment specifying the log coordinates. Copy and paste the entire line into MySQL command-line tool without the initial '--'. Complete the CHANGE MASTER TO command by specifying the rest of the information for connecting to the master, including MASTER_HOST , MASTER_USER , and MASTER_PASSWORD .

   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
     MASTER_HOST='master-IP', MASTER_USER='replication-user', MASTER_PASSWORD='slave-password';
   

   You can find master-IP from the Cloud SQL Console or using the Cloud SDK command line tool.

4. Start replication on the slave by executing the following statement:

   START SLAVE;
   

5. You can check replication status, including the delay in applying updates to the replica with the following command:

   SHOW SLAVE STATUS\G;
   

   This command displays a list of replication information about the slave. For more information on the information displayed, see Checking Replication Status . Note that when you see the slave state "Waiting for master to send event", replication is working. To verify, make a change in the master, and then verify the change in the slave.

Notes

• The only extra charge for replicating a Cloud SQL instance is for the outbound network cost. See our pricing page for more details.
• If replication is interrupted for just a few hours, for example by a network or server outage, then the slave will fall behind in replication but should catch once it reconnects to the master and start replicating again. However, if replication is interrupted for longer than Cloud SQL replication logs are preserved (7 backups) then you will have to restore your slave from a dump.
• You should be able to replicate to any MySQL instance that can connect to Cloud SQL using MySQL wire protocol, supports row-based replication, and is of the same (or later) version as your Cloud SQL instance. This includes most on-premise, or conventionally-hosted databases. However, replicating to a MySQL instance hosted in another cloud platform may not be possible. Check with the other provider's documentation.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .