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:
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select a replica instance by clicking its name.
- Click Disable replication in the button bar.
- Click OK .
gcloud
gcloud sql instances patch REPLICA_NAME \ --no-enable-database-replication
REST v1
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:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/ project-id /instances/ replica-name
Request JSON body:
{ "settings": { "databaseReplicationEnabled": "False" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
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:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id /instances/ replica-name
Request JSON body:
{ "settings": { "databaseReplicationEnabled": "False" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
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:
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select a replica instance by clicking its name.
- Click Enable replication .
- Click Ok .
gcloud
gcloud sql instances patch REPLICA_NAME \ --enable-database-replication
REST v1
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:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/ project-id /instances/ replica-name
Request JSON body:
{ "settings": { "databaseReplicationEnabled": "True" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
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:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id /instances/ replica-name
Request JSON body:
{ "settings": { "databaseReplicationEnabled": "True" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Promote a replica
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:
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select a replica instance by clicking its name.
- Click Promote replica .
- Click Ok .
gcloud
gcloud sql instances promote-replica REPLICA_NAME
REST v1
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:promoteReplica page to send the REST API request.
Before using any of the request data, make the following replacements:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/v1/projects/ project-id /instances/ replica-name /promoteReplica
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
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:promoteReplica page to send the REST API request.
Before using any of the request data, make the following replacements:
- project-id : The project ID
- replica-name : The name of the replica instance
HTTP method and URL:
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id /instances/ replica-name /promoteReplica
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
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:
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:
- replica_parallel_workers
- replica_parallel_type
- replica_preserve_commit_order
- replica_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:
Read replica flag | Allowed values | MySQL 5.7 default value | MySQL 8.0 and later default value |
---|---|---|---|
replica_parallel_workers
|
0-1024 | 0 | 0 (MySQL 8.0.26 and earlier) 4 (MySQL 8.0.27 and later) |
replica_parallel_type
|
DATABASE, LOGICAL_CLOCK
|
DATABASE
|
DATABASE
(MySQL 8.0.26 and earlier)LOGICAL_CLOCK
(MySQL 8.0.27 and later) |
replica_preserve_commit_order
|
ON, OFF
|
OFF
|
OFF
(MySQL 8.0.26 and earlier)ON
(MySQL 8.0.27 and later) |
replica_pending_jobs_size_max
|
1024-1GB | 16MB | 128MB |
The replica_preserve_commit_order
flag prevents gaps in the sequence of
transactions executed from the replica's relay log.
The replica_preserve_commit_order=1
setting requires the following:
- Enabling binary logs on the replica (only for MySQL 5.7, MySQL 8.0.18 and earlier version)
- Setting the
replica_parallel_type
toLOGICAL_CLOCK
The replica_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:
Primary instance flag | Allowed values | MySQL 5.7 default value | MySQL 8.0 default value | MySQL 8.4 default value |
---|---|---|---|---|
binlog_transaction_dependency_history_size
|
1-1000000 | 25000 | 25000 | 25000 |
binlog_transaction_dependency_tracking
|
COMMIT_ORDER, WRITESET, WRITESET_SESSION
|
COMMIT_ORDER
|
WRITESET
|
n/a (deprecated in MySQL 8.4) |
transaction_write_set_extraction
|
OFF, MURMUR32, XXHASH64
|
OFF
|
XXHASH64
|
n/a (deprecated in MySQL 8.4) |
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 CLI , 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.)
(
cloudsql.googleapis.com
/database
/replication
/state
)Indicates whether replication is actively streaming logs from the primary to the replica. Possible values are:
-
Running
-
Stopped
-
Error
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.
(
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 REPLICA STATUS
is run on the replica. For more
information, see Checking
Replication Status
in the MySQL Reference Manual.
(
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.
(
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:
-
Yes
-
No
-
Connecting
This metric reports the value of Slave_IO_Running
when SHOW REPLICA STATUS
is run on the replica. For more
information, see Checking
Replication Status
in the MySQL Reference Manual.
(
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:
-
Yes
-
No
-
Connecting
This metric reports the value of Slave_SQL_Running
when SHOW REPLICA STATUS
is run on the replica. For more
information, see Checking
Replication Status
in the MySQL Reference Manual.
To check replication status:
Console
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.
- Select the Dashboards tab.
- Click Create dashboard .
- Give the dashboard a name and click OK.
- Click Add chart .
- For Resource Type select Cloud SQL Database .
- Do any of the following:
- To monitor the replication state metric
: in the Select a
metric
field, type
Replication state
. Then add a filter forstate = "Running"
. The chart shows 1 if replication is running and 0 otherwise. - To monitor the replication lag metric
: in the Select a
metric
field, type
replica_lag
. The chart shows the amount of time that the replica's state lags behind that of its primary. - To monitor the status of the replica's I/O thread
: in the Select a metric
field, type
Slave I/O thread running state
. Then add a filter onstate = "Yes"
. The chart shows 1 if the thread is running and 0 otherwise. - To monitor the status of the replica's SQL thread
: in the Select a metric
field, type
Slave SQL thread running state
. Then add a filter onstate = "Yes"
. The chart shows 1 if the thread is running and 0 otherwise.
gcloud
For a replica instance, check the replication status with:
gcloud sql instances describe REPLICA_NAME
In the output, look for the properties databaseReplicationEnabled
and masterInstanceName
.
For a primary instance, check if there are replicas with:
gcloud sql instances describe PRIMARY_INSTANCE_NAME
In the output, look for the property replicaNames
.
mysql Client
- Connect to the replica with a MySQL client.
For information, see Connection Options for External Applications .
- Check the replica's status:
SHOW REPLICA STATUS \ G
Look for the following metrics in the output of the command:
-
Master_Host
: The name of the primary instance. -
Slave_IO_Running
,Slave_SQL_Running
: Whether the I/O and SQL threads, respectively, are running. These threads are responsible for transferring events from the primary to the replica's relay log and executing those events from the relay log. The value of the metric isYes
if the thread is running. Both threads must be running for replication to be active. -
Seconds_Behind_Master
: The amount of time, in seconds, by which the replica lags in processing the primary's transactions, i.e. 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. The value isNULL
if replication is broken. -
Master_Log_file
,Read_Master_Log_Pos
,Relay_Master_Log_File
,Exec_Master_Log_Pos
: These metrics show the coordinates (filename and offset) that the I/O thread has read events up to (Master_Log_file
andRead_Master_Log_Pos
) and that the SQL thread has executed events up to (Relay_Master_Log_File
andExec_Master_Log_Pos
). If they are the same (i.e.Master_Log_file
is equal toRelay_Master_Log_File
andRead_Master_Log_Pos
is equal toExec_Master_Log_Pos
) then the replica has processed all of the events it has received from the primary.
-
For more details about the output from this command, see the MySQL documentation on Checking Replication Status .
Troubleshoot
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.
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.
Restart the replica instance to reclaim the temporary memory space.
Edit the instance
to enable automatic storage increase
.
- Slow queries on the replica. Find and fix them.
- All tables must have a 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.
Some possible solutions include:
- Configure parallel replication .
- Set the innodb_flush_log_at_trx_commit
flag on the read replica to 2.
See Tips for working with flags for more information about this flag.
- Edit the instance to increase the size of the replica.
- Reduce the load on the database.
- Send read traffic to the read replica.
- Index the tables.
- Identify and fix slow write queries.
- Recreate the replica.
To avoid a long transaction, some possible solutions include:
- Break the transaction into multiple small transactions
- Chunk a single large write query into smaller batches
- Try to separate long SELECT queries from a transaction mixed with DMLs
On the primary instance that's displaying the error message, set the parallel replication flags:
- Modify the
binlog_transaction_dependency_tracking
andtransaction_write_set_extraction
flags:-
binlog_transaction_dependency_tracking=COMMIT_ORDER
-
transaction_write_set_extraction=OFF
-
- Add the
slave_pending_jobs_size_max
flag:slave_pending_jobs_size_max=33554432
- Modify the
transaction_write_set_extraction
flag:transaction_write_set_extraction=XXHASH64
- Modify the
binlog_transaction_dependency_tracking
flag:binlog_transaction_dependency_tracking=WRITESET
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 .