MySQL® version upgrade
You can upgrade a Managed Service for MySQL® cluster to any supported minor or major version.
In single-host clusters, the only master host is brought out of its running state 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 request performance: this is especially prominent for large databases with active use of indexes.
In multi-host clusters, upgrades follow the procedure below:
-
The replicas are withdrawn from service one by one for an upgrade. The replicas are queued randomly. Following the upgrade, the replicas get back online. Read performance may temporarily decrease at this stage because some replicas will be unavailable.
-
A master host is closed for writes. A new master host is selected from among the replicas and opened for writes. As a result, the cluster is upgraded with minimal downtime.
-
The original master host is shut down, upgraded, and resumes its operation as a replica. The master does not switch back in order to minimize the risks of additional switching.
Note
In MySQL® 8.0, replica switching is more reliable and efficient due to improved metadata processing.
To learn more about updates within a single version and host maintenance, see Maintenance.
Alert
-
Once your DBMS is upgraded, you cannot roll a cluster back to the previous version.
-
Update your cluster during low load periods to reduce risks and the impact on users.
-
The success of MySQL® version upgrade depends on many factors, such as:
- 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 condition and data quality.
We recommend that you begin by upgrading a test cluster that has the same data and settings.
Before a version upgrade
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 at the preparation stage:
-
See MySQL® changelog
for how upgrades may affect your applications.Examples of changes in MySQL® 8.0
-
Changes in the behavior of SQL functions and query optimizer:
-- Example of query for execution plan analysis EXPLAIN ANALYZE SELECT * FROM <table_name> WHERE complex_condition ORDER BY <field>;
-
Deprecated and removed features, especially related to security:
-- Testing authentication mechanisms SELECT user, host, plugin FROM mysql.user WHERE user NOT LIKE 'mysql.%';
-
Changes in full-text search and JSON processing:
-- 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.
-
-
Try a version upgrade on a test cluster.
-
Deploy a test cluster from a backup of the main cluster using the
PRESTABLE
environment and upgrade it to the required version. -
Check the operation of critical queries and stored procedures.
-
Check the functionality that uses JSON and full-text search.
-
Perform load testing:
-- 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;
-
-
Create a backup of the main cluster just before the upgrade.
-
Ensure cluster fault tolerance:
- Make sure the main and test clusters have at least two replica hosts and one master host. Add hosts as needed.
- Optionally, check replication status and latency:
-- Checking replication status SHOW SLAVE STATUS\G -- Checking replication latency 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';
Updating the MySQL® version
- Navigate to the folder dashboard and select Managed Service for MySQL.
- Select the cluster you need from the list and click
Edit. - In the Version field, select a new version number.
- 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 upgrade time depends on multiple factors, e.g., the amount of data or the number of databases in the cluster. The upgrade usually takes several minutes, and 10 minutes or more for large databases.
If you do not have the Yandex Cloud CLI yet, install and initialize it.
The folder specified when creating the CLI profile is used by default. 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 the --folder-name
or --folder-id
parameter.
To update the MySQL® version:
-
Get a list of your MySQL® clusters using this command:
yc managed-mysql cluster list
-
Get information about the cluster you need and check the MySQL® version in the
config.version
parameter:yc managed-mysql cluster get <cluster_name_or_ID>
-
Run the MySQL® upgrade:
yc managed-mysql cluster update <cluster_name_or_ID> \ --mysql-version <new_version_number>
The upgrade time depends on multiple factors, e.g., the amount of data or the number of databases in the cluster. The upgrade usually takes several minutes, and 10 minutes or more for large databases.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
Add the
version
field to theyandex_mdb_mysql_cluster
resource or change the field value if it already exists:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... version = "<MySQL®_version>" ... }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for MySQL® cluster operations:
- Creating a cluster, including by restoring one from a backup: 15 minutes.
- Editing a cluster, including the MySQL® version update: 60 minutes.
- Deleting a cluster: 15 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
-
Get an IAM token for API authentication and put it into the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the Cluster.update method and send the following request, e.g., via cURL
: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.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
: List of parameters to update as a single string, separated by commas.Only one parameter is provided in this case.
-
configSpec.version
: New MySQL® version.
You can request the cluster ID with the list of clusters in the folder.
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and put it into the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the ClusterService/Update call and send the following request, e.g., via gRPCurl
: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 ofpaths[]
strings.Format for listing settings
"update_mask": { "paths": [ "<setting_1>", "<setting_2>", ... "<setting_N>" ] }
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 parameters to update as an array ofpaths[]
strings.Only one parameter is provided in this case.
-
config_spec.version
: New MySQL® version.
You can request the cluster ID with the list of clusters in the folder.
-
-
View the server response to make sure the request was successful.
The upgrade time depends on multiple factors, e.g., the amount of data or the number of databases in the cluster. The upgrade usually takes several minutes, and 10 minutes or more for large databases.
Examples
Let's look at a case where a cluster is upgraded from version 5.7 to 8.0. This scenario is particularly interesting because it covers the most significant changes in the MySQL® architecture.
-
Get a list of clusters and find out their IDs and names:
yc managed-mysql cluster list
Result example:
+----------------------+------------+---------------------+--------+---------+ | ID | NAME | CREATED AT | HEALTH | STATUS | +----------------------+------------+---------------------+--------+---------+ | c9q8p8j2gaih******** | mysql406 | 2021-10-23 12:44:17 | ALIVE | RUNNING | +----------------------+------------+---------------------+--------+---------+
-
Get detailed information about a specific cluster:
yc managed-mysql cluster get mysql406
The output will show the current version of MySQL®:
id: c9q8p8j2gaih******** ... config: version: "5.7" ...
-
Start the upgrade to 8.0:
yc managed-mysql cluster update mysql406 --mysql-version 8.0
Once you execute this command, the upgrade process will start, which will take anywhere from a few minutes to an hour depending on database size and cluster configuration.
-
Open the current Terraform configuration file with an infrastructure plan.
-
In the
version
field, specify the8.0
value in theyandex_mdb_mysql_cluster
resource:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... version = "8.0" ... }
-
Make sure the settings are correct:
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Apply the changes:
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Terraform will automatically detect the need to upgrade the version and start the process.
After a successful upgrade:
-
Check the status of all critical components:
-- Checking InnoDB status SHOW ENGINE INNODB STATUS\G -- Checking replication status SHOW SLAVE STATUS\G
-
Make sure the applications work correctly:
- Check the execution time of critical requests.
- Check error statistics.
- Track resource usage.
-
Optimize your configuration for the new version:
-- 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%';
See also
- Technical overview of MySQL® 8.0 features with focus on the new version's changes in terms of performance and functionality.