Creating MySQL clusters

1. In the management console, select the folder where you want to create a DB cluster.

2. Select Managed Service for MySQL.

3. Click Create cluster.

4. Enter a name for the cluster in the Cluster name field. The cluster name must be unique within Yandex.Cloud.

5. Select the environment where you want to create the cluster (you can't change the environment once the cluster is created):

   • PRODUCTION: For stable versions of your apps.
   • PRESTABLE: For testing, including the Managed Service for MySQL service itself. The Prestable environment is first updated with new features, improvements, and bug fixes. However, not every update ensures backward compatibility.

6. Select the DBMS version.

7. 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 Host classes. When you change the host class for the cluster, the characteristics of all existing hosts change, too.

8. Under Storage size:

   • Select the type of storage, either a more flexible network type (network-hdd or network-ssd) or faster local SSD storage (local-ssd). The size of the local storage can only be changed in 100 GB increments.
   • Select the size to be used for data and backups. For more information about how backups take up storage space, see Backups.

9. Under Database, specify the DB attributes:

   • Database name. The DB name must be unique within the folder and contain only Latin letters, numbers, and underscores.
   • The name of the user who is the DB owner. The username may only contain Latin letters, numbers, and underscores.
   • User password (from 8 to 128 characters).

10. Under Hosts, select parameters for the database hosts created with the cluster (keep in mind that if you use SSDs when creating a MySQL cluster, you can set at least three hosts). If you open Advanced settings, you can choose specific subnets for each host. By default, each host is created in a separate subnet.

11. If necessary, configure additional cluster settings:

    • Backup start (UTC): The time in UTC when you want to start creating a backup of a cluster (in 24-hour format). If the time is not set, the backup will start at 22:00 UTC.

    • Maintenance window: Settings of the technical support window. You can use the settings to specify the preferred start time for cluster host maintenance (for example, you can select the time when the cluster is least loaded with requests):

      • To specify the preferred start time for the maintenance window, select by schedule and set the desired day of the week and hour of day in UTC (Coordinated Universal Time) using the drop-down lists.
      • To allow maintenance at any time, select arbitrary.

      Maintenance may include updating the DBMS version, applying patches, and so on.

    • Access from DataLens: Enable this option to be able to analyze data from the cluster in Yandex DataLens. For more information about setting up a connection, see Connecting to DataLens.

    • Access from the management console: Select this option to be able to execute SQL queries to cluster databases from the Yandex.Cloud management console.

12. If necessary, configure the DBMS settings:

    • Audit log: Controls writing the MySQL audit log.

      By default, disabled.

      For more information, see the documentation for MySQL.

    • Auto increment increment: Sets the interval between the values of AUTO_INCREMENT columns.

      The minimum value is 1 and the maximum value is 65535. Defaults to 1.

      For more information, see the documentation for MySQL.

    • Auto increment offset: Sets the initial value for AUTO_INCREMENT columns. This setting is ignored if its value is greater than that of Auto increment increment.

      The minimum value is 1 and the maximum value is 65535. Defaults to 1.

      For more information, see the documentation for MySQL.

    • Binlog cache size: The size of the cache (in bytes) for storing changes to the binary log while performing a transaction.

      The minimum value is 4096 (4 KB) and the maximum value is 67108864 (64 MB). Defaults to 32768 (32 KB).

      For more information, see the documentation for MySQL.

    • Binlog group commit sync delay: Sets the delay before synchronizing the binary log to disk when performing a COMMIT for the binary log. To synchronize more transactions to disk at a time, set the delay to a value greater than zero. This will reduce the total time per COMMIT for a group of transactions.

      The minimum value is 0 (no delay) and the maximum value is 1000000 (one second). Defaults to 0.

      For more information, see the documentation for MySQL.

    • Binlog row image: Determines how row images are written to the binary log for row-based replication:

      • FULL (default): Log all columns in both the "before" and "after" images.
      • MINIMAL: Log only those columns in the "before" image that are required to identify the rows to be changed; log only those columns in the "after" image where a value was specified by the SQL statement, or generated by auto-increment.
      • NOBLOB: Log all columns in the "before" and "after" images (same as FULL), except for BLOB and TEXT columns that either haven't changed, or aren't required to identify rows.

      For more information, see the documentation for MySQL.

    • Binlog rows query log events: Controls logging information events (for example, query log events) in the binary log.

      By default, disabled.

      For more information, see the documentation for MySQL.

    • Character set server: The character set used by the MySQL server when working with data and exchanging information with MySQL clients. This choice affects the performance of SQL functions for manipulating strings and other functions.

      By default: utf8mb4.

      For more information, see the documentation for MySQL.

    • Collation server: The algorithm for collating characters used by the MySQL server when working with data and exchanging information with MySQL clients. This choice affects the performance of SQL functions for sorting data, manipulating strings, and other functions.

      By default: utf8mb4_0900_ai_ci.

      For more information, see the documentation for MySQL.

    • Default authentication plugin: The authentication plugin used in Managed Service for MySQL clusters:

      • mysql_native_password: The authentication method that was used in MySQL prior to the introduction of authentication plugins.
      • sha256_password: The authentication method using the SHA-256 hashing algorithm for passwords.
      • caching_sha2_password (default): Similar to sha256_password, uses server-side caching for better performance and provides some additional features.

      For more information, see the documentation for MySQL.

    • Default time zone: The server time zone.

      By default: Europe/Moscow.

      For more information, see the documentation for MySQL.

    • Explicit defaults for timestamp: Controls non-standard default values and NULL value handling in TIMESTAMP columns.

      By default, the setting is enabled, which disables any nonstandard behaviors.

      For more information, see the documentation for MySQL.

    • General log: Controls writing the MySQL general query log.

      By default, disabled.

      For more information, see the documentation for MySQL.

    • Group concat max len: The maximum result length (in bytes) for the GROUP_CONCAT() function.

      The minimum value is 4 and the maximum value is 33554432 (32 MB). Defaults to 1024 (1 KB).

      For more information, see the MySQL documentation

    • Innodb adaptive hash index: Controls the InnoDB adaptive hash index. It may be desirable to disable this index for some types of database workloads. The MySQL documentation recommends performing load testing on real data to determine whether to enable the adaptive hash index.

      By default, the adaptive hash index is enabled.

      For more information, see the documentation for MySQL.

    • Innodb buffer pool size: The size of the InnoDB buffer pool (in bytes) used for caching table and index data. A larger buffer pool requires fewer I/O operations to access the same table data more than once.

      The minimum value is 5242880 (5 MB). The default value is 50% of the total RAM of a Managed Service for MySQL cluster's host.

      For more information, see the documentation for MySQL.

    • Innodb flush log at trx commit: Determines the behavior of MySQL for a transaction's COMMIT:

      • 0: Logs are written and flushed to disk once a second. Transactions for which logs haven't been flushed can be lost in a crash.
      • 1 (default): Required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
      • 2: Logs are written after each transaction commit, but flushed to disk once a second. Transactions for which logs haven't been flushed can be lost in a crash.

      For more information, see the documentation for MySQL.

    • Innodb io capacity: The number of I/O operations per second (IOPS) available to all InnoDB background tasks. This setting affects processes that require I/O, such as flushing data to disk, and can be used to limit the number of I/O operations.

      The minimum value is 100 and the maximum value is 100000. Defaults to 200.

      For more information, see the documentation for MySQL.

    • Innodb io capacity max: The maximum number of I/O operations per second (IOPS) available to all InnoDB background tasks. If a host's flushing activity falls behind, InnoDB may start flushing data to disk at a higher rate of IOPS than defined by the Innodb io capacity setting but within the specified maximum number of IOPS.

      The minimum value is 100 and the maximum value is 100000. Defaults to 2000.

      For more information, see the documentation for MySQL.

    • Innodb lock wait timeout: The maximum time (in seconds) an InnoDB transaction waits for a row lock. When a lock wait timeout occurs, an error is returned and the current SQL statement is rolled back (not the entire transaction).

      You might decrease this value for OLTP systems and interactive user applications. You might increase this value for long-running operations in an application, such as a transform step for a large array of data in a data warehouse that waits for large INSERT or UPDATE queries to finish.

      The minimum value is 1 and the maximum value is 28800 (480 minutes, or 8 hours). Defaults to 50.

      For more information, see the documentation for MySQL.

    • Innodb log buffer size: The size of the buffer (in bytes) that InnoDB uses to write the log files on disk. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. This saves disk I/O.

      The minimum value is 1048576 (1 MB) and the maximum value is 268435456 (256 MB). Defaults to 16777216 (16 MB).

      For more information, see the documentation for MySQL.

    • Innodb log file size: The size of a single InnoDB redo log file (in bytes). The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. At the same time, larger log files make crash recovery slower.

      The minimum value is 268435456 (256 MB) and the maximum value is 4294967296 (4 GB). Defaults to 268435456 (256 MB).

      For more information, see the documentation for MySQL.

    • Innodb numa interleave: Controls the NUMA Interleave memory policy for allocation of the InnoDB buffer pool.

      This policy is disabled by default.

      For more information, see the documentation for MySQL.

    • Innodb print all deadlocks: Controls writing information about all deadlocks to the error log. If disabled, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command.

      By default, the option for showing information about all deadlocks is off.

      For more information, see the documentation for MySQL.

    • Innodb purge threads: The number of InnoDB I/O threads used for purge operations. Increasing the number of these threads can improve efficiency on systems where data manipulation operations (INSERT, UPDATE, or DELETE) are performed on multiple tables.

      The minimum value is 1 and the maximum value is 16. Defaults to 4.

      For more information, see the documentation for MySQL.

    • Innodb read io threads: The number of InnoDB I/O threads used for read operations.

      The minimum value is 1 and the maximum value is 16. Defaults to 4.

      For more information, see the documentation for MySQL.

    • Innodb temp data file max size: The maximum size of an InnoDB temporary tablespace (in bytes).

      The minimum value is 0 (no temporary tablespace is used). The maximum value is 107374182400 (100 GB). Defaults to 0.

      For more information, see the documentation for MySQL.

    • Innodb thread concurrency: The maximum number of concurrently executed InnoDB threads.

      The minimum value is 0 (no limit) and the maximum value is 1000. Defaults to 0.

      For more information, see the documentation for MySQL.

    • Innodb write io threads: The number of InnoDB I/O threads used for write operations.

      The minimum value is 1 and the maximum value is 16. Defaults to 4.

      For more information, see the documentation for MySQL.

    • Join buffer size: The minimum size of the buffer (in bytes) that is used for:

      • Plain index scans.
      • Range index scans.
      • Full table scans (for JOIN operations where no index is used).

      One join buffer of the specified size is allocated for each full join between two tables. Increase the value of this setting to get a faster full join when adding indexes is not possible.

      The minimum value is 1024 (1 KB) and the maximum value is 16777216 (16 MB). Defaults to 262144 (256 KB).

      For more information, see the documentation for MySQL.

    • Long query time: If a query takes longer than this number of seconds, it's considered slow. It's not recommended to set small values because this may result in incorrectly regarding most queries as long-running.

      The minimum value is 0 and the maximum value is 3600 (1 hour). Defaults to 0.

      For more information, see the documentation for MySQL.

    • Max allowed packet: The maximum size (in bytes) of one packet, string, or parameter sent by the mysql_stmt_send_long_data() function.

      The default value is small in order to discard incorrect packages, which are usually larger. Increase this value if you're using large BLOB columns or long strings.

      The minimum value is 1024 (1 KB) and the maximum value is 134217728 (128 MB). Defaults to 16777216 (16 MB).

      For more information, see the documentation for MySQL.

    • Max connections: The maximum number of simultaneous connections permitted for MySQL cluster hosts.

      Minimum value: 10, maximum value: 10000, default value: 100 × <number of vCPU on the host> × <vCPU share on the host>, but no less than 100.

      For more information, see the documentation for MySQL.

    • Max heap table size: The maximum size of user-created MEMORY tables (in bytes). Changing this setting has no effect on any existing MEMORY table. It's also used in conjunction with the Tmp table size setting to limit the size of internal in-memory tables.

      The minimum value is 16384 (16 KB) and the maximum value is 134217728 (128 MB). Defaults to 16777216 (16 MB).

      For more information, see the documentation for MySQL.

    • Net read timeout: The maximum number of seconds to wait for more data from a network connection before aborting the read.

      The minimum value is 1 and the maximum value is 1200 (20 minutes). Defaults to 30.

      For more information, see the documentation for MySQL.

    • Net write timeout: The maximum number of seconds to wait for data to be written to a network connection before aborting the write.

      The minimum value is 1 and the maximum value is 1200 (20 minutes). Defaults to 60.

      For more information, see the documentation for MySQL.

    • Regexp time limit: The limit on the number of steps for matching operations performed by REGEXP_LIKE() and similar functions when using regular expressions. This setting indirectly affects execution time.

      The minimum value is 0 (no limit) and the maximum value is 1048576. By default: 0.

      For more information, see the documentation for MySQL.

    • Rpl semi sync master wait for slave count: Number of replica acknowledgments that the master must receive during semisynchronous replication before a transaction's COMMIT.

      The minimum value is 1 and the maximum value is 2. Defaults to 1.

      For more information, see the documentation for MySQL.

    • Slave parallel type: Specifies the policy used to decide which transactions are allowed to execute in parallel on the replica when multithreaded replication is enabled (set by Slave parallel workers):

      • LOGICAL_CLOCK: Transactions that are part of a group commit for the same binary log on the source are executed in parallel on the replica.
      • DATABASE (default): Transactions that update different databases are applied in parallel.

      For more information, see the documentation for MySQL.

    • Slave parallel workers: Sets the number of threads for executing replication transactions in parallel on the replica.

      The minimum value is 0 (multithreading on the replica is disabled) and the maximum value is 64. Defaults to 0.

      For more information, see the documentation for MySQL.

    • Sort buffer size: The size of the buffer (in bytes) used for performing in-memory sorts.

      The minimum value is 32768 (32 KB) and the maximum value is 16777216 (16 MB). Defaults to 262144 (256 KB).

      For more information, see the documentation for MySQL.

    • Sql mode: MySQL server SQL modes:

      • ALLOW_INVALID_DATES: Do not perform full checking of dates. In this mode, the server checks that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. Invalid dates like 2004-04-31 are converted to 0000-00-00 and a warning is returned.

      • ANSI_QUOTES: Treat " as an identifier quote character and not as a string quote character. With this mode enabled, use single quotes ' instead of doubles " for strings.

      • ERROR_FOR_DIVISION_BY_ZERO: Division by zero returns NULL and a warning. This SQL mode is deprecated.

      • HIGH_NOT_PRECEDENCE: The precedence of the NOT operator is higher when parsing Boolean expressions. With this mode enabled, an expression like NOT a BETWEEN b AND c is parsed as (NOT a) BETWEEN b AND c instead of NOT (a BETWEEN b AND c).

      • IGNORE_SPACE: Permits spaces between a function name and the opening bracket (. As a result, built-in function names are treated as keywords. Identifiers that are the same as function names must be quoted.

      • NO_AUTO_VALUE_ON_ZERO: Only NULL inserted into AUTO_INCREMENT columns generates the next sequence number for the column. Normally, new sequence numbers are generated when inserting either 0 or NULL into it. So this mode can be useful if you need to explicitly store 0 in this column.

      • NO_BACKSLASH_ESCAPES: Disables the use of the backslash character (\) as an escape character. With this mode enabled, backslash is treated as an ordinary character.

      • NO_DIR_IN_CREATE: When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives.

      • NO_ENGINE_SUBSTITUTION: Do not use the default storage engine automatically and return an error if the CREATE TABLE or ALTER TABLE statement specifies an engine that is unavailable.

      • NO_UNSIGNED_SUBTRACTION: A negative result is allowed when using subtraction between integer values, one of which is unsigned.

      • NO_ZERO_DATE: Affects whether the server permits 0000-00-00 as a valid date:

        • If strict SQL mode is disabled, 0000-00-00 is permitted and inserting it produces a warning.
        • If strict SQL mode is enabled, 0000-00-00 is not permitted. If you try to insert it, an error occurs.

        This SQL mode is deprecated.

      • NO_ZERO_IN_DATE: Affects the use of dates in which the month or day is zero:

        • If strict SQL mode is disabled, dates with zero parts are inserted as 0000-00-00 with a warning returned.
        • If strict SQL mode is enabled, dates with zero parts are not permitted. If you try to insert them, an error occurs.

        This SQL mode is deprecated.

        See also: NO_ZERO_DATE.

      • ONLY_FULL_GROUP_BY: Reject queries in which SELECT, HAVING, or ORDER BY refer to nonaggregated columns that are not named in the GROUP BY clause (standard SQL-92).

      • PAD_CHAR_TO_FULL_LENGTH: CHAR column values are padded with spaces to their full length. This does not apply to VARCHAR columns.

      • PIPES_AS_CONCAT: Treat || as a string concatenation operator (same as CONCAT()) rather than a synonym for OR.

      • REAL_AS_FLOAT: Treat REAL as a synonym for FLOAT (by default, MySQL treats REAL as a synonym for DOUBLE).

      • STRICT_ALL_TABLES: Enable strict SQL mode for all storage engines.

      • STRICT_TRANS_TABLES: Enable strict SQL mode for all transactional storage engines and, when possible, for nontransactional storage engines.

      • TIME_TRUNCATE_FRACTIONAL: Enables truncation of a fractional part when inserting a TIME, DATE, or TIMESTAMP value into a column that has fewer fractional digits (by default, MySQL uses rounding instead of truncation).

      • ANSI: A combination of the following modes:

        • REAL_AS_FLOAT.
        • PIPES_AS_CONCAT.
        • ANSI_QUOTES.
        • IGNORE_SPACE.
        • ONLY_FULL_GROUP_BY.

      • TRADITIONAL: A combination of the following modes:

        • STRICT_ALL_TABLES.
        • STRICT_TRANS_TABLES.
        • NO_ZERO_DATE.
        • NO_ZERO_IN_DATE.
        • ERROR_FOR_DIVISION_BY_ZERO.
        • NO_ENGINE_SUBSTITUTION.

      You can select multiple modes from the list or completely disable all SQL Mode settings.

      By default, the following set of SQL modes is used:

      • ERROR_FOR_DIVISION_BY_ZERO.
      • NO_ENGINE_SUBSTITUTION.
      • NO_ZERO_DATE.
      • NO_ZERO_IN_DATE.
      • ONLY_FULL_GROUP_BY.
      • STRICT_TRANS_TABLES.

      For more information, see the documentation for MySQL.

    • Sync binlog: Determines how often the binary log is synchronized to disk:

      • 0: Synchronization is disabled. The MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This method provides the best performance. However, data might be lost in the event of a power failure or operating system crash: transactions might be committed before they're synced to the binary log.
      • 1: The binary log is synced to disk before transactions are committed. This is the safest method, but it may have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are in a prepared state. This lets you perform automatic recovery and roll back the transactions, which guarantees that no transaction is lost from the binary log.
      • N: The binary log is synchronized to disk after collecting N commit groups for the binary log. In the event of a power failure or operating system crash, transactions might be committed before they're synced to the binary log. This method may have a negative impact on performance due to the increased number of disk writes. The higher the N value, the better the performance and the higher the risk of data loss.

      The minimum value is 0 and the maximum value is 4096. Defaults to 1.

      For more information, see the documentation for MySQL.

    • Table definition cache: The number of table definitions that can be stored in the definition cache. If your database has a large number of tables, increase this value to speed up opening of tables.

      The minimum value is 400 and the maximum value is 524288. Defaults to 2000.

      For more information, see the documentation for MySQL.

    • Table open cache: The size of the open tables cache for all threads. If the value of Opened tables is large and you don't use FLUSH_TABLES often, increase the value of this setting.

      Increasing it requires increasing the number of file descriptors for the MySQL server.

      The minimum value is 400 and the maximum value is 524288. Defaults to 4000.

      For more information, see the documentation for MySQL.

    • Table open cache instances: To improve scalability, the open tables cache can be partitioned into smaller segments. This value sets the number of such cache instances.

      The minimum value is 1 and the maximum value is 32. Defaults to 16.

      For more information, see the documentation for MySQL.

    • Thread cache size: The number of threads that are cached to handle new connections. When establishing a new connection, threads from the cache are reused first and only then new threads are created. Increase this value to improve performance if you have a lot of new connections.

      The minimum value is 10 and the maximum value is 10000. Defaults to 10.

      For more information, see the documentation for MySQL.

    • Thread stack: The stack size (in bytes) for each thread. The default value is large enough for normal operation of the MySQL server. If the thread stack size is too small, it limits the complexity of SQL statements, the recursion depth of stored procedures, and other memory-consuming actions.

      The minimum value is 131072 (128 KB) and the maximum value is 16777216 (16 MB). Defaults to 196608 (192 KB).

      For more information, see the documentation for MySQL.

    • Tmp table size: The maximum size of in-memory temporary tables (in bytes). If a table exceeds this limit, it's converted to an on-disk temporary table. This setting doesn't affect user-created MEMORY tables. Increase this value if you run many advanced GROUP BY queries and your cluster hosts have enough RAM.

      The minimum value is 1024 (1 KB) and the maximum value is 134217728 (128 MB). Defaults to 16777216 (16 MB).

      For more information, see the documentation for MySQL.

    • Transaction isolation: The default transaction isolation level:

      • READ-COMMITTED: A query only sees the strings that were committed before it is run.
      • REPEATABLE-READ: All queries in the current transaction only see the strings that were committed before the first query to select and update data that was executed in this transaction.
      • SERIALIZABLE: Same as REPEATABLE-READ, except that InnoDB implicitly converts SELECT statements to SELECT ... FOR SHARE if the autocommit mode is disabled. If autocommit is on, a SELECT is executed in its own transaction in read only mode and can be serialized.

      For more information, see the documentation for MySQL.

13. Click Create cluster.

To create a cluster with a single host, you should pass a single --host parameter.

Let's say we need to create a MySQL cluster with the following characteristics:

  - Named `my-mysql`.

• Version 8.0.
• In the production environment.
• In the default network.
• With one s2.micro host in the b0rcctk2rvtr8efcch64 subnet, in the ru-central1-c availability zone.
• With 20 GB fast network storage (network-ssd).
• With one user (user1) with the password user1user1.
• With 1 db1 database, in which user1 has full rights (the same as GRANT ALL PRIVILEGES on db1.*).

1. Run the command to create a cluster:

   yc managed-mysql cluster create \
      --name="my-mysql" \
      --mysql-version 8.0 \
      --environment=production \
      --network-name=default \
      --host zone-id=zone-id=ru-central1-c,subnet-id=b0rcctk2rvtr8efcch63 \
      --resource-preset s2.micro \
      --disk-type network-ssd \
      --disk-size 20 \
      --user name=user1,password="user1user1" \
      --database name=db1
   

2. Run the command to change the user1 user's permissions.

   yc managed-mysql user grant-permission user1 \
      --cluster-name="my-mysql" \
      --database=db1 \
      --permissions ALL