Migration of Standalone Application and Database from VM (Google Compute Engine) to a modern architecture, using Google Kubernetes Engine (APP) + Cloud SQL (DB) with HA

Overview

This project modernized a simple patient management web application that initially ran on virtual machines. The target architecture moved the application to managed and scalable Google Cloud services. The final result was a containerized Node.js application deployed on a GKE Autopilot cluster and connected to Cloud SQL for MySQL through private networking.

The implementation also validated database high availability by configuring Cloud SQL in a regional, multi-zone setup and successfully testing failover. Along the way, several real-world troubleshooting scenarios appeared, including package repositories, authentication modes, IAM permissions, EJS compatibility, Cloud Build permissions, and TLS requirements.

Project

Module 7 — Google Cloud Hands-on Implementation

Domain

Application modernization, managed database, and Kubernetes

Platform

Google Cloud Platform (GCP)

Services used

Compute Engine, Cloud SQL for MySQL, Artifact Registry, Cloud Build, Google Kubernetes Engine Autopilot, Cloud Storage, VPC/Private Service Access

Outcome

Containerized application running on GKE and connected to Cloud SQL over private IP with high availability validated

Solution Architecture

Purpose of the exercise

The main purpose was to practice modernizing a traditional VM-based architecture into a more production-oriented cloud-native architecture using managed services, container image automation, and Kubernetes orchestration.

· Create an initial environment with an application VM and a database VM.

· Install and configure MySQL 8 on the database VM.

· Run a Node.js application connected to the local VM-based database.

· Create a Cloud SQL MySQL 8 instance with private IP and high availability.

· Containerize the application and publish the image to Artifact Registry using Cloud Build.

· Deploy the application to GKE Autopilot using a Kubernetes manifest.

· Migrate data from the local MySQL database to Cloud SQL.

· Validate connectivity, authentication, and failover behavior.

Implemented architecture

The final architecture consists of a containerized Node.js application exposed through a Kubernetes LoadBalancer service. The persistence layer was migrated to Cloud SQL for MySQL, accessed by private IP through the default VPC using Private Service Access.

Components & Role

Compute Engine — aus-app01

Initial environment used to run the Node.js application during preparation.

Compute Engine — aus-db01

Initial MySQL server used as the source database for migration.

Cloud SQL for MySQL

Managed database configured with private IP and high availability.

Artifact Registry

Repository used to store the Docker image.

Cloud Build

Service used to build and push the container image.

GKE Autopilot

Managed Kubernetes cluster used to run the containerized application.

Cloud Storage

Temporary bucket used to store the SQL dump during import.

Private Service Access

Private connectivity between the VPC and Cloud SQL.

User -> GKE LoadBalancer -> Node.js Pod -> Cloud SQL private IP -> clinic database

Implementation steps

1 Environment preparation

Two Compute Engine VMs were created: aus-app01 for the application and aus-db01 for the database. Required APIs were enabled, including Compute Engine, Kubernetes Engine, Artifact Registry, Cloud Build, and Service Networking.

VM Instances (aus-app01 & aus-db01)

2 MySQL installation and configuration

The aus-db01 VM was configured with MySQL Community Server 8.0.46. The clinic database was created from the lab scripts, and the app user was granted remote access permissions.

3 Initial application execution on a VM

The Node.js application was installed on aus-app01. The index.js file was configured to use the private IP address of aus-db01, with the app user and the clinic database.

Application running (using VM instances)

4 Cloud SQL creation

A Cloud SQL for MySQL 8.0 instance was created in australia-southeast1 with private IP, Private Service Access, 10 GB SSD storage, and multi-zone high availability.

Cloud SQL instance (tcb-gcp-aus-db01)

5 Containerization and image publishing

The application was packaged as a Docker image. The build was executed with Cloud Build and the image was published to Artifact Registry under the aus-repo repository.

Artifact Registry (aus-repo)

6 Deployment to GKE Autopilot

The autopilot-cluster-1 cluster was created in australia-southeast1. The tcb-clinic.yaml manifest was updated with the image path, the Cloud SQL private IP, and the database environment variables.

GKE Cluster

7 Database migration

The clinic database was exported from the aus-db01 VM into clinic.sql, uploaded to Cloud Storage, and imported into Cloud SQL. The patient table was validated with sample records.

Database dump generation (from aus-db01 instance)

Database dump loaded into Cloud SQL instance

8 High availability validation

With the application running on GKE and Cloud SQL configured as regional, a database failover was triggered. The application recovered successfully after the primary zone changed.

Application running GKE Cluster over Australia Region (Primary: australia-southeast1-a / Secundary: australia-southeast1-c)

Starts failover process

Change zones (Primary: australia-southeast1-c / Secondary: australia-southeast1-a)

Application running on (Primary: australia-southeast1-c / Secondary: australia-southeast1-a)

Cloud SQL logs showing Failover test finished

Challenges and resolutions

a) Challenge: mysql-server package unavailable — Likely cause: The MySQL repository on Debian 12 had an expired GPG key — Resolution: The repository was cleaned/reconfigured and MySQL Community Server 8.0.46 was installed.

b) Challenge: Risk of using MariaDB Server — Likely cause: Debian installed MariaDB through default-mysql-server, while the lab expected MySQL 8.— Resolution: MariaDB Server was replaced with MySQL Server 8 for better alignment with Cloud SQL MySQL.

c) Challenge: ER_NOT_SUPPORTED_AUTH_MODE — Likely cause: The older Node.js MySQL driver was incompatible with MySQL 8’s caching_sha2_password plugin. — Resolution: The app user was changed to mysql_native_password.

d) Challenge: EJS syntax error — Likely cause: The templates used older EJS include syntax. — Resolution: The include syntax was updated to <%- include(‘partials/_header’) %> and <%- include(‘partials/_footer’) %>.

e) Challenge: Cloud Build 403 error — Likely cause: The build service account lacked permissions to read temporary Cloud Storage objects.— Resolution: Cloud Build, Storage, and Artifact Registry permissions were granted appropriately.

f) Challenge: Cloud SQL import 412 error — Likely cause: The Cloud SQL service account lacked read access to the dump bucket. — Resolution: storage.objectViewer was granted to the Cloud SQL service account.

g) Challenge: Cloud SQL access denied— Likely cause: The app user did not have an explicit grant on the clinic schema. — Resolution: GRANT ALL PRIVILEGES ON clinic.* TO ‘app’@’%’ was applied.

h) Challenge: Node.js required TLS— Likely cause: The MySQL 8 client worked, but the older Node.js mysql driver failed without TLS. — Resolution: The connection configuration was updated with ssl: { rejectUnauthorized: false }.

Results achieved

1. Node.js application running as a container on GKE Autopilot.

2. Docker image stored in Artifact Registry.

3. clinic database migrated to Cloud SQL for MySQL.

4. Private connectivity between GKE and Cloud SQL validated.

5. External LoadBalancer exposing the application.

6. Cloud SQL configured with high availability and failover validated.

7. Troubleshooting across IAM, MySQL, EJS, Kubernetes, Cloud Build, and TLS documented.