How to create a cron job in Google Cloud Platform that will start Cloud SQL instances at a predefined time?

Question

I want to create a cron job, which when triggered will start a Cloud SQL instance. I found a way to start compute engine instances, but how do I go about starting cloud Sql instances?

Link for starting compute engine instances-

https://cloud.google.com/scheduler/docs/start-and-stop-compute-engine-instances-on-a-schedule

Answer 1

Create a Cloud Scheduler job that calls Pub-Sub topic which should be bound with a Cloud Function. In order to access Cloud SQL instance from Cloud Function, you need to enable Cloud SQL Admin API.

Method 1

With this method, your previous SQL Configuration will not be lost after executing any of the below source codes. This will only STOP and START your instance.

1. Source code of STOP SQL instance cloud function (Python 3.7)

main.py

# 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 = '<YOUR_PROJECT_ID>'

def stop_database(event, context):

    instance = '<YOUR_SQL_INSTANCE_ID>'
    request = service.instances().get(project=project, instance=instance)
    response = request.execute()

    response["settings"]["activationPolicy"] = "NEVER"
    
    request = service.instances().update(
       project=project,
       instance=instance,
       body=response)
    response = request.execute()

requirements.txt

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

---

2. Source code of START SQL instance cloud function (Python 3.7)

main.py

# 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 = '<YOUR_PROJECT_ID>'

def start_database(event, context):

    instance = '<YOUR_SQL_INSTANCE_ID>'
    request = service.instances().get(project=project, instance=instance)
    response = request.execute()

    response["settings"]["activationPolicy"] = "ALWAYS"
    
    request = service.instances().update(
       project=project,
       instance=instance,
       body=response)
    response = request.execute()

requirements.txt

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

---

3. Link each Pub-Sub topic with relevant Cloud Function.

---

4. Schedule Cron-jobs in Cloud Scheduler.

Create separate Scheduler jobs for starting and stopping SQL instance. And then add the Frequency as follows for an everyday job.

Examples:

• Everyday, 09:00 AM -> 0 9 * * *
• Everyday, 10:30 PM -> 30 22 * * *

Method 2

With this method, you can put the same pre-defined configuration every time you START or STOP the instance through these functions. But if you change any configuration from Google cloud Console, those will be lost once these functions will be executed by the scheduled jobs.

1. Source code of STOP SQL instance cloud function (Python 3.7)

main.py

# 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 = '<YOUR_PROJECT_ID>'

def stop_database(event, context):

    instance = '<YOUR_SQL_INSTANCE_ID>'
    request = service.instances().get(project=project, instance=instance)
    response = request.execute()

    j = response["settings"]
    settingsVersion = int(j["settingsVersion"])
    
    dbinstancebody = {
      "settings": {
         "settingsVersion": settingsVersion,
         "tier": "db-f1-micro",
         "activationPolicy": "NEVER",
         "databaseFlags": [
            {
               "name": "default_time_zone",
               "value": "+05:30"
            }
         ],
         "ipConfiguration": {
            "authorizedNetworks": [
               {
                  "name": "VM",
                  "value": "<YOUR_EXTERNAL_ACCESS_IP>"
               }
            ]
         },
         "backupConfiguration": {
            "enabled": "true"
         }
      }
    }
    
    request = service.instances().update(
       project=project,
       instance=instance,
       body=dbinstancebody)
    response = request.execute()

requirements.txt

Same as above Method 1

---

2. Source code of START SQL instance cloud function (Python 3.7)

main.py

# 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 = '<YOUR_PROJECT_ID>'

def start_database(event, context):

    instance = '<YOUR_SQL_INSTANCE_ID>'
    request = service.instances().get(project=project, instance=instance)
    response = request.execute()

    j = response["settings"]
    settingsVersion = int(j["settingsVersion"])
    
    dbinstancebody = {
      "settings": {
         "settingsVersion": settingsVersion,
         "tier": "db-f1-micro",
         "activationPolicy": "ALWAYS",
         "databaseFlags": [
            {
               "name": "default_time_zone",
               "value": "+05:30"
            }
         ],
         "ipConfiguration": {
            "authorizedNetworks": [
               {
                  "name": "VM",
                  "value": "<YOUR_EXTERNAL_ACCESS_IP>"
               }
            ]
         },
         "backupConfiguration": {
            "enabled": "true"
         }
      }
    }
    
    request = service.instances().update(
       project=project,
       instance=instance,
       body=dbinstancebody)
    response = request.execute()

requirements.txt

Same as above Method 1

---

3. Same as Method 1 -> Step 3

---

4. Same as Method 1 -> Step 4

Answer 2

In order to achieve this you can use a Cloud Function to make a call to the Cloud SQL Admin API to start and stop your Cloud SQL instance (you will need 2 Cloud functions)

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

You can see my code on how to use a Cloud Function to start a Cloud SQL instance and stop a Cloud SQL instance

After creating your Cloud Function you can configure the Cloud Scheduler to trigger the HTTP address of each Cloud function or you can follow the recommended approach of this guide and trigger the functions with pub/sub