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_algorithm
ALGORITHM
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_log
0
, 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_tables
tmp_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_connection
string
utf8
(>= MariaDB 5.5) character_set_database
string
latin1
character_set_filesystem
binary
, 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_results
string
utf8
character_set_server
--character-set-server
string
latin1
character_set_system
string
utf8
character_sets_dir
--character-sets-dir=path
check_constraint_checks
0
, 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_connection
string
collation_database
collation_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_type
NO_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_insert
AUTO
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_file
core.${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_format
datetime_format
debug
--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_sync
string
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_write
ON
, 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_increment
4
, 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_tables
aria_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_algorithm
aes_cbc
is the recommended one. See Table and Tablespace Encryption. --encryption-algorithm=value
enum
none
none
, aes_ecb
, aes_cbc
, aes_ctr
enforce_storage_engine
NO_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_pushdown
engine_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_limit
0
(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_count
numeric
event_scheduler
ON
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_user
NULL
if native MariaDB authentication is used. string
NULL
flush
ON
, the server will synchronize all changes to disk after each statement. --flush
boolean
OFF
flush_time
--flush_time=#
numeric
0
foreign_key_checks
0
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_len
REPAIR 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_len
REPAIR 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_file
storage/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_log
NONE
, 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_compress
YES
, otherwise it will be NO
. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES
. have_crypt
YES
, otherwise it will be set to NO
. If set to NO
, the ENCRYPT() function cannot be used. have_csv
YES
, otherwise will be set to NO
. Removed in MariaDB 10.0, use the Information Schema PLUGINS table or SHOW ENGINES instead. have_dynamic_loading
YES
, otherwise will be set to NO
. have_geometry
YES
, otherwise will be set to NO
. have_ndbcluster
have_partitioning
YES
, 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_profiling
YES
, otherwise will be set to NO
. See SHOW PROFILES() and SHOW PROFILE(). have_query_cache
YES
, otherwise will be set to NO
. have_rtree_keys
YES
, otherwise will be set to NO
. have_symlink
YES
. 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_type
SINGLE_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_size
0
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
hostname
string
identity
idle_readonly_transaction_timeout
0
, 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_timeout
0
, 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_timeout
0
, 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_transaction
1
if a transaction is in progress, 0
if not. boolean
0
init_connect
--init-connect=name
string
init_file
init-file=file_name
file name
insert_id
numeric
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_create
0
, 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_support
large_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_id
numeric
lc_messages
en_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_dir
lc_messages
system variable . --lc-messages-dir=path
directory name
lc_time_names
--lc-time-names=name
string
en_US
license
GPL
. string
local_infile
1
, 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_timeout
0
(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_error
hostname.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_output
FILE
), 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_filter
admin
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_limit
1
, 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_verbosity
query_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_warnings
0
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_updates
sql_low_priority_updates
is a synonym. --low-priority-updates
boolean
0
lower_case_file_system
OFF
, file names are case-sensitive. If set to ON
, they are not case-sensitive. boolean
##
lower_case_table_names
0
(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_connections
1
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_threads
0
, 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_threads
max_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_data
max_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_count
0
, 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_length
max_sort_length
bytes of each value and ignores the rest. --max-sort-length=#
numeric
1024
4
to 8388608
max_sp_recursion_depth
0
, 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_tables
max_user_connections
0
, 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_limit
0
, 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_count
256
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_table
1
(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_passwords
1
(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_limit
Too 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_level
1
, 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_depth
0
, 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_limit
optimizer-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_selectivity
1
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
port
0
, 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
profiling
1
(0
is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE(). boolean
OFF
profiling_history_size
0
, no profiles are stored. See SHOW PROFILES. --profiling-history-size=#
numeric
15
0
to 100
progress_report_time
0
, 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_version
numeric
10
0
to 4294967295
proxy_protocol_networks
--proxy-protocol-networks=value
string
proxy_user
NULL
. string
pseudo_slave_mode
numeric
OFF
pseudo_thread_id
numeric
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_size
0
, 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_comments
1
(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_type
0
, 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_invalidate
0
, 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_seed1
rand_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_seed2
range_alloc_block_size
--range-alloc-block-size=#
numeric
4096
4096
to 4294967295
read_buffer_size
--read-buffer-size=#
numeric
131072
8200 to 2147479552
read_only
1
(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_rank
safe_show_database
--safe-show-database
(until MySQL 4.1.1) boolean
secure_auth
mysql_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_memory
shared_memory_base_name
MYSQL
and is case sensitive. See also shared_memory. string
MYSQL
skip_external_locking
fcntl()
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_time
slow_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_null
SELECT * 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_selects
boolean
1
sql_big_tables
big_tables
. --sql-big-tables
boolean
0
sql_buffer_result
boolean
0
sql_log_off
boolean
0
sql_log_update
sql_low_priority_updates
low_priority_updates
. --sql-low-priority-updates
boolean
0
sql_max_join_size
sql_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_notes
boolean
1
sql_quote_show_create
boolean
1
sql_safe_updates
--sql-safe-updates[={0|1}]
boolean
OFF
sql_select_limit
numeric
18446744073709551615
sql_warnings
boolean
OFF (0)
storage_engine
standard_compliant_cte
standards_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_zone
time_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_instances
8
, 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_type
tcp_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_size
thread_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_format
time_zone
SYSTEM
, the session's time zone is determined by the system_time_zone
system variable. --default-time-zone=string
string
SYSTEM
timed_mutexes
OFF
, 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
timestamp
timestamp_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_size
Created_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_only
OFF
, 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_checks
1
updatable_views_with_limit
0
prohibits this, while 1
permits it while issuing a warning (the default). --updatable-views-with-limit=#
1
use_stat_tables
never
: 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_comment
mariadb.org binary distribution
. version_compile_machine
i686
. version_compile_os
debian-linux-gnu
. version_malloc_library
version_source_revision
wait_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/