Creating an MySQL® 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:

1. 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.

2. View a description of the create Managed Service for MySQL® cluster CLI command:

   yc managed-mysql cluster create --help
   

3. 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_for_electing_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 <network-hdd|network-ssd|network-ssd-nonreplicated|local-ssd> \
     --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 or production.

   • assign-public-ip: Public access to the host, true or false.

   • --disk-type: Disk type.

     Warning

     You cannot change disk type after you create a cluster.

   • backup-priority: Host priority when selecting a new master host, between 0 and 100.

   • backup-priority: Backup priority, between 0 and 100.

   • 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=<true_or_false> \
     --websql-access=<true_or_false> \
     --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: Enables access from Yandex DataLens. The default value is false. For more information about setting up a connection, see Connecting to a MySQL® cluster from DataLens.
   • websql-access: Enables you to run SQL queries against cluster databases from the Yandex Cloud management console using Yandex WebSQL. The default value is false.
   • deletion-protection: Cluster deletion protection, true or false.
   • performance-diagnostics: Enabling statistics collection for cluster performance diagnostics. The values of the sessions-sampling-interval and the statements-sampling-interval parameters may range from 1 to 86400 seconds.

   The DB name may contain Latin letters, numbers, hyphens, and underscores. The name may be up to 63 characters long. Such names as mysql, sys, information_schema, and performance_schema are reserved for Managed Service for MySQL®. You cannot create DBs with these names.

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   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 allows you to quickly create a cloud infrastructure in Yandex Cloud and manage it using configuration files. Configuration files store the infrastructure description in the HashiCorp Configuration Language (HCL). Terraform and its providers are distributed under the Business Source License.

For more information about the provider resources, see the documentation on the Terraform website or the mirror.

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:

1. 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

     The DB name may contain Latin letters, numbers, hyphens, and underscores. The name may be up to 63 characters long. Such names as mysql, sys, information_schema, and performance_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 or PRODUCTION.
   • version: MySQL® version, 5.7 or 8.0.
   • deletion_protection: Cluster deletion protection, true or false.
   • assign_public_ip: Public access to the host, true or false.
   • backup-priority: Host priority when selecting a new master host, between 0 and 100.
   • backup-priority: Backup priority, between 0 and 100.

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   • 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 the weekly type in DDD format, e.g., MON.
     • hour: Hour of the day for the weekly type in the HH format, e.g., 21.

   • To enable access from DataLens and allow execution of SQL queries from the management console using Yandex WebSQL, add a section named access to the cluster description:

     resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
       ...
       access {
         data_lens = <true_or_false>
         web_sql   = <true_or_false>
         ...
       }
       ...
     }
     

   • 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 start hour
     • minutes: Backup start minute

   • 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 to 60. The default value is 7.

   • To enable statistics collection for cluster performance diagnostics, add the performance_diagnostics section to your Managed Service for MySQL® cluster description:

     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 the statements_sampling_interval parameters may range from 1 to 86400 seconds.

   For more information about the resources you can create with Terraform, see the provider documentation.

2. Make sure the configuration files are correct.

   1. In the command line, go to the folder where you created the configuration file.

   2. Run a check using this command:

      terraform plan
      

   If the configuration is described correctly, the terminal will display a list of created resources and their parameters. If the configuration contains any errors, Terraform will point them out. This is a test step; no resources will be created.

3. Create a Managed Service for MySQL® cluster.

   1. If the configuration does not contain any errors, run this command:

      terraform apply
      

   2. Confirm that you want to create the resources.

   After this, all required resources will be created in the specified folder and the IP addresses of the VMs will be displayed in the terminal. You can check the new resources and their configuration using 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 to host the Managed Service for MySQL® cluster 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.

  The DB name may contain Latin letters, numbers, hyphens, and underscores. The name may be up to 63 characters long. Such names as mysql, sys, information_schema, and performance_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 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 between 1 and 86400 seconds.
• configSpec.performanceDiagnostics.statementsSamplingInterval: Statement sampling interval. Acceptable values are between 1 and 86400 seconds.

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.

• s2.micro host in the new mysubnet subnet, in the ru-central1-a availability zone: 1. The mysubnet subnet will have a range of 10.5.0.0/24.

• New security group: mysql-sg, allowing Managed Service for MySQL® cluster connections from the internet via port 3306.

• Network SSD storage (network-ssd): 20 GB

• User: user1, with the user1user1 password

• Database: db1, in which user1 has full rights (same as GRANT 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"]
}

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: 3

  One 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 port 3306.

• Network SSD storage (network-ssd): 32 GB

• User: user1, with the user1user1 password

• Database: db1, in which user1 has full rights (same as GRANT 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"]
}