Replication in Cloud SQL

Replication is the ability to create copies of a Cloud SQL instance or an on-premises database, and offload work to the copies.

Introduction

The primary reason for using replication is to scale the use of data in a database without degrading performance. Other reasons include:

  • Migrating data between regions
  • Migrating data between platforms
  • Migrating data from an on-premises database to Cloud SQL

Additionally, a replica could be promoted if the original instance becomes corrupted.

When referring to a Cloud SQL instance, the instance that is replicated is called the primary instance and the copies are called read replicas. The primary instance and read replicas all reside in Cloud SQL.

When referring to an on-premises database, the replication scenario is called replicating from an external server. In this scenario, the database that is replicated is the source database server. The copies that reside in Cloud SQL are called Cloud SQL replicas. There is also an instance that represents the source database server in Cloud SQL called the source representation instance.

You can also use the Database Migration Service for continuous replication from a source database server to Cloud SQL.

Cloud SQL supports the following types of replicas:

Cloud SQL does not support replication between two external servers.

Read replicas

You use a read replica to offload work from a Cloud SQL instance. The read replica is an exact copy of the primary instance. Data and other changes on the primary instance are updated in almost real time on the read replica.

Read replicas are read-only; you cannot write to them. The read replica processes queries, read requests, and analytics traffic, thus reducing the load on the primary instance. You can have up to 10 read replicas per primary instance.

You connect to a replica directly using its connection name and IP address. If you're connecting to a replica using a private IP address, you don't need to create an additional VPC private connection for the replica as it is inherited from the primary instance.

For information about how to create a read replica, see Creating read replicas. For information about managing a read replica, see Managing read replicas.

As a best practice, put read replicas in a different zone than the primary instance when you use HA on your primary instance. This practice ensures that read replicas continue to operate when the zone that contains the primary instance has an outage. See the Overview of high availability for more information.

Cross-region read replicas

Cross-region replication lets you create a read replica in a different region from the primary instance. You create a cross-region read replica the same way as you create an in-region replica.

Cross-region replicas:

  • Improve read performance by making replicas available closer to your application's region.
  • Provide additional disaster recovery capability to guard against a regional failure.
  • Let you migrate data from one region to another with minimum downtime.

See Promoting replicas for regional migration or disaster recovery for more information about cross-region replicas.

External read replicas

External read replicas are external MySQL instances that replicate from a Cloud SQL primary instance. For example, a MySQL instance running on Compute Engine is considered an external instance.

External read replicas have the following restrictions:

  • Replicating to a MySQL instance hosted by another cloud platform might not be possible; check the documentation from the other provider. For example, setting the configuration field replicate-ignore-db is required, and cloud providers where this is not allowed are not supported. See Configuring external replicas for other required configuration fields.
  • If replication is interrupted for a few hours, for example by a network or server outage, the replica falls behind the primary. The replica catches up once it reconnects to the primary and starts replicating again. However, if replication is interrupted for longer than Cloud SQL replication logs are preserved (seven backups), you must delete the replica and create a new one.
  • The data flowing from the primary to the external replica is charged as network egress. See the pricing page for network egress pricing for your Cloud SQL instance type.

Replication use cases

The following use cases apply for each type of replication.

Name Primary Replica Benefits and use cases More information
Read replica Cloud SQL instance Cloud SQL instance
  • Additional read capacity
  • Analytics target
Cross-region read replica Cloud SQL instance Cloud SQL instance
  • Additional read capacity
  • Analytics target
  • Additional disaster recovery capability
  • Improve read performance
  • Migrate data between regions
External read replica Cloud SQL instance MySQL instance external to Cloud SQL
  • Reduced latency for external connections
  • Analytics target
  • Migration path to other platforms
Replication from an external server MySQL instance external to Cloud SQL Cloud SQL for MySQL instance
  • Migration path to Cloud SQL
  • Data replication to Google Cloud Platform
  • Analytics target

Prerequisites for creating a read replica

Before you can create a read replica of a primary Cloud SQL instance, the instance must meet the following requirements:

  • Automated backups must be enabled.
  • Binary logging must be enabled which requires point-in-time recovery to be enabled. Learn more about the impact of these logs.
  • At least one backup must have been created after binary logging was enabled.

Additional requirements for the external replica:

  • The MySQL version of the replica must be the same or higher than the MySQL version of the primary instance. Learn more.
  • For security, you must configure SSL/TLS on your primary instance. Learn more.

Impact of enabling binary logging

You must enable point-in-time recovery to enable binary logging on the primary instance to support read replicas. This has the following impacts:

  • Performance overhead

    Cloud SQL uses row-based replication with MySQL flags sync_binlog=1 and innodb_support_xa=true. Therefore, an additional disk fsync is required for each write operation, which reduces performance.

  • Storage overhead

    Storage of the binary logs is charged at the same rate as regular data. The binary logs are automatically truncated to the age of the oldest automated backup. Cloud SQL currently retains the most recent seven automated backups, and all on-demand backups. The size of the binary logs, and therefore the amount charged, depends on the workload. For example, a write-heavy workload consumes more binary log space than a read-heavy workload.

    You can see the size of binary logs by using the SHOW BINARY LOGS MySQL command.

    When backups are taken, the logs are stored in the backup along with the data.

  • Instance restart

    When you enable or disable binary logging, the instance is restarted. Existing database connections are lost and must be reestablished.

Binary logging on read replicas

  • Binary logging is supported on read replica instances (MySQL 5.7 and 8.0 only). You enable binary logging on a replica with the same API commands as on the primary, using the replica's instance name instead of the primary's instance name.

    Binary logging durability on the replica (but not on the primary) instance can be set with the sync_binlog flag, which controls how often the MySQL server synchronizes the binary log to disk.

    Binary logging can be enabled on a replica even when backup is disabled on the primary.

    If a replica that has this value set is promoted to a standalone server, then the setting is reset to the safe value 1 on the standalone server.

Billing

  • A read replica is charged at the same rate as a standard Cloud SQL instance. There is no charge for the data replication.
  • Because a replica always maintains a connection to its primary, the primary instance is never deactivated. This scenario could result in a billing increase for the primary instance. Learn more.
  • For external replicas, the data flowing from the primary to the external replica is charged as network egress. See the pricing page for network egress pricing for your Cloud SQL instance type.
  • In addition to the regular cost associated with any Cloud SQL instances, a cross-region replica incurs cross-region network egress charges for replication logs sent from the primary to the replica, as described in Network Egress Pricing.
  • Pricing for a cross-region read replica is the same as for creating a new instance in the region. Refer to Cloud SQL instance pricing and select the appropriate region.

Quick reference for Cloud SQL read replicas

Topic Discussion
High availability Read replicas neither provide high availability nor offer it.
Failover A primary instance cannot failover to a read replica, and read replicas are unable to failover in any way during an outage.
Maintenance windows Maintenance windows cannot be set on read replicas and they do not share maintenance windows with the primary instance. Maintenance can occur at any time on the read replica. Maintenance occurs on read replicas at a different time than on the primary instance.
Disruptive upgrades Read replicas can experience a disruptive upgrade at any time.
Performance When you create a read replica, it does not impact the performance or availability of the primary instance.
Multiple read replicas You can create up to 10 read replicas for a single primary instance.
Load balancing Cloud SQL does not provide load balancing between replicas. Use connection pooling and distribute queries across replicas.
Settings The MySQL settings of the primary instance are propagated to the replica, including root password and changes to the user table. CPU and memory changes are not propagated to the replica.
Parallel replication For information about using parallel replication for performance improvements, see Configuring parallel replication.
Cores and memory Read replicas can use a different number of cores and amount of memory than the primary instance.
User tables You cannot make changes to the user table on the replica. All user changes must be done on the primary instance.
Backups You cannot configure backups on the replica.
Restoring the primary instance You cannot restore the primary of a replica while the replica exists. Before restoring an instance from a backup, or performing a point-in-time recovery on it, you must promote or delete all of its replicas.
Deleting the primary instance Before you can delete a primary instance, you must promote all of its read replicas to stand-alone instances or delete the read replicas.
Disabling binary logging Before you can disable binary logs on a primary instance, you must promote or delete all of its read replicas.
Creating a replica of a replica You cannot create a replica of a replica.
Stopping a replica You cannot stop a replica. You can restart, delete, or disable replication on it, but you cannot stop it as you can a primary instance.
Private IP If you're connecting to a replica using a private IP address, you don't need to create an additional VPC private connection for the replica as it is inherited from the primary instance.

What's next