This page describes how to manage read replicas. These operations include disabling and enabling replication, promoting a replica, configuring parallel replication, and checking the replication status.
For more information about how replication works, see Replication in Cloud SQL.
Disable replication
By default, a replica starts with replication enabled. However, you can disable replication, for example, to debug or analyze the state of an instance. When you are ready, you explicitly re-enable replication. Disabling or re-enabling replication doesn't restart the replica instance.
Disabling replication does not stop the replica instance; it becomes a read-only instance that is no longer replicating from its primary instance. You continue to be charged for the instance. On the disabled replica, you can re-enable replication, delete the replica, or promote the replica to a stand-alone instance.
To disable replication:
In the Google Cloud console, go to the Cloud SQL Instances page. Go to Cloud SQL Instances To execute this cURL command at a command line prompt, you acquire an access token by using the gcloud auth print-access-token command. You can also use the
APIs Explorer on the Instances:patch page to send the REST API request. Before using any of the request data, make the following replacements: HTTP method and URL: Request JSON body: To send your request, expand one of these options: Save the request body in a file called request.json, and execute the following command: Save the request body in a file called request.json,
and execute the following command: Invoke-WebRequest ` You should receive a JSON response similar to the following: To execute this cURL command at a command line prompt, you acquire an access token by using the
gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:patch page to send the REST API request. Before using any of the request data, make the following replacements: HTTP method and URL: Request JSON body: To send your request, expand one of these options: Save the request body in a file called request.json, and execute the following command: Save the request body in a file called request.json, and execute the following command: Invoke-WebRequest ` You should receive a JSON response similar to the following:Console
gcloud
gcloud sql instances patch REPLICA_NAME \
--no-enable-database-replication
REST v1
curl (Linux, macOS, or Cloud Shell)
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name"PowerShell (Windows)
$headers = @{ "Authorization" = "Bearer $cred" }
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name" | Select-Object -Expand ContentResponse
{
"kind": "sql#operation",
"targetLink": "//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name",
"status": "PENDING",
"user": "",
"insertTime": "2020-01-21T22:43:37.981Z",
"operationType": "UPDATE",
"name": "operation-id",
"targetId": "replica-name",
"selfLink": "//sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id",
"targetProject": "project-id"
}
REST v1beta4
curl (Linux, macOS, or Cloud Shell)
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name"PowerShell (Windows)
$headers = @{ "Authorization" = "Bearer $cred" }
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name" | Select-Object -Expand ContentResponse
{
"kind": "sql#operation",
"targetLink": "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name",
"status": "PENDING",
"user": "",
"insertTime": "2020-01-21T22:43:37.981Z",
"operationType": "UPDATE",
"name": "operation-id",
"targetId": "replica-name",
"selfLink": "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
"targetProject": "project-id"
}
Enable replication
If a replica has not been replicating for a long time, it will take longer for it to catch up to the primary instance. In this case, delete the replica and create a new one.
To enable replication:
In the Google Cloud console, go to the Cloud SQL Instances page. Go to
Cloud SQL Instances To execute this cURL command at a command line prompt, you acquire an access token by using the
gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:patch page to send the REST API request. Before using any of the request data, make the following replacements: HTTP method and URL: Request JSON body: To send your request, expand one of these options: Save the request body in a file called request.json, and execute the following command: Save the request body in a file called request.json, and execute the following command: Invoke-WebRequest ` You should receive a JSON response similar to the following: To
execute this cURL command at a command line prompt, you acquire an access token by using the gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:patch page to send the REST API request. Before using any of the request
data, make the following replacements: HTTP method and URL: Request JSON body: To send your request, expand one of these options: Save the request body in a file called request.json, and execute the following command: Save the request body in a file called request.json, and execute the following command: Invoke-WebRequest ` You should receive a JSON response similar to the following:Console
gcloud
gcloud sql instances patch REPLICA_NAME \
--enable-database-replication
REST v1
curl (Linux, macOS, or Cloud Shell)
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name"PowerShell (Windows)
$headers = @{ "Authorization" = "Bearer $cred" }
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name" | Select-Object -Expand ContentResponse
{
"kind": "sql#operation",
"targetLink": "//sqladmin.googleapis.com/v1/projects/project-id/instances/replica-name",
"status": "PENDING",
"user": "",
"insertTime": "2020-01-21T22:43:37.981Z",
"operationType": "UPDATE",
"name": "operation-id",
"targetId": "replica-name",
"selfLink": "//sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id",
"targetProject": "project-id"
}
REST v1beta4
curl (Linux, macOS, or Cloud Shell)
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name"PowerShell (Windows)
$headers = @{ "Authorization" = "Bearer $cred" }
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name" | Select-Object -Expand ContentResponse
{
"kind": "sql#operation",
"targetLink": "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name",
"status": "PENDING",
"user": "",
"insertTime": "2020-01-21T22:43:37.981Z",
"operationType": "UPDATE",
"name": "operation-id",
"targetId": "replica-name",
"selfLink": "//sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
"targetProject": "project-id"
}
Promoting a read replica stops replication and converts the instance to a standalone Cloud SQL primary instance with read and write capabilities.
When promoted, read replicas are automatically configured with backups, but they aren't automatically configured as high availability (HA) instances. You can enable high availability after promoting the replica just as you would for any non-replica instance. Configuring a read replica for high availability is done the same way as for a primary instance. Learn more about configuring the instance for high availability.
Before promoting a read replica, if the primary is still available and serving clients, you should do the following:
- Stop all writes to the primary instance.
- Check the replication status of the replica (follow the instructions in the mysql Client tab).
- Verify that the replica is replicating, and then wait until the replication lag reported by the Seconds_Behind_Master metric is 0.
Otherwise, a newly promoted instance may be missing some transactions that were committed to the primary instance.
To promote a replica to a standalone instance:
Confirm that the promoted instance is configured correctly. In particular, consider configuring the instance for high availability if needed.
Configure parallel replication
Reducing replication lag is important for managing replication performance. Replication lag occurs when the updates to a read replica fall behind the updates to the primary instance. This section describes how users can enable parallel replication, which can reduce replication lag.
In MySQL replication, a replication SQL thread is used to execute the transactions that are collected in the relay log on the read replica. Parallel replication reduces replication lag by increasing the number of SQL threads that work to execute these transactions. Read replicas with parallel replication enabled are sometimes called multithreaded replicas.
Parallel replication is available in these three scenarios in Cloud SQL for MySQL:
- Read replicas
- Replication from an external server
- Legacy high availability configuration
For simplicity, this page uses the terms "primary instance" and "read replica."
Basic steps to change parallel replication flags
The steps for enabling parallel replication are the following:
- On a read replica, disable replication.
- On the read replica, set the flags for parallel replication. Use the gcloud command to set the flags. The Google Cloud console option is disabled when replication is disabled.
- On the read replica, enable replication.
- Optionally, on the primary instance, set the flags to optimize performance for parallel replication.
Read replicas: Flags for parallel replication
Cloud SQL for MySQL supports several flags for parallel replication on read replicas. For information about the flags, click these links to the MySQL 8.0 documentation:
- slave_parallel_workers
- slave_parallel_type
- slave_preserve_commit_order
- slave_pending_jobs_size_max
Changing these flags does not restart the read replica.
The following table contains the allowed ranges and default values for these flags:
slave_parallel_workers | 0-1024 | 0 | 0 |
slave_parallel_type | DATABASE, LOGICAL_CLOCK | DATABASE | DATABASE |
slave_preserve_commit_order | 0, 1 | 0 | 1 |
slave_pending_jobs_size_max | 1024-1GB | 16MB | 128MB |
The slave_preserve_commit_order flag prevents gaps in the sequence of transactions executed from the replica's relay log.
The slave_preserve_commit_order=1 setting requires the following:
- Enabling binary logs on the replica
- Setting the slave_parallel_type to LOGICAL_CLOCK
The slave_pending_jobs_size_max flag sets the maximum memory, in bytes, available to applier queues holding events not yet applied.
Primary instance: Flags for parallel replication
Cloud SQL for MySQL supports several flags for use on a primary instance. You can use these flags to tune replication performance for associated read replicas with parallel replication enabled. For information about the flags, click these links to the MySQL 8.0 documentation:
- binlog_transaction_dependency_history_size
- binlog_transaction_dependency_tracking
- transaction_write_set_extraction
Changing these flags does not restart the primary instance.
The following table contains the allowed ranges and default values for these flags:
binlog_transaction_dependency_history_size | 1-1000000 | 25000 | 25000 |
binlog_transaction_dependency_tracking | COMMIT_ORDER, WRITESET, WRITESET_SESSION | COMMIT_ORDER | COMMIT_ORDER |
transaction_write_set_extraction | OFF, MURMUR32, XXHASH64 | OFF | XXHASH64 |
In MySQL 5.7, if binlog_transaction_dependency_tracking is set to WRITESET or WRITESET_SESSION, then transaction_write_set_extraction should be set to a non-OFF value (XXHASH64 or MURMUR32).
Check replication status
When you view a replica instance using the Google Cloud console or log into the instance using an administration client, you get details about replication, including status and metrics. When you use the gcloud, you get a brief summary of the replication configuration.
Before checking the replication status for a Cloud SQL replica instance, use the
gcloud sql instances describe command to display the status of the instance. As a result, you can see whether replication is enabled for the replica instance.
The following metrics are available for replica instances. (Learn more about additional metrics available for all instances, including non-replica instances.)
Replication State (cloudsql.googleapis.com/database/replication/state) | Indicates whether replication is actively streaming logs from the primary to the replica. Possible values are:
This metric reports Running if both the replica's I/O and SQL threads report they are running. See the Slave I/O thread running state and Slave SQL thread running state metrics below for more information, or consult Checking Replication Status in the MySQL Reference Manual. |
Replication Lag (cloudsql.googleapis.com/database/replication/replica_lag) | The amount of time that the replica's state is lagging behind the state of the primary instance. This is the difference between (1) the current time and (2) the original timestamp at which the primary committed the transaction that is currently being applied on the replica. In particular, writes may be counted as lagging even if they have been received by the replica, if the replica has not yet applied the write to the database. For cascading replicas, each primary-replica pair is monitored separately and there's no single metric that yields the end-to-end (primary to replica) lag. This metric reports the value of Seconds_Behind_Master when SHOW SLAVE STATUS is run on the replica. For more information, see Checking Replication Status in the MySQL Reference Manual. |
Network Lag (cloudsql.googleapis.com/database/replication/network_lag) | The amount of time, in seconds that it takes from writing the binlog in the primary database to reach the IO thread in the replica. If the network_lag is zero, or negligible, but the `replica_lag` is high, it indicates that the SQL thread is not able to apply replication changes fast enough. |
Slave I/O thread running state (cloudsql.googleapis.com/database/mysql/replication/slave_io_running_state) | Indicates whether the I/O thread for reading the primary instance's binary log is running on the replica. Possible values are:
This metric reports the value of Slave_IO_Running when SHOW SLAVE STATUS is run on the replica. For more information, see Checking Replication Status in the MySQL Reference Manual. |
Slave SQL thread running state (cloudsql.googleapis.com/database/mysql/replication/slave_sql_running_state) | Indicates whether the SQL thread for executing events in the relay log is running on the replica. Possible values are:
This metric reports the value of Slave_SQL_Running when SHOW SLAVE STATUS is run on the replica. For more information, see Checking Replication Status in the MySQL Reference Manual. |
To check replication status:
Cloud SQL reports the Replication State and Replication Lag metrics on the default Cloud SQL monitoring dashboard. To view other metrics for in-region and cross-region replicas, and replicas of external
servers, create a custom dashboard and add the metrics you wish to monitor to it: In the Google Cloud console, go to the Monitoring page. Go to Monitoring For a replica instance, check the replication status with: In the output, look for the properties databaseReplicationEnabled and masterInstanceName. For a primary instance, check if there are replicas with: In the output, look for the property replicaNames. For information, see Connection Options for External Applications. Look for the following metrics in the output of the command: For more details about the output from this command, see the MySQL documentation on
Checking Replication Status.Console
gcloud
mysql Client
Troubleshoot
Read replica did not start replicating on creation. | There's probably a more specific error in the log files. Inspect the logs in Cloud Logging to find the actual error. |
Unable to create read replica - invalidFlagValue error. | One of the flags in the request is invalid. It could be a flag you provided explicitly or one that was set to a default value. First, check that the value of the max_connections flag is greater than or equal to the value on the primary. If the max_connections flag is set appropriately, inspect the logs in Cloud Logging to find the actual error. |
Unable to create read replica - unknown error. | There's probably a more specific error in the log files. 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. | The primary instance disk size can become full during replica creation. Edit the primary instance to upgrade it to a larger disk size. |
The replica instance is using too much memory. | The replica uses temporary memory to cache often-requested read operations, which can lead it to use more memory than the primary instance. Restart the replica instance to reclaim the temporary memory space. |
Replication stopped. | The maximum storage limit was reached and automatic storage increase isn't enabled. Edit the instance to enable automatic storage increase. |
Replication lag is consistently high. | 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:
Some possible solutions include:
|
Replication lag suddenly spikes. | This is caused by long-running transaction(s). When a transaction (single statement or multi-statements) commits on the source instance, the start time of the transaction is recorded in the binary log. When the replica receives this binlog event, it compares that timestamp with the current timestamp to calculate replication lag. Hence, a long-running transaction on the source would result in an immediate large replication lag on the replica. If the amount of row changes in the
transaction is large, the replica would also spend a long time to execute it. During the time, replication lag is increasing. Once the replica finishes this transaction, the catch up period would depend on the write workload on the source and the replica's processing speed. To avoid a long transaction, some possible solutions include:
|
Changing parallel replication flags results in an error. | An incorrect value is set for one of or more of these flags. On the primary instance that's displaying the error message, set the parallel replication flags:
|
Replica creation fails with timeout. | Long-running uncommitted transactions on the primary instance can cause read replica creation to fail. Recreate the replica after stopping all running queries. |
What's next
- Learn how to create a read replica.
- Learn about Cloud SQL stored procedures for read replica indexes.
- Learn how to configure an external replica configuration.
- Learn how to configure an external primary configuration.
- Learn more about requirements and best practices for replication.