Introduction > Demos > HA Cloud SQL

HA Cloud SQL

Create an HA Cloud SQL (MySQL) instance
1. Conditions
  1. Create a database with user ‘andrew’ GRANT USAGE ON *.* TO andrew@'%' IDENTIFIED BY 'password';
Create a GCE instance (VM)
1. Conditions
  1. The instance has external IP (Notes: this step may require adjusting the organizational policies to allow external IP for GCE VM)
  2. There is a firewall rule that allow SSH access (tcp on port 22)

Write few bash scripts to test connection, and repeat it every second

checkMySQLConnection.sh

#!/bin/bash
USER=andrew
PASS=password
REMOTEHOST=10.74.32.3
MYSQL_CONN="-h${REMOTEHOST} -u${USER} -p${PASS}"
mysqladmin ping ${MYSQL_CONN} 2>/dev/null 1>/dev/null
if [ $? -eq 0 ]
        then
                echo $(date) "DB connection is AVAILABLE"
        else
                echo $(date) "DB connection is NOT available" 
fi

Run command while sleep 1; do sh checkMySQLConnection.sh; done

Write Cloud Function code to update the instance type upon receiving a message from cloud pub/sub
1. May need to overwrite org policy constraints/cloudfunctions.allowedIngressSettings
2. Require Cloud SQL Admin API to be enabled
3. It’s simpler to create a new service account for Cloud Function instead of figuring out ways to update the default service account’s permissions (i.e. appspot) which by default, since it’s automatically created by GCP, does not appear in IAM page.
Run the demo
1. Open 2 SSH windows
  1. Run the checker script while sleep 1; do sh checkProxySQLConnection.sh; done
  2. Run the main script python3 main.py
  3. Capture the timestamp when the primary’s writer is stopped, and the timestamp where the replica is promoted and available. Expecting this to be around 30 seconds.
Clean up & prep for the next demo
1. Delete the promoted replica
2. Reset
  1. Update reset.py with new replica’s name. Run python3 reset.py
  2. Capture new replica IP
  3. Update proxySQL with the new replica IP
```
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='proxysql> '

update mysql_servers set hostname='10.74.32.31' where hostname='10.74.32.25';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
```
  1. Edit updateProxySQL.py, update replica’s IP
  2. Edit promoteReplica.py, update replica’s name

REFERENCES

Stopping a primary instance which has read replicas is not allowed on console, but possible through APIs
https://cloud.google.com/solutions/cloud-sql-mysql-disaster-recovery-complete-failover-fallback
Python code to update instance type

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

credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1', credentials=credentials, cache_discovery=False)
project = 'addo-demo-cloudsql'
instance_name = 'ha-demo'
scaledup_tier = 'db-n1-standard-2'
scaleddown_tier = 'db-n1-standard-1'

def scaledb(event, context):
print(event)
request = service.instances().get(project=project, instance=instance_name)
response = request.execute()

updated_dbinstancebody = {
    "settings": {
    "settingsVersion": response["settings"]["settingsVersion"],
    "tier": scaledup_tier,
    "activationPolicy": "ALWAYS"
    }
}
patch(instance_name, updated_dbinstancebody)

def patch(instance, dbinstancebody):
request = service.instances().get(project=project, instance=instance)
response = request.execute()

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

Requirements.txt

google-api-python-client==1.10.0
google-auth-httplib2==0.0.4
google-auth==1.19.2
oauth2client==4.1.3

Credits:
1. https://stackoverflow.com/questions/49547016/how-to-run-google-cloud-sql-only-when-i-need-it
Update downtime is around 3 minutes

Tue Apr 12 17:42:20 UTC 2022 DB connection is AVAILABLE
Tue Apr 12 17:42:21 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:22 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:23 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:24 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:25 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:26 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:27 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:28 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:29 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:30 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:31 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:32 UTC 2022 DB connection is NOT available
Tue Apr 12 17:42:33 UTC 2022 DB connection is NOT available
Tue Apr 12 17:44:41 UTC 2022 DB connection is NOT available
Tue Apr 12 17:45:13 UTC 2022 DB connection is AVAILABLE

Failover downtime is around 25 seconds. Failover can be done on the cloud console.

Wed Apr 13 01:54:20 UTC 2022 DB connection is AVAILABLE
Wed Apr 13 01:54:21 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:22 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:23 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:24 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:25 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:26 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:27 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:28 UTC 2022 DB connection is NOT available
Wed Apr 13 01:54:44 UTC 2022 DB connection is AVAILABLE

AWS RDS has downtime during the update (scale vertical) too!
1. https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-downtime-impact/ (typically 60-120 seconds). It’s because the machine is change vs. for maintenance the same machine is reused (i.e. just the software is updated)
2. For maintenance (i.e. not update) AWS RDS use failover to minimize the downtime. Cloud SQL does seem to provide this feature too (https://cloud.google.com/sql/docs/mysql/maintenance#how_maintenance_works). Cloud SQL use “maintenance failover workflow” which is largely similar to “automatic failover workflow” for HA instances.
Python CloudSQL cloudsqlamdin APIs
1. https://developers.google.com/resources/api-libraries/documentation/sqladmin/v1beta4/python/latest/sqladmin_v1beta4.instances.html#promoteReplica
Another option is to scale
1. Scale horizontally instead with read-replicas (https://cloud.google.com/community/tutorials/horizontally-scale-mysql-database-backend-with-google-cloud-sql-and-proxysql)
2. Change to another database type which scale easier (e.g. NoSQL)