Contact UsTry it for free
© 2025 Direct Cursus Technology L.L.C.

MySQL® settings

Written by
Updated at December 10, 2025

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

Cluster-level DBMS settings

  • Audit log All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Manages MySQL® audit logging.

    By default, audit logging is disabled.

    For more information, see this MySQL® article.

  • Audit log policy Management console

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Defines which events will be written to the audit log:

    • ALL: All events.
    • LOGINS (default): Only login events.
    • QUERIES: Only query events.
    • NONE: None.

    The Audit log policy setting is only relevant if Audit log is enabled.

    For more information, see this MySQL® article.

  • Auto increment All interfaces

    Specifies the step between successive values in AUTO_INCREMENT columns.

    The valid values range from 1 to 65535. The default value is 1.

    For more information, see this MySQL® article.

  • Auto increment offset All interfaces

    Specifies the initial value for AUTO_INCREMENT columns. If greater than Auto increment, this setting is ignored.

    The valid values range from 1 to 65535. The default value is 1.

    For more information, see this MySQL® article.

  • Binlog cache size All interfaces

    Defines the cache size (in bytes) for binary log changes during a transaction.

    The valid values range from 4096 (4 KB) to 67108864 (64 MB). The default value is 32768 (32 KB).

    For more information, see this MySQL® article.

  • Binlog group commit sync delay All interfaces

    Specifies the delay before syncing the binary log to disk during a COMMIT. 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 valid values range from 0 (no delay) to 1000000 (one second). The default value is 0.

    For more information, see this MySQL® article.

  • Binlog row image All interfaces

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

    • FULL (default): Log all columns in both the before and after images.
    • MINIMAL: Log only columns required for identifying the rows to change in the before image and only columns updated by an SQL statement or affected by an auto increment in the after image.
    • NOBLOB: Log all columns in 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 this MySQL® article.

  • Binlog rows query log events All interfaces

    Specifies whether to write informational events, such as query log events, to the binary log.

    By default, this setting is disabled.

    For more information, see this MySQL® article.

  • Binlog transaction dependency tracking Management console CLI API

    Specifies how to compute logical timestamps, allowing replicas to identify transactions that can run concurrently. It is used to generate dependency information, which is written to the binary log when multi-threaded replication (Slave parallel workers) is enabled with the Slave parallel type set to LOGICAL_CLOCK.

    Possible values:

    • COMMIT_ORDER (default): Two transactions are considered independent if their commit time windows overlap.

    • WRITESET: This approach is based on COMMIT_ORDER. In addition to the above condition, two transactions are considered conflicting if they share a hash value in their write sets.

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

      • The transactions are dependent according to WRITESET.
      • The transactions committed within the same user session.

    For more information, see this MySQL® article.

  • Character set server All interfaces

    Specifies the character set the MySQL® cluster uses for processing data and communicating with MySQL® clients. It also determines how SQL string functions and other features work.

    The default value is utf8mb4.

    For more information, see this MySQL® article.

  • Collation server All interfaces

    Specifies the collation algorithm the MySQL® cluster uses when processing data and communicating with MySQL® clients. It also determines how SQL sorting and string functions, and other features work.

    The default value is utf8mb4_0900_ai_ci.

    For more information, see this MySQL® article.

  • Default authentication plugin All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Specifies the authentication plugin used by the Managed Service for MySQL® cluster:

    • 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 this MySQL® article.

  • Default time zone All interfaces

    Specifies the server time zone.

    The default value is Europe/Moscow.

    For more information, see this MySQL® article.

  • Explicit defaults for timestamp All interfaces

    Manages non-standard default values and NULL value processing in TIMESTAMP columns.

    By default, this setting is enabled, disables any non-standard behaviors.

    For more information, see this MySQL® article.

  • General log All interfaces

    Manages MySQL® general query logging.

    By default, it is disabled.

    For more information, see this MySQL® article.

  • Group concat max len All interfaces

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

    The valid values range from 4 to 33554432 (32 MB). The default value is 1024 (1 KB).

    For more information, see this MySQL® article.

  • Innodb adaptive hash index All interfaces

    Manages the InnoDB adaptive hash index. Depending on the database workload, disabling this index may improve performance. The MySQL® guides recommend running load testing on real data to determine whether to enable or disable it.

    By default, the adaptive hash index is enabled.

    For more information, see this MySQL® article.

  • Innodb buffer pool size All interfaces

    Warning

    If your cluster is running MySQL® 5.7, changing this setting will cause the cluster hosts to restart one at a time. MySQL® 8.0 cluster will restart only if you decrease the value of this setting.

    Specifies 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 when the same table data is accessed repeatedly.

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

    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 these configuration recommendations and thisMySQL® article.

  • Innodb change buffering All interfaces

    Determines which secondary index changes will be temporarily stored in the Innodb change buffer before being written to disk.

    Possible values:

    • none: Do not store changes.
    • inserts: Store changes resulting from insert operations.
    • deletes: Store changes resulting from delete operations.
    • changes: Store changes resulting from insert and delete operations.
    • purges: Store changes resulting from background delete operations.
    • all: Store all changes. This is a default value.

    For more information, see this MySQL® article.

  • Innodb compression level Management console CLI API

    Sets the zlib compression level used for InnoDB compressed tables and indexes.

    The valid values range from 0 to 9. The default value is 6.

    For more information, see this MySQL® article.

  • Innodb flush log at trx commit All interfaces

    Determines MySQL® log-flushing 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. If the system crashes, transactions whose logs were not flushed to disk may be lost.

    For more information, see this MySQL® article.

  • Innodb ft max token size All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

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

    The valid values range from 10 to 84. The default value is 84.

    For more information, see this MySQL® article.

  • Innodb ft min token size All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

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

    The valid values range from 0 to 16. The default value is 3.

    For more information, see this MySQL® article.

  • Innodb io capacity All interfaces

    Specifies the number of I/O operations per second (IOPS) for all InnoDB background operations. 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 valid values range from 100 to 100000. The default value is 200.

    For more information, see this MySQL® article.

  • Innodb io capacity max All interfaces

    Sets the upper IOPS limit for all InnoDB background operations. If the host falls behind on flushing, InnoDB may start flushing data to disk at a higher rate than defined by the Innodb io capacity but within this upper IOPS limit.

    The valid values range from 100 to 100000. The default value is 2000.

    For more information, see this MySQL® article.

  • Innodb lock wait timeout All interfaces

    Specifies the maximum time (in seconds) an InnoDB transaction waits for a row lock. Upon timeout, the system returns an error and the current SQL statement (not the entire transaction) is rolled back.

    You can decrease this value for OLTP applications and interactive user apps. You can increase this value if your application runs lengthy operations, such as large INSERT or UPDATE queries awaiting completion when transforming large datasets from the storage system.

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

    For more information, see this MySQL® article.

  • Innodb log buffer size All interfaces

    Sets the size of the buffer (in bytes) that InnoDB uses when writing logs to disk. With a larger buffer, InnoDB can process larger transactions without disk flushes prior to commit, thus lowering I/O load.

    The valid values range from 1048576 (1 MB) to 268435456 (256 MB). The default value is 16777216 (16 MB).

    For more information, see this MySQL® article.

  • Innodb log file size All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Sets the size of a single InnoDB redo log file (in bytes). A larger size decreases checkpoint flushes to disk, thus saving I/O resources, yet results in slower recovery after failures.

    The valid values range from 268435456 (256 MB) to 4294967296 (4 GB). The default value is 268435456 (256 MB).

    For more information, see this MySQL® article.

  • Innodb lru scan depth Management console API

    Sets the number of free pages that InnoDB attempts to keep in each buffer pool to speed up page read and create operations.

    The valid values range from 100 to 4294967295. The default value is 1024.

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

    • Increase the value only if the cluster has spare I/O capacity under typical workloads.
    • Decrease the value if the system lacks resources with the current setting.

    In most cases, the default value works well.

    Refer to the Percona blog and MySQL® article, to learn:

  • Innodb numa interleave All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Manages the NUMA interleave policy for allocating memory to the InnoDB buffer pool.

    This policy is disabled by default.

    For more information, see this MySQL® article.

  • Innodb online alter log max size All interfaces

    Sets the size of temporary InnoDB log files (in bytes) used during online DDL operations. Increasing this value enables more concurrent DML operations during online DDL, but prolongs the final table lock required to apply the log data.

    The valid values range from 65536 (64 KB) to 107374182400 (100 GB). The default value is 134217728 (128 MB).

    For more information, see this MySQL® article.

  • Innodb page size All interfaces

    Specifies the page size (in bytes) for InnoDB tablespaces. You cannot edit this setting once the cluster is created.

    Allowed 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 this MySQL® article.

  • Innodb print all deadlocks All interfaces

    Determines whether InnoDB writes complete information about all deadlocks to the error log. If this setting is disabled, the SHOW ENGINE INNODB STATUS command will only show information about the last deadlock.

    By default, printing all deadlocks is disabled.

    For more information, see this MySQL® article.

  • Innodb purge threads All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Specifies the number of InnoDB I/O threads used for purge operations. Increasing this value can be useful for systems where DML operations (INSERT, UPDATE, or DELETE) affect multiple tables.

    The valid values range from 1 to 16. The default value is 4.

    For more information, see this MySQL® article.

  • Innodb read io threads All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

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

    The valid values range from 1 to 16. The default value is 4.

    For more information, see this MySQL® article.

  • Innodb status output Management console

    Manages the output of the standard InnoDB monitor.

    By default, the standard monitor is disabled.

    For more information, see this MySQL® article.

  • Innodb strict mode Management console

    Manages InnoDB strict mode. When this setting is enabled, specific conditions that are normally treated as warnings are considered errors.

    By default, InnoDB strict mode is enabled.

    For more information, see this MySQL® article.

  • Innodb temp data file max size All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Sets the maximum size of an InnoDB temporary tablespace (in bytes).

    The valid values range from 0 (no temporary tablespace is used) to 107374182400 (100 GB). The default value is 0.

    For more information, see this MySQL® article.

  • Innodb thread concurrency All interfaces

    Sets the maximum number of concurrent threads in InnoDB.

    The valid values range from 0 (no restrictions) to 1000. The default value is 0.

    For more information, see this MySQL® article.

  • Innodb write io threads All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

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

    The valid values range from 1 to 16. The default value is 4.

    For more information, see this MySQL® article.

  • Interactive timeout All interfaces

    Sets the activity wait interval (in seconds) for an interactive connection before the server terminates it.

    The valid values range from 600 (10 minutes) to 86400 (24 hours). The default value is 28800 (8 hours).

    For more information, see this MySQL® article.

  • Join buffer size All interfaces

    Sets the minimum buffer size (in bytes) used for:

    • Plain index scans.
    • Range index scans.
    • Full table scans (for JOIN operations hat do not use indexes).

    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 valid values range from 1024 (1 KB) to 16777216 (16 MB). The default value is 262144 (256 KB).

    For more information, see this MySQL® article.

  • Lock wait timeout Management console CLI API

    Specifies the timeout (in seconds) for an attempt to acquire a metadata lock.

    The valid values range from 1 to 31536000 (1 year). The default value is 31536000.

    For more information, see this MySQL® article.

  • 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 this MySQL® article.

  • Log slow filter Management console CLI API

    Specifies the filter for the 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 to 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 appear in the slow query log.

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

    For more information, see this Percona article.

  • Log slow rate limit All interfaces

    Sets the proportion of queries that appear in the slow query log. Depending on the value of Log slow rate type, this setting applies to either individual queries or sessions.

    If its value is set to 1, each query or session considered slow is logged. With other values, every Nth query or session is logged, reducing the cluster load.

    Queries that run longer than Slow query log always write time are always written to the slow query log regardless of the Log slow rate limit value.

    The valid values range from 1 to 1000. The default value is 1.

    For more information, see this Percona article.

  • Log slow rate type All interfaces

    Sets the type of logging for Log slow rate limit:

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

    The default value is query.

    For more information, see this Percona article.

  • Log slow sp statements All interfaces

    Manages logging of statements executed within stored procedures in the slow query log.

    By default, statement logging is enabled.

    For more information, see this Percona article.

  • Long query time All interfaces

    Sets the maximum execution time (in seconds) before the query is considered slow. Smaller values of this setting result in more statements being considered slow.

    The valid values range from 0 to 3600 (1 hour). The default value is 10.

    For more information, see this MySQL® article.

  • Lower case table names All interfaces

    Specifies how table names are stored and compared:

    • 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 once the cluster is created.

    For more information, see this MySQL® article.

  • Max allowed packet All interfaces

    Sets the maximum size (in bytes) of a single packet, row, or parameter sent by mysql_stmt_send_long_data().

    The default value is small to discard oversized packets. Increase the setting value when using large BLOB columns or long strings.

    The valid values range from 1024 (1 KB) to 1073741824 (1 GB). The default value is 16777216 (16 MB).

    For more information, see this MySQL® article.

  • Max connections All interfaces

    Sets the maximum number of simultaneous client connections supported by a MySQL® host.

    The minimum value is 10. The maximum and default values depend on the selected host class and are calculated as follows:

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

    For more information, see this MySQL® article.

  • Max digest length Management console

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Sets the memory allocation (in bytes) used for computing normalized statement digests.

    The valid values range from 1024 (1 KB) to 8192 (8 KB). The default value is 1024 (1 KB).

    For more information, see this MySQL® article.

  • Max execution time All interfaces

    Sets the execution timeout (in milliseconds) for SELECT statements.

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

    For more information, see this MySQL® article.

  • Max heap table size All interfaces

    Sets the maximum size of user-created MEMORY tables (in bytes). Changing this setting has no effect on any existing MEMORY table. It also works with Tmp table size for internal in-memory tables.

    The valid values range from 16384 (16 KB) to 134217728 (128 MB). The default value is 16777216 (16 MB).

    For more information, see this MySQL® article.

  • Max prepared stmt count Management console CLI API

    Sets the maximum number of prepared statements on the server.

    This limit helps protect the server from DoS attacks that overflow server memory with prepared statements.

    The valid values range from 0 to 4194304. The default value is 16382.

    For more information, see this MySQL® article.

  • Max sp recursion depth Management console CLI API

    Sets the maximum number of recursive calls allowed for stored procedures.

    The valid values range from 0 (recursion is disabled) to 255. The default value is 0.

    For more information, see this MySQL® article.

  • Mdb force SSL All interfaces

    Enables the require_secure_transport setting for all cluster hosts, restricting access to SSL or TLS connections.

    This setting is disabled by default.

    For more information, see this MySQL® article.

  • Mdb offline mode disable lag All interfaces

    Specifies the replication lag (in seconds) before switching MySQL® to offline_mode = OFF. Make sure it is less than Mdb offline mode enable lag.

    The valid values range from 60 (1 minute) to 86400 (24 hours). The default value is 300 (5 minutes).

  • Mdb offline mode enable lag All interfaces

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

    The valid values range from 600 (10 minutes) to 432000 (5 days). The default value is 86400 (24 hours).

  • Mdb preserve binlog bytes All interfaces

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

    The valid values range from 1073741824 (1 GB) to 107374182400 (100 GB). The default value is 1073741824 (1 GB).

  • Mdb priority choice max lag All interfaces

    Specifies the replication lag (in seconds) before a host is promoted from replica to master. Make sure it is less than Mdb offline mode enable lag.

    The valid values range from 0 to 86400 (24 hours). The default value is 60 (1 minute).

  • Net read timeout All interfaces

    Sets the maximum number of seconds to wait for more data from a network connection before aborting the read.

    The valid values range from 1 to 1200 (20 minutes). The default value is 30.

    For more information, see this MySQL® article.

  • Net write timeout All interfaces

    Sets the maximum number of seconds to wait for data to write to a network connection before aborting the write.

    The valid values range from 1 to 1200 (20 minutes). The default value is 60.

    For more information, see this MySQL® article.

  • Optimizer search depth Management console CLI API

    Specifies the maximum depth of search performed by the query optimizer.

    A value larger than the number of relations in a query results in a better query plan, but takes longer to generate the plan. A value smaller than the number of relations in a query returns an execution plan quicker, but the plan may be far from being optimal.

    The valid values range from 0 (the system will select the search depth automatically) to 62. The default value is 62.

    For more information, see this MySQL® article.

  • Optimizer switch Management console CLI API

    Sets a list of flags, each enabling or disabling a specific optimizer behavior.

    For more information, see this MySQL® article.

  • Range optimizer max mem size All interfaces

    Sets the limit (in bytes) on memory consumption for the range optimizer.

    The valid values range from 1048576 (1 MB) to 268435456 (256 MB). The default value is 8388608 (8 MB).

    For more information, see this MySQL® article.

  • Regexp time limit All interfaces

    Sets 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 valid values range from 0 (no limit) to 1048576. The default value is 0.

    This feature is only supported by MySQL® 8.0.

    For more information, see this MySQL® article.

  • Replication optimize for static plugin config Management console API

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

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

    Enable it along with Replication sender observe commit only to improve cluster performance even with a large number of replicas.

    This setting is disabled by default.

    For more information, see this MySQL® article.

  • Replication sender observe commit only Management console API

    Enables a limit on callbacks for semi-sync replication. If disabled, the number of locks and contending replicas increases. This results in decreased cluster performance.

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

    Enable it along with Replication optimize for static plugin config to improve cluster performance even with a large number of replicas.

    This setting is disabled by default.

    For more information, see this MySQL® article.

  • Rpl semi sync master wait for slave count All interfaces

    Sets the number of replicas the master must get responses from before committing a transaction in semi-synchronous replication.

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

    For more information, see this MySQL® article.

  • Slave parallel type All interfaces

    Specifies the policy used to decide which transactions are allowed to run in parallel on the replica when multi-threaded replication is enabled with 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 this MySQL® article.

  • Slave parallel workers All interfaces

    Sets the number of threads for executing replication transactions in parallel on the replica.

    The minimum value is 0 (multi-threading on the replica is disabled), and the maximum, 64. The default value is 8.

    For more information, see this MySQL® article.

  • Slow query log All interfaces

    Enables or disables slow query logging. A query is considered slow if its execution time exceeds the value set in Long query time.

    Allowed values:

    • 0 or OFF
    • 1 or ON

    The default value is OFF.

    For more information, see this MySQL® article.

  • Slow query log always write time All interfaces

    Specifies the query execution time (in seconds) after which a query is always written to the slow query log regardless of the Log slow rate limit.

    The valid values range from 0 to 3600 (1 hour). The default value is 10.

    For more information, see this Percona article.

  • Sort buffer size All interfaces

    Sets the size of the buffer (in bytes) used for in-memory sorts.

    The valid values range from 32768 (32 KB) to 16777216 (16 MB). The default value is 262144 (256 KB).

    For more information, see this MySQL® article.

  • Sql mode All interfaces

    MySQL® cluster SQL modes:

    • ALLOW_INVALID_DATES: Bypasses full date validation. In this mode, the server only 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 with a warning.

    • 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 is parsed as (NOT a) BETWEEN b AND c rather than NOT (a BETWEEN b AND c).

    • IGNORE_SPACE: Allows spaces between a function name and the ( character. As a result, built-in function names are treated as keywords, and object IDs that match these 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: Ignores the INDEX DIRECTORY and DATA DIRECTORY directives during table creation.

    • NO_ENGINE_SUBSTITUTION: Disables automatically using the default storage engine if the engine specified in CREATE TABLE or ALTER TABLE is unavailable, returning an error instead.

    • NO_UNSIGNED_SUBTRACTION: Allows negative results when subtracting integers, even if one of them 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 a zero month or day are not allowed. If you try to insert them, you will get an error.

      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: Ensures that 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: Enables strict SQL mode for all storage engines.

    • STRICT_TRANS_TABLES: Enables strict SQL mode for all transactional storage engines and, when possible, for non-transactional ones.

    • 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 rather than truncation.

    • ANSI: Combination of the following modes:

      • REAL_AS_FLOAT
      • PIPES_AS_CONCAT
      • ANSI_QUOTES
      • IGNORE_SPACE
      • ONLY_FULL_GROUP_BY

    • TRADITIONAL: 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 SQL modes are 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 this MySQL® article.

  • SQL require primary key All interfaces

    Prohibits creating or importing tables without a primary key as well as removing the key from the tables.

    This setting is disabled by default.

    For more information, see this MySQL® article.

  • Sync binlog All interfaces

    Determines how often the server synchronizes the binary log to disk:

    • 0: Synchronization is disabled. MySQL® relies on the operating system to flush the binary log to disk from time to time, as it does for any other file. This approach provides the best performance. However, data may be lost in the event of a power failure or operating system crash: transactions may be committed but not yet synced with the binary log.
    • 1: Binary log is synchronized to disk before transactions are committed. This is the safest option, 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 only in prepared state. This allows you to perform automatic recovery and roll back the transactions, ensuring that no transaction is lost from the binary log.
    • N: Binary log is synchronized to disk after collecting N binary log commit groups. In the event of a power failure or operating system crash, transactions may be committed but not yet synced to the binary log. This approach 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 valid values range from 0 to 4096. The default value is 1.

    For more information, see this MySQL® article.

  • Table definition cache All interfaces

    Sets the number of table definitions that can be stored in the definition cache. If your database has a large number of tables, increase this setting to speed up opening of tables.

    The valid values range from 400 to 524288. The default value is 2000.

    For more information, see this MySQL® article.

  • Table open cache All interfaces

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

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

    The valid values range from 400 to 524288. The default value is 4000.

    For more information, see this MySQL® article.

  • Table open cache instances All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    To improve scalability, the open table cache can be partitioned into smaller cache instances. This value sets the number of such cache instances.

    The valid values range from 1 to 32. The default value is 16.

    For more information, see this MySQL® article.

  • Thread cache size All interfaces

    Sets the number of threads that are cached to process 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 valid values range from 10 to 10000. The default value depends on the selected host class and is calculated as follows:

    max_connections / 10

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

    For more information, see this MySQL® article.

  • Thread stack All interfaces

    Warning

    Changing this setting will cause the cluster hosts to restart one at a time.

    Sets the stack size (in bytes) for each thread. The default value is large enough for normal MySQL® operation. Setting it too small can limit the complexity of SQL statements, stored procedure recursion depth, and other memory-consuming features.

    The valid values range from 131072 (128 KB) to 16777216 (16 MB). The default value is 196608 (192 KB).

    For more information, see this MySQL® article.

  • Tmp table size All interfaces

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

    The valid values range from 1024 (1 KB) to 134217728 (128 MB). The default value is 16777216 (16 MB).

    For more information, see this MySQL® article.

  • Transaction isolation All interfaces

    Sets 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 this MySQL® article.

  • Wait timeout All interfaces

    Sets the activity wait interval (in seconds) for an non-interactive connection before the server terminates it.

    The valid values range from 600 (10 minutes) to 86400 (24 hours). The default value is 28800 (8 hours).

    For more information, see this MySQL® article.

Custom 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 this MySQL® article.

  • Administrative privileges Management console CLI Terraform

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

    You can grant the following administrative privileges to users:

    • REPLICATION CLIENT: Enables using the following statements:

    • REPLICATION SLAVE: Allows connections to the replication thread from outside Yandex Cloud. It enables using the following statements:

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

    • FLUSH OPTIMIZER COSTS: Enables using the FLUSH OPTIMIZER_COSTS operator.

    • SHOW ROUTINE: Enables viewing all definitions of stored procedures and functions in the information_schema.ROUTINES table (including those where a user with this privilege is not specified in the DEFINER field). It enables using the following statements:

    • MDB ADMIN: Includes the PROCESS, REPLICATION_CLIENT, REPLICATION_SLAVE, and FLUSH_OPTIMIZER_COSTS privileges. Additionally, it allows you to:

      • Run the KILL command for user queries.
      • Create and delete user databases and users.
      • Grant permissions for objects created by a user.

      Warning

      Objects created by a user with the MDB ADMIN privilege are not displayed in the user interface. Use this privilege to create temporary auxiliary databases and users.

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

  • Connection limits Management console CLI Terraform

    This section allows you to configure user restrictions.

    • Max connections per hour

      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

      Maximum number of queries 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

      Maximum number of UPDATE queries 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

      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 this MySQL® article.

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

Previous
User permissions
Next
SQL command limits