MySQL® settings

For Managed Service for MySQL® clusters, you can configure MySQL® settings. Some settings are configured at the cluster level, while others, at the user level.

The label next to the setting name shows which interface can be used to set the value of this setting: the management console, CLI, API, or Terraform. The All interfaces label means that all of the above interfaces are supported.

Depending on the selected interface, the same setting may be represented in a different way. For example, Innodb buffer pool size in the management console matches:

• innodb_buffer_pool_size in gRPC, CLI, or Terraform.
• innodbBufferPoolSize in the REST API.

How settings depend on the host classHow settings depend on the host class

The values of some MySQL® settings can be automatically adjusted when you change the host class:

• If the values were not specified or are not suitable for the new class, the default settings for this class will be applied.
• If the settings you specified manually are suitable for the new class, they will not change.

The settings that depend on the host class are:

• Innodb buffer pool size.
• Max connections.
• Thread cache size.

Cluster-level DBMS settingsCluster-level DBMS settings

• Audit log All interfaces

  Manages writing the MySQL® audit log.

  By default, this setting is disabled.

  For more information, see the MySQL® documentation.

• Auto increment All interfaces

  Sets the interval between the values of AUTO_INCREMENT columns.

  The minimum value is 1, and the maximum value is 65535. The default value is 1.

  For more information, see the MySQL® documentation.

• Auto increment offset All interfaces

  Sets the initial value for AUTO_INCREMENT columns. This setting is ignored if its value is greater than Auto increment.

  The minimum value is 1, and the maximum value is 65535. The default value is 1.

  For more information, see the MySQL® documentation.

• Binlog cache size All interfaces

  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). The default value is 32768 (32 KB).

  For more information, see the MySQL® documentation.

• Binlog group commit sync delay All interfaces

  Delays binary log synchronization with the disk when performing a COMMIT operation for the binary log. To synchronize more transactions with the 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). The default value is 0.

  For more information, see the MySQL® documentation.

• Binlog row image All interfaces

  Indicates how to write row images to the binary log for row-based replication:

  • FULL (default): Log all columns to both the before and after images.
  • MINIMAL: Log only those columns to the before image that are required to identify the rows to change; log only those columns in the after image where the value was set with an SQL statement or auto increment operation.
  • NOBLOB: Log all columns to the before and after images (same as FULL), except for BLOB and TEXT columns that either have not changed or are not required to identify rows.

  For more information, see the MySQL® documentation.

• Binlog rows query log events All interfaces

  Controls logging information events (for example, query log events) in the binary log.

  By default, this setting is disabled.

  For more information, see the MySQL® documentation.

• Binlog transaction dependency tracking Management console CLI API

  The schema to calculate logical temporary labels that help replicas determine which transactions can be executed concurrently. It is used for generating dependency information, which is written to a binary log when multithreaded replication (Slave parallel workers) with the LOGICAL_CLOCK Slave parallel type policy is enabled.

  Possible values:

  • COMMIT_ORDER (default): Two transactions are considered independent if the period of committing the first transaction overlaps with the period of committing the second one.

  • WRITESET: Schema is based on COMMIT_ORDER. Additionally, two transactions are considered conflicting if a value (hash) appears in the record sets of both transactions.

  • WRITESET_SESSION: Two transactions are considered dependent if at least one of these conditions is met:

    • The transactions are dependent according to the WRITESET schema.
    • The transactions were committed during one user session.

  For more information, see the MySQL® documentation.

• Character set server All interfaces

  The character set used by the MySQL® cluster when working with data and exchanging information with MySQL® clients. This choice affects the performance of SQL functions for manipulating strings and other features.

  The default value is utf8mb4.

  For more information, see the MySQL® documentation.

• Collation server All interfaces

  The algorithm for collating characters used by the MySQL® cluster 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 features.

  The default value is utf8mb4_0900_ai_ci.

  For more information, see the MySQL® documentation.

• Default authentication plugin All interfaces

  The authentication plugin used in Managed Service for MySQL® clusters:

  • mysql_native_password: Authentication method used in MySQL® prior to authentication plugins.
  • sha256_password: Authentication using SHA-256 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 MySQL® documentation.

• Default time zone All interfaces

  The server time zone.

  The default value is Europe/Moscow.

  For more information, see the MySQL® documentation.

• Explicit defaults for timestamp All interfaces

  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 MySQL® documentation.

• General log All interfaces

  Controls writing the MySQL® general query log.

  By default, this setting is disabled.

  For more information, see the MySQL® documentation.

• Group concat max len All interfaces

  The maximum result length (in bytes) for the GROUP_CONCAT() function.

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

  For more information, see the MySQL® documentation.

• Innodb adaptive hash index All interfaces

  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 MySQL® documentation.

• Innodb buffer pool size All interfaces

  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 134217728 (128 MB). The maximum and default values depend on the selected host class and are set according to the table:

  Amount of GB RAM on the host	Default value	Maximum value
  2	268435456 (0.25 GB)	536870912 (0.5 GB)
  4	1610612736 (1.5 GB)	2684354560 (2.5 GB)
  ≥ 8	0.5 × RAM	0.8 × RAM

  For more information, see the configuration recommendations and MySQL® documentation.

• Innodb compression level Management console CLI API

  The zlib compression level used for InnoDB compressed tables and indexes.

  The minimum value is 0, and the maximum value is 9. The default value is 6.

  For more information, see the MySQL® documentation.

• Innodb flush log at trx commit All interfaces

  Determines MySQL® behavior for transaction COMMIT operations:

  • 1 (default): Full ACID compliance. Logs are written and flushed to disk at each transaction commit.
  • 2: Logs are written after each transaction is committed but flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

  For more information, see the MySQL® documentation.

• Innodb ft max token size All interfaces

  The maximum length of words stored in the InnoDB FULLTEXT index.

  The minimum value is 10, and the maximum value is 84. The default value is 84.

  For more information, see the MySQL® documentation.

• Innodb ft min token size All interfaces

  The minimum length of words stored in the InnoDB FULLTEXT index.

  The minimum value is 0, and the maximum value is 16. The default value is 3.

  For more information, see the MySQL® documentation.

• Innodb io capacity All interfaces

  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. The default value is 200.

  For more information, see the MySQL® documentation.

• Innodb io capacity max All interfaces

  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. The default value is 2000.

  For more information, see the MySQL® documentation.

• Innodb lock wait timeout All interfaces

  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 (not the entire transaction) is rolled back.

  You can decrease this value for OLTP applications and interactive user applications. You can increase this value if your application has lengthy operations, e.g., large INSERT or UPDATE queries awaiting completion when transforming large arrays of data from the data storage.

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

  For more information, see the MySQL® documentation.

• Innodb log buffer size All interfaces

  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). The default value is 16777216 (16 MB).

  For more information, see the MySQL® documentation.

• Innodb log file size All interfaces

  The size of a single InnoDB redo log file (in bytes). The larger the value, the less checkpoint 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). The default value is 268435456 (256 MB).

  For more information, see the MySQL® documentation.

• Innodb lru scan depth Management console API

  The number of free pages that InnoDB attempts to save in each buffer pool to accelerate page read and create operations.

  The minimum value is 100, and the maximum value is 4294967295. The default value is 1024.

  The higher the value, the more the setting affects the cluster performance and memory:

  • Increase the value only if the cluster has spare resources for data I/O under typical workload.
  • Decrease the value if you lack resources at the current value.

  In most cases, the default value is suitable.

  For more information, see the Percona blog and MySQL® documentation:

  • Calculating the setting value
  • Setting description

• Innodb numa interleave All interfaces

  Controls the NUMA Interleave policy for allocation of the InnoDB buffer pool.

  This policy is disabled by default.

  For more information, see the MySQL® documentation.

• Innodb online alter log max size All interfaces

  The size of temporary InnoDB log files (in bytes) received during online DDL queries. The greater value means more DML operations during DDL queries but a longer period after a DDL query during which a table is locked because log data is applied.

  The minimum value is 65536 (64 KB), and the maximum value is 107374182400 (100 GB). The default value is 134217728 (128 MB).

  For more information, see the MySQL® documentation.

• Innodb page size All interfaces

  The page size for InnoDB tablespace (in bytes). You cannot edit this setting after you create a cluster.

  Acceptable values: 4096 (4 KB), 8192 (8 KB), 16384 (16 KB), 32768 (32 KB), and 65536 (64 KB). The default value is 16384 (16 KB).

  For more information, see the MySQL® documentation.

• Innodb print all deadlocks All interfaces

  Controls writing information about all deadlocks to the error log. If this setting is disabled, the SHOW ENGINE INNODB STATUS command will only output information about the last deadlock.

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

  For more information, see the MySQL® documentation.

• Innodb purge threads All interfaces

  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. The default value is 4.

  For more information, see the MySQL® documentation.

• Innodb read io threads All interfaces

  The number of InnoDB I/O threads used for read operations.

  The minimum value is 1, and the maximum value is 16. The default value is 4.

  For more information, see the MySQL® documentation.

• Innodb status output Management console

  Manages output of the standard InnoDB monitor.

  By default, the standard monitor is disabled.

  For more information, see the MySQL® documentation.

• Innodb strict mode Management console

  Manages the strict mode in Innodb. When enabled, specific conditions that are normally treated as warnings are considered errors.

  By default, InnoDB strict mode is enabled.

  For more information, see the MySQL® documentation.

• Innodb temp data file max size All interfaces

  The maximum size of InnoDB temporary tablespace (in bytes).

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

  For more information, see the MySQL® documentation.

• Innodb thread concurrency All interfaces

  The maximum number of concurrently executed InnoDB threads.

  The minimum value is 0 (no restrictions). The maximum value is 1000. The default value is 0.

  For more information, see the MySQL® documentation.

• Innodb write io threads All interfaces

  The number of InnoDB I/O threads used for write operations.

  The minimum value is 1, and the maximum value is 16. The default value is 4.

  For more information, see the MySQL® documentation.

• Interactive timeout All interfaces

  The period of time (in seconds) for which the server waits for activity during an interactive connection before terminating it.

  The minimum value is 600 (10 minutes), and the maximum value is 86400 (24 hours). The default value is 28800 (8 hours).

  For more information, see the MySQL® documentation.

• Join buffer size All interfaces

  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 indexes are 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). The default value is 262144 (256 KB).

  For more information, see the MySQL® documentation.

• Lock wait timeout Management console CLI API

  The maximum wait time (in seconds) when attempting to get a metadata lock.

  The minimum value is 1, the maximum value is 31536000 (1 year). The default value is 31536000.

  For more information, see the MySQL® documentation.

• Log error verbosity Management console

  Defines the type of events written to the error log:

  • 2: Errors and warnings.
  • 3 (default): Errors, warnings, and information messages.

  For more information, see the MySQL® documentation.

• Log slow filter Management console CLI API

  Filter for a slow query log by query type. It is a string with any combination of the following comma-separated values:

  • full_scan: Log queries that perform a full table scan.
  • full_join: Log queries that perform a FULL JOIN without indexes.
  • tmp_table: Log queries that create an implicit internal temporary table.
  • tmp_table_on_disk: Log queries that save a temporary table on the disk.
  • filesort: Log queries that perform file sorting.
  • filesort_on_disk: Log queries that perform sorting of disk files.

  If the filter value is set, only query types included in the filter will appear in the slow query log.

  The default value is "" (empty string, query filtering is disabled).

  For more information, see the Percona documentation.

• Log slow rate limit All interfaces

  The setting shows which part of queries will be included in the slow query log. Depending on the Log slow rate type setting value, the setting is applied to either individual queries (QUERY) or sessions (SESSION).

  If its value is set to 1, each query or session considered slow will be logged. With other values, every Nth query or cluster will be logged, which will reduce cluster load.

  If query processing time exceeds the value of Slow query log always write time, the query is written to the slow query log regardless of the value of Log slow rate limit.

  The minimum value is 1, and the maximum value is 1000. The default value is 1.

  For more information, see the Percona documentation.

• Log slow rate type All interfaces

  Sets the entry type for the slow query log to configure Log slow rate limit:

  • query: At the query level
  • session: At the session level

  The default value is query.

  For more information, see the Percona documentation.

• Log slow sp statements All interfaces

  Controls writing of expressions run by stored procedures to the slow query log.

  By default, the writing of expressions is enabled.

  For more information, see the Percona documentation.

• Long query time All interfaces

  If a query takes longer than this number of seconds, it is considered slow. The lower the value, the more queries will be considered slow.

  The minimum value is 0, and the maximum value is 3600 (1 hour). The default value is 10.

  For more information, see the MySQL® documentation.

• Lower case table names All interfaces

  Determines the type of storage and comparison of table names:

  • 0 (default): Table names are saved with no changes. Comparisons are case-sensitive.
  • 1: Table names are saved in lowercase. Comparisons are case-insensitive.

  You cannot edit this setting after you create a cluster.

  For more information, see the MySQL® documentation.

• Max allowed packet All interfaces

  The maximum size (in bytes) of a package, string, or parameters 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 are using large BLOB columns or long strings.

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

  For more information, see the MySQL® documentation.

• Max connections All interfaces

  The maximum number of simultaneous connections permitted for MySQL® cluster hosts.

  The minimum value is 10. The maximum and default values depend on the selected host class and are set using these formulas:

  • Maximum value: <number_of_MB_RAM_per_host> / 8.
  • Default value: <number_of_MB_RAM_per_host> / 32, but not less than 100.

  For more information, see the MySQL® documentation.

• Max digest length Management console

  The amount of memory (in bytes) allocated for the calculation of normalized statement digests.

  The minimum value is 1024 (1 KB), and the maximum value is 8192 (8 KB). The default value is 1024 (1 KB).

  For more information, see the MySQL® documentation.

• Max execution time All interfaces

  The timeout for running SELECT SQL queries in milliseconds.

  The minimum value is 0. In this case, there is no limit on query runtime. The maximum value is 4294967295.

  For more information, see the MySQL® documentation.

• Max heap table size All interfaces

  The maximum size of user-created MEMORY tables (in bytes). Changing this setting has no effect on any existing MEMORY table. It is 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). The default value is 16777216 (16 MB).

  For more information, see the MySQL® documentation.

• Max prepared stmt count Management console CLI API

  Maximum number of prepared requests on the server.

  This option can be used for protecting from DoS attacks that overflow the server memory by a large number of prepared requests.

  The minimum value is 0, and the maximum value is 4194304. The default value is 16382.

  For more information, see the MySQL® documentation.

• Max sp recursion depth Management console CLI API

  The maximum number of recursive invocations of a stored procedure.

  The minimum value is 0 (recursion is disabled). The maximum value is 255. The default value is 0.

  For more information, see the MySQL® documentation.

• Mdb offline mode disable lag All interfaces

  The replication lag (in seconds) before switching MySQL® to offline_mode = OFF. The value must be less than the Mdb offline mode enable lag value.

  The minimum value is 60 (1 minute), and the maximum value is 86400 (24 hours). The default value is 300 (5 minutes).

• Mdb offline mode enable lag All interfaces

  The replication lag (in seconds) before switching MySQL® to offline_mode = ON so that users get up-to-date data.

  The minimum value is 600 (10 minutes), and the maximum value is 432000 (5 days). The default value is 86400 (24 hours).

• Mdb preserve binlog bytes All interfaces

  The size of the binary logs (in bytes) to store on cluster hosts.

  The minimum value is 1073741824 (1 GB), and the maximum value is 107374182400 (100 GB). The default value is 1073741824 (1 GB).

• Mdb priority choice max lag All interfaces

  The replication lag (in seconds) before switching the cluster host role from replica to master. The value must be less than the Mdb offline mode enable lag value.

  The minimum value is 0, and the maximum value is 86400 (24 hours). The default value is 60 (1 minute).

• Net read timeout All interfaces

  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). The default value is 30.

  For more information, see the MySQL® documentation.

• Net write timeout All interfaces

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

  For more information, see the MySQL® documentation.

• Optimizer search depth Management console CLI API

  Maximum depth of search performed by the query optimizer.

  If the setting value is higher than the number of relationships in the query, a better query execution plan will be created; it will, however, require more time to create such a plan. If the value is lower, the query execution plan will be generated faster, although it may not be perfect.

  The minimum value is 0 (the system will select the search depth automatically), and the maximum value is 62. The default value is 62.

  For more information, see the MySQL® documentation.

• Optimizer switch Management console CLI API

  String with a set of flags. Each flag enables or disables certain optimizer behavior.

  For more information, see the MySQL® documentation.

• Range optimizer max mem size All interfaces

  The maximum memory consumption (in bytes) by the range optimizer.

  The minimum value is 1048576 (1 MB), and the maximum value is 268435456 (256 MB). The default value is 8388608 (8 MB).

  For more information, see the MySQL® documentation.

• Regexp time limit All interfaces

  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. The default value is 0.

  For more information, see the MySQL® documentation.

• Replication optimize for static plugin config Management console API

  Allows replicas to use shared locks with semi-sync replication. If the setting is disabled, replicas use more locks. This results in increased contention between replicas attempting to get a lock and decreased cluster performance.

  The setting improves performance of replicas and replication source hosts, since they use the same lock mechanism.

  Enable this setting together with Replication sender observe commit only. This will improve cluster performance even with a large number of replicas.

  This setting is disabled by default.

  For more information, see the MySQL® documentation.

• Replication sender observe commit only Management console API

  Imposes a limit on callbacks for semi-sync replication. If you do not limit them, the number of locks and contending replicas will increase. This results in decreased cluster performance.

  The setting improves performance of replicas and replication source hosts, since they use the same lock mechanism.

  Enable this setting together with Replication optimize for static plugin config. This will improve cluster performance even with a large number of replicas.

  This setting is disabled by default.

  For more information, see the MySQL® documentation.

• Rpl semi sync master wait for slave count All interfaces

  The number of replicas the master must get a response from during semi-synchronous replication before performing a COMMIT on a transaction.

  The minimum value is 1, and the maximum value is 2. The default value is 1.

  For more information, see the MySQL® documentation.

• Slave parallel type All interfaces

  Specifies the policy used to decide which transactions are allowed to run 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 source binary log run in parallel on the replica.
  • DATABASE (default): Transactions that update different databases run in parallel.

  For more information, see the MySQL® documentation.

• Slave parallel workers All interfaces

  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. The default value is 8.

  For more information, see the MySQL® documentation.

• Slow query log All interfaces

  Permits logging slow queries. A query is considered slow if its execution time exceeds the Long query time setting value.

  Acceptable values:

  • 0 or OFF
  • 1 or ON

  The default value is OFF.

  For more information, see the MySQL® documentation.

• Slow query log always write time All interfaces

  Query execution time (in seconds) after which a query is unconditionally written to the slow query log ignoring the Log slow rate limit setting.

  The minimum value is 0, and the maximum value is 3600 (1 hour). The default value is 10.

  For more information, see the Percona documentation.

• Sort buffer size All interfaces

  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). The default value is 262144 (256 KB).

  For more information, see the MySQL® documentation.

• Sql mode All interfaces

  MySQL® cluster 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, such as 2004-04-31, are converted to 0000-00-00 and a warning is returned.

  • ANSI_QUOTES: Treat " quotes as identifier quotes and not string quotes. With this mode enabled, use single quotes (') rather than double quotes (") for strings.

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

  • HIGH_NOT_PRECEDENCE: Gives higher precedence to the NOT operator when parsing Boolean expressions. In this mode, the NOT a BETWEEN b AND c expression will be interpreted as (NOT a) BETWEEN b AND c instead of NOT (a BETWEEN b AND c).

  • IGNORE_SPACE: Allows 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 the AUTO_INCREMENT column 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 the 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 the use of the 0000-00-00 date:

    • If strict SQL mode is disabled, the 0000-00-00 date is allowed, and inserting it produces a warning.
    • If strict SQL mode is enabled, the 0000-00-00 date is not allowed. If you try to insert it, you will get an error.

    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 a zero month or day 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: Prohibits queries in which SELECT, HAVING, or ORDER BY refer to non-aggregated columns that are not included in GROUP BY (SQL-92 behavior).

  • 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 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 engines.

  • STRICT_TRANS_TABLES: Enable strict SQL mode for all transactional engines and, when possible, for non-transactional 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 MySQL® documentation.

• Sync binlog All interfaces

  Determines how often the binary log is synchronized to disk:

  • 0: Synchronization is disabled. MySQL® relies on the operating system to flush the binary log to the disk from time to time, as it does for any other file. This method provides the best performance. However, data might be lost in case of power failure or operating system crash, as transactions might be committed before they are synced to the binary log.
  • 1: The binary log is synchronized 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 case power fails or the operating system crashes, transactions might be committed before they are 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. The default value is 1.

  For more information, see the MySQL® documentation.

• Table definition cache All interfaces

  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. The default value is 2000.

  For more information, see the MySQL® documentation.

• Table open cache All interfaces

  The size of the open tables cache for all threads. If the value of Opened tables is large and you do not use FLUSH_TABLES often, increase the value of this setting.

  Increasing it requires increasing the number of file descriptors for the MySQL® cluster hosts.

  The minimum value is 400, and the maximum value is 524288. The default value is 4000.

  For more information, see the MySQL® documentation.

• Table open cache instances All interfaces

  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. The default value is 16.

  For more information, see the MySQL® documentation.

• Thread cache size All interfaces

  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. The default value depends on the selected host class and is determined by the formula:

  max_connections / 10
  

  Where max_connections is the default value of the Max connections setting for the selected host class.

  For more information, see the MySQL® documentation.

• Thread stack All interfaces

  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). The default value is 196608 (192 KB).

  For more information, see the MySQL® documentation.

• Tmp table size All interfaces

  The maximum size of in-memory temporary tables (in bytes). If a table exceeds this limit, it is converted to an on-disk temporary table. This setting does not affect user-created MEMORY tables. Increase its value if you run many complex GROUP BY queries and your hosts have enough RAM.

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

  For more information, see the MySQL® documentation.

• Transaction isolation All interfaces

  The default transaction isolation level:

  • READ-COMMITTED: Query only sees the strings committed before it is run.
  • REPEATABLE-READ: All queries in the current transaction only see the strings committed before running the first select or update data query within this transaction.
  • SERIALIZABLE: Similar to REPEATABLE-READ, except that InnoDB implicitly converts SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, SELECT remains in its own transaction in read only mode and can be serialized.

  For more information, see the MySQL® documentation.

• Wait timeout All interfaces

  The period of time (in seconds) for which the server waits for activity during a non-interactive connection before terminating it.

  The minimum value is 600 (10 minutes), and the maximum value is 86400 (24 hours). The default value is 28800 (8 hours).

  For more information, see the MySQL® documentation.

Custom settingsCustom settings

These settings apply at the individual user level.

• Authentication plugin Management console CLI Terraform

  Authentication plugin that defines user authentication policy.

  By default, it is not specified, and the Default authentication plugin option you select when creating a cluster applies.

  To learn more about authentication plugins, see the MySQL® documentation.

• Administrative privileges Management console CLI Terraform

  Administrative privileges are user permissions that apply at the level of the entire DB cluster.

  You can grant the following administrative privileges to users:

  • REPLICATION CLIENT: Enables use of statements:

    • SHOW MASTER STATUS: Provides information about the status of the master's binary logs.
    • SHOW REPLICA | SLAVE STATUS: Provides information about the status of the the replication thread basic parameters.
    • SHOW BINARY LOGS: Displays a list of binary logs on the cluster host.

  • REPLICATION SLAVE: Enables connections to the replication thread from outside Yandex Cloud. Enables use of statements:

    • SHOW REPLICAS | SHOW SLAVE HOSTS: Displays a list of currently registered replicas.
    • SHOW RELAYLOG EVENTS: Shows events in a replica relay log.
    • SHOW BINLOG EVENTS: Shows events in a binary log.

  • PROCESS: Allows to use the SHOW PROCESSLIST operator and view the status of data storage systems, e.g., SHOW ENGINE INNODB STATUS. In Managed Service for MySQL®, this privilege also grants permission to read mysql, performance_schema, and sys system database tables.

  By default, this setting is not specified, which means the user has no administrative privileges. You can also grant multiple privileges to users.

• Connection limits Management console CLI Terraform

  This section configures user restrictions.

  • Max connections per hour

    The maximum number of connections per hour allowed for the user.

    The minimum value is 0 (no limit).
    By default, this setting is not specified, which means there is no limit.

  • Max questions per hour

    The maximum number of requests per hour allowed for the user (except for queries executed within stored procedures).

    The minimum value is 0 (no limit).
    By default, this setting is not specified, which means there is no limit.

  • Max updates per hour

    The maximum number of UPDATE requests per hour allowed for the user.

    The minimum value is 0 (no limit).
    By default, this setting is not specified, which means there is no limit.

  • Max user connections

    The maximum number of simultaneous connections allowed for the user.

    The minimum value is 0 (no limit).
    By default, this setting is not specified and the max_user_connections global cluster setting applies.

  For more information, see the MySQL® documentation.

For information about managing user permissions to access DB objects, see User permissions.