DEV Community

Charlotte Towell
Charlotte Towell

Posted on

MySQL 5.7 - 8.0 on Google CloudSQL: Inplace Migration

#mysql #googlecloud #database #devops

As of May 2025 - MySQL 5.7 is now in extended support for CloudSQL meaning your cloud bill is about to ⬆️⬆️⬆️

This is how I upgraded our CloudSQL instance(s) from MySQL 5.7 - MySQL 8.0 with minimal downtime via an inplace migration.

⚠️ Always clone your instance and complete these steps fully before performing on your production instance

Let's get started

Prerequisites:

Remember to add to your PATH variable so you can run the following commands:

mysql --version
mysqlsh --version
Enter fullscreen mode Exit fullscreen mode

Connect to Instance From Cloud Shell

🔗 Google Docs: Connect Instance Cloud Shell

Set the root user password

>gcloud sql users set-password root
--host=%
--instance=<instance_id>
--prompt-for-password
Enter fullscreen mode Exit fullscreen mode

Then connect to the instance to check it worked:

> gcloud sql connect <instance_id> --user=root
Allowlisting your IP for incoming connection for 5 minutes...done.
Enter fullscreen mode Exit fullscreen mode

You can also now run status command to see the current version info

Now connect to mysqlsh for the purpose of saving password (otherwise you need to enter password many times when running the below utility checker script):

>mysqlsh root@<ip>
Please provide the password for 'root@<ip>': *********************
Save password for 'root@<ip>'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 9.3.0
Enter fullscreen mode Exit fullscreen mode

Run the MySQL Upgrade Utility Checker

MySQL provides an Upgrade Utility Checker

Running as reccomended results in a timeout, instead use the following shell script which runs each check at a time, and saves to a file.

#!/bin/bash

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

# Prompt for IP address
echo -n "Enter MySQL server IP: "
read ip

# Create save directory
save_path="$HOME/Desktop/mysql-upgrade-checks"
mkdir -p "$save_path"

echo -e "\n${CYAN}Fetching available checks from $ip...${NC}"

# Script to list checks
script_to_list_checks='var checks = util.checkForServerUpgrade(null, { list: true }); print(JSON.stringify(checks));'

# Get list of checks
raw_checks=$(mysqlsh --js --uri root@$ip --execute="$script_to_list_checks" 2>&1)

# Parse the check names from the output
# Extract check IDs from the "- checkName" lines in the "Included:" section
checks=$(echo "$raw_checks" | awk '
/^Included:$/ { in_included = 1; next }
/^Excluded:$/ { in_included = 0; next }
in_included && /^- / { 
    # Extract the check name (first word after "- ")
    gsub(/^- /, "")
    print $1
}
')

if [ -z "$checks" ]; then
    echo -e "${RED}Error: Could not find any checks in the response.${NC}"
    echo "Raw output: $raw_checks"
    exit 1
fi

# Count checks
check_count=$(echo "$checks" | wc -l)
echo -e "${GREEN}Found $check_count checks.${NC}"

# Script template with placeholder
script_template='var result = util.checkForServerUpgrade(null, {
  targetVersion: "8.0",
  outputFormat: "JSON",
  include: ["__CHECK_ID__"]
});
print(JSON.stringify(result, null, 2));'

# Process each check
echo "$checks" | while read -r check_id; do
    if [ -n "$check_id" ]; then
        output_file="$save_path/$check_id.json"
        echo -e "${YELLOW}Running check: $check_id ...${NC}"

        # Replace placeholder with actual checkId
        script=$(echo "$script_template" | sed "s/__CHECK_ID__/$check_id/g")

        # Run the check and save output, filtering out "undefined"
        mysqlsh --js --uri root@$ip --execute="$script" 2>&1 | grep -v "^undefined$" > "$output_file"

        if [ -f "$output_file" ]; then
            echo -e "${GREEN}✔ Saved: $output_file${NC}"
        else
            echo -e "${RED}⚠ Failed to save output for $check_id${NC}"
        fi
    fi
done

echo -e "\n${CYAN}✅ All checks completed. Results saved to: $save_path${NC}"
Enter fullscreen mode Exit fullscreen mode

You will need the public IP address of the instance which you can find from Google Cloud Console:
Instances Public IP Address

Additionally, make sure you are IP whitelisted for your CloudSQL instance, otherwise connection will be lost part-way through the checks if > 5 minutes.

Review the Upgrade Utility Checker Results

As per the above script, the results will be saved to JSON files in the specified location $HOME/Desktop/mysql-upgrade-checks.

For this step internally, I created a python file to parse the JSON files into a condensed easy-to-read report. Below is a snippet of this outputted report, I highly suggest writing a script to produce a similar output for review:

=============
MySQL UPGRADE COMPATIBILITY CHECK REPORT
=============

SERVER INFORMATION
----------------------------------------
Server Address: <ip:
Current Version: 5.7.44-google-log - (Google)
Target Version: 8.0.42

EXECUTIVE SUMMARY
----------------------------------------
Total Files Processed: 37
Total Errors: 92
Total Warnings: 3093
Total Notices: 14259
Checks with Issues: 8
Checks OK: 23

❌ UPGRADE BLOCKED: Critical errors detected that must be resolved before upgrade.

CHECK SUMMARY ANALYSIS
=============================
🚨 CRITICAL ERRORS (Must Fix) (1 checks)
--------------------------------------------------
Check: MySQL syntax check for routine-like objects
  Source: syntax.json
  Affects 1 unique objects
  Affects 92 databases/schemas
  Total occurrences: 92
  Sample objects: example_proc_name (Routine)

⚠️  WARNINGS (Should Address) (5 checks)
--------------------------------------------------
...
ℹ️  NOTICES (Informational) (1 checks)
--------------------------------------------------
...
CHECK-BY-CHECK SUMMARY
============================
...
Enter fullscreen mode Exit fullscreen mode

Review this report and fix any critical issues accordingly. This may involve running ALTER statements to fix certain columns, or cleaning up unused legacy databases. When fixed, re-run the utility checker until you confirm there are no upgrade conflicts.

Take Backup of Instance

Note: that this is extra, as CloudSQL also takes automatic backups both pre & post upgrade

gcloud sql backups create --async --instance=<instance_id> --description=pre-mysql8-upgrade

Manually created backups are not deleted automatically, unless that instance is deleted. Google Docs Link

View the backup to confirm it was successfully made:

>gcloud sql backups list --instance <instance_id>
ID             WINDOW_START_TIME              ERROR  STATUS      INSTANCE
<backup_id>  2025-07-04T06:25:41.618+00:00  -      SUCCESSFUL  <instance_id>
<backup_id>  2025-07-04T04:05:16.766+00:00  -      SUCCESSFUL  <instance_id>
Enter fullscreen mode Exit fullscreen mode

or view more detail about specific backup like:

>gcloud sql backups describe <backup_id> --instance <instance_id>
backupKind: SNAPSHOT
databaseVersion: MYSQL_5_7
description: pre-mysql8-upgrade
endTime: '2025-07-04T06:27:12.911Z'
enqueuedTime: '2025-07-04T06:25:41.618Z'
id: '<backup_id>'
instance: <instance_id>
kind: sql#backupRun
location: <location>
maxChargeableBytes: '<bytes>'
selfLink: https://sqladmin.googleapis.com/sql/v1beta4/projects/<project_id>/instances/<instance_id>/backupRuns/<backup_id>
startTime: '2025-07-04T06:25:41.627Z'
status: SUCCESSFUL
type: ON_DEMAND
windowStartTime: '2025-07-04T06:25:41.618Z'
Enter fullscreen mode Exit fullscreen mode

Run Upgrade (Will cause some downtime!)

Get the database version for upgrade by running:
gcloud sql instances describe <instance_id> --format="table(upgradableDatabaseVersions)"

Choose the database version that you ran the MySQL utility checker against. You can see it at the top of the summary reports.

MYSQL_8_0_42

gcloud sql instances patch <instance_id> --database-version=<DATABASE_VERSION>

Depending on the size of your instance, this make take some time, up to 1 hour. The database will only be offline during a portion of this window.

You may get a timeout error which is fine, just run the following:

ERROR: (gcloud.sql.instances.patch) Operation https://sqladmin.googleapis.com/sql/v1beta4/projects/<project_id>/operations/<operation_id> is taking longer than expected. You can continue waiting for the operation by running `gcloud beta sql operations wait --project <project_id> <operation_id>`
Enter fullscreen mode Exit fullscreen mode

List operations against the instance

>gcloud sql operations list --instance=<instance_id> --filter=STATUS=RUNNING
NAME            TYPE    START                          END  ERROR  STATUS
<operation_id>  UPDATE  2025-07-04T06:41:23.812+00:00   T     -    RUNNING
Enter fullscreen mode Exit fullscreen mode

Use the operation ID to monitor the status

In the event of errors! Revert to backup

gcloud sql backups restore <backup_id> --resotre-instance=<instance_id>

Complete the upgrade

See CloudSQL upgrade guide for recomendations about additional needed testing i.e. updating user privileges. This will differ based on your instance configuration.

Google Docs Link

The main change needed will be to update your database user permissions.

GRANT ALL PRIVILEGES no longer works, so instead do this, tweaking based on which permissions you wish to grant to the user

GRANT
    ALTER,
    ALTER ROUTINE,
    CREATE,
    CREATE ROUTINE,
    CREATE TEMPORARY TABLES,
    CREATE VIEW,
    DELETE,
    DROP,
    EVENT,
    EXECUTE,
    INDEX,
    INSERT,
    LOCK TABLES,
    PROCESS,
    REFERENCES,
    SELECT,
    SHOW DATABASES,
    SHOW VIEW,
    TRIGGER,
    UPDATE
ON *.* TO `user`@`%`;
Enter fullscreen mode Exit fullscreen mode

Finally, delete backups

After a period of time, if all is well, you can delete the manually created backup via:

>gcloud beta sql backups delete <backup_id> --instance=<instance_id>

You can also use the same method to delete the CloudSQL automatic backups, else they will persistent indefinitely.

Upgrade Complete!

Just like that, no more extended support bills to pay to Google Cloud :)

Top comments (0)