Requirements and tips for configuring replication

This page provides requirements and best practices for working with read replicas and external replicas.

For information about how to configure a read replica, see Creating read replicas.

Read replicas

Read replicas are Cloud SQL instances that replicate from a Cloud SQL primary instance. Read replicas are read-only. You cannot write to them. You can use a replica to offload read requests and analytics traffic from the primary instance.

For information about how to configure a read replica, see Creating Read Replicas.

Read replica primary instance requirements

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

  • Binary logging must be enabled. Learn more.
  • At least one backup must have been created after binary logging was enabled.

Quick reference for Cloud SQL read replica topics

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 multiple read replicas for a single primary instance.
Load balancing Cloud SQL does not provide load balancing between replicas.
Settings The MySQL settings of the primary instance are propagated to the replica, including root password and changes to the user table. Tier changes are not propagated to the replica.
Machine types Read replicas can be a different machine type (or tier) than the primary instance. Read replicas can have more CPUs and memory than the primary instance, but they cannot have less.
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.

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.

External read replicas

External read replicas are external MySQL instances that are replicating from a Cloud SQL primary.

For information about how to configure an external read replica configuration, see Configuring External Replicas.

External read replica configuration requirements

Requirements for the primary instance:

The primary instance must have binary logging enabled. Learn more.

Requirements for the external replica:

The MySQL version of the replica should be the same or higher than the MySQL version of the primary instance. Learn more.

Information about the external replica configuration

The following facts apply to the external replica configuration:

  • A MySQL instance running on Compute Engine is considered an external instance.
  • 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.
  • Replicating to a MySQL instance hosted by another cloud platform might not be possible; check the documentation from the other provider.
  • If replication is interrupted for just a few hours, for example by a network or server outage, the replica falls behind the primary. The replica should catch 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 (7 backups), you must delete the replica and create a new one.
  • For security, you should configure SSL/TLS on your primary instance. Learn more.

Binary logging impact

You must enable the binary log of 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

    The storage of binary logs is charged at the same rate as regular data. Binary logs are automatically truncated to the age of the oldest automated backup. Cloud SQL currently retains the most recent seven automated, 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.

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
Read replica did not start replicating on creation. At least one backup must have been created after binary logging was enabled. Wait until at least one backup has been created after enabling binary logs.
Unable to create read replica - unknown error. There could be many root causes. Check the logs to find more information.
Disk is full. The primary instance disk size can become full during replica creation. Upgrade the primary instance to a larger disk size.
Replica instance is using too much memory. Replicas can cache often-requested read operations. Restart the replica instance to reclaim the temporary memory space.
Replication stopped. Max storage space was reached and automatic storage increase is not enabled. Enable automatic storage increase.
Replication lag is consistently high. There can be many different root causes. Increase the instance size of the replica or reduce the load on the database.

Read replica did not start replicating on creation

Read replica did not start replicating on creation.

The issue might be

The primary instance must have at least a week's worth of binlogs or else replicas cannot start replicating.

Things to try

Wait until there are enough binlogs.


Unable to create read replica - unknown error

Unable to create read replica - unknown error.

The issue might be

There is probably a more specific error in the log files.

Things to try

Inspect the logs in Cloud Logging to find the actual error. If the error is: set Service Networking service account as servicenetworking.serviceAgent role on consumer project, then disable and re-enable the Service Networking API. This action creates the service account necessary to continue with the process.


Disk is full

UPDATE_DISK_SIZE or mysqld: disk is full error.

The issue might be

The primary instance disk size can become full during replica creation.

Things to try

Edit the primary instance to upgrade it to a larger disk size.


Replica instance is using too much memory

The replica instance is using too much memory.

The issue might be

The replica uses temporary memory to cache often-requested read operations, which can lead it to use more memory than the primary instance.

Things to try

Restart the replica instance to reclaim the temporary memory space.


Replication stopped

Replication stopped.

The issue might be

The maximum storage limit was reached and >automatic storage increase is disabled.

Things to try

Edit the instance to enable automatic storage increase.


Replication lag is consistently high

Replication lag is consistently high.

The issue might be

The write load is too high for the replica to handle. Replication lag takes place when the SQL thread on a replica is unable to keep up with the IO thread. Some kinds of queries or workloads can cause temporary or permanent high replication lag for a given schema. Some of the typical causes of replication lag are:

  • Slow queries on the slave. These are discoverable by enabling log_slow_slave_statements and fixing them.
  • All tables should have unique/primary key. Every update on such a table without a unique/primary key causes full table scans on th replica.
  • Queries like DELETE ... WHERE field < 50000000 cause replication lag with row-based replication since a huge number of updates are piled up on the replica.

Things to try

Edit the instance to increase the size of the replica, or reduce the load on the database.

What's next