Check for MySQL Major Version

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk level: Low (generally tolerable level of risk)

Ensure that your Google Cloud MySQL database instances are using the latest major version of MySQL database in order to receive the latest database features and benefit from enhanced performance and security.

Security
Reliability
Performance
efficiency

By upgrading your Google Cloud MySQL instances with the newer version of MySQL database, you will install new MySQL features and security patches, introduce performance improvements, and provide better compatibility with other cloud applications and services.

For MySQL, the first two numbers in the version number represent the major version number. For example, for version 5.7.25, Cloud SQL service uses 5.7 as the major version, and 25 as the minor version.


Audit

To determine if your Google Cloud MySQL database instances are using the latest major version of MySQL, perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and MySQL then press Enter, to list only the MySQL database instances provisioned for the selected GCP project.

05 Click on the identifier (ID) of the database instance that you want to examine.

06 In the navigation panel, select Overview to access the configuration details available for the selected instance.

07 In the Configuration section, check the MySQL database major version available for the Database version is <mysqlql-version> attribute, where <mysqlql-version> is the MySQL engine version installed. Compare the installed version with the latest major version of the MySQL database engine supported by Google Cloud SQL service. If the MySQL version installed is lower than the latest major version supported by Google Cloud MySQL instances, the selected instance is not using the newest version of the MySQL database.

08 Repeat step no. 5 – 7 to check the database engine version for other MySQL instances available within the selected project.

09 Repeat steps no. 2 – 8 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account:

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-mobile-project-123123
cc-bigdata-project-112233

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to describe the name of each MySQL database instance provisioned for the selected Google Cloud project:

gcloud sql instances list
	--project cc-mobile-project-123123
	--filter='DATABASE_VERSION:MYSQL*'
	--format="(NAME)"

04 The command output should return the requested database instance name(s):

NAME
cc-app-mysql-instance
cc-web-mysql-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the MySQL database instance that you want to examine as identifier parameter and custom query filters to describe the MySQL major version installed on the selected database instance:

gcloud sql instances describe cc-app-mysql-instance
	--format=json | jq '.databaseVersion'

06 The command output should return the requested flag configuration value:

"MYSQL_5_6"

Compare the version returned by the sql instances describe command output with the latest major version of the MySQL database engine supported by Google Cloud SQL service. If the version installed is lower than the latest major version supported by Google Cloud MySQL instances, the selected instance is not using the newest version of the MySQL database.

07 Repeat step no. 5 and 6 to check the database engine version for other MySQL database instances created for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

MySQL database version cannot be automatically upgraded within Google Cloud Platform (GCP). To upgrade your Google Cloud MySQL instances to the latest major version of the MySQL database, you have to re-create the existing instance, export data from the existing (source) instance, and importing that data into a new (target) instance running the latest major version of MySQL. To implement the rule remediation/resolution process, perform the following operations:

Note: Before moving your data to a database instance running a newer version of MySQL, you should familiarize yourself with the differences between the MySQL database engine versions.

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and MySQL then press Enter, to display only the MySQL database instances available for the selected project.

05 Click on the ID of the database instance that you want to re-create and collect all the configuration information available for the selected resource.

06 Navigate back to the Cloud SQL Instances at https://console.cloud.google.com/sql/instances and click on the CREATE INSTANCE button from the dashboard top menu to initiate the MySQL database instance setup.

07 From Choose your database engine panel, select Choose MySQL.

08 On the Instance info panel, perform the following actions:

  1. Provide a unique identifier for the new instance within the Instance ID box.
  2. Type your password for the "root'@'%" user in the Root password text box.
  3. Use the Region and Zone settings to deploy the new database instance in the same region and zone as the source instance.
  4. Select the latest major version of the MySQL database engine from the Database version dropdown list.
  5. Click on Show configuration options to open the Configuration options panel, and configure the rest of the instance settings based on the configuration information taken from the source instance at step no. 5. Ensure that the new instance has sufficient storage to hold the source instance data, the same authorized networks as the source instance and the same user accounts, with the same MySQL privileges and passwords.
  6. Click Create to launch your new MySQL database instance.

09 Put your source MySQL instance into read-only mode by setting the "read_only" database flag to On. Check this conformity rule to see how MySQL database flags are configured.

10 Export the source instance data to an SQL dump file and import that data to the newly created (target) instance.

11 Update your application(s) to connect to the new database instance.

12 Once the new database is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud monthly bill. Click on the identifier (ID) of the resource that you want to delete (see Audit section part I to identify the source instance).

13 Click on the DELETE button from the dashboard top menu to initiate the removal process.

14 Within Delete instance? confirmation box, confirm resource deletion by typing the instance identifier, then click DELETE.

15 Repeat step no. 5 – 14 to upgrade other MySQL instances available in the selected project to the latest major version of MySQL database.

16 Repeat steps no. 2 – 15 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances describe command (Windows/macOS/Linux) using the name of the MySQL database instance that you want to re-create as identifier parameter and custom query filters to describe the configuration metadata available for the selected database instance:

gcloud sql instances describe cc-app-mysql-instance
	--format=json

02 The command output should return the requested configuration metadata:

{
  "backendType": "SECOND_GEN",
  "databaseVersion": "MYSQL_5_6",
  "gceZone": "us-central1-a",
  "instanceType": "CLOUD_SQL_INSTANCE",
  "kind": "sql#instance",
  "name": "cc-app-mysql-instance",
  "project": "cc-mobile-project-123123",
  "region": "us-central1",
  "serviceAccountEmailAddress": "pabcd1234abcd-123123@gcp-sa-cloud-sql.iam.gserviceaccount.com",
  "settings": {
    "activationPolicy": "ALWAYS",
    "availabilityType": "ZONAL",

    ...

    "dataDiskSizeGb": "100",
    "dataDiskType": "PD_SSD",
    "ipConfiguration": {
      "ipv4Enabled": true
    },
    "locationPreference": {
      "kind": "sql#locationPreference",
      "zone": "us-central1-a"
    },
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "tier": "db-n1-standard"
  },
  "state": "RUNNABLE"
}

03 Run sql instances create command (Windows/macOS/Linux) using the information returned at the previous step as configuration data for the command parameters, to create a new Google Cloud SQL database instance with the MySQL database major version set to MySQL 5.7:

gcloud sql instances create cc-new-mysql-instance
	--database-version=MYSQL_5_7
	--tier=db-n1-standard
	--storage-size=100
	--storage-type=SSD
	--zone=us-central1-a
	--availability-type=ZONAL
	--activation-policy=always
	--assign-ip
	--require-ssl
	--root-password=xxxxxxxxxx
	--project=cc-mobile-project-123123

04 The command output should return the metadata for the newly created MySQL 5.7 database instance:

Creating Cloud SQL instance...done.

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-new-mysql-instance].

NAME                   DATABASE_VERSION  LOCATION       TIER            PRIMARY_ADDRESS  STATUS
cc-new-mysql-instance  MYSQL_5_7         us-central1-a  db-n1-standard  xxx.xxx.xxx.xxx  RUNNABLE

05 Put your source MySQL instance into read-only mode by setting the "read_only" database flag to On. Check this rule to see how MySQL database flags are configured.

06 Export the source instance data to an SQL dump file and import that data to the newly created (target) instance.

07 Update your application(s) to connect to the new database instance.

08 Once the new database is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud bill. Run sql instances delete command (Windows/macOS/Linux) using the ID of the MySQL database instance that you want to remove as identifier parameters (see Audit section part II to identify the right resource), to delete the specified database instance:

gcloud sql instances delete cc-app-mysql-instance

09 Type Y to confirm the resource removal. All data, including backups, is permanently lost when that instance is deleted, therefore make sure that your data has been successfully exported to the new MySQL instance before removal:

All of the instance data will be lost when the instance is deleted.
Do you want to continue (Y/n)? Y

10 The output should return the sql instances delete command request status:

Deleting Cloud SQL instance...done.
Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-app-mysql-instance].

11 Repeat step no. 1 – 10 to upgrade other MySQL instances available in the selected project to the latest major version of MySQL database.

12 Repeat steps no. 1 – 11 for each project created within your Google Cloud account.

References

Publication date Apr 21, 2021

Unlock the Remediation Steps


Gain free unlimited access
to our full Knowledge Base


Over 750 rules & best practices
for AWS and Azure

You are auditing:

Check for MySQL Major Version

Risk level: Low