Create and manage read pools

gcloud

For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI . For information about starting Cloud Shell, see Use Cloud Shell .

Use the following gcloud sql instances create command to create a read pool with multiple read pool nodes:

  
gcloud  
sql  
instances  
create  
 READ_POOL_NAME 
  
 \ 
  
--tier = 
 TIER 
  
--edition = 
ENTERPRISE_PLUS  
 \ 
  
--instance-type = 
READ_POOL_INSTANCE  
--node-count = 
 NODE_COUNT 
  
 \ 
  
--master-instance-name = 
 PRIMARY_INSTANCE_NAME 
  

Make the following replacements:

• READ_POOL_NAME : the name you want to use for the read pool.
• TIER : the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4 .
• NODE_COUNT : the number of read pool nodes you want in the read pool. Choose any number from 1 to 20 .
• PRIMARY_INSTANCE_NAME : the name of the primary instance associated with the read pool, such as my-primary-instance .

Terraform

To create a read pool, use a Terraform resource . Then, set the instance_type attribute to "READ_POOL_INSTANCE" and the node_count attribute to the number of nodes you want to use.

The following example includes resources for the primary instance and the read pool.

 resource "google_sql_database_instance" "primary" {
  name             = "mysql-primary"
  database_version = "MYSQL_8_4"
  region           = "europe-west4"

  instance_type = "CLOUD_SQL_INSTANCE"

  settings {
    tier    = "db-perf-optimized-N-2"
    edition = "ENTERPRISE_PLUS"

    backup_configuration {
      enabled            = true
      binary_log_enabled = true
    }

    ip_configuration {
      ipv4_enabled = true
    }
  }
}

resource "google_sql_database_instance" "replica" {
  name             = "mysql-replica"
  database_version = "MYSQL_8_4"
  region           = "europe-west4"

  master_instance_name = google_sql_database_instance.primary.name
  instance_type        = "READ_POOL_INSTANCE"
  node_count           = 2

  settings {
    tier    = "db-perf-optimized-N-2"
    edition = "ENTERPRISE_PLUS"

    ip_configuration {
      ipv4_enabled = true
    }
  }
} 

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell .

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT= PROJECT_ID 
   

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module ).

1. In Cloud Shell , create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf . In this tutorial, the file is referred to as main.tf .

   mkdir DIRECTORY 
   && cd DIRECTORY 
   && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf .

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Delete the changes

To delete your changes, do the following:

1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false .

   deletion_protection =  "false"

2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

3. Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:

   terraform destroy

REST v1

Use the insert method of the instances resource to create a read pool with multiple read pool nodes. The databaseVersion property must be the same as the primary.

Before using any of the request data, make the following replacements:

• PROJECT : the name of the project where you want the primary instance and read pool to reside.
• REGION : the region for the read pool, such as us-east1 . The region must be the same as the primary instance.
• TIER : the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4 .
• PRIMARY_INSTANCE_NAME : the name of the primary instance.
• READ_POOL_NAME : the name you want to use for the read pool, such as my-read-pool .
• DATABASE_VERSION : the database version you want to use. For example, MYSQL_8_0_37 .
• NODE_COUNT : the number of read pool nodes you want in the read pool. Choose any number from 1 to 20 .
• FULL_NETWORK_NAME : the full network path where you want the read pool to reside, such as projects/vpc-host-project/global/networks/my-network-name .

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ PROJECT 
/instances

Request JSON body:

{
  "name": " READ_POOL_NAME 
",
  "masterInstanceName": " PRIMARY_INSTANCE_NAME 
",
  "project": " PROJECT 
",
  "databaseVersion": " DATABASE_VERSION 
",
  "region": " REGION 
",
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT 
,
  "settings": {
    "tier": " TIER 
",
    "edition": "ENTERPRISE_PLUS",
    "ipConfiguration": {
      "ipv4Enabled": false,
      "privateNetwork": " FULL_NETWORK_NAME 
"
    }
  }
}

To send your request, expand one of these options:

curl -X POST \
-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 
/instances"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT 
/instances" | 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 
/instances/ READ_POOL_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE_READ_POOL",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_ID 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT 
"
}

REST v1beta4

Use the insert method of the instances resource to create a read pool with multiple read pool nodes. The databaseVersion property must be the same as the primary.

Before using any of the request data, make the following replacements:

• PROJECT : the name of the project where you want the primary instance and read pool to reside.
• REGION : the region for the read pool, such as us-east1 . The region must be the same as the primary instance.
• TIER : the machine type you want to use for each read pool node in the read pool, such as db-perf-optimized-N-4 .
• PRIMARY_INSTANCE_NAME : the name of the primary instance.
• READ_POOL_NAME : the name you want to use for the read pool, such as my-read-pool .
• DATABASE_VERSION : the database version you want to use. For example, MYSQL_8_0_37 .
• NODE_COUNT : the number of read pool nodes you want in the read pool. Choose any number from 1 to 20 .
• FULL_NETWORK_NAME : the full network path where you want the read pool to reside, such as projects/vpc-host-project/global/networks/my-network-name .

HTTP method and URL:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT 
/instances

Request JSON body:

{
  "name": " READ_POOL_NAME 
",
  "masterInstanceName": " PRIMARY_INSTANCE_NAME 
",
  "project": " PROJECT 
",
  "databaseVersion": " DATABASE_VERSION 
",
  "region": " REGION 
",
  "instanceType": "READ_POOL_INSTANCE",
  "nodeCount": NODE_COUNT 
,
  "settings": {
    "tier": " TIER 
",
    "edition": "ENTERPRISE_PLUS",
    "ipConfiguration": {
      "ipv4Enabled": false,
      "privateNetwork": " FULL_NETWORK_NAME 
"
    }
  }
}

To send your request, expand one of these options:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT 
/instances"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT 
/instances" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT 
/instances/ READ_POOL_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE_READ_POOL",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_ID 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT 
"
}