This page describes how to upgrade the database major version by upgrading your Cloud SQL instance in-place rather than by migrating data.
Introduction
Database software providers periodically release new major versions that contain new features, performance improvements, and security enhancements. Cloud SQL takes in new versions after they'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 by migrating data. In-place upgrades are a simpler way to upgrade your instance's major version. You don't need to migrate data or change application connection strings. With in-place upgrades, you can retain the name, IP address, and other settings of your current instance after the upgrade. In-place upgrades don't require you to move data files and can be completed faster. In some cases, the downtime is shorter than what migrating your data entails.
The Cloud SQL for the MySQL in-place upgrade operation uses themysql_upgrade
utility.
Plan a major version upgrade
Choose a target major version.
gcloud
For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.
To check the database versions that you can target for an in-place upgrade on your instance, do the following:
- Run the following command.
- In the output of the command,
locate the section that is labeled
upgradableDatabaseVersions
. - 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.
gcloud sql instances describe INSTANCE_NAME
Replace INSTANCE_NAME with the name of the instance.
REST v1
To check which target database versions are available for a major version in-place upgrade, use the
instances.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/v1/projects/PROJECT_ID/instances/INSTANCE_NAME
To send your request, expand one of these options:
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 major version in-place upgrade of an instance, use the
instances.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:
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, see Database versions and version policies.
Consider the features offered in each database major version and address incompatibilities.
New major versions introduce incompatible changes that might require you to modify the application code, the schema, or the database settings. Before you can upgrade your database instance, review the release notes of your target major version to determine the incompatibilities that you must address.
After upgrading to a later version, the default value of some system variables might change. For example, the default value of
character_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. See Configure database flags for more information. Most of the default value changes are done by the MySQL Community (see more at Upgrade Server Defaults).Pre-check for upgrades from MySQL 5.7 to 8.0.
Perform a pre-check before running upgrades from MySQL 5.7 to 8.0. You can use the Upgrade Checker Utility in MySQL shell. If any issues are found during the pre-check, fix them before proceeding to the upgrade. Cloud SQL doesn't support pre-checking during a major version upgrade. An attempt to upgrade an instance that has failed pre-checking might also fail.
Check for disk space and instance machine types.
A major version upgrade requires additional resources, such as disk space, to store upgraded tables. If the disk space isn't enough, the upgrade fails and rolls back. For an upgrade from MySQL 5.7 to 8.0, additional memory is required to convert old metadata to the new data dictionary. Before running a major version upgrade, ensure that you have more than 100K of memory for each table. You can temporarily increase the memory by changing the machine type.
Check for user grant changes in MySQL 8.0
Cloud SQL for MySQL version 8.0 uses a flag called
partial_revokes
, which is set toON
by default. Unlike MySQL 5.7, this flag 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 full name of the required database schemas instead of using wildcard characters.For more information on how this flag works in MySQL 8.0, see partial_revokes in MySQL 8.0.
Test the upgrade with a dry run.
Perform a dry run of the end-to-end upgrade process in a test environment before you upgrade the production database. You can clone your instance to create an identical copy of the data on which to test the upgrade process.
In addition to validating that the upgrade completes successfully, run tests to ensure that the application behaves as expected on the upgraded database.
Decide on a time to upgrade.
Upgrading requires the instance to become unavailable for a period of time. 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:
-
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:
- Addition of new reserved keywords, such as
RANKS
,GROUPS
,FUNCTION
, in stored procedures, triggers, etc. See Keywords and Reserved Words for more information. - Invalid UTF Characters in table definitions.
- Uncommitted XA transitions that must be committed (using the
XA COMMIT
statement) or rolled back (using theXA ROLLBACK
statement). - Foreign key constraint in names longer than 64 characters.
- Spatial data type in mix index of columns. For more information, see Spatial Data Type for more information.
For more information, see Preparing your installation for upgrade and Upgrading to MySQL 8.0?.
- Addition of new reserved keywords, such as
-
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. Lack of required disk space causes the upgrade to fail and roll back to the original version. Cloud SQL recommends that you have a minimum of 100k 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, see changing the machine type. -
Upgrade your read replicas.
If you use read replicas, you must upgrade all the read replicas first before upgrading the primary instance. If the replica is upgraded but the primary instance is not, the replication might break if the primary uses statements or functions that are no longer supported in a later version of MySQL used by the replica. To avoid such issues, Cloud SQL recommends that you:
- Upgrade the primary immediately after upgrading the replicas.
- Avoid running queries that are incompatible with the new version on the primary instance until the upgrade has successfully completed.
- (Optional) Pause all
WRITE
statements to the primary instance until the upgrade has successfully completed. - (Optional) Remove all replicas before upgrading the primary and re-create the replicas once the upgrade as completed.
If the replication breaks, then the replica is rolled back to the primary instance's version. You can upgrade the replica again, but if the issue persists, then see FAQs.
Upgrade the database major version in-place
When you initiate an upgrade operation, Cloud SQL first checks the configuration of your instance to ensure that it's compatible for an upgrade. After verifying your configuration, Cloud SQL makes your instance unavailable, makes a pre-upgrade backup, performs the upgrade, makes your instance available, and makes a post-upgrade backup.
When you upgrade to MySQL 8.0, Cloud SQL automatically provisions your instance on the default minor version.Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Click Edit.
- In the Instance info section, click the Upgrade button and confirm that you want to go to the upgrade page.
- On the Choose a database version page, click the Database version for upgrade list and select one of the available database major versions.
- Click Continue.
- In the Instance ID box, enter the name of the instance and then click the Start upgrade button.
Verify that the upgraded database major version appears below the instance name on the instance Overview page.
gcloud
Start the upgrade.
Use the
gcloud sql instances patch
command with the--database-version
flag.Before running the command, replace the following:
- INSTANCE_NAME: The name of the instance.
- DATABASE_VERSION: The enum for the database major version, which must be later than the current version. Specify a database version for a major version that is available as an upgrade target for the instance. You can obtain this enum as the first step of Plan for upgrade. If you need a complete list of database version enums, then see SqlDatabaseEnums.
gcloud sql instances patch INSTANCE_NAME \ --database-version=DATABASE_VERSION
Major version upgrades take several minutes to complete. You might see a message indicating that the operation is taking longer than expected. You can either ignore this message or run the
gcloud sql operations wait
command to dismiss the message.Get the upgrade operation name.
Use the
gcloud sql operations list
command with the--instance
flag.Before running the command, replace the INSTANCE_NAME variable with the name of the instance.
gcloud sql operations list --instance=INSTANCE_NAME
Monitor the status of the upgrade.
Use the
gcloud sql operations describe
command.Before running the command, replace the OPERATION variable with the upgrade operation name retrieved in the previous step.
gcloud sql operations describe OPERATION
REST v1
Start the in-place upgrade.
Use a PATCH request with the
instances:patch
method.Before using any of the request data, replace these variables:
- PROJECT_ID: The ID of the project.
- INSTANCE_NAME: The name of the instance.
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME
Request JSON body:
{ "databaseVersion": DATABASE_VERSION }
Replace DATABASE_VERSION with the enum for the database major version, which must be later than the current version. Specify a database version for a major version that is available as an upgrade target for the instance. You can obtain this enum as the first step of Plan for upgrade. If you need a full list of database version enums, then see SqlDatabaseVersion.
Get the upgrade operation name.
Use a GET request with the
operations.list
method after replacing PROJECT_ID with the ID of the project.HTTP method and URL:
GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations
Monitor the status of the upgrade.
Use a GET request with the
operations.get
method after replacing the following variables:- PROJECT_ID: The ID of the project.
- OPERATION_NAME: The upgrade operation name retrieved in the previous step.
HTTP method and URL:
GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operation/OPERATION_NAME
Terraform
To update the version of the database, use a Terraform resource and the Terraform provider for Google Cloud, version 4.34.0 or later.
Apply the changes
To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Google Cloud project where you want to apply your Terraform configurations.
You only need to run this command once per project, and you can run it in any directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Environment variables are overridden if you set explicit values in the Terraform configuration file.
Prepare the directory
Each Terraform configuration file must have its own directory (also called a root module).
-
In Cloud Shell, create a directory and a new
file within that directory. The filename must have the
.tf
extension—for examplemain.tf
. In this tutorial, the file is referred to asmain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
If you are following a tutorial, you can copy the sample code in each section or step.
Copy the sample code into the newly created
main.tf
.Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
- Review and modify the sample parameters to apply to your environment.
- Save your changes.
-
Initialize Terraform. You only need to do this once per directory.
terraform init
Optionally, to use the latest Google provider version, include the
-upgrade
option:terraform init -upgrade
Apply the changes
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Delete the changes
To delete your changes, do the following:
- To disable deletion protection, in your Terraform configuration file set the
deletion_protection
argument tofalse
.deletion_protection = "false"
- Apply the updated Terraform configuration by running the following command and
entering
yes
at the prompt:terraform apply
-
Remove resources previously applied with your Terraform configuration by running the following command and entering
yes
at the prompt:terraform destroy
When you place an in-place upgrade request, Cloud SQL first performs a pre-upgrade check. If Cloud SQL determines that your instance isn't ready for an upgrade, your upgrade request fails with a message suggesting how you can address the issue. See also Troubleshoot a major version upgrade.
Automatic upgrade backups
When you perform a major version upgrade, Cloud SQL automatically makes two on-demand backups, called upgrade backups:
- The first upgrade backup is the pre-upgrade backup, which is made immediately before starting the upgrade. You can use this backup to restore your database instance to its state on the previous version.
- The second upgrade backup is the post-upgrade backup, which is made immediately after new writes are allowed to the upgraded database instance.
When you view your list of
backups, the
upgrade backups are listed with type On-demand
. Upgrade backups are labeled so
that you can identify them easily.
For example, if you're upgrading from MySQL 5.7 to MySQL 8.0, your pre-upgrade
backup is labeled Pre-upgrade backup, MYSQL_5_7 to MYSQL_8_0.
and your
post-upgrade backup Post-upgrade backup, MYSQL_8_0 from MYSQL_5_7.
As with other on-demand backups, upgrade backups persist until you delete them or delete the instance. If you have PITR enabled, you can't delete your upgrade backups while they're in your retention window. If you need to delete your upgrade backups, you must disable PITR or wait until your upgrade backups are no longer in your retention window.
Complete the major version upgrade
After you've finished upgrading your primary instance, perform the following steps to complete your upgrade:-
Perform acceptance tests.
Run tests to confirm that your upgraded system is performing as expected.
-
(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, see What 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 have
CREATE 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.You can reset user privileges using the following steps:
Create a user with the
cloudsqlsuperuser
role granted.Use the created user to revoke all previous privileges from an upgraded user with:
REVOKE ALL PRIVILEGES ON *.* FROM user@host
- Grant the expected privileges to the upgraded user.
-
(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.
Troubleshoot a major version upgrade
Cloud SQL returns an error message if you attempt an invalid upgrade command, for example, if your instance contains invalid database flags for the new version.
If your upgrade request fails, check the syntax of your upgrade request. If the request has a valid structure, try looking into the following suggestions.
View upgrade logs
If any issues occur with a valid upgrade request, Cloud SQL
publishes error logs to projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fmysql.err
. Each log entry contains a label with the
instance identifier to help you identify the instance with the upgrade error.
Look for such upgrade errors and resolve them.
To view error logs, follow these steps:
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
In the Operations and logs pane of the instance Overview page, click the View MySQL error logs link.
The Logs Explorer page opens.
View logs as follows:
- To list all error logs in a project, select the log name in the Log name log filter.
For more information on query filters, see Advanced queries.
- To filter the upgrade error logs for a single instance, enter the
following query in the Search all fields box, after replacing
DATABASE_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 named
shopping-db
running in the projectbuylots
, use the following query filter:resource.type="cloudsql_database" resource.labels.database_id="buylots:shopping-db" logName : "projects/buylots/logs/cloudsql.googleapis.com%2Fmysql.err"
Restore to the previous major version
If your upgraded database system doesn't perform as expected, you might need to restore your instance to the previous version. You do so by restoring your pre-upgrade backup to a Cloud SQL recovery instance, which is a new instance running the pre-upgrade version.
To restore to the previous version, perform the following steps:
Identify your pre-upgrade backup.
Create a recovery instance.
Create a new Cloud SQL instance using the major version that Cloud SQL was running when the pre-upgrade backup was made. Set the same flags and instance settings that the original instance uses.
Restore your pre-upgrade backup.
Restore your pre-upgrade backup to the recovery instance. This might take several minutes to complete.
Add your read replicas.
If you were using read replicas, add them individually.
Connect your application.
Having recovered your database system, update your application with details about the recovery instance and its read replicas. You can resume serving traffic 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 an external replica.
- Upgrading instances from MySQL 5.7 to 8.0 that have more than 512,000 tables might take a long time and timeout.
FAQs
The following questions might come up when upgrading the database major version.
- 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 uses 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 upgrade time corresponds to the number of objects in your databases. If you have multiple instances that need to be upgraded, then your total upgrade time increases proportionately.
- Can I monitor each step in my upgrade process?
- While Cloud SQL allows you to monitor whether an upgrade operation is still in progress, you are unable to 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 the
character_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, see Configure database flags. If a certain flag or value is no longer supported in your target version, then Cloud SQL automatically removes the flag during the upgrade.
- What can I do if the replication breaks after upgrading the replica?
- If the replication breaks after upgrading the replica, it's rolled back to the primary instance's MySQL version.
You can upgrade the replica again but if the issue persists, the replication might break again.
If the replica is not rolled back, you have two options:
-
Delete the broken replica, create a new replica, and upgrade the new replica.
If the upgrade fails again, it is likely caused by incompatible changes added to the primary when it was upgraded. Trouble the upgrade to locate the issue, fix the primary instance, and try to upgrade the replica. See troubleshoot for more information.
-
Upgrade the primary instance
The upgrade operation on the primary instance recreates the replicas if the replica thread is not working.
-
- Why did my upgraded replica roll back to the old version?
If an upgrade is unsuccessful, 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 the
mysql.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 contains
Access 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. See prepare for a major version upgrade on how to fix incompatibility issues before upgrading again.
What's next
- Learn about options for connecting to an instance.
- Learn about importing and exporting data.
- Learn more about setting database flags.