[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Step-by-step guides](index.md) > Clusters > MySQL® version update

# MySQL® version upgrade

You can upgrade a Managed Service for MySQL® cluster to any supported minor or major version.

In single-host clusters, only the master host is taken offline for upgrades. Unlike multi-host clusters, these clusters are not available for reads and writes during an upgrade. After the DBMS resumes operation, it will take time to warm up the buffer pool, which may temporarily affect the query performance, particularly for large, heavily indexed databases.

In multi-host clusters, upgrades proceed in this sequence:

1. Replicas are taken offline for an upgrade and stopped, one by one. The replicas are queued randomly. Following the upgrade, the replicas get back online. Read performance may temporarily degrade at this stage, as some replicas will be unavailable.

1. The master host turns off for the upgrade. During the master upgrade, one of the replicas takes over its role and becomes available for writing. Once the upgrade is complete, the host with the highest [failover priority](../concepts/replication.md#master-failover) becomes the new master. If the cluster has several hosts with maximum priority, the one with the least lag behind the master will be selected.

    For the master host to preserve its master role after an upgrade, set the highest failover priority for it. You can set failover priority when [creating a cluster](cluster-create.md) or [updating host settings](hosts.md#update).

{% note info %}

In MySQL® 8.0, replica failover is more reliable and efficient due to improved metadata processing.

{% endnote %}

To learn about upgrades within the same version and host maintenance, see [Maintenance](../concepts/maintenance.md).

{% note alert %}

* After the DBMS upgrade, you cannot revert the cluster to the previous version.
* Upgrade your cluster during low load periods to reduce risks and minimize the impact on users.
* Whether a MySQL® version upgrade succeeds depends on many factors, such as the following:

   * Cluster settings and specific configurations.
   * Nature and structure of stored data.
   * MySQL® features deployed (especially JSON functionality and full-text search).
   * App compatibility with the new version.
   * Correctness of stored procedures and triggers.
   * Current database state and data quality.

   We recommend that you start with [upgrading a test cluster](#before-update) with the same data and configuration.

{% endnote %}

## Before upgrading a version {#before-update}

When getting ready for an upgrade, a comprehensive approach to testing and compatibility analysis is of particular importance. Our experience shows that most upgrade issues can be prevented in advance:

1. Check the MySQL® [release notes](https://docs.percona.com/percona-server/8.0/release-notes/release-notes_index.html) to learn how upgrades may affect your applications.

   {% cut "Examples of changes in MySQL® 8.0" %}

   * Changes in the behavior of SQL functions and query optimizer:

      ```sql
      -- Example of a command for analyzing a query execution plan
      EXPLAIN ANALYZE
      SELECT * FROM <table_name>
      WHERE complex_condition
      ORDER BY <field>;
      ```

   * Deprecated and removed features, especially related to ​​security:

      ```sql
      -- Checking authentication
      SELECT user, host, plugin 
      FROM mysql.user 
      WHERE user NOT LIKE 'mysql.%';
      ```

   * Changes in full-text search and JSON processing:

      ```sql
      -- Checking the use of JSON functions
      SELECT COUNT(*) 
      FROM information_schema.routines 
      WHERE routine_definition LIKE '%JSON%';
      ```

   * New default values ​​for DBMS settings.
   * Aspects of working with metadata and temporary tables.

   {% endcut %}

1. Try upgrading a test cluster.
   
   1. Deploy a test cluster from a backup of the main cluster using the `PRESTABLE` environment and upgrade it to the required version.
   1. Make sure critical queries and stored procedures work as expected.
   1. Check the features that use JSON and full-text search.
   1. Run a load test:

      ```sql
      -- Monitoring performance during tests
      SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT
      FROM performance_schema.events_statements_summary_by_digest
      WHERE SCHEMA_NAME = '<DB_name>'
      ORDER BY AVG_TIMER_WAIT DESC
      LIMIT 10;
      ```

1. [Create a backup](cluster-backups.md) of the main cluster immediately before upgrading.

1. Ensure [high availability](../concepts/high-availability.md) of the cluster:
   
   1. Make sure the main and test clusters have at least one master and one replica. [Add hosts](hosts.md#add) as needed.
   1. Optionally, check the replication status and lag:

     ```sql
     -- Checking the replication status
     SHOW SLAVE STATUS\G

     -- Checking the replication lag
     SELECT
       SUBSTRING_INDEX(HOST, ':', 1) AS slave_host,
       SUBSTRING_INDEX(HOST, ':', -1) AS slave_port,
       SECONDS_BEHIND_MASTER,
       SLAVE_IO_RUNNING,
       SLAVE_SQL_RUNNING
     FROM information_schema.PROCESSLIST
     WHERE COMMAND = 'Binlog Dump';
     ```

## Upgrading the MySQL® version {#start-update}

{% list tabs group=instructions %}

- Management console {#console}

   1. Navigate to **Managed Service for&nbsp;MySQL**.
   1. Select your cluster from the list and click ![image](../../_assets/pencil.svg) **Edit**.
   1. In the **Version** field, select the new version number.
   1. Click **Save changes**.

   As soon as you run the upgrade, the cluster status will change to **Updating**. Wait for the operation to complete and then check the cluster version.

   The time required for an upgrade depends on multiple factors, such as the amount of data or the number of databases in your cluster. Usually, an upgrade takes a few minutes; for large databases, it can take 10 minutes or more.

- CLI {#cli}

   If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

   The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

   To upgrade a MySQL® version:

   1. Get the list of your MySQL® clusters using this command:

      ```bash
      yc managed-mysql cluster list
      ```

   1. Get the details of the cluster in question and check its MySQL® version in the `config.version` setting:

      ```bash
      yc managed-mysql cluster get <cluster_name_or_ID>
      ```

   1. Start the MySQL® upgrade:

      ```bash
      yc managed-mysql cluster update <cluster_name_or_ID> \
         --mysql-version <new_version_number>
      ```

   The time required for an upgrade depends on multiple factors, such as the amount of data or the number of databases in your cluster. Usually, an upgrade takes a few minutes; for large databases, it can take 10 minutes or more.

- Terraform {#tf}

   1. Open the current Terraform configuration file with the infrastructure plan.

      For more on how to create this file, see [Creating a cluster](cluster-create.md).

   1. Add the `version` field to the `yandex_mdb_mysql_cluster` resource or change the field value if it already exists:

      ```hcl
      resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
         ...
         version = "<MySQL®_version>"
         ...
      }
      ```

   1. Make sure the settings are correct.

      1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

   1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         terraform plan
         ```
      
         If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

   For more information, see [this Terraform provider article](../../terraform/resources/mdb_mysql_cluster.md).

   {% note warning "Timeouts" %}
   
   The Terraform provider sets the following timeouts for Managed Service for MySQL® cluster operations:
   
   * Creating a cluster, including by restoring it from a backup: 15 minutes.
   * Updating a cluster, including the MySQL® version update: 60 minutes.
   * Deleting a cluster: 15 minutes.
   
   Operations exceeding the timeout are aborted.
   
   {% cut "How do I change these limits?" %}
   
   Add the `timeouts` section to the cluster description, such as the following:
   
   ```hcl
   resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
     ...
     timeouts {
       create = "1h30m" # 1 hour 30 minutes
       update = "2h"    # 2 hours
       delete = "30m"   # 30 minutes
     }
   }
   ```
   
   {% endcut %}
   
   {% endnote %}

- REST API {#api}

   1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

   1. Call the [Cluster.update](../api-ref/Cluster/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

      {% note warning %}
      
      The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `updateMask` parameter as a single comma-separated string.
      
      {% endnote %}

      ```bash
      curl \
         --request PATCH \
         --header "Authorization: Bearer $IAM_TOKEN" \
         --header "Content-Type: application/json" \
         --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>' \
         --data '{
                   "updateMask": "configSpec.version",
                   "configSpec": {
                     "version": "<MySQL®_version>"
                   }
                 }'
      ```

      Where:

      * `updateMask`: Comma-separated string of settings to update.

         Here, we provide only one setting.

      * `configSpec.version`: New MySQL® version.

      You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

   1. Check the [server response](../api-ref/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

   1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

   1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
      
      ```bash
      cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
      ```
      
      Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
   1. Call the [ClusterService/Update](../api-ref/grpc/Cluster/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

      {% note warning %}
      
      The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `update_mask` parameter as an array of `paths[]` strings.
      
      {% cut "Format for listing settings" %}
      
      ```yaml
      "update_mask": {
          "paths": [
              "<setting_1>",
              "<setting_2>",
              ...
              "<setting_N>"
          ]
      }
      ```
      
      {% endcut %}
      
      {% endnote %}

      ```bash
      grpcurl \
         -format json \
         -import-path ~/cloudapi/ \
         -import-path ~/cloudapi/third_party/googleapis/ \
         -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/cluster_service.proto \
         -rpc-header "Authorization: Bearer $IAM_TOKEN" \
         -d '{
               "cluster_id": "<cluster_ID>",
               "update_mask": {
                 "paths": [
                   "config_spec.version"
                 ]
               },
               "config_spec": {
                 "version": "<MySQL®_version>"
               }
             }' \
         mdb.api.cloud.yandex.net:443 \
         yandex.cloud.mdb.mysql.v1.ClusterService.Update
      ```

      Where:

      * `update_mask`: List of settings you want to update as an array of strings (`paths[]`).

         Here, we provide only one setting.

      * `config_spec.version`: New MySQL® version.

      You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

   1. Check the [server response](../api-ref/grpc/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

The time required for an upgrade depends on multiple factors, such as the amount of data or the number of databases in your cluster. Usually, an upgrade takes a few minutes; for large databases, it can take 10 minutes or more.

## Examples {#examples}

Let's consider the following example showing how to upgrade a cluster from version 5.7 to 8.0. This use case is particularly interesting as it covers the most significant changes in the MySQL® architecture.

{% list tabs group=instructions %}

- CLI {#cli}

   1. Get the list of clusters with their IDs and names:

      ```bash
      yc managed-mysql cluster list
      ```

      Result example:

      ```text
      +----------------------+------------+---------------------+--------+---------+
      |          ID          |    NAME    |     CREATED AT      | HEALTH | STATUS  |
      +----------------------+------------+---------------------+--------+---------+
      | c9q8p8j2gaih******** |  mysql406  | 2021-10-23 12:44:17 | ALIVE  | RUNNING |
      +----------------------+------------+---------------------+--------+---------+
      ```

   1. Get detailed information about a specific cluster:

      ```bash
      yc managed-mysql cluster get mysql406
      ```

      The output will show the current MySQL® version:

      ```text
        id: c9q8p8j2gaih********
        ...
        config:
          version: "5.7"
          ...
      ```

   1. Start the upgrade to 8.0:

      ```bash
      yc managed-mysql cluster update mysql406 --mysql-version 8.0
      ```

      As soon as you run this command, the system will start upgrading your cluster. It may take anywhere from a few minutes to an hour depending on the database size and cluster configuration.

- Terraform {#tf}

   1. Open the current Terraform configuration file with the infrastructure plan.
   1. Set the `version` field to `8.0` in the `yandex_mdb_mysql_cluster` resource:

      ```hcl
      resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
         ...
         version = "8.0"
         ...
      }
      ```

   1. Make sure the settings are correct:

      1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

   1. Apply the changes:

      1. Run this command to view the planned changes:
      
         ```bash
         terraform plan
         ```
      
         If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

   Terraform will automatically detect the need for version upgrade and start the process.

{% endlist %}

After a successful upgrade:

1. Check the status of all critical components:

   ```sql
   -- Checking the InnoDB status
   SHOW ENGINE INNODB STATUS\G

   -- Checking the replication status
   SHOW SLAVE STATUS\G
   ```

1. Make sure your applications work correctly:

   * Check the execution time of critical queries.
   * Check error statistics.
   * Monitor resource usage.

1. Optimize your configuration for the new version:

   ```sql
   -- Analyzing buffer pool usage
   SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES
   FROM information_schema.INNODB_BUFFER_POOL_STATS;
   
   -- Checking performance settings
   SHOW VARIABLES LIKE '%buffer%';
   ```

## Useful links {#see-also}

* [Technical overview of MySQL® 8.0 features](../concepts/mysql-57-80-comparison.md) with focus on the new version's changes in terms of performance and functionality.