Creating a MySQL cluster
A MySQL cluster consists of one or more database hosts. In multi-host clusters, semi-synchronous replication is configured automatically.
For more about Managed Service for MySQL cluster structure, see Resource relationships in Managed Service for MySQL.
Note
- The number of hosts you can create together with a MySQL cluster depends on the selected disk type and host class.
- Available disk types depend on the selected host class.
How to create a MySQL cluster
-
In the management console
, select the folder where you want to create a DB cluster. -
Select Managed Service for MySQL.
-
Click Create cluster.
-
Enter a name for the Managed Service for MySQL cluster in the Cluster name field. It must be unique within the folder.
-
Select the environment where you want to create the Managed Service for MySQL cluster (you cannot change the environment once the cluster is created):
PRODUCTION
: For stable versions of your apps.PRESTABLE
: For testing purposes. The prestable environment is similar to the production environment and likewise covered by the SLA, but it is the first to get new functionalities, improvements, and bug fixes. In the prestable environment, you can test compatibility of new versions with your application.
-
Select the DBMS version.
-
Select the host class that defines the technical specifications of the VMs where the DB hosts will be deployed. All available options are listed in MySQL host classes. When you change the host class for the Managed Service for MySQL cluster, the characteristics of all existing hosts change, too.
-
Under Size of storage:
-
Select the disk type.
The selected type determines the increments in which you can change your disk size:
- Network HDD and SSD storage: In increments of 1 GB.
- Local SSD storage:
- For Intel Broadwell and Intel Cascade Lake: In increments of 100 GB.
- For Intel Ice Lake: In increments of 368 GB.
- Non-replicated SSD storage: In increments of 93 GB.
-
Select the storage size to be used for data and backups. For more information about how backups take up storage space, see Backups in Managed Service for MySQL.
Note
If the DB storage is 95% full, the Managed Service for MySQL cluster will switch to read-only mode. Increase the storage size in advance.
-
-
Under Database, specify the DB attributes:
-
DB name. The name must be unique within the folder.
A database name may contain letters, numbers, and underscores. The name may be up to 63 characters long. Such names as
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL. You cannot create DBs with these names. -
DB owner username and password.
Note
A username may contain letters, numbers, hyphens, and underscores, but must start with a letter or an underscore. It can be between 1 and 32 characters long.
The password must be between 8 and 128 characters.
-
-
Under Network settings, select:
- Cloud network for the Managed Service for MySQL cluster.
- Security groups for the Managed Service for MySQL cluster network traffic. You may also need to set up security groups to connect to the Managed Service for MySQL cluster.
-
Under Hosts, click
- Availability zone.
- Host subnet: By default, each host is created in a separate subnet.
- Select Public access if the host must be accessible from outside Yandex Cloud.
- Priority for assigning the host as a master.
- Host priority as a MySQL replica for creating backups.
If you selected
local-ssd
ornetwork-ssd-nonreplicated
under Size of storage, you need to add at least three hosts to the Managed Service for MySQL cluster. After creating a Managed Service for MySQL cluster, you can add extra hosts to it if there are enough folder resources available. -
Configure additional Managed Service for MySQL cluster settings, if required:
-
Backup start time (UTC): Time interval during which the cluster backup starts. Time is specified in 24-hour UTC format. The default time is
22:00 - 23:00
UTC. -
Retention period for automatic backups, days
Automatic backups are stored for the specified number of days.
-
Maintenance window: Maintenance window settings:
- To enable maintenance at any time, select arbitrary (default).
- To specify the preferred maintenance start time, select by schedule and specify the desired day of the week and UTC hour. For example, you can choose a time when the cluster is least loaded.
Maintenance operations are carried out both on enabled and disabled clusters. They may include updating the DBMS, applying patches, and so on.
-
DataLens access
This setting allows you to analyze cluster data in Yandex DataLens.
For more information about setting up a connection, see Connecting to DataLens.
-
Access from the management console
It allows you to execute SQL queries against the databases in the cluster from the Yandex Cloud dashboard.
-
Data Transfer access: Enable this option to allow access to the cluster from Yandex Data Transfer in Serverless mode.
This will enable you to connect to Yandex Data Transfer running in Kubernetes via a special network. It will also cause other operations to run faster, such as transfer launch and deactivation.
-
Statistics sampling: Enable this option to use the Performance diagnostics in Managed Service for MySQL tool in the cluster.
-
Deletion protection: Manages cluster protection from accidental deletion by a user.
Cluster deletion protection will not prevent a manual connection to delete the contents of a database.
-
-
If required, configure DBMS cluster-level settings.
Note
Some MySQL settings depend on the selected host class.
-
Click Create cluster.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To create a Managed Service for MySQL cluster:
-
Check whether the folder has any subnets for the Managed Service for MySQL cluster hosts:
yc vpc subnet list
If there are no subnets in the folder, create the required subnets in Yandex Virtual Private Cloud.
-
View a description of the create Managed Service for MySQL cluster CLI command:
yc managed-mysql cluster create --help
-
Specify the Managed Service for MySQL cluster parameters in the create command:
yc managed-mysql cluster create \ --name=<cluster_name> \ --environment <environment> \ --network-name <network_name> \ --host zone-id=<availability_zone>,` `subnet-id=<subnet_ID>,` `assign-public-ip=<public_access_to_host>,` `priority=<priority_when_selecting_new_master_host>,` `backup-priority=<backup_priority> \ --mysql-version <MySQL_version> \ --resource-preset <host_class> \ --user name=<username>,password=<user_password> \ --database name=<DB_name> \ --disk-size <storage_size_GB> \ --disk-type <disk_type> \ --security-group-ids <list_of_security_group_IDs>
You need to specify the
subnet-id
if the selected availability zone has two or more subnets.Where:
-
environment
:prestable
orproduction
. -
assign-public-ip
: Public access to the host,true
orfalse
. -
backup-priority
: Priority when selecting a new master host, between0
and100
. -
backup-priority
: Backup priority, between0
and100
. -
mysql-version
: MySQL version,5.7 or 8.0
.
Configure additional Managed Service for MySQL cluster settings, if required:
yc managed-mysql cluster create \ ... --backup-window-start <backup_start_time> \ --backup-retain-period-days=<backup_retention_period> \ --datalens-access=<access_from_DataLens> \ --websql-access=<queries_from_management_console> \ --deletion-protection=<deletion_protection> \ --performance-diagnostics enabled=true,` `sessions-sampling-interval=<session_sampling_interval>,` `statements-sampling-interval=<statement_sampling_interval>
Where:
-
backup-window-start
: Backup start time. -
backup-retain-period-days
: Retention period for automatic backups (in days). -
datalens-access
: Access to the cluster from DataLens,true
orfalse
. -
websql-access
: Queries from the management console,true
orfalse
. -
deletion-protection
: Cluster deletion protection,true
orfalse
. -
performance-diagnostics
: Enabling statistics collection for cluster performance diagnostics. The values of thesessions-sampling-interval
and thestatements-sampling-interval
parameters may range from1
to86400
seconds.
A database name may contain letters, numbers, and underscores. The name may be up to 63 characters long. Such names as
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL. You cannot create DBs with these names.Cluster deletion protection will not prevent a manual connection to delete the contents of a database.
Configure the DBMS settings, if required.
Note
When creating a cluster, the
anytime
maintenance mode is set by default. You can set a specific maintenance period when updating the cluster settings. -
Terraform
For more information about the provider resources, see the documentation on the Terraform
If you change the configuration files, Terraform automatically detects which part of your configuration is already deployed, and what should be added or removed.
If you don't have Terraform, install it and configure the Yandex Cloud provider.
To create a Managed Service for MySQL cluster:
-
In the configuration file, describe the parameters of the resources you want to create:
-
DB cluster: Description of the cluster and its hosts
-
Database: Description of the cluster DB
A database name may contain letters, numbers, and underscores. The name may be up to 63 characters long. Such names as
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL. You cannot create DBs with these names. -
User: Description of the cluster user
-
Network: Description of the cloud network where a cluster will be located. If you already have a suitable network, you don't have to describe it again.
-
Subnets: Description of the subnets to connect the cluster hosts to. If you already have suitable subnets, you don't have to describe them again.
Here is an example of the configuration file structure:
resource "yandex_mdb_mysql_cluster" "<cluster_name>" { name = "<cluster_name>" environment = "<environment>" network_id = "<network_ID>" version = "<MySQL_version>" security_group_ids = [ "<list_of_security_group_IDs>" ] deletion_protection = <cluster_deletion_protection> resources { resource_preset_id = "<host_class>" disk_type_id = "<disk_type>" disk_size = "<storage_size_GB>" } host { zone = "<availability_zone>" subnet_id = "<subnet_ID>" assign_public_ip = <public_access_to_host> priority = <priority_when_selecting_master_host > backup_priority = <backup_priority> } } resource "yandex_mdb_mysql_database" "<DB_name>" { cluster_id = "<cluster_ID>" name = "<DB_name>" } resource "yandex_mdb_mysql_user" "<username>" { cluster_id = "<cluster_ID>" name = "<username>" password = "<user_password>" permission { database_name = "<DB_name>" roles = ["ALL"] } } resource "yandex_vpc_network" "<network_name>" { name = "<network_name>" } resource "yandex_vpc_subnet" "<subnet_name>" { name = "<subnet_name>" zone = "<availability_zone>" network_id = "<network_ID>" v4_cidr_blocks = ["<range>"] }
Where:
environment
: Environment,PRESTABLE
orPRODUCTION
.version
: MySQL version,5.7 or 8.0
.deletion_protection
: Cluster deletion protection,true
orfalse
.assign_public_ip
: Public access to the host,true
orfalse
.backup-priority
: Host priority when selecting a new master host, between0
and100
.backup-priority
: Backup priority, between0
and100
.
Cluster deletion protection will not prevent a manual connection to delete the contents of a database.
-
To set up the maintenance window (for disabled clusters as well), add the
maintenance_window
block to the cluster description:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... maintenance_window { type = <maintenance_type> day = <day_of_week> hour = <hour> } ... }
Where:
type
: Maintenance type. The possible values include:anytime
: Anytime.weekly
: By schedule.
day
: Day of the week for theweekly
type inDDD
format, e.g.,MON
.hour
: Hour of the day for theweekly
type in theHH
format, e.g.,21
.
-
To allow access from DataLens and access to SQL queries from the management console, add a block named
access
to the Managed Service for MySQL cluster description:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... access { web_sql = <access_from_management_console> data_lens = <access_from_DataLens> ... } ... }
Where:
-
web_sql
: Execution of SQL queries from the management console,true
orfalse
. -
data_lens
: Access from DataLens,true
orfalse
.
-
-
To set the backup start time, add the
backup_window_start
section to the Managed Service for MySQL cluster description:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... backup_window_start { hours = <hour> minutes = <minute> } ... }
Where:
hours
: Backup starting hourminutes
: Backup start minutes
-
To set the retention period for backup files, define the
backup_retain_period_days
parameter in the Managed Service for MySQL cluster description:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... backup_retain_period_days = <backup_retention_period> ... }
Where
backup_retain_period_days
is the retention period for automatic backups (in days).Acceptable values are from
7
to60
. The default value is7
. -
To enable statistics collection for cluster performance diagnostics, add the
performance_diagnostics
section to your Managed Service for MySQL cluster:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... performance_diagnostics { enabled = true sessions_sampling_interval = <session_sampling_interval> statements_sampling_interval = <statement_sampling_interval> } ... }
The values of the
sessions_sampling_interval
and thestatements_sampling_interval
parameters may range from1
to86400
seconds.
For more information about the resources you can create with Terraform, see the provider documentation
. -
-
Make sure the configuration files are correct.
-
In the command line, go to the folder where you created the configuration file.
-
Run the check using the command:
terraform plan
If the configuration is described correctly, the terminal displays a list of created resources and their parameters. If there are errors in the configuration, Terraform points them out. This is a test step. No resources are created.
-
-
Create a Managed Service for MySQL cluster.
-
If the configuration doesn't contain any errors, run the command:
terraform apply
-
Confirm that you want to create the resources.
After this, all the necessary resources will be created in the specified folder and the IP addresses of the VMs will be displayed in the terminal. You can check resource availability and their settings in the management console
.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 } }
-
To create a MySQL cluster, use the create REST API method for the Cluster resource or the ClusterService/Create gRPC API call and provide the following in the request:
-
ID of the folder where the Managed Service for MySQL cluster should be placed, in the
folderId
parameter. -
Managed Service for MySQL cluster name in the
name
parameter. It must be unique within the folder. -
Managed Service for MySQL cluster environment in the
environment
parameter. -
Managed Service for MySQL cluster configuration in the
configSpec
parameter. -
DB configuration in one or more
databaseSpecs
parameters.A database name may contain letters, numbers, and underscores. The name may be up to 63 characters long. Such names as
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL. You cannot create DBs with these names. -
User settings in one or more
userSpecs
parameters. -
Configuration of the Managed Service for MySQL cluster hosts in one or more
hostSpecs
parameters. -
Network ID in the
networkId
parameter. -
Security group IDs in the
securityGroupIds
parameter.
If required, provide the backup start time in the configSpec.backupWindowStart
parameter and the retention period for automatic backups (in days) in the configSpec.backupRetainPeriodDays
parameter. Acceptable values are from 7
to 60
. The default value is 7
.
To allow connection to cluster hosts from the internet, provide the true
value in the hostSpecs.assignPublicIp
parameter.
To allow access to the cluster from Yandex Data Transfer in Serverless mode, pass true
for the configSpec.access.dataTransfer
parameter.
This will enable you to connect to Yandex Data Transfer running in Kubernetes via a special network. It will also cause other operations to run faster, such as transfer launch and deactivation.
To activate cluster access from DataLens, pass true
for the configSpec.access.dataLens
parameter.
To enable statistics collection for cluster performance diagnostics, specify true
for the configSpec.performanceDiagnostics.enabled
parameter. Optionally add the following parameters:
configSpec.performanceDiagnostics.sessionsSamplingInterval
: Session sampling interval. Acceptable values are between1
and86400
seconds.configSpec.performanceDiagnostics.statementsSamplingInterval
: Statement sampling interval. Acceptable values are between1
and86400
seconds.
Warning
If you specified security group IDs when creating a Managed Service for MySQL cluster, you may also need to configure security groups to connect to the cluster.
Examples
Creating a single-host cluster
To create a Managed Service for MySQL cluster with a single host, provide a single --host
parameter.
Create a Managed Service for MySQL cluster with the following test characteristics:
- Name:
my-mysql
- Version:
8.0
- Environment:
Production
- Network:
default
- Security group ID:
enp6saqnq4ie244g67sb
- Number of
s2.micro
class hosts in theb0rcctk2rvtr8efcch64
subnet, in theru-central1-a
availability zone: 1 - Network SSD storage (
network-ssd
): 20 GB - User:
user1
, with theuser1user1
password - Database:
db1
, in whichuser1
has full rights (same asGRANT ALL PRIVILEGES on db1.*
) - Protection against accidental cluster deletion: Enabled
-
Run this command to create a Managed Service for MySQL cluster:
yc managed-mysql cluster create \ --name="my-mysql" \ --mysql-version 8.0 \ --environment=production \ --network-name=default \ --security-group-ids enp6saqnq4ie244g67sb \ --host zone-id=ru-central1-a,subnet-id=b0rcctk2rvtr8efcch64 \ --resource-preset s2.micro \ --disk-type network-ssd \ --disk-size 20 \ --user name=user1,password="user1user1" \ --database name=db1 \ --deletion-protection=true
-
Run the change permissions command for the
user1
user.yc managed-mysql user grant-permission user1 \ --cluster-name="my-mysql" \ --database=db1 \ --permissions ALL
Create a Managed Service for MySQL cluster and a network for it with the following test characteristics:
-
Name:
my-mysql
-
Version:
8.0
-
Environment:
PRESTABLE
-
Cloud ID:
b1gq90dgh25bebiu75o
-
Folder ID:
b1gia87mbaomkfvsleds
-
New network:
mynet
-
Number of
s2.micro
hosts in the newmysubnet
subnet, in theru-central1-a
availability zone: 1. Themysubnet
subnet will have a range of10.5.0.0/24
. -
New security group:
mysql-sg
, allowing Managed Service for MySQL cluster connections from the internet on port3306
. -
Network SSD storage (
network-ssd
): 20 GB -
User:
user1
, with theuser1user1
password -
Database:
db1
, in whichuser1
has full rights (same asGRANT ALL PRIVILEGES on db1.*
) -
Protection against accidental Managed Service for MySQL cluster deletion: Enabled
The configuration file for this Managed Service for MySQL cluster is as follows:
resource "yandex_mdb_mysql_cluster" "my-mysql" {
name = "my-mysql"
environment = "PRESTABLE"
network_id = yandex_vpc_network.mynet.id
version = "8.0"
security_group_ids = [ yandex_vpc_security_group.mysql-sg.id ]
deletion_protection = true
resources {
resource_preset_id = "s2.micro"
disk_type_id = "network-ssd"
disk_size = 20
}
host {
zone = "ru-central1-a"
subnet_id = yandex_vpc_subnet.mysubnet.id
}
}
resource "yandex_mdb_mysql_database" "db1" {
cluster_id = yandex_mdb_mysql_cluster.my-mysql.id
name = "db1"
}
resource "yandex_mdb_mysql_user" "<username>" {
cluster_id = yandex_mdb_mysql_cluster.my-mysql.id
name = "user1"
password = "user1user1"
permission {
database_name = yandex_mdb_mysql_database.db1.name
roles = ["ALL"]
}
}
resource "yandex_vpc_network" "mynet" {
name = "mynet"
}
resource "yandex_vpc_security_group" "mysql-sg" {
name = "mysql-sg"
network_id = yandex_vpc_network.mynet.id
ingress {
description = "MySQL"
port = 3306
protocol = "TCP"
v4_cidr_blocks = [ "0.0.0.0/0" ]
}
}
resource "yandex_vpc_subnet" "mysubnet" {
name = "mysubnet"
zone = "ru-central1-a"
network_id = yandex_vpc_network.mynet.id
v4_cidr_blocks = ["10.5.0.0/24"]
}
Creating a multi-host cluster
To create a multi-host Managed Service for MySQL cluster, provide as many --host
parameters as there should be hosts in your cluster.
Create a Managed Service for MySQL cluster with the following test characteristics:
-
Name:
my-mysql-3
-
Version:
8.0
-
Environment:
prestable
-
Network:
default
-
Security group ID:
enp6saqnq4ie244g67sb
-
s2.micro
public hosts: 3One host will be added to each subnet of the
default
network:subnet-a
:10.5.0.0/24
, availability zone:ru-central1-a
.subnet-b
:10.6.0.0/24
, availability zone:ru-central1-b
.subnet-d
:10.7.0.0/24
, availability zone:ru-central1-d
.
The host residing in
subnet-b
will have the backup priority. Backups will be created from this host's data unless you choose it to be the master host. -
Network SSD storage (
network-ssd
): 32 GB -
User:
user1
, with theuser1user1
password -
Database:
db1
, in whichuser1
has full rights (same asGRANT ALL PRIVILEGES on db1.*
)
-
Run this command to create a Managed Service for MySQL cluster:
yc managed-mysql cluster create \ --name="my-mysql-3" \ --mysql-version 8.0 \ --environment=prestable \ --network-name=default \ --security-group-ids enp6saqnq4ie244g67sb \ --host zone-id=ru-central1-a,` `subnet-name=subnet-a,` `assign-public-ip=true \ --host zone-id=ru-central1-b,` `subnet-name=subnet-b,` `backup-priority=10,` `assign-public-ip=true \ --host zone-id=ru-central1-d,` `subnet-name=subnet-d,` `assign-public-ip=true \ --resource-preset s2.micro \ --disk-type network-ssd \ --disk-size 32 \ --user name=user1,password="user1user1" \ --database name=db1
-
Run the change permissions command for the
user1
user.yc managed-mysql user grant-permission user1 \ --cluster-name="my-mysql-3" \ --database=db1 \ --permissions ALL
Create a Managed Service for MySQL cluster and a network for it with the following test characteristics:
-
Name:
my-mysql-3
-
Version:
8.0
-
Environment:
PRESTABLE
-
Cloud ID:
b1gq90dgh25bebiu75o
-
Folder ID:
b1gia87mbaomkfvsleds
-
New network:
mynet
-
s2.micro
public hosts: 3One host will be added to each one of the new subnets:
mysubnet-a
:10.5.0.0/24
, availability zone:ru-central1-a
.mysubnet-b
:10.6.0.0/24
, availability zone:ru-central1-b
.mysubnet-d
:10.7.0.0/24
, availability zone:ru-central1-d
.
These subnets will belong to the
mynet
network.The host residing in
mysubnet-b
will have the backup priority. Backups will be created from this host's data unless you choose it to be the master host. -
New security group:
mysql-sg
, allowing Managed Service for MySQL cluster connections from the internet via port3306
. -
Network SSD storage (
network-ssd
): 32 GB -
User:
user1
, with theuser1user1
password -
Database:
db1
, in whichuser1
has full rights (same asGRANT ALL PRIVILEGES on db1.*
)
The configuration file for this Managed Service for MySQL cluster is as follows:
resource "yandex_mdb_mysql_cluster" "my-mysql-3" {
name = "my-mysql-3"
environment = "PRESTABLE"
network_id = yandex_vpc_network.mynet.id
version = "8.0"
security_group_ids = [ yandex_vpc_security_group.mysql-sg.id ]
resources {
resource_preset_id = "s2.micro"
disk_type_id = "network-ssd"
disk_size = 32
}
host {
zone = "ru-central1-a"
subnet_id = yandex_vpc_subnet.mysubnet-a.id
assign_public_ip = true
}
host {
zone = "ru-central1-b"
subnet_id = yandex_vpc_subnet.mysubnet-b.id
assign_public_ip = true
backup_priority = 10
}
host {
zone = "ru-central1-d"
subnet_id = yandex_vpc_subnet.mysubnet-d.id
assign_public_ip = true
}
}
resource "yandex_mdb_mysql_database" "db1" {
cluster_id = yandex_mdb_mysql_cluster.my-mysql-3.id
name = "db1"
}
resource "yandex_mdb_mysql_user" "user1" {
cluster_id = yandex_mdb_mysql_cluster.my-mysql-3.id
name = "user1"
password = "user1user1"
permission {
database_name = yandex_mdb_mysql_database.db1.name
roles = ["ALL"]
}
}
resource "yandex_vpc_network" "mynet" {
name = "mynet"
}
resource "yandex_vpc_security_group" "mysql-sg" {
name = "mysql-sg"
network_id = yandex_vpc_network.mynet.id
ingress {
description = "MySQL"
port = 3306
protocol = "TCP"
v4_cidr_blocks = [ "0.0.0.0/0" ]
}
}
resource "yandex_vpc_subnet" "mysubnet-a" {
name = "mysubnet-a"
zone = "ru-central1-a"
network_id = yandex_vpc_network.mynet.id
v4_cidr_blocks = ["10.5.0.0/24"]
}
resource "yandex_vpc_subnet" "mysubnet-b" {
name = "mysubnet-b"
zone = "ru-central1-b"
network_id = yandex_vpc_network.mynet.id
v4_cidr_blocks = ["10.6.0.0/24"]
}
resource "yandex_vpc_subnet" "mysubnet-d" {
name = "mysubnet-d"
zone = "ru-central1-d"
network_id = yandex_vpc_network.mynet.id
v4_cidr_blocks = ["10.7.0.0/24"]
}