This page describes how to upgrade the database major version by upgrading yourCloud SQL instance in-place rather thanby migrating data.

Introduction

Database software providers periodically release new major versions that containnew features, performance improvements, and security enhancements.Cloud SQL takes innew versions afterthey're released. After Cloud SQL offers support for a new major version,you can upgrade your instances to keep your database updated.

You can upgrade the database version of an instance in-place or bymigrating data.In-place upgradesare a simpler way to upgrade your instance's major version. You don't need tomigrate data or change application connection strings. With in-place upgrades,you can retain the name, IP address, and other settings of your current instanceafter the upgrade. In-place upgrades don't require you to move data files andcan be completed faster. In some cases, the downtime is shorter than whatmigrating your data entails.

Plan a major version upgrade

1. Confirm that you have the required role to perform a majorversion upgrade:Cloud SQL Owner orCloud SQL Admin.

2. Choose a target major version.

   gcloud

   For information about installing and getting started with the gcloud CLI, seeInstall the gcloud CLI. For information about starting Cloud Shell, seeUse Cloud Shell.

   To check the database versions that you can target for an in-place upgrade on your instance, do the following:

   1. Run the following command.

   gcloud sql instances describeINSTANCE_NAME

   ReplaceINSTANCE_NAME with the name of the instance.

   2. In the output of the command, locate the section that is labeledupgradableDatabaseVersions.
   3. Each subsection returns a database version that is available for upgrade. In each subsection, review the following fields.
   • majorVersion: the major version that you can target for the in-place upgrade.
   • name: the database version string that includes the major version. For Cloud SQL for MySQL, this field also includes the minor version of the database.
   • displayName: the display name for the database version.

   REST v1

   To check which target database versions are available for a majorversion in-place upgrade, use theinstances.get methodof the Cloud SQL Admin API.

   Before using any of the request data, make the following replacements:

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

   GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "MYSQL_8_0"  name: "MYSQL_8_0_36"  display_name: "MySQL 8.0.36"}

   REST v1beta4

   To check which target database versions are available for majorversion in-place upgrade of an instance, use theinstances.get method of the Cloud SQL Admin API.

   Before using any of the request data, make the following replacements:

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

   GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "MYSQL_8_0"  name: "MYSQL_8_0_36"  display_name: "MySQL 8.0.36"}

   For the complete list of the database versions that Cloud SQL supports, seeDatabase versions and version policies.

3. Consider the features offered in each database major version and addressincompatibilities.

   • MySQL 8.4
   • MySQL 8.0
   • MySQL 5.7

   New major versions introduce incompatible changes that might require you tomodify the application code, the schema, or the database settings. Beforeyou can upgrade your database instance, review the release notes of yourtarget major version to determine the incompatibilities that you mustaddress.

   After upgrading to a later version, the default value of some system variables might change. For example, the default value ofcharacter_set_server in MySQL 5.6 and MySQL 5.7 isutf8. When you upgrade to MySQL 8.0, the default value ofcharacter_set_server changes toutf8mb4. To revert toutf8, you must manually change the database flag value to its old value. SeeConfigure database flags for more information. Most of the default value changes are done by the MySQL Community (see more atUpgrade Server Defaults).

   Note: You can upgrade only from one major version to the next. MySQL doesn'tsupport skipping major versions. You can't upgrade directly from MySQL 5.6to MySQL 8.0 or from MySQL 5.7 to MySQL 8.4.

4. Perform the precheck for upgrades.

   • If you are upgrading from MySQL 5.7 to 8.0, then perform aprecheck for upgrades from MySQL 5.7 to 8.0. You can usetheUpgrade Checker Utility in the MySQL shell.

   • If you are upgrading from MySQL 8.0 to 8.4, then perform aprecheck for upgrades from MySQL 8.0 to 8.4. You can usetheUpgrade Checker Utility in the MySQL shell.

     If any issues are found during the precheck, then fixthem before proceeding to the upgrade. Cloud SQL doesn'tsupport prechecking during a major version upgrade. An attemptto upgrade an instance that has failed prechecking might also fail.

     Important: If you are running the Upgrade Checker Utility forMySQL 8.4 from the MySQL shell, then you can safely ignore theprecheck warnings forsysvarAllowedValuesandremovedSysVars.

5. Check for disk space and instance machine types.

   A major version upgrade requires additional resources, such as disk space, tostore upgraded tables. If the disk space isn't enough, the upgrade fails androlls back. For an upgrade from MySQL 5.7 to 8.0, additional memory is required to convertold metadata to the new data dictionary. Before running a major versionupgrade, ensure that you have more than 100 KB of memory for each table. Youcan temporarily increase the memory bychanging the machine type.

6. For upgrades from MySQL 5.7 to MySQL 8.0, check for user grantchanges in MySQL 8.0

   Cloud SQL for MySQL version 8.0 uses a flag calledpartial_revokes, which is set toON by default. Unlike MySQL 5.7, thisflag removes the ability to use wildcard characters in databaseGRANT commands.To ensure database users have access to the correct database schemas,modify database user privileges before upgrading to MySQL 8.0.Update the user's privileges to use the fullname of the required database schemas instead of using wildcard characters.

   For more information on how this flag works in MySQL 8.0,seepartial_revokes in MySQL 8.0.

7. Test the upgrade with a dry run.

   Perform a dry run of the end-to-end upgrade process in a test environmentbefore you upgrade the production database. You canclone your instanceto create an identical copy of the data on which to test the upgrade process.

   In addition to validating that the upgrade completes successfully, runtests to ensure that the application behaves as expected on the upgradeddatabase.

   Note: Ensure that you have enough disk space before running a major version upgrade.If you haven't enabledautomatic storage increases,take note of the disk storage used by the upgraded dry run instance. This helps youdetermine whether you need to increase the storage capacity for theproduction instance before upgrading.

8. Decide on a time to upgrade.

   Upgrading requires the instance to become unavailable for a period oftime. Plan to upgrade during a time period when database activity is low.

Prepare for a major version upgrade

Before you upgrade, complete the following steps:

1. For upgrades from MySQL 5.7 to 8.0 ONLY: Check and fix incompatible issues found during the precheck process. Common issues found can include:

   1. Addition of new reserved keywords, such asRANKS,GROUPS,FUNCTION, in stored procedures, triggers, and other database objects. SeeKeywords and Reserved Words for more information.
   2. Invalid UTF Characters in table definitions.
   3. Uncommitted XA transitions that must be committed (using theXA COMMIT statement) or rolled back (using theXA ROLLBACK statement).
   4. Foreign key constraint in names longer than 64 characters.
   5. Spatial data type in mix index of columns. For more information, seeSpatial Data Type.

   For help troubleshooting error and known issues during the precheck, seeTroubleshooting in-place major version upgrade to MySQL 8.0. For more information about common MySQL issues, seePreparing your installation for upgrade andUpgrading to MySQL 8.0?.

   For upgrades from MySQL 8.0 to MySQL 8.4 ONLY: Check and fix incompatible issues found during the precheck process. A common issue might include:

   • Outdated replication terminology. The termsMASTER andSLAVE are completely removed from MySQL 8.4. If you still use any commands or configurations with these terms, then you must replace or remove the them. For more information about the removal and replacement of these terms, seeWhat Is New in MySQL 8.4 since MySQL 8.0.
   • Update the authentication plugin of your existing user accounts to use thecaching_sha2_password authentication plugin instead of the deprecatedmysql_native_password plugin.
     
     To change your existing database user accounts to use thecaching_sha2_password authentication plugin, use the following command:

     ALTERUSER'username'@'%'IDENTIFIEDWITHcaching_sha2_passwordBY'user_password';

     Replaceusername anduser_password with the values for the user account that you are updating.

2. Check disk space and instance machine type

   Major version upgrades require additional disk space and memory to store the upgraded tables and new data dictionary. Insufficient disk space causes the upgrade to fail and roll back to the original version. Cloud SQL recommends that you have a minimum of 100 KB in memory for each table.

   Note: You can temporarily increase memory by changing the machine type before running the major version upgrade. To learn more, seechanging the machine type.

Perform the major version upgrade

You can upgrade the major version of a single Cloud SQL instance, oryou can upgrade the major version of a primary instance and include all ofits replicas in the upgrade, including cascading replicas and cross-regionreplicas.

Upgrade the major version of a single instance

When you initiate an upgrade operation for a single instance, Cloud SQL doesthe following:

1. Checks the configuration of your instance to ensure thatthe instance is compatible for an upgrade.
2. After Cloud SQL verifies the configuration,then Cloud SQL makes the instance unavailable.
3. Makes a pre-upgrade backup.
4. Performs the upgrade on the instance.
5. Makes your instance available.
6. Makes a post-upgrade backup.

resource "google_sql_database_instance" "instance" {  name             = "mysql-instance"  region           = "us-central1"  database_version = "MYSQL_8_0"  settings {    tier = "db-n1-standard-2"  }  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.  deletion_protection = false}

When you place an in-place upgrade request, Cloud SQL first performs apre-upgrade check. If Cloud SQL determines that your instance isn'tready for an upgrade, then your upgrade request fails with a message suggesting howyou can address the issue. See alsoTroubleshoot a major versionupgrade.

To include replicas in a major version upgrade, you can't use theGoogle Cloud console or Terraform. You can only usegcloud CLI or the Cloud SQL Admin API.

Automatic upgrade backups

When you perform a major version upgrade, Cloud SQL automatically makestwo on-demand backups, called upgrade backups:

• The first upgrade backup is thepre-upgrade backup, which is madeimmediately before starting the upgrade. You can use this backup to restoreyour database instance to its state on the previous version.
• The second upgrade backup is thepost-upgrade backup, which is madeimmediately after new writes are allowed to the upgraded database instance.

When youview your list ofbackups, theupgrade backups are listed with typeOn-demand. Upgrade backups are labeled sothat you can identify them quickly.For example, if you're upgrading from MySQL 5.7 to MySQL 8.0, then your pre-upgradebackup is labeledPre-upgrade backup, MYSQL_5_7 to MYSQL_8_0. and yourpost-upgrade backupPost-upgrade backup, MYSQL_8_0 from MYSQL_5_7.If you're upgrading from MySQL 8.0 to MySQL 8.4, then your pre-upgradebackup is labeledPre-upgrade backup, MYSQL_8_0 to MYSQL_8_4. and yourpost-upgrade backupPost-upgrade backup, MYSQL_8_4 from MYSQL_8_0.

As with other on-demand backups, upgrade backups persist until you delete themor delete the instance. If you have PITR enabled, you can't delete your upgradebackups while they're in your retention window. If you need to delete yourupgrade backups, you must disable PITR or wait until your upgrade backupsare no longer in your retention window.

Complete the major version upgrade

1. Perform acceptance tests.

   Run tests to confirm that your upgraded system is performing as expected.

2. Optional: Update user privileges.

   If you have upgraded to MySQL 8.0, then note that MySQL has changed the security and account management systems. For more information, seeWhat is New in MySQL 8.0 for more information.

   This might cause users that were created in the MySQL 5.7 version of the instance to not have the same privileges as users created directly in MySQL 8.0. For example, a user upgraded from MySQL 5.7 might not haveCREATE ROLE andDROP ROLE privileges because these privileges did not exist in MySQL 5.7. Cloud SQL recommends that you reset user privileges after upgrading versions to fix any issues related to user privileges.

   If you have upgraded from MySQL 8.0 to MySQL 8.4, then there are additional changes to user privileges, including the addition of privileges introduced in MySQL 8.4 and the removal of privileges that existed in MySQL 8.0. For more information, seeMySQL 8.4 user privileges (cloudsqlsuperuser).

   You can update user privileges in either MySQL 8.0 or MySQL 8.4 by doing the following:

   1. Create a user with thecloudsqlsuperuser role granted.

   2. Use the created user to revoke all previous privileges from an upgraded user with:

      REVOKEALLPRIVILEGESON*.*FROMuser@host

   3. Grant the expected privileges to the upgraded user.

3. Optional: Create a backup.

   Although Cloud SQL automatically creates a backup after you upgrade your primary instance, Cloud SQL recommends that you create a backup on your own so that you can recover your database, if necessary.

4. Optional: If you have upgraded to Cloud SQL for MySQL 8.4, then also update all your connectors, clients, and MySQL shells to MySQL 8.4.

Troubleshoot a major version upgrade

Cloud SQL returns an error message if you attempt an invalid upgradecommand, for example, if your instance contains invalid database flags for thenew version.

If your upgrade request fails, check the syntax of your upgrade request. If therequest has a valid structure, try looking into the following suggestions.

View error logs

If any issues occur with a valid upgrade request, then Cloud SQLpublishes error logs toprojects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fmysql.err. Each log entry contains a label with theinstance identifier to help you identify the instance with the upgrade error.Look for such upgrade errors and resolve them.

To view error logs, use the Google Cloudconsole::

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.

3. In theOperations and logs pane of the instanceOverview page, clicktheView MySQL error logs link.

   TheLogs Explorer page opens.

4. View logs as follows:

   • To list all error logs in a project, select the log name in theLogname log filter.

   For more information on query filters, seeAdvanced queries.

   • To filter the upgrade error logs for a single instance, enter thefollowing query in theSearch all fields box, after replacingDATABASE_ID

   with the project ID followed by the instance name in this format:project_id:instance_name.

   resource.type="cloudsql_database"resource.labels.database_id="DATABASE_ID"logName:"projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fmysql.err"

   For example, to filter the upgrade error logs by an instance namedshopping-db running in the projectbuylots, use the following queryfilter:

   resource.type="cloudsql_database"resource.labels.database_id="buylots:shopping-db"logName:"projects/buylots/logs/cloudsql.googleapis.com%2Fmysql.err"

   You can either review all logs reported within a given timeframe, or you can filter logs by severity. A common option for troubleshooting might include selecting the following filters:

   • Emergency
   • Alert
   • Critical
   • Error

   You might experience some known issues during precheck and upgrade from MySQL version 5.7 to 8.0. For help troubleshooting these issues, seeTroubleshooting in-place major version upgrade to MySQL 8.0.

Restore the primary instance to the previous major version

If your upgraded database system doesn't perform as expected, then youmight need to restore your primary instance to the previous version.You do so by restoring your pre-upgrade backup to a Cloud SQLrecovery instance, which is a new instance running the pre-upgrade version.

To restore a primary instance to the previous version, perform the following steps:

1. Identify your pre-upgrade backup.

   SeeAutomatic upgrade backups.

2. Create a recovery instance.

   Create a new Cloud SQLinstance using the major versionthat Cloud SQL was running when the pre-upgrade backup was made. Setthe sameflags andinstancesettings that the originalinstance uses.

3. Restore your pre-upgrade backup.

   Restoreyour pre-upgrade backup to the recovery instance. This might take severalminutes to complete.

4. Add your read replicas.

   If you're using read replicas, then add the read replicas individually.

5. Connect your application.

   Having recovered your database system, update your application with detailsabout the recovery instance and its read replicas. You can resume servingtraffic on the pre-upgrade version of your database.

Limitations

This section lists limitations for an in-place major version upgrade.

• You can't perform an in-place major version upgrade on anexternal replica.
• Upgrading instances from MySQL 5.7 to 8.0 that have more than 512,000 tables might take a long time and timeout.
• Upgrading instances from MySQL 8.0 to 8.4 that have more than 512,000 tables might take a long time and timeout.

• When upgrading from MySQL 8.0 to 8.4, if you have upgraded a replicato MySQL 8.4 but not the primary instance, then it's possible to create a new user account on a non-upgraded primary instance with the deprecatedmysql_native_password authentication plugin. To avoid this situation, make sure that you upgrade the primary instance immediately after upgrading the replicas, or only create new user accounts on the primary instance by using the following command.

  CREATEUSER'USERNAME'@'%'IDENTIFIEDWITHcaching_sha2_passwordBY'PASSWORD';

  ReplaceUSERNAME andPASSWORD withappropriate values.

FAQs

The following questions might come up when upgrading the database major version.

Is my instance unavailable during an upgrade?

Yes. Your instance remains unavailable for a period of time while Cloud SQL performs the upgrade.

How long does an upgrade take?

Upgrading a single instance typically takes less than 10 minutes. If your instance configuration has a small number of vCPUs or memory, then your upgrade might take more time.

If your instance hosts too many databases or tables, or your databases are very large, then the upgrade might take hours or even time out because the total upgrade time corresponds to the number of objects in your databases. If you have multiple instances that need to be upgraded, then your upgrade time increases proportionately. If you include replicas in your upgrade, then the upgrade operation can take up to an hour to complete, depending on the number of replicas that your primary instance has.

Can I monitor each step in my upgrade process?

While Cloud SQL lets you monitor whether an upgrade operation is still in progress, you can't track the individual steps in each upgrade.

Can I cancel my upgrade after I've started it?

No, you can't cancel an upgrade once it has started. If your upgrade fails, Cloud SQL automatically recovers your instance on the previous version.

What happens to my settings during an upgrade?

When you perform an in-place major version upgrade, Cloud SQL retains your database settings, including your instance name, IP address, explicitly configured flag values, and user data. However, the default value of the system variables might change. For example, the default value of thecharacter_set_server flag in MySQL 5.7 isutf8. When you upgrade to MySQL 8.0, the default value of the flag changes toutf8mb4. To revert it toutf8, set the flag value back to previous value.

To learn more, seeConfigure database flags. If a certain flag or value is no longer supported in your target version, thenCloud SQL automatically removes the flag during the upgrade.

What can I do if replication breaks after I upgrade a replica?

If replication breaks after you upgrade a replica, then Cloud SQL rolls back the replica to the MySQL major version of the primary instance. You can upgrade the replica again, but if the issue persists, then replication might break again. For this reason, we recommend that youinclude your replicas when you perform a major version upgrade instead of upgrading each replica separately.

If the replica isn't rolled back to the same major version as your primary instance, then you have two options:

1. Delete the broken replica, create a new replica, and upgrade the new replica.

   If the upgrade fails again, it's likely caused by incompatible changes added to the primary instance when it was upgraded. Troubleshoot the upgrade to locate the issue, fix the primary instance, and try to upgrade the replica. For more information, seetroubleshoot.

2. Upgrade the primary instance

   If the replica thread is not working, then the upgrade operation on the primary instance recreates the replicas.

Why did my upgraded replica roll back to the previous major version?

If an upgrade is unsuccessful, then the replica is rolled back to the primary instance's version. You can upgrade the replica again but if the issue persists, you can check themysql.err log on the replica to find the source. Search for keywords such as[REPL]... failed executing transaction.... end_log_pos...; Failure Reason.

If the error message containsAccess denied for AuthId.... with user privilege changes, there is likely a query being executed using MySQL 5.7 user privileges on mysql and sys schemas, and might fail due to the changes to the security and account management systems in MySQL 8.0. To resolve this issue, you must stop the queries on the primary instance before upgrading the primary instance to the new version and then retry the replica upgrade. Cloud SQL recommends that you temporarily stop all such queries in the primary instance as well before upgrading to the new version as it might lead to a similar issue.

If you don't see the the failure reason, such as Access denied for AuthID.... in the MySQL logs, then your issue is likely caused by new incompatible data that might have been added to the primary instance after the replica had been upgraded. Seeprepare for a major version upgrade on how to fix incompatibility issues before upgrading again.

What's next

• Learn aboutoptions for connecting to an instance.
• Learn aboutimporting and exporting data.
• Learn more about [setting database flags][flags].