Start/Stop Google Cloud SQL instances using Cloud Functions

Question

I am very new to Google Cloud Platform. I am looking for ways to automate starting and stopping a mySQL instance at a predefined time. I found that we could create a cloud function to start/stop an instance and then use the cloud scheduler to trigger this. However, I am not able to understand how this works.

I used the code that I found in GitHub. https://github.com/chris32g/Google-Cloud-Support/blob/master/Cloud%20Functions/turn_on_cloudSQL_instance https://github.com/chris32g/Google-Cloud-Support/blob/master/Cloud%20Functions/turn_off_CloudSQL_instance

However, I am not familiar with any of the programming languages like node, python or go. That was the reason for the confusion. Below is the code that I found on GitHub to Turn On a Cloud SQL instance:

# This file uses the Cloud SQL API to turn on a Cloud SQL instance.
from googleapiclient import discovery
from oauth2client.client import GoogleCredentials

credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials)
project = 'wave24-gonchristian'  # TODO: Update placeholder value.


def hello_world(request):

    instance = 'test'  # TODO: Update placeholder value.
    request = service.instances().get(project=project, instance=instance)
    response = request.execute()
    j = response["settings"]
    settingsVersion = int(j["settingsVersion"])

    dbinstancebody = {
       "settings": {
           "settingsVersion": settingsVersion,
           "tier": "db-n1-standard-1",
           "activationPolicy": "Always"
       }
    }

    request = service.instances().update(
       project=project,
       instance=instance,
       body=dbinstancebody)
    response = request.execute()
# pprint(response)

    request_json = request.get_json()

    if request.args and 'message' in request.args:
        return request.args.get('message')
    elif request_json and 'message' in request_json:
        return request_json['message']
    else:
        return f"Hello World!"

________________________

requirements.txt

google-api-python-client==1.7.8
google-auth-httplib2==0.0.3
google-auth==1.6.2
oauth2client==4.1.3

As I mentioned earlier, I am not familiar with Python. I just found this code on GitHub. I was trying to understand what this specific part does:

dbinstancebody = {
       "settings": {
           "settingsVersion": settingsVersion,
           "tier": "db-n1-standard-1",
           "activationPolicy": "Always"
       }
}

Answer 1

please try the code below :

from pprint import pprint
from googleapiclient import discovery
from oauth2client.client import GoogleCredentials
import os

credentials = GoogleCredentials.get_application_default()
service = discovery.build("sqladmin", "v1beta4", credentials=credentials)
project_id = os.environ.get("GCP_PROJECT")

# setup this vars using terraform and assign the value via terraform
desired_policy = os.environ.get("DESIRED_POLICY") # ALWAYS or NEVER
instance_name = os.environ.get("INSTANCE_NAME")

def cloudsql(request):
    request = service.instances().get(project=project_id, instance=instance_name)
    response = request.execute()

    state = response["state"]
    instance_state = str(state)

    x = response["settings"]
    current_policy = str(x["activationPolicy"])

    dbinstancebody = {"settings": {"activationPolicy": desired_policy}}


    if instance_state != "RUNNABLE":
        print("Instance is not in RUNNABLE STATE")
    else:
        if desired_policy != current_policy:
            request = service.instances().patch(
                project=project_id, instance=instance_name, body=dbinstancebody
            )
            response = request.execute()

            pprint(response)
        else:
            print(f"Instance is in RUNNABLE STATE but is also already configured with the desired policy: {desired_policy}")

In my repo you can have more information on how to setup the cloud function using Terraform. This cloud function is intended to do what you want but it is using environment variables, if you dont want to use them, just change the variables values on the python code.

Here is my repository Repo

Answer 2

You can use the API to amend the activationPolicy to "NEVER" to stop the server or "ALWAYS" to start it.

# PATCH
https://sqladmin.googleapis.com/sql/v1beta4/projects/{project}/instances/{instance}

# BODY
{
  "settings": {
    "activationPolicy": "NEVER"
  }
}

See this article in the Cloud SQL docs for more info: Starting, stopping, and restarting instances. You can also try out the instances.patch method in the REST API reference.

Answer 3

dbinstancebody = {
       "settings": {
           "settingsVersion": settingsVersion,
           "tier": "db-n1-standard-1",
           "activationPolicy": "Always"
       }
}

The code block above specifies sql instance properties you would like to update, amongst which the most relevant for your case is activationPolicy which allows you to stop / start sql instance.

For Second Generation instances, the activation policy is used only to start or stop the instance. You change the activation policy by starting and stopping the instance. Stopping the instance prevents further instance charges.

Activation policy can have two values Always or Never. Always will start the instance and Never will stop the instance.