MariaDB has many system variables that can be changed to suit your needs.
The full list of server variables are listed in the contents on this page, and most are described on this page, but some are described elsewhere:
See also the Full list of MariaDB options, system and status variables.
Most of these can be set with command line options and many of them can be changed at runtime.
There are a few ways to see the full list of server system variables:
SHOW VARIABLES;
mysqld --verbose --help
There are several ways to set server system variables:
shell> ./mysqld_safe --aria_group_commit="hard"
aria_group_commit = "hard"
SET GLOBAL aria_group_commit="hard";
By convention, server variables have usually been specified with an underscore in the configuration files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.
Variables that take a numeric size can either be specified in full, or with a suffix for easier readability. Valid suffixes are:
| Suffix | Description | Value |
|---|---|---|
| K | kilobytes | 1024 |
| M | megabytes | 10242 |
| G | gigabytes | 10243 |
| T | terabytes | 10244 (from MariaDB 10.3.3) |
| P | petabytes | 10245 (from MariaDB 10.3.3) |
| E | exabytes | 10246 (from MariaDB 10.3.3) |
The suffix can be upper or lower-case.
alter_algorithmALGORITHM for ALTER TABLE if no ALGORITHM clause is specified. The deprecated variable old_alter_table is an alias for this. COPY corresponds to the pre-MySQL 5.1 approach of creating an intermediate table, copying data one row at a time, and renaming and dropping tables. INPLACE requests that the operation be refused if it cannot be done natively inside a the storage engine. DEFAULT (the default) chooses INPLACE if available, and falls back to COPY. NOCOPY refuses to copy a table. INSTANT refuses an operation that would involve any other than metadata changes. --alter-algorithm=default enumerated DEFAULT DEFAULT, COPY, INPLACE, NOCOPY, INSTANT analyze_sample_percentage--analyze-sample-percentage=# numeric 100.000000 0 to 100 autocommit--autocommit[=#] boolean 1 automatic_sp_privileges--automatic-sp-privileges, --skip-automatic-sp-privileges boolean 1 back_log0, or the --autoset-back-log option is used, will be autoset to the lower of 900 and (50 + max_connections/5) (>= MariaDB 10.1.7). --back-log=# 900 and (50 + max_connections/5) (>= MariaDB 10.1.7) 150 or the value of max_connections (beween MariaDB 10.0.8 and MariaDB 10.1.7) 50 (<= MariaDB 10.0.8) basedir--basedir=path or -b path big_tablestmp_memory_table_size system variable. tmp_memory_table_size system variable to 0. sql_big_tables is a synonym. --big-tables boolean 0 bind_address--bind-address=addr string bulk_insert_buffer_size--bulk-insert-buffer-size=# numeric 8388608 0 to 4294967295 0 to 18446744073709547520 character_set_client--skip-character-set-client-handshake option. If the client does not request a character set, or requests a character set that the server does not support, the global value will be used. utf16, utf32 and ucs2 cannot be used as client character sets. string utf8 (>= MariaDB 5.5) character_set_connectionstring utf8 (>= MariaDB 5.5) character_set_databasestring latin1 character_set_filesystembinary, so no conversion takes place. This could be useful for statements such as LOAD_FILE() or LOAD DATA INFILE on system where multi-byte file names are use. --character-set-filesystem=name string binary character_set_resultsstring utf8 character_set_server--character-set-server string latin1 character_set_systemstring utf8 character_sets_dir--character-sets-dir=path check_constraint_checks0, will disable constraint checks, for example when loading a table that violates some constraints that you plan to fix later. --check-constraint-checks=[0|1] collation_connectionstring collation_databasecollation_server variable is used. This variable is dynamic, but should not be set manually, only by the server. string collation_server--collation-server=name string latin1_swedish_ci completion_typeNO_CHAIN or 0 (the default), there is no effect on commits and rollbacks. If set to CHAIN or 1, a COMMIT statement is equivalent to COMMIT AND CHAIN, while a ROLLBACK is equivalent to ROLLBACK AND CHAIN, so a new transaction starts straight away with the same isolation level as transaction that's just finished. If set to RELEASE or 2, a COMMIT statement is equivalent to COMMIT RELEASE, while a ROLLBACK is equivalent to ROLLBACK RELEASE, so the server will disconnect after the transaction completes. Note that the transaction completion type only applies to explicit commits, not implicit commits. --completion-type=name enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2) NO_CHAIN (>=MariaDB/MySQL 5.5.3), 0 (<= MariaDB/MySQL 5.5.2) 0, 1, 2 (All versions), NO_CHAIN, CHAIN, RELEASE (>=MariaDB/MySQL 5.5.3) concurrent_insertAUTO or 1, the default, MariaDB allows concurrent INSERTs and SELECTs for MyISAM tables with no free blocks in the data. If set to NEVER or 0, concurrent inserts are disabled. If set to ALWAYS or 2, concurrent inserts are permitted for all MyISAM tables, even those with holes, in which case new rows are added at the end of a table if the table is being used by another thread. If the --skip-new option is used when starting the server, concurrent_insert is set to NEVER. --concurrent-insert[=name] enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2) AUTO (>=MariaDB/MySQL 5.5.3), 1 (<= MariaDB/MySQL 5.5.2) 0, 1, 2 (All versions), AUTO, NEVER, ALWAYS (>=MariaDB/MySQL 5.5.3) FLUSH TABLES If you want it to affect also cached tables. connect_timeout--connect-timeout=# 10 core_filecore.${PID}, and it is usually written to the data directory. However, this can be changed. --core-file sets the value to ON. It cannot be disabled in the case of Windows >= MariaDB 10.4.3. --core-file ON OFF datadir--datadir=path or -h path date_formatdatetime_formatdebug--debug will write a trace of what mysqld is doing to /tmp/mysqld.trace. -#, --debug[=debug_options] string d:t:i:o,/tmp/mysqld.trace (Unix) or d:t:i:O,\mysqld.trace (Windows) debug_no_thread_alarm--debug-no-thead-alarm=# boolean OFF debug_syncstring OFF or ON - current signal signal name default_password_lifetime--default-password-lifetime=# 0 0 to 4294967295 default_regex_flags| Value | Pattern equivalent | Meaning |
| DOTALL | (?s) | . matches anything including NL |
| DUPNAMES | (?J) | Allow duplicate names for subpatterns |
| EXTENDED | (?x) | Ignore white space and # comments |
| EXTRA | (?X) | extra features (e.g. error on unknown escape character) |
| MULTILINE | (?m) | ^ and $ match newlines within data |
| UNGREEDY | (?U) | Invert greediness of quantifiers |
--default-regex-flags=value DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY default_storage_engine--default-storage-engine=name InnoDB (>= MariaDB 5.5), MyISAM (<MariaDB 5.3) default_table_type--default-table-type=name default_tmp_storage_engine--default-tmp-storage-engine=name enumeration default_week_format--default-week-format=# numeric 0 0 to 7 delay_key_writeON, the default, any DELAY KEY WRITEs are honored. The key buffer is then flushed only when the table closes, speeding up writes. MyISAM tables should be automatically checked upon startup in this case, and --external locking should not be used, as it can lead to index corruption. If set to OFF, DELAY KEY WRITEs are ignored, while if set to ALL, all new opened tables are treated as if created with DELAY KEY WRITEs enabled. --delay-key-write[=name] enumeration ON ON, OFF, ALL delayed_insert_limit--delayed-insert-limit=# numeric 100 1 to 4294967295 delayed_insert_timeout--delayed-insert-timeout=# numeric 300 delayed_queue_size--delayed-queue-size=# 1000 1 to 4294967295 disconnect_on_expired_password--disconnect-on-expired-password[={0|1}] OFF div_precision_increment4, so SELECT 2/15 would return 0.1333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333. --div-precision-increment=# numeric 4 0 to 30 encrypt_tmp_disk_tablesaria_used_for_temp_tables=ON is set. See Data at Rest Encryption and Enabling Encryption for Internal On-disk Temporary Tables. --encrypt-tmp-disk-tables[={0|1}] boolean OFF encrypt_tmp_files--encrypt-tmp-files[={0|1}] boolean OFF (>= MariaDB 10.1.7), ON (<= MariaDB 10.1.6) encryption_algorithmaes_cbc is the recommended one. See Table and Tablespace Encryption. --encryption-algorithm=value enum none none, aes_ecb, aes_cbc, aes_ctr enforce_storage_engineNO_ENGINE_SUBSTITUTION SQL_MODE has been set or not. If set (the default from MariaDB 10.1.7), the query will fail, while if not set, a warning will be returned and the table created according to the engine specified by this variable. The variable has a session scope, but is only modifiable by a user with the SUPER privilege. string none engine_condition_pushdownengine_condition_pushdown={on|off} flag in MariaDB 10.0.. Specifies whether the engine condition pushdown optimization is enabled. Since MariaDB 10.1.1, engine condition pushdown is enabled for all engines that support it. --engine-condition-pushdown boolean OFF (MariaDB 5.5) ON (<= MariaDB 5.3) eq_range_index_dive_limit0 (unlimited, the default), index dives are always used. --eq-range-index-dive-limit=# numeric 200 (>= MariaDB 10.4.3), 0 (<= MariaDB 10.4.2) 0 to 4294967295 error_countnumeric event_schedulerON or OFF, while DISABLED means it cannot be set at runtime. Setting the variable will cause a load of events if they were not loaded at startup. --event-scheduler[=value] enumeration OFF ON (or 1), OFF (or 0), DISABLED expensive_subquery_limit--expensive-subquery-limit=# numeric 100 0 upwards explicit_defaults_for_timestamp--explicit-defaults-for-timestamp=[={0|1}] bolean OFF external_userNULL if native MariaDB authentication is used. string NULL flushON, the server will synchronize all changes to disk after each statement. --flush boolean OFF flush_time--flush_time=# numeric 0 foreign_key_checks0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0. boolean 1 ft_boolean_syntax--ft-boolean-syntax=name string + -><()*:""&| ft_max_word_lenREPAIR TABLE table_name QUICK statement. See innodb_ft_max_token_size for the InnoDB equivalent. --ft-max-word-len=# numeric 84 10 ft_min_word_lenREPAIR TABLE table_name QUICK statement. See innodb_ft_min_token_size for the InnoDB equivalent. --ft-min-word-len=# numeric 4 1 ft_query_expansion_limit--ft-query-expansion-limit=# numeric 20 0 to 1000 ft_stopword_filestorage/myisam/ft_static.c file) is used. Stopwords can be disabled by setting this variable to '' (an empty string). If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_server_stopword_table for the InnoDB equivalent. --ft-stopword-file=file_name file name (built-in) general_logNONE, no logs will be written even if general_query_log is set to 1. --general-log boolean 0 general_log_file.log as a suffix. --general-log-file=file_name file name host_name.log group_concat_max_len--group-concat-max-len=# numeric 1048576 (1M) >= MariaDB 10.2.4 1024 (1K) <= MariaDB 10.2.3 4 to 4294967295 4 to 18446744073709547520 .
have_compressYES, otherwise it will be NO. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES. have_cryptYES, otherwise it will be set to NO. If set to NO, the ENCRYPT() function cannot be used. have_csvYES, otherwise will be set to NO. Removed in MariaDB 10.0, use the Information Schema PLUGINS table or SHOW ENGINES instead. have_dynamic_loadingYES, otherwise will be set to NO. have_geometryYES, otherwise will be set to NO. have_ndbclusterhave_partitioningYES, unless the --skip-partition option is used, in which case will be set to DISABLED. Will be set to NO otherwise. Removed in MariaDB 10.0 - SHOW PLUGINS should be used instead. have_profilingYES, otherwise will be set to NO. See SHOW PROFILES() and SHOW PROFILE(). have_query_cacheYES, otherwise will be set to NO. have_rtree_keysYES, otherwise will be set to NO. have_symlinkYES. NO. --symbolic-links option and the skip option prefix (i.e. --skip-symbolic-links), then the value will be DISABLED. INDEX DIRECTORY and DATA DIRECTORY table options. histogram_size--histogram-size=# numeric 254 (>= MariaDB 10.4.3), 0 (<= MariaDB 10.4.2) 0 to 255 histogram_typeSINGLE_PREC_HB - single precision height-balanced. DOUBLE_PREC_HB - double precision height-balanced. --histogram-type=value enumeration DOUBLE_PREC_HB (>= MariaDB 10.4.3), SINGLE_PREC_HB(<= MariaDB 10.4.2) SINGLE_PREC_HB, DOUBLE_PREC_HB host_cache_size0 disables the cache. Changing the value while the server is running causes an implicit FLUSH HOSTS, clearing the host cache and truncating the performance_schema.host_cache table. If you are connecting from a lot of different machines you should consider increasing. --host-cache-size=#. numeric 128 0 to 65536 hostnamestring identityidle_readonly_transaction_timeout0, the default, connections are never killed. See also idle_transaction_timeout, idle_write_transaction_timeout and Transaction Timeouts. numeric 0 0 to 31536000 idle_transaction_timeout0, the default, connections are never killed. See also idle_readonly_transaction_timeout, idle_write_transaction_timeout and Transaction Timeouts. numeric 0 0 to 31536000 idle_write_transaction_timeout0, the default, connections are never killed. See also idle_transaction_timeout, idle_readonly_transaction_timeout and Transaction Timeouts. Called idle_readwrite_transaction_timeout until MariaDB 10.3.2. numeric 0 0 to 31536000 ignore_db_dirs--ignore-db-dirs=dir. string in_transaction1 if a transaction is in progress, 0 if not. boolean 0 init_connect--init-connect=name string init_fileinit-file=file_name file name insert_idnumeric interactive_timeout--interactive-timeout=# numeric 28800 1 to 2147483 1 to 31536000 join_buffer_size--join-buffer-size=# numeric 262144 (256kB) (>=MariaDB 10.1.7), 131072 (128kB) (<=MariaDB 10.1.6) 128 to 18446744073709547520 8228 to 18446744073709547520 join_buffer_space_limit--join-buffer-space-limit=# numeric 2097152 2048 to 99999999997952 join_cache_level--join-cache-level=# numeric 2 0 to 8 keep_files_on_create0, the default, if MariaDB finds another .MYD file in the database directory it will overwrite it. Setting this variable to 1 means that MariaDB will return an error instead, just as it usually does in the same situation outside of the database directory. The same applies for .MYI files and no INDEX DIRECTORY option. --keep-files-on-create=# boolean OFF large_files_supportlarge_page_size/proc/meminfo. See large_pages. numeric large_pages--large-pages or disabled with --skip-large-pages. Large pages are used for the innodb buffer pool and for online DDL (of size 3* innodb_sort_buffer_size (or 6 when encryption is used)). To use large pages, the Linux sysctl variable kernel.shmmax must be large than the llocation. Also the sysctl variable vm.nr_hugepages multipled by large-page) must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equalivent in /etc/security/limits.conf / or in systemd LimitMEMLOCK). If these operating system controls or insufficient free huge pages are available, the allocation of large pages will fall back to conventional memory allocation and a warning will appear in the logs. Only allocations of the default Hugepagesize currently occur (see /proc/meminfo). --large-pages, --skip-large-pages boolean OFF last_insert_idnumeric lc_messagesen_US by default, which means that error messages are in English by default. lc_messages_dir system variable to construct the path to the error messages file. --lc-messages=name string en_us lc_messages_dirlc_messages system variable . --lc-messages-dir=path directory name lc_time_names--lc-time-names=name string en_US licenseGPL. string local_infile1, LOCAL is supported for LOAD DATA INFILE statements. If set to 0, usually for security reasons, attempts to perform a LOAD DATA LOCAL will fail with an error message. --local-infile=# boolean ON lock_wait_timeout0 (from MariaDB 10.3.0) means no wait. See WAIT and NOWAIT. --lock-wait-timeout=# numeric 86400 (1 day) >= MariaDB 10.2.4 31536000 (1 year) <= MariaDB 10.2.3 0 to 31536000 (>= MariaDB 10.3) 1 to 31536000 (<= MariaDB 10.2) locked_in_memory--memlock boolean OFF log-l [filename] or --log[=filename] string OFF MySQL 5.1.29 and replaced by general_log MariaDB 10.0 log_disabled_statements--log-disabled_statements=value set slave and/or sp log_errorhostname.err. If a configuration file sets --log-error, one can reset it with --skip-log-error (useful to override a system wide configuration file). --log-error[=name], --skip-log-error file name log_outputFILE), it can also be stored in the general_log and slow_log tables in the mysql database (TABLE), or not stored at all (NONE). More than one option can be chosen at the same time, with NONE taking precedence if present. Logs will not be written if logging is not enabled. See Writing logs into tables, and the slow_query_log and general_log server system variables. --log-output=name set FILE TABLE, FILE or NONE log_queries_not_using_indexes--log-queries-not-using-indexes boolean OFF log_slow_admin_statements--log-slow-admin-statements boolean ON (>= MariaDB 10.2.4) OFF (<= MariaDB 10.2.3) log_slow_disabled_statements--log-slow-disabled_statements=value set sp admin, call, slave and/or sp log_slow_filteradmin log administrative queries (create, optimize, drop etc...) filesort logs queries that use a filesort. filesort_on_disk logs queries that perform a a filesort on disk. filesort_priority_queue (from MariaDB 10.3.2) full_join logs queries that perform a join without indexes. full_scan logs queries that perform full table scans. query_cache log queries that are resolved by the query cache . query_cache_miss logs queries that are not found in the query cache. tmp_table logs queries that create an implicit temporary table. tmp_table_on_disk logs queries that create a temporary table on disk. log-slow-filter=value1[,value2...] enumeration admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk (<= MariaDB 10.3.0) admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk (>= MariaDB 10.3.1) admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk log_slow_queries--log-slow-queries[=name] boolean OFF log_slow_rate_limit1, or every query, while setting it to 20 would log every 20 queries, or five percent. Aims to reduce I/O usage and excessively large slow query logs. See also Slow Query Log Extended Statistics. log-slow-rate-limit=# numeric 1 1 upwards log_slow_verbosityquery_plan logs query execution plan information innodb an unused Percona XtraDB option for logging XtraDB/InnoDB statistics. explain prints EXPLAIN output in the slow query log. See EXPLAIN in the Slow Query Log. (added in MariaDB 10.0.5) log-slow-verbosity=value1[,value2...] enumeration query_plan, innodb, explain (from MariaDB 10.0.5) log_tc_size--log-tc server option and the --tc-heuristic-recover option. log-tc-size=# numeric 24576 12288 to 18446744073709551615 log_warnings0 disables additional warning logging. Note that this does not prevent all warnings, there is a core set of warnings that will always be written to the error log. The additional warnings are as follows: --user master.info file or the file that is configured by the master_info_file option. relay-log.info file or the file that is configured by the relay_log_info_file system variable. KILL. completion_type is set to RELEASE. -W [level] or --log-warnings[=level] numeric 2 (>= MariaDB 10.2.4) 1 (<= MariaDB 10.2.3) 0 to 4294967295
long_query_time--long-query-time=# numeric 10.000000 >= MariaDB 10.1.13, 10 <= MariaDB 10.1.12 0 upwards low_priority_updatessql_low_priority_updates is a synonym. --low-priority-updates boolean 0 lower_case_file_systemOFF, file names are case-sensitive. If set to ON, they are not case-sensitive. boolean ## lower_case_table_names0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase. --lower-case-table-names[=#] numeric 0 (Unix), 1 (Windows), 2 (Mac OS X) 0 to 2 max_allowed_packet--max-allowed-packet=# numeric 16777216 (16M) >= MariaDB 10.2.4 4194304 (4M) >= MariaDB 10.1.7 1048576 (1MB) <= MariaDB 10.1.6 1073741824 (1GB) (client-side) 1024 to 1073741824 max_connect_errors--max-connect-errors=# numeric 100 (from MariaDB 10.0), 10 (before MariaDB 10.0) max_connections1 to 10 to avoid possible unexpected results for the user (MDEV-18252). --max-connections=# numeric 151 10 to 100000 (>= MariaDB 10.3.6, MariaDB 10.2.15, MariaDB 10.1.33) 1 to 100000 (<= MariaDB 10.3.5, MariaDB 10.2.14, MariaDB 10.1.32) max_delayed_threads0, DELAYED is ignored entirely. The session value can only be set to 0 or to the same as the global value. --max-delayed-threads=# numeric 20 0 to 16384 max_digest_length--max-digest-length=# numeric 1024 0 to 1048576 max_error_count--max-error-count=# numeric 64 0 to 65535 max_heap_table_size--max-heap-table-size=# numeric 16777216 16384 to 4294966272 max_insert_delayed_threadsmax_join_size--max-join-size=# numeric 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 4294967295 (< MariaDB/MySQL 5.5.0) 1 to 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 1 to 4294967295 (< MariaDB/MySQL 5.5.0) max_length_for_sort_datamax_length_for_sort_data, then we add these to the sort key. This can speed up the sort as we don't have to re-read the same row again later. Setting the value too high can slow things down as there will be a higher disk activity for doing the sort. --max-length-for-sort-data=# numeric 1024 4 to 8388608 max_long_data_size--max-long-data-size=# numeric 16777216 (16M) >= MariaDB 10.2.4 4194304 (4M) <= MariaDB 10.2.3, >= MariaDB 10.1.7 1048576 (1M) <= MariaDB 10.1.6 1024 to 4294967295 max_password_errors--max-password-errors=# numeric 4294967295 1 to 4294967295 max_prepared_stmt_count0, no prepared statements are permitted on the server. --max-prepared-stmt-count=# numeric 16382 0 to 4294967295 (>= MariaDB 10.3.6), 0 to 1048576 (<= MariaDB 10.3.5) max_recursive_iterations--max-recursive-iterations=# numeric 4294967295 0 to 4294967295 max_rowid_filter_size--max-rowid-filter-size=# numeric 131072 1024 to 18446744073709551615 max_seeks_for_key--max-seeks-for-key=# numeric 4294967295 1 to 4294967295 max_session_mem_used--max-session-mem-used=# numeric 9223372036854775807 (8192 PB) 8192 to 18446744073709551615 max_sort_lengthmax_sort_length bytes of each value and ignores the rest. --max-sort-length=# numeric 1024 4 to 8388608 max_sp_recursion_depth0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increase thread_stack as well. This limit doesn't apply to stored functions. --max-sp-recursion-depth[=#] numeric 0 0 to 255 max_statement_time--max-statement-time[=#] numeric 0.000000 >= MariaDB 10.1.13, 0 <= MariaDB 10.1.12 0 upwards max_tmp_tablesmax_user_connections0, there is no per user limit. From MariaDB 5.3, setting it to -1 stops users without the SUPER privilege from connecting to the server. The session variable is always read-only and only privileged users can modify user limits. The session variable defaults to the global max_user_connections variable, unless the user's specific MAX_USER_CONNECTIONS resource option is non-zero. When both global variable and the user resource option are set, the user's MAX_USER_CONNECTIONS is used. Note: This variable does not affect users with the SUPER privilege. --max-user-connections=# 0 or -1) numeric 0 -1 to 4294967295 max_write_lock_count--max-write-lock-count=# numeric 4294967295 0-4294967295 metadata_locks_cache_size--metadata-locks-cache-size=# numeric 1024 1 to 1048576 metadata_locks_hash_instances--metadata-locks-hash-instances=# numeric 8 1 to 1024 min_examined_row_limit0, the default, no row limit is used. --min-examined-row-limit=# numeric 0 0-4294967295 mrr_buffer_size--mrr-buffer-size=# numeric 262144 8192 to 2147483648 multi_range_count256 mysql56_temporal_format--mysql56-temporal-format boolean ON named_pipe--named-pipe boolean OFF net_buffer_length--net-buffer-length=# numeric 16384 1024 to 1048576 net_read_timeout--net-read-timeout=# numeric 30 1 upwards net_retry_count--net-retry-count=# numeric 10 1 to 4294967295 net_write_timeout--net-write-timeout=# numeric 60 1 upwards old--old boolean OFF old_alter_table1 (0 is default), MariaDB reverts to the non-optimized, pre-MySQL 5.1, method of processing ALTER TABLE statements. A temporary table is created, the data is copied over, and then the temporary table is renamed to the original. --old-alter-table enumerated (>=MariaDB 10.3.7), boolean (<= MariaDB 10.3.6) 0 (<= MariaDB 10.3.6) old_mode--old-mode string (empty string) old_passwords1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin. boolean OFF open_files_limitToo many open files error, then you should increase this limit. If set to 0, then MariaDB will calculate a limit based on the following: setrlimit. MariaDB cannot set this to exceed the hard limit imposed by the operating system. Therefore, you may also need to change the hard limit. There are a few ways to do so. mysqld_safe to start mysqld, then see the instructions at mysqld_safe: Configuring the Open Files Limit. systemd to start mysqld, then see the instructions at systemd: Configuring the Open Files Limit. mysql user account by modifying /etc/security/limits.conf. See Configuring Linux for MariaDB: Configuring the Open Files Limit for more details. --open-files-limit=count numeric 0 to 4294967295 optimizer_prune_level1, the default, the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space. If set to 0, heuristics are disabled and an exhaustive search is performed. --optimizer-prune-level[=#] boolean 1 optimizer_search_depth0, MariaDB will automatically choose a reasonable value. Since the better results from more optimal planning usually offset the longer time spent on planning, this is set as high as possible by default. 63 is a valid value, but its effects (switching to the original find_best search) are deprecated. --optimizer-search-depth[=#] numeric 62 0 to 63 optimizer_selectivity_sampling_limitoptimizer-selectivity-sampling-limit[=#] numeric 100 10 upwards optimizer_switch--optimizer-switch=value string condition_pushdown_for_derived={on|off} (>=MariaDB 10.2.2) condition_pushdown_for_subquery={on|off} (>=MariaDB 10.4.0) default - set all optimizations to their default values. derived_merge={on|off} (>=MariaDB 5.3) - see Derived table merge optimization derived_with_keys={on|off} (>=MariaDB 5.3) - see Derived table with key optimization engine_condition_pushdown={on|off} (>=MariaDB 5.5). Deprecated in MariaDB 10.1.1 as engine condition pushdown is now automatically enabled for all engines that support it. exists_to_in={on|off} (>=MariaDB 10.0) - see EXISTS-to-IN optimization extended_keys={on|off} (>= MariaDB 5.5) - see Extended Keys firstmatch={on|off} (>=MariaDB 5.3) - see First Match Strategy index_condition_pushdown={on|off} (>=MariaDB 5.3) - see Index Condition Pushdown index_merge={on|off} index_merge_intersection={on|off} index_merge_sort_intersection={on|off} (>=MariaDB 5.3) - more details index_merge_sort_union={on|off} index_merge_union={on|off} in_to_exists={on|off} (>=MariaDB 5.3) - see IN-TO-EXISTS transformation join_cache_bka={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms join_cache_hashed={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms join_cache_incremental={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms loosescan={on|off} (>=MariaDB 5.3) - see LooseScan strategy materialization={on|off} (>=MariaDB 5.3) - Semi-join and non semi-join materialization. mrr={on|off} (>=MariaDB 5.3) - see Multi Range Read optimization mrr_cost_based={on|off} (>=MariaDB 5.3) - see Multi Range Read optimization mrr_sort_keys={on|off} (>=MariaDB 5.3) - see Multi Range Read optimization optimize_join_buffer_size={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms orderby_uses_equalities={on|off} (>= MariaDB 10.1.15, MariaDB 10.2.1) - if not set, the optimizer ignores equality propagation. See MDEV-8989. outer_join_with_cache={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms partial_match_rowid_merge={on|off} (>= MariaDB 5.3) - see Non-semi-join subquery optimizations partial_match_table_scan={on|off} (>= MariaDB 5.3) - see Non-semi-join subquery optimizations semijoin={on|off} (>=MariaDB 5.3) - see Semi-join subquery optimizations semijoin_with_cache={on|off} (>=MariaDB 5.3) - see Block-Based Join Algorithms subquery_cache={on|off} (>=MariaDB 5.5) - see subquery cache. table_elimination={on|off} (>=MariaDB 5.3) - see Table Elimination User Interface optimizer_use_condition_selectivity1 Use selectivity of predicates as in MariaDB 5.5. 2 Use selectivity of all range predicates supported by indexes. 3 Use selectivity of all range predicates estimated without histogram. 4 Use selectivity of all range predicates estimated with histogram. 5 Additionally use selectivity of certain non-range predicates calculated on record sample. --optimizer-use-condition-selectivity=# numeric 4 (>= MariaDB 10.4.1), 1 (<= MariaDB 10.4.0) 1 to 5 optimizer_trace--optimizer-trace=value enum enabled=off enabled={on|off|default} optimizer_trace_max_mem_size--optimizer-trace-max-mem-size=# numeric 1048576 1 to 18446744073709551615 pid_file--pid-file=file_name file name plugin_dir--plugin-dir=path directory name BASEDIR/lib/plugin plugin_maturity--plugin-maturity=level unknown (<= MariaDB 10.3.2) unknown, experimental, alpha, beta, gamma, stable port0, will default to, in order of preference, my.cnf, the MYSQL_TCP_PORT environment variable, /etc/services, built-in default (3306). --port=#, -P numeric 3306 0 to 65535 preload_buffer_size--preload-buffer-size=# numeric 32768 1024 to 1073741824 profiling1 (0 is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE(). boolean OFF profiling_history_size0, no profiles are stored. See SHOW PROFILES. --profiling-history-size=# numeric 15 0 to 100 progress_report_time0, progress reporting will be disabled. --progress-report-time=# numeric 56 before MariaDB 5.5.39 and MariaDB 10.0.10, 5 as of MariaDB 5.5.40 and MariaDB 10.0.11 0 to 4294967295 protocol_versionnumeric 10 0 to 4294967295 proxy_protocol_networks--proxy-protocol-networks=value string proxy_userNULL. string pseudo_slave_modenumeric OFF pseudo_thread_idnumeric query_alloc_block_size--query-alloc-block-size=# numeric 16384 (from MariaDB 10.1.2), 8192 (before MariaDB 10.1.2) 1024 to 4294967295 query_cache_limit--query-cache-limit=# numeric 1048576 (1MB) 0 to 4294967295 query_cache_min_res_unit--query-cache-min-res-unit=# numeric 4096 (4KB) 0 to 4294967295 query_cache_size0, the default before MariaDB 10.1.7, effectively disables the query cache. Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. --query-cache-size=# numeric 1M (>= MariaDB 10.1.7), 0 (<= MariaDB 10.1.6) (although frequently given a default value in some setups) 0 upwards in units of 1024. query_cache_strip_comments1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the query cache. Multiple space, line feeds, tab and other white space characters will also be removed. query-cache-strip-comments boolean OFF query_cache_type0, the query cache is disabled (although a buffer of query_cache_size bytes is still allocated). If set to 1 all SELECT queries will be cached unless SQL_NO_CACHE is specified. If set to 2 (or DEMAND), only queries with the SQL CACHE clause will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime. Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. --query-cache-type=# enumeration OFF (>= MariaDB 10.1.7), ON (<= MariaDB 10.1.6) 0 or OFF, 1 or ON, 2 or DEMAND query_cache_wlock_invalidate0, the default, results present in the query cache will be returned even if there's a write lock on the table. If set to 1, the client will first have to wait for the lock to be released. --query-cache-wlock-invalidate boolean OFF query_prealloc_size--query-prealloc-size=# numeric 24576 (from MariaDB 10.1.2) 8192 (before MariaDB 10.1.2) 1024 to 4294967295 (from MariaDB 10.1.2), 8192 to 4294967295 (before MariaDB 10.1.2) rand_seed1rand_seed1 and rand_seed2 facilitate replication of the RAND() function. The master passes the value of these to the slaves so that the random number generator is seeded in the same way, and generates the same value, on the slave as on the master. Until MariaDB 10.1.4, the variable value could not be viewed, with the SHOW VARIABLES output always displaying zero. numeric 0 to 18446744073709551615 rand_seed2range_alloc_block_size--range-alloc-block-size=# numeric 4096 4096 to 4294967295 read_buffer_size--read-buffer-size=# numeric 131072 8200 to 2147479552 read_only1 (0 is default), no updates are permitted except from users with the SUPER privilege or slave servers updating from a master. The read_only variable is useful for slave servers to ensure no updates are accidentally made outside of what are performed on the master. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements are excluded from this limitation. From MariaDB 5.5, if read_only is set to 1, then the SET PASSWORD statement is limited only to users with the SUPER privilege. Attempting to set this variable to 1 will fail if the current session has table locks or transactions pending, while if other sessions hold table locks, the statement will wait until these locks are released before completing. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set. --read-only boolean OFF read_rnd_buffer_size--read-rnd-buffer-size=# numeric 262144 8200 to 2147483647 -
-
rowid_merge_buff_size--rowid-merge-buff-size=# numeric 8388608 0 to 2147483647 rpl_recovery_ranksafe_show_database--safe-show-database (until MySQL 4.1.1) boolean secure_authmysql_old_password authentication plugin. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format. --secure-auth boolean ON (>= MariaDB 10.1.7), OFF (<= MariaDB 10.1.6) secure_file_priv--secure-file-priv=path path name secure_timestamp--secure-timestamp=value enum NO session_track_schema--session-track-schema={0|1} boolean ON session_track_state_change--session-track-state-change={0|1} boolean OFF session_track_system_variables* character tracks all session variables. --session-track-system-variables=value string autocommit, character_set_client, character_set_connection, character_set_results, time_zone (>= MariaDB 10.3.1), empty string (<= MariaDB 10.3.0) session_track_transaction_info--session-track-transaction-info=value enum OFF OFF, STATE, CHARACTERISTICS shared_memoryshared_memory_base_nameMYSQL and is case sensitive. See also shared_memory. string MYSQL skip_external_lockingfcntl() function with the F_SETLK option to get file-based locks on Unix, and it will use the LockFileEx() function to get file-based locks on Windows. --skip-external-locking boolean 1 skip_name_resolve--skip-name-resolve boolean 0 skip_networking--skip-networking boolean 0 skip_show_database--skip-show-database boolean 0 slow_launch_timeslow_launch_threads server status variable is incremented. --slow-launch-time=# numeric 2 slow_query_log--slow-query-log boolean 0 NONE, no logs will be written even if slow_query_log is set to 1. slow_query_log_file--slow-query-log-file=file_name file name host_name-slow.log socket/tmp/mysql.sock, often changed by the distribution, for example /var/lib/mysql/mysql.sock. On Windows, this is the name of the named pipe used for local client connections, by default MySQL. On Windows, this is not case-sensitive. --socket=name file name /tmp/mysql.sock (Unix), MySQL (Windows) sort_buffer_size--sort-buffer-size=# number 2M (2097152) (some distributions increase the default) sql_auto_is_nullSELECT * FROM table_name WHERE auto_increment_column IS NULL will return an auto-increment that has just been successfully inserted, the same as the LAST_INSERT_ID() function. Some ODBC programs make use of this IS NULL comparison. boolean 0 (>= MariaDB/MySQL 5.5.3), 1 (MariaDB/MySQL <= 5.5.2) sql_big_selectsboolean 1 sql_big_tablesbig_tables. --sql-big-tables boolean 0 sql_buffer_resultboolean 0 sql_log_offboolean 0 sql_log_updatesql_low_priority_updateslow_priority_updates. --sql-low-priority-updates boolean 0 sql_max_join_sizesql_mode--sql-mode=value[,value[,value...]] string STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.2.4) NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.1.7) (empty string) (<= MariaDB 10.1.6) sql_notesboolean 1 sql_quote_show_createboolean 1 sql_safe_updates--sql-safe-updates[={0|1}] boolean OFF sql_select_limitnumeric 18446744073709551615 sql_warningsboolean OFF (0) storage_enginestandard_compliant_ctestandards_compliant_cte. --standard-compliant-cte={0|1} boolean ON stored_program_cache--stored-program-cache=# numeric 256 256 to 524288 strict_password_validation--strict-password-validation boolean ON sync_frm--sync-frm boolean TRUE system_time_zonetime_zone system variable, which is the variable that actually controls a session's active time zone. The system time zone is used for a session when time_zone is set to the special value SYSTEM. string table_definition_cache--table-definition-cache=# numeric 400 400 to 2097152 (>= MariaDB 10.4.2, MariaDB 10.3.13, MariaDB 10.2.22, MariaDB 10.1.38, MariaDB 10.0.38) 400 to 524288 (<= MariaDB 10.4.1, MariaDB 10.3.12, MariaDB 10.2.21, MariaDB 10.1.37, MariaDB 10.0.37) table_lock_wait_timeout--table-lock-wait-timeout=# numeric 50 1 to 1073741824 MariaDB/MySQL 5.5.3 table_open_cache--table-open-cache=# numeric 2000 (>= MariaDB 10.1.7), 400 (<= MariaDB 10.1.6) 1 to 1048576 (1024K) (>= MariaDB 10.1.20, MariaDB 10.0.35) 1 to 524288 (512K) (<= MariaDB 10.1.19, MariaDB 10.0.34) table_open_cache_instances8, which is expected to handle up to 100 CPU cores. If your system is larger than this, then you may benefit from increasing the value of this system variable. numeric 8 (>= MariaDB 10.2.2) 1 to 64 table_typetcp_keepalive_interval--tcp-keepalive-interval=# numeric 0 0 to 2147483 tcp_keepalive_probes--tcp-keepalive-probes=# numeric 0 0 to 2147483 tcp_keepalive_time--tcp-keepalive-time=# numeric 0 0 to 2147483 tcp_nodelay--tcp-nodelay={0|1} boolean 1 thread_cache_sizethread_cache_size is ignored. From MariaDB 10.2.0, the default is automatically set to the smaller of either 256, or the max_connections size. --thread-cache-size=# numeric 0 (<= MariaDB 10.1), Auto (from MariaDB 10.2.0) 0 to 16384 thread_concurrency--thread-concurrency=# numeric 10 1 to 512 thread_stack--thread-stack=# numeric 299008 (MariaDB 10.2.5) 297984 (MariaDB 10.2.1) 296960 (MariaDB 10.2.0) 295936 (MariaDB 10.1) 294912 (<= MariaDB 10.0) 131072 to 18446744073709551615 time_formattime_zoneSYSTEM, the session's time zone is determined by the system_time_zone system variable. --default-time-zone=string string SYSTEM timed_mutexesOFF, the default, disables mutex timing, while ON enables it. See also SHOW ENGINE for more on mutex statistics. Deprecated in MariaDB 5.5.39, as has no effect anymore. --timed-mutexes boolean OFF timestamptimestamp_value (Unix epoch timestamp, not MariaDB timestamp), DEFAULT tmp_disk_table_size--tmp-disk-table-size=# numeric 18446744073709551615 (max unsigned integer, no limit) 1024 to 18446744073709551615 tmp_memory_table_size--tmp-memory-table-size=# tmp_table_sizeCreated_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit. Defaults may be different on some systems, see for example Differences in MariaDB in Debian. From MariaDB 10.2.7, tmp_memory_table_size is an alias. --tmp-table-size=# numeric 16777216 (16MB) 1024 to 4294967295 (< MariaDB 10.5) 0 to 4294967295 (>= MariaDB 10.5.0) tmpdir--tmpdir=path or -t path transaction_alloc_block_size--transaction-alloc-block-size=# 8192 1024 to 4294967295 1024 transaction_prealloc_size--transaction-prealloc-size=# 4096 1024 to 4294967295 1024 tx_isolation--transaction-isolation=name REPEATABLE-READ READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE tx_read_onlyOFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. --transaction-read-only=# OFF-READ unique_checks1 updatable_views_with_limit0 prohibits this, while 1 permits it while issuing a warning (the default). --updatable-views-with-limit=# 1 use_stat_tablesnever: The optimizer will not use data from statistics tables. complementary: The optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine. preferably: Prefer the data from statistics tables, if it's not available there, use the data from the storage engine. complementary_for_queries: Same as complementary, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). From MariaDB 10.4.1. preferably_for_queries: Same as preferably, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). From MariaDB 10.4.1. --use-stat-tables=mode enum preferably_for_queries (>= MariaDB 10.4.1), never (<= MariaDB 10.4.0) version-debug indicates debugging support was enabled on the server, and -log indicates at least one of the binary log, general log or slow query log are enabled, for example 10.0.1-MariaDB-mariadb1precise-log. From MariaDB 10.2.1, this variable can be set at startup in order to fake the server version. -V, --version[=name] (>= MariaDB 10.2.1), --version (<= MariaDB 10.2.0) version_commentmariadb.org binary distribution. version_compile_machinei686. version_compile_osdebian-linux-gnu. version_malloc_libraryversion_source_revisionwait_timeout--wait-timeout=# 28800 1 to 2147483 1 to 31536000 warning_count
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/server-system-variables/