Replication is the ability to create copies of a Cloud SQL instance or an on-premises database, and offload work to the copies.
IntroductionThe primary reason for using replication is to scale the use of data in a database without degrading performance.
Other reasons include:
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 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.
In a disaster recovery scenario, you can promote a replica to convert it to a primary instance. This way, you can use it in place of an instance that's in a region that's having an outage. You can also promote a replica to replace an instance that's corrupted.
Cloud SQL supports the following types of replicas:
By using connector enforcement, you can enforce using only the Cloud SQL Auth Proxy or Cloud SQL Language Connectors to connect to Cloud SQL instances. With connector enforcement, Cloud SQL rejects direct connections to the database. You can't create read replicas for an instance that has connector enforcement enabled. Similarly, if an instance has read replicas, then you can't enable connector enforcement for the instance.
This limitation doesn't apply to the instances that have private service access configured.You can also use
Database Migration Servicefor continuous replication from a source database server to Cloud SQL.
Cloud SQL doesn't support replication between two external servers. However, Cloud SQL supports global transaction identifier (GTID)-based replication. GTIDs uniquely identify each transaction on the server and within a replication setup. Because each transaction has a unique identifier, the MySQL server can keep track of the transactions that it has run. A GTID uses absolute coordinates so the replica of a Cloud SQL instance can point to its primary instance, and you don't have to specify a filename for the binary log or a position in the CHANGE MASTER statement. There are fewer errors with replicas and with point-in-time recovery. Because of these benefits, you can't disable GTID-based replication in Cloud SQL.
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.
Note: We recommend that you limit the number of direct read replicas of your primary instance to 10 or fewer. If you need additional replicas, then you can create cascading read replicas.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 because the connection 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.
Note: Read replicas don't provide failover capability. To provide failover capability for an instance, see Configuring an instance for high availability.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.
Selecting an appropriate machine typeRead replicas can have a different number of vCPUs and memory from that of the primary. You should monitor metrics on your instance such as CPU and memory usage to ensure that the replica instance is sized correctly for its workload, especially if it is smaller than the primary instance. A replica instance that is undersized is more prone to poor performance, such as frequent out-of-memory (OOM) events.
Storage capacity on read replicasWhen a primary instance is resized, all of its read replicas are resized, if needed, so that they have at least as much storage capacity as the updated primary instance.
Cross-region read replicasCross-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:
See Promoting replicas for regional migration or disaster recovery for more information about cross-region replicas.
Cascading read replicasCascading replication lets you create a read replica under another read replica in the same or a different region. The following scenarios are use cases for using cascading replicas:
Cascading replicas let you add read replicas to any existing replicas. You can add up to four levels of replicas, including the primary instance. When you promote the replica at the top of a cascading replica hierarchy, it becomes a primary instance and its cascading replicas continue to replicate.
To plan your configuration, you need to have a goal for what the read replicas intend to do. The next two sections describe the configurations for disaster recovery and multi-region replication.
Disaster recoveryTo understand how cascading replicas help you recover quickly during an outage, consider the following replication scenario:
Configuration Outage PromotionIf you want to use an instance in Region B in a disaster recovery configuration and have:
You can create read replicas under the cascading replica in Region B.
On the Outage tab, if there's an outage in Region A, the cascading replica is promoted to a primary instance. It already has read replicas underneath it, reducing the recovery time objective (RTO).
On the Promote tab, you see that when a cascading replica is promoted, its replicas are also promoted and continue to replicate under it.
Multi-region replicationAnother use case for cascading replicas is to distribute read capacity to a second region in a cost-efficient manner. Cascading replicas C and D can be created that replicate from Replica B. Clients can distribute read queries across replicas B, C, and D to reduce the load on each replica. The cost of cross-region network traffic is incurred only once, from the primary instance to Replica B. Replication from B to C and D uses in-region network transfer, which is free.
You can create a hierarchy of up to four instances using cascading replicas for multi-region replication:
Primary A → Replica B → Replica C and Replica D
RestrictionsExternal 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:
replicate-ignore-db is required, and cloud providers where this is not allowed aren't supported. See Configuring external replicas for other required configuration fields.If you create an external read replica for an instance, and enforce using only the Cloud SQL Auth Proxy or Cloud SQL Language Connectors to connect to an instance that has private services access configured for it, then you must add the replica's subnet ranges to the primary instance's authorized networks. You must configure all ranges as authorized networks of the Cloud SQL instance.
gcloudTo set IP authorization for an instance to allow traffic from IP address ranges of an external read replica, use the gcloud sql instances patch command:
gcloud sql instances patch \ --authorized-networks=IP_ADDRESS_RANGE_1/24,IP_ADDRESS_RANGE_2/24
Replace IP_ADDRESS_RANGE_1 and IP_ADDRESS_RANGE_2 with the IP address ranges of your external read replica.
RESTBefore using any of the request data, make the following replacements:
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME
Request JSON body:
{
"kind": "sql#instance",
"name": INSTANCE_NAME,
"project": PROJECT_ID,
"settings": {
"ipConfiguration": {
"authorizedNetworks": [{"kind": "sql#aclEntry", "value": "IP_ADDRESS_RANGE_1/24"}, {"kind": "sql#aclEntry", "value": "IP_ADDRESS_RANGE_2/24"}]},
"kind": "sql#settings"
}
}
To send your request, expand one of these options:
curl (Linux, macOS, or Cloud Shell) Note: The following command assumes that you have logged in to thegcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.
Save the request body in a file named request.json, and execute the following command:
curl -X PATCH \PowerShell (Windows) Note: The following command assumes that you have logged in to the
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"
gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.
Save the request body in a file named request.json, and execute the following command:
$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }Invoke-WebRequest `
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content
You should receive a JSON response similar to the following:
{
"kind": "sql#operation",
"targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME",
"status": "PENDING",
"user": "user@example.com",
"insertTime": "2020-01-16T02:32:12.281Z",
"operationType": "UPDATE",
"name": "OPERATION_ID",
"targetId": "INSTANCE_NAME",
"selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
"targetProject": "PROJECT_ID"
}
The following use cases apply for each type of replication.
Prerequisites for creating a read replicaBefore you can create a read replica of a primary Cloud SQL instance, the instance must meet the following requirements:
Additional requirements for the external replica:
You must enable point-in-time recovery to enable binary logging on the primary instance to support read replicas. This has the following impacts:
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 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 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.
enable binary logging and enable point-in-time recovery are interchangeable. 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.stop a replica. You can restart, delete, or disable replication on it, but you cannot stop it as you can a primary instance. Upgrading a replica Read replicas can experience a disruptive upgrade at any time. User tables You cannot make changes on the replica. All user changes must be done on the primary instance. What's next
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4