Configuring Replication

Google Cloud SQL supports the following read replica scenarios:

• Cloud SQL instances replicating from a Cloud SQL master instance. Replicas are other instances in the same project and region as the master instance. This feature is in Beta.

• External MySQL instances replicating from a Cloud SQL master instance. External replicas are in hosting environments, outside of Cloud SQL.

This page describes how to configure both types of replication in Google Cloud SQL. For more information about replication in MySQL, see the MySQL Replication documentation.

Contents

• Overview
  • Binary logging impact
• Enabling Cloud SQL read replicas (Beta)
  • Configuring the master instance
  • Creating a replica
  • Managing replicas
• Enabling external read replicas
  • Configuring the master instance
  • Configuring an external replica

Overview

Google Cloud SQL enables data to be replicated from Cloud SQL (master) instances to Cloud SQL (slave) instances or external (slave) MySQL instances. Cloud SQL slave instances must be in the same project and region as the master instance. External slave instances must:

• Be able to connect to the Cloud SQL master instance with the MySQL wire protocol.
• Support row-based replication.
• Be the same (or later) version of the Cloud SQL instance being replicated.

You should be able to enable replication to most external instances, including most on-premises or conventionally-hosted databases. However, replicating to a MySQL instance hosted by another cloud platform may not be possible: you will need to check with the other provider's documentation.

Cloud SQL instances replicating from a Cloud SQL master instance have the following benefits:

• Additional read capacity for applications dominated by reads.
• In-cloud analytics that doesn't interfere with your transactional database.

External MySQL instances replicating from a Cloud SQL master instance have 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.

It is not currently possible to replicate from external instances to Cloud SQL instances.

Binary logging impact

You must enable the binary log of the master instance to support read replicas; however, this has the following impact:

• Performance overhead
  Google 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.

Enabling Cloud SQL read replicas (Beta)

Read replicas are not fully supported by the Cloud SQL Admin Command Line. Currently, you can view replication settings for a master or replica instance, but you cannot create, start, stop, or promote a replica using the command line tool.

To configure a Cloud SQL instance as a replica of another Cloud SQL instance, you must enable the binary log for the master instance and have at least one successful backup of the master instance after binary logging was enabled.

Keep the following in mind when working with Cloud SQL read replicas:

• The only extra charge for replicating a Cloud SQL instance to another Cloud SQL instance is the cost of the replica instance. See the pricing page for details.
• Read replicas are currently supported only for MySQL 5.5 instances.
• Replicas must be created in the same project and region as the master.
• The activation policy of a replica instance must be set to ALWAYS, meaning the instance is never shut down. Because a replica (that is not stopped) always has a connection to its master, the master instance will never become passive regardless of its activation policy. For more information about the implication of this in terms of charges, see the FAQ: How is use of my instance calculated?
• MySQL settings of the master instance are propagated to the replica, including root password and changes to the user table.
• After the replica is created, you can change all configuration settings of the replica, except that you can not enable backups or change the activation policy.
• To delete a master instance with one or more read replicas, you must promote or delete all of the read replicas before you can delete the master.
• You cannot disable binary logs on master as long as it has read replicas.
• To make replicas crash-safe, the server flags sync_master_info, sync_relay_log_info, and sync_relay_log are set to 1. Because these settings result in slower write performance in SYNCHRONOUS replication mode, we create the read replica instances with the replication mode set to ASYNCHRONOUS. You can change the replication type, but you will incur the slower write performance. For more information, see the FAQ.

Configuring the master instance

1. Check if backups are enabled, and if not, enable them.

   Google Developers Console

   1. Go to the Google Developers Console and select a project by clicking on the project name.
   2. In the sidebar on the left, click Storage > Cloud SQL to show a list of Cloud SQL instances for the project.
   3. Select an instance by clicking the instance name.
   4. Click Edit.
   5. In the Backups section, enable backups and select a backup window.
   6. Click Save to apply the change.

   Cloud SQL Admin Command Line

   1. Using the Google Cloud SDK, select a project.

      $ gcloud config set project YOUR_PROJECT_ID

   2. Check the backup configuration setting of the instance.

      $ gcloud sql instances describe YOUR_INSTANCE_NAME

      In the output, look for the enabled property of the backupConfiguration setting. If the property is true, backups are enabled. If false, continue to the next step to enable them.

   3. Enable the backups for the instance.

      $ gcloud sql instances patch YOUR_INSTANCE_NAME --backup-start-time HH:MM

      Backup start times are specified in UTC timezone in the 24 hour format, for example, "16:00".

   Cloud SQL API

   1. Use the get method of the instances resource to return the values of the current backup configuration. For example, using cURL, the request is:

      curl --header 'Authorization: accessToken' \
           -X GET \
           https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/master-instance-name?fields=settings
      

      The output will contain the following:

      "backupConfiguration": [
       {
          "kind": "sql#backupConfiguration",
          "startTime": "12:00",
          "enabled": false,
          "id": "193f7fff-6994-4db0-9f7a-43906344ac45",
          "binaryLogEnabled": false
       }
      

   2. Use the patch method of the instances resource to enable backups. Specify the properties of the backup configuration you want to keep as is and those you want to change. In this case, set enabled to true and the startTime to a value which is the start of the backup window.

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

2. Enable the binary log of the master instance.

   Google Developers Console

   1. Go to the Google Developers Console and select a project by clicking on the project name.
   2. In the sidebar on the left, click Storage > Cloud SQL to show a list of Cloud SQL instances for the project.
   3. Select an instance to edit by clicking the instance name.
   4. Click Edit.
   5. In the Backups section, enable binary logging.
   6. Click Save to apply the change.

   Cloud SQL Admin Command Line

   1. Using the Google Cloud SDK, select a project.

      $ gcloud config set project YOUR_PROJECT_ID

   2. Check the binary log status of the instance.

      $ gcloud sql instances get YOUR_INSTANCE_NAME | grep binaryLogEnabled

      If the property is true binary logs are enabled. If false, continue to the next step to enable it.

   3. Enable the binary log for the instance.

      $ gcloud sql instances patch --enable-bin-log YOUR_INSTANCE_NAME

   Cloud SQL API

   1. Use the get method of the instances resource to return the values of the current backup configuration. For example, using cURL, the request is:

      curl --header 'Authorization: accessToken' \
           -X GET \
           https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/master-instance-name?fields=settings
      

   2. Use the patch method of the instances resource to enable binary logging. Specify the properties of the backup configuration you want to keep as is and those you want to change. In this case, you want to set binaryLogEnabled to true along with existing settings.

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

Creating a replica

After you enable binary logs for the master instance, you must wait until at least one scheduled backup has succeeded before you can create a replica.

To check if a backup exists for the master instance:

To create a replica

curl --header 'Authorization: Bearer accessToken' \
     --header 'Content-Type: application/json' \
     --data '{"masterInstanceName": "master-instance-name", "instance": "replica-name", "region": "master-region", settings": {"tier":"tier", "activationPolicy":"ALWAYS", "replicationType": "ASYNCHRONOUS"}}' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances

Managing replicas

By default, a replica starts with replication started. There may be times however when you need to stop replication, for example, to debug or analyze the state of an instance. After fixing the problem, you will need to restart replication. Stopping or starting replication will restart the instance.

Stopping replication does not shut down the replica instance. You will be charged for the instance because it is running. At this point, you can restart replication on the replica, delete the replica, or promote the replica to a stand-alone instance. Promoting a replica to a stand-alone Cloud SQL instance is an irreversible action. Once promoted, an instance cannot be converted back to a read replica.

To stop replication:

$ gcloud sql instances patch READ_REPLICA_NAME --no-enable-database-replication 

curl --header 'Authorization: Bearer accessToken' \
     --header 'Content-Type: application/json' \
     --data '{"settings": {"databaseReplicationEnabled" : "False" }}' \
     -X PATCH \
     https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/replica-instance-name

To start replication:

$ gcloud sql instances patch READ_REPLICA_NAME --enable-database-replication 

curl --header 'Authorization: Bearer accessToken' \
     --header 'Content-Type: application/json' \
     --data '{"settings": {"databaseReplicationEnabled" : "True" }}' \
     -X PATCH \
     https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/replica-instance-name/startReplica

To promote a replica to a stand-alone instance:

$ gcloud sql instances promote-replica READ_REPLICA_NAME

curl --header 'Authorization: Bearer accessToken' \
     --header 'Content-Length: 0' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/replica-instance-name/promoteReplica

You can check the status of replication in a number of ways. When you log into an individual replica instance (for example, using MySQL Client), you can get the most detail about replication, including status and metrics. When you use the Google Developers Console or the Cloud SQL Admin Command Line, you get a brief summary of replication.

To check replication status:

$ gcloud sql instances describe READ_REPLICA_NAME

$ gcloud sql instances describe MASTER_INSTANCE_NAME

Enabling external read replicas

To replicate from a Cloud SQL instance (master) to external read replicas (slaves), you must perform configuration on both the master and each read replica. These steps are discussed below.

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

Configuring the master instance

To prepare a Cloud SQL master instance for replication to an external MySQL instance, do the following:

1. Enable access to the master instance from the read replica.

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

2. Enable the binary log of the master instance.

   Google Developers Console

   1. Go to the Google Developers Console and select a project by clicking on the project name.
   2. In the sidebar on the left, click Storage > Cloud SQL to show a list of Cloud SQL instances for the project.
   3. Select an instance to edit by clicking the instance name.
   4. Click Edit.
   5. In the Options section, select Enabled for the BINARY LOG.
   6. Click Confirm to apply the change.

   Cloud SQL Admin Command Line

   1. Using the Google Cloud SDK, select a project.

      $ gcloud config set project YOUR_PROJECT_ID

   2. Check the binary log status of the instance.

      $ gcloud sql instances describe YOUR_INSTANCE_NAME | grep binaryLogEnabled

   3. Enable the binary log for the instance.

      $ gcloud sql instances patch YOUR_INSTANCE_NAME --enable-bin-log 

   Cloud SQL API

   1. Use the get method of the instances resource to return the values of the current backup configuration. For example, using cURL, the request is:

      curl --header 'Authorization: accessToken' \
           -X GET \
           https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instances/master-instance-name?fields=settings
      

      The output will contain the following:

      "backupConfiguration": [
       {
          "kind": "sql#backupConfiguration",
          "startTime": "12:00",
          "enabled": false,
          "id": "193f7fff-6994-4db0-9f7a-43906344ac45",
          "binaryLogEnabled": false
       }
      

   2. Use the patch method of the instances resource to enable binary logging. Specify the properties of the backup configuration you want to keep as is and those you want to change. In this case, you want to set binaryLogEnabled to true along with existing settings.

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

3. Create a database dump of the master instance, either using the Google Developers Console, or by using the mysqldump utility(--master-data=2 is required for obtaining a dump that contains the replication log coordinates and --events is required for including the contents of the mysql.event table).
4. Start replication on the master instance by executing the following two statements using the MySQL command-line tool:

   CREATE USER 'replication-user'@'%' IDENTIFIED BY 'replica-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 privileges to the account.

Configuring an external replica

After you have configured the master following the steps above, you are now ready to create an external read replica. It should be possible to replicate your Cloud SQL instance to any external MySQL instance, version 5.5 or above, in a host that can access the IP address of your Cloud SQL master instance. The process for setting up an external read replica of a Cloud SQL instance is similar to configuring any other MySQL replica: the replica 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 replica. See the MySQL documentation for more details.

To configure an external read replica:

1. On the replica instance, create your new external MySQL instance from the dump file you created of the master instance.

   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 read replica.

   The server ID is a system variable that enables master and replica 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 replica (of the master). You can set the server ID in a number for ways, for example, by adding it to the my.cnf option file of the replica 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 replica.

   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='replica-password';
   

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

4. Start replication on the replica 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 replica. For more information on the information displayed, see Checking Replication Status. Note that when you see the replica 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 replica.

Notes

• If replication is interrupted for just a few hours, for example by a network or server outage, then the replica will fall behind in replication but should catch up once it reconnects to the master and starts replicating again. However, if replication is interrupted for longer than Cloud SQL replication logs are preserved (7 backups) then you must create a new replica. You might also find it convenient to create a new replica when a large amount of data has changed during the interruption, rather than let the replica catch up.
• 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-premises, 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.