Server System Variables

You are viewing an old version of this article. View the current version here.

About the Server System Variables
Setting Server System Variables
List of Server System Variables

About the Server System Variables

MariaDB has many system variables that can be changed to suit your needs. 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:

While in the mysql client, run:

SHOW VARIABLES;

See SHOW VARIABLES for instructions on using this command.

From your shell, run mysqld like so:

mysqld --verbose --help

Setting Server System Variables

There are several ways to set server system variables:

Specify them on the command line:

shell> ./mysqld_safe --aria_group_commit="hard"

Specify them in your my.cnf file:

aria_group_commit = "hard"

Set them from the mysql client using the SET command. Only variables that are dynamic can be set at runtime in this way.

SET GLOBAL aria_group_commit="hard";

List of Server System Variables

`auto_increment_increment`

Description: The increment for all AUTO_INCREMENT values on the server, by default 1.
Commandline: --auto-increment-increment[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 to 65535

`auto_increment_offset`

Description: The offset for all AUTO_INCREMENT values on the server, by default 1.
Commandline: --auto-increment-offset[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 to 65535

`autocommit`

Description: If set to 1, the default, all queries are committed immediately. If set to 0, they are only committed upon a COMMIT statement, or rolled back with a ROLLBACK statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
Commandline: --autocommit[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`automatic_sp_privileges`

Description: When set to 1, the default, when a stored routine is created, the creator is automatically granted permission to ALTER (which includes dropping) and to EXECUTE the routine. If set to 0, the creator is not automatically granted these privileges.
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 1

`back_log`

Description: Connections take a small amount of time to start, and this setting determines the number of outstanding connection requests MariaDB can have, or the size of the listen queue for incoming TCP/IP requests. Requests beyond this will be refused. Increase if you expect short bursts of connections. Cannot be set higher than the operating system limit (see the Unix listen() man page).
Commandline: --back_log=#
Scope: Global
Dynamic: No
Type: number
Default Value: 50

`basedir`

Description: Path to the MariaDB installation directory. Other paths are usually resolved relative to this base directory.
Commandline: --basedir=path or -b path
Scope: Global
Dynamic: No
Type: directory name

`big_tables`

Description: Old variable, which if set to 1, allows large result sets by saving all temporary sets to disk, avoiding 'table full' errors. No longer needed, as the server now handles this automatically. sql_big_tables is a synonym.
Commandline: --big-tables
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`bind_address`

Description: By default, the MariaDB server listens for TCP/IP connections on a network socket bound to a single address, 0.0.0.0. You can specify an alternative when the server starts using this option; either a host name, an IPv4 or an IPv6 address.
Commandline: --bind-address=addr
Scope: Global
Dynamic: No
Data Type: numeric
Valid Values: Host name, IPv4, IPv6 (>= MySQL 5.5.3)

`binlog_cache_size`

Description: If the binary log is active, this variable determines the size in bytes of the cache holding a record of binary log changes during a transaction. Since MySQL 5.5.9, a separate variable, binlog_stmt_cache_size, determines the cache size for binary log statements outside of a transaction. The binlog_cache_disk_use and binlog_cache_use server status variables will indicate whether this variable needs to be increased (you want a low ratio of binlog_cache_disk_use to binlog_cache_use). Between MySQL 5.5.3 and MySQL 5.5.8, binlog_stmt_cache_size determined the size of both the transaction and non-transaction binary log caches.
Commandline: --binlog_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 32768
Range - 32 bit: 4096 to 4294967295
Range - 64 bit: 4096 to 18446744073709547520

`binlog_checksum`

Description: Enable/Disable binlog checksums.
Commandline: --binlog_checksum=[0|1]
Scope: Global
Access Type: Can be changed dynamically
Data Type: bool
Default Value: OFF (0)
Introduced: MariaDB 5.3

`binlog_direct_non_transactional_updates`

Description: Replication inconsistencies can occur due when a transaction updates both transactional and non-transactional tables and the updates to the non-transactional tables are visible before being written to the binary log. This is because, to preserve causality, the non-transactional statements are written to the transaction cache, which is only flushed on commit. Setting binlog_direct_non_transactional_updates to 1 (0 is default) will cause non-transactional tables to be written straight to the binary log, rather than the transaction cache. This setting has no effect when row-based binary logging is used, as it requires statement-based logging. See binlog_format. Use with care, and only in situations where no dependencies exist between the non-transactional and transactional tables, for example INSERTing into a non-transactional table based upon the results of a SELECT from a transactional table.

Commandline: --binlog_direct_non_transactional_updates[=value]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF (0)
Introduced: MariaDB/MySQL 5.5.2

`binlog_format`

Description: Determines whether replication is row-based, statement-based or mixed. As of MariaDB/MySQL 5.5, statement-based is the default.
Commandline: --binlog-format=format
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: STATEMENT (>=MariaDB/MySQL 5.5)
Valid Values: ROW, STATEMENT or MIXED

`binlog_optimize_thread_scheduling`

Description: Run fast part of group commit in a single thread, to optimize kernel thread scheduling. On by default. Disable to run each transaction in group commit in its own thread, which can be slower at very high concurrency. This option is mostly for testing one algorithm versus another, and it should not normally be necessary to change it.
Commandline: --binlog-optimize-thread-scheduling or --skip-binlog-optimize-thread-scheduling
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: ON
Introduced: MariaDB 5.2

`binlog_stmt_cache_size`

Description: If the binary log is active, this variable determines the size in bytes of the cache holding a record of binary log changes outside of a transaction (from MySQL 5.5.3 to 5.5.8 it set the cache size for both transaction and non-transaction caches). The variable binlog_cache_size, determines the cache size for binary log statements inside a transaction. The binlog_stmt_cache_disk_use and binlog_stmt_cache_use server status variables will indicate whether this variable needs to be increased (you want a low ratio of binlog_stmt_cache_disk_use to binlog_stmt_cache_use).
Commandline: --binlog_stmt_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 32768
Range - 32 bit: 4096 to 4294967295
Range - 64 bit: 4096 to 18446744073709547520
Introduced: MariaDB/MySQL 5.5.9

`bulk_insert_buffer_size`

Description: Size in bytes of the per-thread cache tree used to speed up bulk inserts into MyISAM and Aria tables. A value of 0 disables the cache tree.
Commandline: --bulk_insert_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8388608
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`character_set_client`

Description: Determines the character set for queries arriving from the client. It can be set per session by the client, although the server can be configured to ignore client requests with the --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.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`character_set_connection`

Description: Character set used for number to string conversion, as well as for literals that don't have a character set introducer.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`character_set_database`

Description: Character set used by the default database, and set by the server whenever the default database is changed. If there's no default database, character_set_database contains the same value as character_set_server. This variable is dynamic, but should not be set manually, only by the server.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`character_set_filesystem`

Description: The character set for the filesystem. Used for converting file names specified as a string literal from character_set_client to character_set_filesystem before opening the file. By default set to 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.
Commandline: --character-set-filesystem=name
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: binary

`character_set_results`

Description: Character set used for results and error messages returned to the client.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`character_set_server`

Description: Default character set used by the server. See character_set_database for character sets used by the default database.
Commandline: --character-set-server
Scope: Global, Session
Dynamic: Yes
Data Type: string

`character_set_system`

Description: Character set used by the server to store identifiers, always set to utf8.
Scope: Global
Dynamic: No
Data Type: string

`character_sets_dir`

Description: Directory where the character sets are installed.
Commandline: --character-sets-dir=path
Scope: Global
Dynamic: No
Type: directory name

`collation_connection`

Description: Collation used for the connection character set.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`collation_database`

Description: Collation used for the default database. Set by the server if the default database changes, if there is no default database the value from the collation_server variable is used. This variable is dynamic, but should not be set manually, only by the server.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`collation_server`

Description: Default collation used by the server. This is set to the default collation for a given character set automatically when character_set_server is changed, but it can also be set manually.
Commandline: --collation-server
Scope: Global, Session
Dynamic: Yes
Data Type: string

`completion_type`

Description: The transaction completion type. If set to 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.
Commandline: --completion_type=name
Scope: Global, Session
Dynamic: Yes
Data Type: enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2)
Default Value: NO_CHAIN (>=MariaDB/MySQL 5.5.3), 0 (<= MariaDB/MySQL 5.5.2)
Valid Values: 0, 1, 2 (All versions), NO_CHAIN, CHAIN, RELEASE (>=MariaDB/MySQL 5.5.3)

`concurrent_insert`

Description: If set to 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.
Commandline: --concurrent_insert[=name]
Scope: Global
Dynamic: Yes
Data Type: enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2)
Default Value: AUTO (>=MariaDB/MySQL 5.5.3), 1 (<= MariaDB/MySQL 5.5.2)
Valid Values: 0, 1, 2 (All versions), AUTO, NEVER, ALWAYS (>=MariaDB/MySQL 5.5.3)

`connect_timeout`

Description: Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'. Increasing may help if clients regularly encounter 'Lost connection to MySQL server at 'X', system error: error_number' type-errors
Commandline: --connect_timeout=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 10

`datadir`

Description: Directory where the data is stored.
Commandline: --datadir=path or -h path
Scope: Global
Dynamic: No
Type: directory name

`date_format`

Description: Unused.

`datetime_format`

Description: Unused.

`deadlock_search_depth_long`

Description: Long search depth for the two-step deadlock detection.
Commandline: --deadlock-search-depth-long=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 15
Range: 0 to 33

`deadlock_search_depth_short`

Description: Short search depth for the two-step deadlock detection.
Commandline: --deadlock-search-depth-short=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4
Range: 0 to 32

`deadlock_timeout_long`

Description: Long timeout in microseconds for the two-step deadlock detection.
Commandline: --deadlock-timeout-long=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 50000000
Range - 32 bit: 0 to 4294967295

`deadlock_timeout_short`

Description: Short timeout in microseconds for the two-step deadlock detection.
Commandline: --deadlock-timeout-short=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10000
Range - 32 bit: 0 to 4294967295

`debug`

Description: Used in debugging to write to a trace file. MariaDB needs to be configured with -SWITH_DEBUG=1 to enable this option.
Commandline: --debug[=debug_options]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: d:t:o,/tmp/mysqld.trace

`debug_sync`

Description: Used in debugging to show the interface to the Debug Sync facility. MariaDB needs to be configured with -DENABLE_DEBUG_SYNC=1 for this variable to be available.
Scope: Session
Dynamic: Yes
Data Type: string
Default Value: OFF or ON - current signal signal name

`default_storage_engine`

Description: The default storage engine. The default storage engine must be enabled at server startup or the server won't start.
Commandline: --default-storage-engine=name
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: InnoDB (>= MariaDB/MySQL 5.5.5), MyISAM (MariaDB/MySQL <=5.5.4)
Introduced: MariaDB/MySQL 5.5.3 (previously storage_engine)

`default_week_format`

Description: Default mode for the WEEK() function. See that page for details on the different modes
Commandline: --default_week_format=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 7

`delay_key_write`

Description: Specifies how MyISAM tables handles CREATE TABLE DELAY_KEY_WRITE. If set to 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.
Commandline: --delay-key-write[=name]
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: ON
Valid Values: ON, OFF, ALL

`delayed_insert_limit`

Description: After this many rows have been inserted with INSERT DELAYED, the handler will check for and execute any waiting SELECT statements.
Commandline: --delayed_insert_limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range - 32 bit: 1 to 4294967295
Range - 64 bit: 1 to 18446744073709547520

`delayed_insert_timeout`

Description: Time in seconds that the INSERT DELAYED handler will wait for INSERT's before terminating.
Commandline: --delayed_insert_timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 300

`delayed_queue_size`

Description: Number of rows, per table, that can be queued when performing INSERT DELAYED statements. If the queue becomes full, clients attempting to perform INSERT DELAYED's will wait until the queue has room available again.
Commandline: --delayed_queue_size=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: InnoDB (>= MariaDB/MySQL 5.5.5), MyISAM (MariaDB/MySQL <=5.5.4)
Introduced: MariaDB/MySQL 5.5.3 (previously storage_engine)
Default Value: 1000
Range - 32-bit: 1 to 4294967295
Range - 64-bit: 1 to 18446744073709547520

`div_precision_increment`

Description: Number of digits to be returned after the decimal point in division operations. By default 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.
Commandline: --div_precision_increment=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4
Range: 0 to 30

`engine_condition_pushdown`

Description: Deprecated and to be replaced by optimizer_switch. Specifies whether the engine condition pushdown optimization is enabled.
Commandline: --engine-condition-pushdown
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: ON
Deprecated: MariaDB/MySQL 5.5.3

`error_count`

Description: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. See SHOW_ERRORS()
Scope: Session
Dynamic: Yes
Data Type: numeric

`event_scheduler`

Description: Status of the Event Scheduler. Can be set to ON or OFF, while DISABLED means it cannot be set at runtime.
Commandline: --event-scheduler[=value]
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: OFF
Valie Values: ON (or 1), OFF (or 0), DISABLED

`expensive_subquery_limit`

Description: Number of rows to be examined for a query to be considered expensive, that is, maximum number of rows a subquery may examine in order to be executed during optimization and used for constant optimization.
Commandline: --expensive-subquery-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 0 upwards
Introduced: MariaDB 5.5.25

`expire_logs_days`

Description: Number of days after which the binary log will be automatically removed. By default 0, or no automatic removal. When using replication, should always be set higher than the maximum lag by any slave.
Commandline: --expire_logs_days=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 99

`external_user`

Description: External user name set by the plugin.used to authenticate the client. NULL if native MariaDB authentication is used.
Scope: Session
Dynamic: No
Data Type: string
Default Value: NULL
Introduced: MariaDB/MySQL 5.5.7

`extra_max_connections`

Description: The number of connections on the extra_port.
Commandline: --extra-max-connections=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 to 100000

`extra_port`

Description: Extra port number to use for tcp-connections in a one-thread-per-connection manner. If set to 0, no other port is used.
Commandline: --extra-port=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0

`flush`

Description: Usually, MariaDB writes changes to disk after each SQL statement, and the operating system handles synchronizing (flushing) it to disk. If set to ON, the server will synchronize all changes to disk after each statement.
Commandline: --flush
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`flush_time`

Description: Interval in seconds that tables are closed to synchronize (flush) data to disk and free up resources. If set to 0, the default, there is no automatic synchronizing tables and closing of tables. This option should not be necessary on systems with sufficient resources.
Commandline: --flush_time=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0

`foreign_key_checks`

Description: If set to 1, the default, foreign key constraints for InnoDB tables are checked, while if set to 0, they are not checked. 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.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`ft_boolean_syntax`

Description: List of operators supported by an IN BOOLEAN MODE full-text search. If you wish to change, note that each character must be ASCII and non-alphanumeric, the full string must be 14 characters and the first or second character must be a space. Positions 10, 13 and 14 are reserved for future extensions. Also, no duplicates are permitted except for the phrase quoting characters in positions 11 and 12, which may be the same.
Commandline: --ft_boolean_syntax=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: + -><()*:""&|

`ft_max_word_len`

Description: Maximum length for a word to be included in the full-text index. 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.
Commandline: --ft_max_word_len=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 84
Minimum Value: 10

`ft_min_word_len`

Description: Minimum length for a word to be included in the full-text index. 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.
Commandline: --ft_min_word_len=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 4
Minimum Value: 1

`ft_query_expansion_limit`

Description: For full-text searches, denotes the numer of top matches when using WITH QUERY EXPANSION.
Commandline: --ft_query_expansion_limit=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 20
Range: 0 to 1000

`ft_stopword_file`

Description: File containing a list of stopwords for use in full-text searches. Unless an absolute path is specified the file will be looked for in the data directory. The file is not parsed for comments, so all words found become stopwords. By default, a built-in list of words (built from 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.
Commandline: --ft_stopword_file=file_name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (built-in)

`general_log`

Description: If set to 0, the default unless the --general-log option is used, the general query log is disabled, while if set to 1, the general query log is enabled. See log_output for how log files are written. If that variable is set to NONE, no logs will be written even if general_query_log is set to 1.
Commandline: --general-log
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 0

`general_log_file`

Description: Name of the general query log file.
Commandline: --general-query-log-file=file_name
Scope: Global
Dynamic: Yes
Data Type: file name
Default Value: host_name.log

`group_concat_max_len`

Description: Maximum length in bytes of the returned result for a GROUP_CONCAT() function.
Commandline: --group_concat_max_len=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range - 32-bit: 4 to 4294967295
Range - 64-bit: 4 to 18446744073709547520

`have_compress`

Description: If the zlib compression library is accessible to the server, this will be set to YES, otherwise it will be NO. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES.
Scope: Global
Dynamic: No

`have_crypt`

Description: If the crypt() system call is available this variable will be set to YES, otherwise it will be set to NO. If set to NO, the ENCRYPT() function cannot be used.
Scope: Global
Dynamic: No

`have_csv`

Description: If the server supports CSV tables, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_dynamic_loading`

Description: If the server supports dynamic loading of plugins, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_geometry`

Description: If the server supports spatial data types, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_innodb`

Description: If the server supports InnoDB tables, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_ndbcluster`

Description: If the server supports NDBCluster (currently disabled in MariaDB).
Scope: Global
Dynamic: No

`have_openssl`

Description: Alias for have_ssl.
Scope: Global
Dynamic: No

`have_partitioning`

Description: If the server supports partitioning, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_profiling`

Description: If statement profiling is available, will be set to YES, otherwise will be set to NO. See SHOW PROFILES() and SHOW PROFILE().
Scope: Global
Dynamic: No

`have_query_cache`

Description: If the server supports the query cache, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_rtree_keys`

Description: If RTREE indexes (used for spatial indexed in MyISAM tables) are available, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_ssl`

Description: If the server supports SSL connections, will be set to YES, otherwise will be set to NO. If set to DISABLED, the server was compiled with SSL support, but was not started with SSL support (see the mysqld options).
Scope: Global
Dynamic: No

`have_symlink`

Description: If symbolic link support is enabled, will be set to YES, otherwise will be set to NO. Required for the INDEX DIRECTORY and DATA DIRECTORY table options (see CREATE TABLE) and Windows symlink support. Will be set to DISABLED if the server is started with the --skip-symbolic-links option.
Scope: Global
Dynamic: No

`hostname`

Description: When the server starts, this variable is set to the server host name.
Scope: Global
Dynamic: No
Data Type: string

`identity`

Description: A synonym for last_insert_id variable.

`ignore_builtin_innodb`

Description: In older versions of MariaDB, setting this to 1 resulted in the built in InnoDB storage engine to be ignored. From MariaDB 5.5, InnoDB is the default and is always present, so this variable is ignored and setting it results in a warning.
Commandline: --ignore-builtin-innodb
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Deprecated: MariaDB/MySQL 5.5.22

`ignore_db_dirs`

Description: Comma-delimited list of directories in the data directory that are not considered as database directories. Set from --ignore-db-dir at startup.
Scope: Global
Dynamic: No
Data Type: string

`init_connect`

Description: String containing one or more SQL statements, separated by semicolons, that will be executed by the server for each client connecting. If there's a syntax error in the one of the statements, the client will fail to connect. For this reason, the statements are not executed for users with the SUPER privilege, who can then still connect and correct the error. See also init_file.
Commandline: --init-connect=name
Scope: Global
Dynamic: Yes
Data Type: string

`init_file`

Description: Name of a file containing SQL statements that will be executed by the server for each client connecting. Each statement should be on a new line with no terminators (such as a semicolon). See also init_connect.
Commandline: init-file=file_name
Scope: Global
Dynamic: No
Data Type: file name

`init_slave`

Description: Similar to init_connect, but the string contains one or more SQL statements, separated by semicolons, that will be executed by a slave server each time the SQL thread starts. These statements are only executed after the acknowledgement is sent to the slave and START SLAVE completes.
Commandline: --init-slave=name
Scope: Global
Dynamic: Yes
Data Type: string

`in_transaction`

Description: Session-only and read-only variable that is set to 1 if you are in a transaction, and 0 if not.
Commandline: No
Scope: Session
Dynamic: No
Data Type: boolean
Default Value: 0
Introduced: MariaDB 5.3

`insert_id`

Description: Value to be used for the next statement inserting a new AUTO_INCREMENT value.
Scope: Session
Dynamic: Yes
Data Type: numeric

`interactive_timeout`

Description: Time in seconds that the server waits for an interactive connection (one that connects with the mysql_real_connect() CLIENT_INTERACTIVE option) to become active before closing it. See also wait_timeout.
Commandline: --interactive_timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Minimum Value: 1

`join_buffer_size`

Description: Minimum size of the buffer used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size. Best left low globally and set high in sessions that require large full joins. In 64-bit platforms, Windows truncates values above 4GB to 4GB with a warning.
Commandline: --join_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 131072
Range - 32-bit (>=MariaDB/MySQL 5.5.3): 128 to 4294967295
Range - 32 bit (<=MariaDB/MySQL 5.5.2): 8200 to 4294967295
Range - 64-bit (>=MariaDB/MySQL 5.5.3): 128 to 18446744073709547520
Range - 64-bit (<=MariaDB/MySQL 5.5.2, Windows): 8228 to 18446744073709547520

`join_buffer_space_limit`

Description: Maximum size in bytes of the query buffer, By default 1024*128*10.
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2097152
Range: 2048 to 99999999997952
Introduced: MariaDB 5.5

`join_cache_level`

Description: Controls which of the eight block-based algorithms can be used for join operations. See Block-based join algorithms for more information.
- 1 – flat (Block Nested Loop) BNL
- 2 – incremental BNL
- 3 – flat Block Nested Loop Hash (BNLH)
- 4 – incremental BNLH
- 5 – flat Batch Key Access (BKA)
- 6 – incremental BKA
- 7 – flat Batch Key Access Hash (BKAH)
- 8 – incremental BKAH
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2
Range: 0 to 8
Introduced: MariaDB 5.3

`keep_files_on_create`

Description: If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is stored in the database directory. When set to 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.
Commandline: --keep_files_on_create=#
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`key_buffer_size`

Description: Size of the buffer for the index blocks used by MyISAM tables and shared for all threads. See Optimizing key_buffer_size for more on selecting the best value.
Commandline: --key_buffer_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 8388608
Range - 32-bit: 8 to 4294967295

`key_cache_age_threshold`

Description: The lower the setting, the more quickly buffers move from the hot key cache sublist to the warm sublist.
Commandline: --key_cache_age_threshold=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 300
Range - 32-bit: 100 to 4294967295
Range - 64-bit: 100 to 18446744073709547520

`key_cache_block_size`

Description: MyISAM key cache block size in bytes .
Commandline: --key_cache_block_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 512 to 16384

`key_cache_division_limit`

Description: Percentage to use for the warm key cache buffer list (the remainder is allocated between the hot and cold caches).
Commandline: --key_cache_division_limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 1 to 100

`key_cache_segments`

Description: The number of segments in a key cache.
Commandline: --key-cache-segments=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 0 (non-segmented)
Range: 0-64
Introduced: MariaDB 5.2.0

`language`

Description: Directory where the error messages are stored. Removed as of MariaDB/MySQL 5.5.0. Use lc_messages_dir and lc_messages instead.
Commandline: --language=name, -L
Scope: Global
Dynamic: No
Data Type: directory name
Default Value: /usr/local/mysql/share/mysql/english/
Removed: MariaDB/MySQL 5.5.0

`large_files_support`

Description: ON if the server if was compiled with large file support or not, else OFF
Scope: Global
Dynamic: No
Introduced: MySQL 3.23.28

`large_page_size`

Description: Indicates the size of memory page if large page support (Linux only) is enabled. See large_pages.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0

`large_pages`

Description: Indicates whether large page support (Linux only) is used.
Commandline: --large-pages
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`last_insert_id`

Description: Contains the same value as that returned by LAST_INSERT_ID(). Note that setting this variable doen't update the value returned by the underlying function.
Scope: Session
Dynamic: Yes
Data Type: numeric

`lc_messages`

Description: Locale to use for error messages. This will be converted to a language name and along with lc_messages_dir will produce the location for the error message file.
Commandline: --lc-messages=name
Scope: Global, Session
Dynamic: Yes
Data Type: string

`lc_messages_dir`

Description: Directory where the error messages are located. Together with lc_messages will produce the locate for the error message file.
Commandline: --lc-messages-dir=path
Scope: Global
Dynamic: No
Data Type: directory name

`lc_time_names`

Description: The locale that determines the language used for the date and time functions DAYNAME(), MONTHNAME() and DATE_FORMAT(). Locale names are language and region subtags, for example 'en_ZA' (English - South Africa) or 'es_US: Spanish - United States'. The default is always 'en-US' regardless of the system's locale setting. See server locale for a full list of supported locales.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: en_US

`license`

Description: Server licence, for example GPL.
Scope: Global
Dynamic: No
Data Type: string

`local_infile`

Description: If set to 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.
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON

`lock_wait_timeout`

Description: Timeout in seconds for attempts to acquire metadata locks. Statements using metadata locks include FLUSH TABLES WITH READ LOCK, LOCK TABLES, HANDLER and DML and DDL operations on tables, stored procedures and functions, and views. The timeout is separate for each attempt, of which there may be multiple in a single statement.
Commandline: --lock_wait_timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 31536000 (1 year)
Range: 1 to 31536000
Introduced: MariaDB/MySQL 5.5.3

`locked_in_memory`

Description: Indicates whether --memlock was used to lock mysqld in memory.
Commandline: --memlock
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`log`

Description: Deprecated, use general_log instead.
Commandline: -l [filename] or --log[=filename]
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: OFF
Deprecated: MySQL 5.1.29 by general_log

`log_bin`

Description: Whether binary logging is enabled or not. If the --log-bin option is used, log_bin will be set to ON, otherwise it will be OFF.
Commandline: --log-bin[=name]
Scope: Global
Dynamic: No
Data Type: binary

`log_bin_trust_function_creators`

Description: By default, CREATE FUNCTION statements are not accepted unless they specify one of DETERMINISTIC, NO SQL or READS SQL DATA. This is because functions that are not one of these may have unexpected consequences on a slave server if they are used in replication. Setting this variable to 1 relaxes this restriction as well as permitting users to create stored routine and stored functions without the SUPER privilege.
Commandline: --log-bin-trust-function-creators[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`log_error`

Description: Specifies the name of the error log. If no name is provided, errors will still be logged to hostname.err.
Commandline: --log-error[=name]
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (empty string)

`log_output`

Description: How the output for the general query log and the slow query log is stored. By default written to file (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 slow_query_log and general_log server system variables.
Commandline: --log-output=name
Scope: Global
Dynamic: Yes
Data Type: set
Default Value: FILE
Valid Values: TABLE, FILE or NONE

`log_queries_not_using_indexes`

Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log. The slow query log needs to be enabled for this to have an effect.
Commandline: --log-queries-not-using-indexes
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`log_slave_updates`

Description: If set to 0, the default, updates on a slave received from a master during replication are not logged in the slave's binary log. If set to 1, they are. The slave's binary log needs to be enabled for this to have an effect.
Commandline: --log-slave-updates
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`log_slow_filter`

Description: Comma-delimited string containing one or more settings for filtering what is logged to the slow query log. If a query matches one of the types listed in the filter, it will be logged.
- full_scan logs queries that perform full table scans.
- full_join logs queries that perform a join without indexes.
- filesort lgs queries that use a filesort.
- filesort_on_disk logs queries that perform a a filesort on disk.
- tmp_table logs queries that create an implicit temporary table.
- tmp_table_on_disk logs queries that create a temporary table on disk.
- query_cache_miss logs queries that are not found in the query cache.
Commandline: <<code>>log_slow_filter=value1[,value2...]</code>>
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk
Valid Values: admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table or tmp_table_on_disk

`log_slow_queries`

Description: Deprecated, use slow_query_log.
Commandline: --log-slow-queries[=name]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated: MySQL 5.1.29

`log_slow_rate_limit`

Description: The slow query log will log every this many queries. The default is 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.
Commandline: sss
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 upwards

`log_slow_verbosity`

Description: Controls information to be added to the slow query log. Options are added in a comma-delimited string.
- microtime logs queries in microseconds,
- query_plan logs query execution plan information
- innodb logs XtraDB/InnoDB statistics
- minimal is equivalent to microtime
- standard is equivalent to microtime, innodb
- full is equivalent to all values.
- profiling enables all queries to be profiled (added in XtraDB 5.5.8-20.0)
- profiling_use_getrusage enables the getrusage function (added in XtraDB 5.5.8-20.0)
Commandline: log-slow-verbosity=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: query_plan
Valid Values: microtime, query_plan, innodb, minimal, standard, full, profiling, profiling_use_getrusage (the latter two added with XtraDB 5.5.8-20.0)

`log_warnings`

Description: If set to 1, the default, all warnings are logged to the error log, including statements that are not safe for statement-based replication. Slave servers also log status information, including the binary log and relay log co-ordinates where they start replicating from, reconnects after a disconnect and when the slave switches to a new relay-log. If set to 0, warnings are not logged. If set to a number greater than 1, aborted connections and access-denied errors are also logged.
Commandline: -W [level] or --log-warnings[=level]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`long_query_time`

Description: If a query takes longer than this many seconds to execute (microseconds can be specified too), the Slow_queries status variable is incremented and, if enabled, the query is logged to the slow query log.
Commandline: --long_query_time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10.000000
Range: 0 upwards

`low_priority_updates`

Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes. Previously named sql_low_priority_updates, which is still a synonym.
Commandline: --low-priority-updates
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`lower_case_file_system`

Description: Read-only variable describing whether the file system is case-sensitive. If set to OFF, file names are case-sensitive. If set to ON, they are not case-sensitive.
Commandline: --lower_case_file_system[=#]
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: ##

`lower_case_table_names`

Description: If set to 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.
Commandline: --lower_case_table_names[=#]
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
Range: 0 to 2

`master_retry_count`

Description: Number of times a slave will attempt to connect to a master before giving up. The retry interval is determined by the MASTER_CONNECT_RETRY option for the CHANGE MASTER statement. A value of 0 means the slave will not stop attempting to reconnect. Reconnects are triggered when a slave has timed out. See slave_net_timeout.
Commandline: --master-retry-count=#
Type: numeric
Default Value: 86400
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709551615

`master_verify_checksum`

Description: Verify binlog checksums when reading events from the binlog on the master.
Commandline: --master_verify_checksum=[0|1]
Scope: Global
Access Type: Can be changed dynamically
Data Type: bool
Default Value: OFF (0)
Introduced: MariaDB 5.3

`max_allowed_packet`

Description: Maximum size in bytes of a packet or a generated/intermediate string. The packet message buffer is initialized with the value from net_buffer_length, but can grow up to max_allowed_packet bytes. Set as large as the largest BLOB, in multiples of 1024. If this value is changed, it should be changed on the client side as well. See slave_max_allowed_packet for a specific limit for replication purposes.
Commandline:
```
--max_allowed_packet=#
```
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 16777216, 1073741824 (client-side)
Range: 1024 to 1073741824

`max_binlog_cache_size`

Description: Restricts the size used to cache a multi-transactional query. The maximum theoretical value is 16 exabytes, but binary log positions cannot go beyond 4GB, so this is the default and recommended value. Between MariaDB/MySQL 5.5.3 and MariaDB/MySQL 5.5.8, max_binlog_cache_size restricted the size for both transaction and non-transaction caches, while from MariaDB 5.5.9, it sets the limit for the transaction cache only. See max_binlog_stmt_cache_size and binlog_cache_size.
Commandline: --max_binlog_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4294963200
Range: 4096 to 18446744073709547520

`max_binlog_size`

Description: If the binary log exceeds this size after a write, the server rotates it by closing it and opening a new binary log. Single transactions will always be stored in the same binary log, so the server will wait for open transactions to complete before rotating. This figure also applies to the size of relay logs if max_relay_log_size is set to zero.
Commandline: --max_binlog_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1073741824 (1GB)
Range: 4096 to 1073741824 (4KB to 1GB)

`max_binlog_stmt_cache_size`

Description: Restricts the size used to cache non-transactional statements. The maximum theoretical value is 16 exabytes, but binary log positions cannot go beyond 4GB, so this is the default and recommended value. Between MariaDB/MySQL 5.5.3 and MariaDB/MySQL 5.5.8, max_binlog_cache_size restricted the size for both transaction and non-transaction caches, while from MariaDB 5.5.9, max_binlog_stmt_cache_size was introduced to set the limit for the non-transaction cache only. See max_binlog_stmt_cache_size and binlog_stmt_cache_size.
Commandline: --max_binlog_stmt_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4294963200
Range: 4096 to 18446744073709547520
Introduced: MariaDB/MySQL 5.5.9

`max_connect_errors`

Description: Limit to the number of successive failed connects from a host before the host is blocked from making further connections. The count for a host is reset to zero if they successfully connect. To unblock, flush the host cache with a FLUSH HOSTS statement or mysqladmin flush-hosts.
Commandline: --max_connect_errors=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 10

`max_connections`

Description: The maximum number of simultaneous client connections. See also Handling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system.
Commandline: --max_connections=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 151
Range: 1..100000

`max_delayed_threads`

Description: Limits to the number of INSERT DELAYED threads. Once this limit is reached, the insert is handled as if there was no DELAYED attribute. If set to 0, DELAYED is ignored entirely. The session value can only be set to 0 or to the same as the global value.
Commandline: --max_delayed_threads=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 20
Range: 0 to 16384

`max_error_count`

Description: Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
Commandline: --max_error_count=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 64
Range: 0 to 65535

`max_heap_table_size`

Description: Maximum size in rows for user-created MEMORY tables. Setting the variable while the server is active has no effect on existing tables unless they are recreated or altered. The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables.
Commandline: --max_heap_table_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16777216
Range - 32 bit: 16384 to 4294967295
Range - 64 bit: 16384 to 1844674407370954752

`max_insert_delayed_threads`

Description: Synonym for max_delayed_threads.

`max_join_size`

Description: Statements will not be performed if they are likely to need to examine more than this number of rows, row combinations or do more disk seeks. Can prevent poorly-formatted queries from taking server resources. Changing this value to anything other the default will reset sql_big_selects to 0. If sql_big_selects is set again, max_join_size will be ignored. This limit is also ignored if the query result is sitting in the query cache. Previously named sql_max_join_size, which is still a synonym.
Commandline: --max_join_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 4294967295 (< MariaDB/MySQL 5.5.0)
Range: 1 to 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 1 to 4294967295 (< MariaDB/MySQL 5.5.0)

`max_length_for_sort_data`

Description: If the extra columns used for the modified filesort algorithm would contain more bytes than this figure, the regular filesort algorithm is used instead. Setting too high can lead some sorts to result in higher disk activity and lower CPU activity.
Commandline: --max_length_for_sort_data=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 4 to 8388608

`max_long_data_size`

Description: Maximum size for parameter values sent with mysql_stmt_send_long_data(). If not set, it will default to max_allowed_packet.
Commandline: --max_long_data_size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1048576
Range: 1024 to 4294967295
Introduced: MariaDB/MySQL 5.5.11

`max_prepared_stmt_count`

Description: Maximum number of prepared statements on the server. Can help prevent certain forms of denial-of-service attacks. If set to 0, no prepared statements are permitted on the server.
Commandline: --max_prepared_stmt_count=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 16382
Range: 0 to 1048576

`max_relay_log_size`

Description: Slave will rotate its relay log if it exceeds this size after a write. If set to 0, the max_binlog_size setting is used instead.
Commandline: --max-relay-log-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0, or 4096 to 1073741824 (4KB to 1GB)

`max_seeks_for_key`

Description: The optimizer assumes that the number specified here is the most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans.
Commandline: --max_seeks_for_key=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value - 32 bit: 4294967295
Default Value - 64 bit: 18446744073709547520
Range - 32 bit: 1 to 4294967295
Range - 64 bit: 1 to 18446744073709547520

`max_sort_length`

Description: Maximum size in bytes used for sorting data values - anything exceeding this is ignored.
Commandline: --max_sort_length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 4 to 8388608

`max_sp_recursion_depth`

Description: Permitted number of recursive calls for a stored procedure. 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.
Commandline: --max_sp_recursion_depth[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 255

`max_tmp_tables`

Description: Unused.

`max_user_connections`

Description: Maximum simultaneous connections permitted for a user account. If set to 0, the default, there is no per-user limit. The session value is read-only and takes the global value unless there's a nonzero MAX_USER_CONNECTIONS value for the user. Previously having a minimum value of 0, from MariaDB 5.3, max_user_connections can be set to -1 to stop users from connecting to the server. The global max_user_connections variable does not affect users with the SUPER privilege.
Commandline: --max_user_connections=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: -1 to 4294967295

`max_write_lock_count`

Description: Read lock requests will be permitted for processing after this many write locks.
Commandline: --max_write_lock_count=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value - 32 bit: 4294967295
Default Value - 64 bit: 18446744073709547520
Range - 32-bit: 0-4294967295
Range - 64-bit: 0-18446744073709547520

`metadata_locks_cache_size`

Description: Size of the metadata locks cache, used for reducing the need to create and destroy synchronization objects. It is particularly helpful on systems where this process is inefficient, such as Windows XP.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1024
Range: 1 to 1048576
Introduced: MariaDB/MySQL 5.5.19

`min_examined_row_limit`

Description: If a query examines more than this number of rows, it is logged to the slow-query-log. If set to 0, the default, no row limit is used.
Commandline: --min-examined-row-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range - 32-bit: 0-4294967295
Range - 64-bit: 0-18446744073709547520

`mrr_buffer_size`

Description: Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization for more information.
Commandline: --mrr-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144
Range - 32 bit: 8192 to 2147483648
Introduced: MariaDB 5.3

`multi_range_count`

Description: Ignored. Use mrr_buffer_size instead.
Default Value: 256

`myisam_block_size`

Description: Block size to be used for MyISAM index pages.
Commandline: --myisam_block_size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1024

`myisam_data_pointer_size`

Description: Size in bytes of the default pointer, used in a MyISAM CREATE TABLE with no MAX_ROWS option.
Commandline: --myisam_data_pointer_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 6
Range: 2 to 7

`myisam_max_sort_file_size`

Description: Maximum size in bytes of the temporary file used while recreating a MyISAM index. If the this size is exceeded, the slower process of using the key cache is done instead.
Commandline: --myisam_max_sort_file_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value - 32 bit: 2147483648
Default Value - 64 bit: 9223372036854775807

`myisam_mmap_size`

Description: Maximum memory in bytes that can be used for memory mapping compressed MyISAM files. Too high a value may result in swapping if there are many compressed MyISAM tables.
Commandline: --myisam_mmap_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value - 32 bit: 4294967295
Default Value - 64 bit: 18446744073709547520
Range - 32-bit: 7 to 4294967295
Range - 64-bit: 7 to 18446744073709547520
Introduced: MariaDB/MySQL 5.5.1

`myisam_recover_options`

Description: MyISAM recovery mode. Multiple options can be selected, comma-delimited.
Scope: Global
Dynamic: No
Data Type: enumeration
Valid Values: OFF, DEFAULT, BACKUP, FORCE or QUICK

`myisam_repair_threads`

Description: If set to more than 1, the default, MyISAM table indexes each have their own thread during repair and sorting.
Commandline: --myisam_repair_threads=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range - 32-bit: 1 to 4294967295
Range - 64-bit: 1 to 18446744073709547520

`myisam_sort_buffer_size`

Description: Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
Commandline: --myisam_sort_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8388608
Range - 32 bit: 4 to 4294967295
Range - 64 bit: 4 to 18446744073709547520

`myisam_stats_method`

Description: Determines how NULLs are treated for MyISAM index statistics purposes. If set to nulls_equal, the default, all NULL index values are treated as a single group. This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. If set to nulls_unequal, the opposite approach is taken, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. Setting to nulls_ignored ignores NULLs altogether from index group calculations. See also Index Statistics, aria_stats_method, innodb_stats_method.
Commandline: --myisam_stats_method=name
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: nulls_equal
Valid Values: nulls_equal, nulls_unequal, nulls_ignored

`myisam_use_mmap`

Description: If set to 1 (0 is default), memory mapping will be used to reading and writing MyISAM tables.
Commandline: --myisam_use_mmap
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`named_pipe`

Description: On Windows systems, determines whether connections over named pipes are permitted.
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`net_buffer_length`

Description: The starting size, in bytes, for the connection and thread buffers for each client thread. The size can grow to max_allowed_packet. This variable's session value is read-only. Can be set to the expected length of client statements if memory is a limitation.
Commandline: --net_buffer_length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16384
Range: 1024 to 1048576

`net_read_timeout`

Description: Time in seconds the server will wait for a client connection to send more data before aborting the read. See also net_write_timeout and slave_net_timeout
Commandline: --net_read_timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 30
Range: 1 upwards

`net_retry_count`

Description: Permit this many retries before aborting when attempting to read or write on a communication port. On FreeBSD systems should be set higher as threads are sent internal interrupts..
Commandline: --net_retry_count=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10
Range - 32-bit: 1 to 4294967295
Range - 64-bit: 1 to 18446744073709547520 to ##

`net_write_timeout`

Description: Time in seconds to wait on writing a block to a connection before aborting the write. See also net_read_timeout and slave_net_timeout.
Commandline: --net_write_timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 60
Range: 1 upwards

`old`

Description: Disabled by default, enabling it reverts index hints to those used before MySQL 5.1.17. Enabling may lead to replication errors.
Commandline: --old
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`old_alter_table`

Description: If set to 1 (0 is default), MariaDB reverts to the pre-MySQL 5.0 method of processing ALTER TABLE statements.
Commandline: --old-alter-table
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`old_passwords`

Description: If set to 1 (0 is default), MariaDB reverts to the pre-MySQL 4.1 form of password hashing.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`open_files_limit`

Description: The number of file descriptors available to mysqld. Increase if you are getting the Too many open files error. If set to 0, then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files.
Commandline: --open-files-limit=count
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1024

`optimizer_prune_level`

Description: If set to 1, the default, the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space. If set to 1, heuristics are disabled and an exhaustive search is performed.
Commandline: --optimizer_prune_level[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`optimizer_search_depth`

Description: Maximum search depth by the query optimizer. Smaller values lead to less time spent on execution plans, but potentially less optimal results. If set to 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.
Commandline: --optimizer_search_depth[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 62
Range: 0 to 62

`optimizer_switch`

Description: A series of flags for controlling the query optimizer.
Commandline: --optimizer_switch=value
Scope: Global, Session
Dynamic: Yes
Data Type: string
Valid Values: engine_condition_pushdown={on|off} (>=MariaDB/MySQL 5.5.3), index_merge={on|off}, index_merge_intersection={on|off}, index_merge_sort_union={on|off}, index_merge_union={on|off}

`performance_schema`

Description: If set to 1 (0 is default), enables the Performance Schema
Commandline: --performance_schema=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: OFF
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_events_waits_history_long_size`

Description: Number of rows contained in the events_waits_history_long table.
Commandline: --performance_schema_events_waits_history_long_size=#
Scope: Global
Dynamic: No
Data Type: numeric
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_events_waits_history_size`

Description: Number of rows per thread contained in the events_waits_history table.
Commandline: --performance_schema_events_waits_history_size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_cond_classes`

Description: Specifies the maximum number of condition instruments.
Commandline: --performance_schema_max_cond_classes=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 80
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_cond_instances`

Description: Specifies the maximum number of instrumented condition objects.
Commandline: <<code>>--performance_schema_max_cond_classes=#<</code
Commandline: --performance_schema_max_cond_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_file_classes`

Description: Specifies the maximum number of file instruments.
Commandline: --performance_schema_max_file_classes=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 50
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_file_handles`

Description: Specifies the maximum number of opened file objects. Should always be higher than open_files_limit.
Commandline: --performance_schema_max_file_handles=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 32768
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_file_instances`

Description: Specifies the maximum number of instrumented file objects.
Commandline: --performance_schema_max_file_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_mutex_classes`

Description: Specifies the maximum number of mutex instruments.
Commandline: --performance_schema_max_mutex_classes=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 200
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_mutex_instances`

Description: Specifies the maximum number of instrumented mutex instrances.
Commandline: --performance_schema_max_mutex_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1000000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_rwlock_classes`

Description: Specifies the maximum number of rwlock instruments.
Commandline: --performance_schema_max_rwlock_classes=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 30
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_rwlock_instances`

Description: Specifies the maximum number of instrumented rwlock objects.
Commandline: --performance_schema_max_rwlock_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1000000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_table_handles`

Description: Specifies the maximum number of opened table objects.
Commandline: --performance_schema_max_table_handles=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_table_instances`

Description: Specifies the maximum number of instrumented table objects.
Commandline: --performance_schema_max_table_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1000
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_thread_classes`

Description: Specifies the maximum number of thread instruments.
Commandline: --performance_schema_max_thread_classes=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 50
Introduced: MariaDB/MySQL 5.5.3

`performance_schema_max_thread_instances`

Description: Specifies how many of the running server threads (see max_connections and max_delayed_threads) can be instrumented. Should be greater than the sum of max_connections and max_delayed_threads.
Commandline: --performance_schema_max_thread_instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1000
Introduced: MariaDB/MySQL 5.5.3

`pid_file`

Description: Full path of the process ID file.
Commandline: --pid-file=file_name
Scope: Global
Dynamic: No
Data Type: file name

`plugin_dir`

Description: Path to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
Commandline: --plugin_dir=path
Scope: Global
Dynamic: No
Data Type: directory name
Default Value: BASEDIR/lib/plugin
Introduced: MariaDB/MySQL 5.5.0

`plugin_maturity`

Description: The lowest acceptable plugin maturity. MariaDB will not load plugins less mature than that.
Commandline: --plugin-maturity=level
Scope: Global
Dynamic: No
Type: enum
Default Value: unknown
Valid Values: unknown, experimental, alpha, beta, gamma, stable
Introduced: MariaDB 5.2.1

`port`

Description: Port to listen for TCP/IP connections.
Commandline: --port=#, -P
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 3306
Range: 0 to 65535

`preload_buffer_size`

Description: Size in bytes of the buffer allocated when indexes are preloaded.
Commandline: --preload_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 32768
Range: 1024 to 1073741824

`profiling`

Description: If set to 1 (0 is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE().
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`profiling_history_size`

Description: Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES.
Commandline: --profiling_history_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 15
Range: 0 to 100

`progress_report_time`

Description: Time in seconds between sending progress reports to the client for time-consuming statements. If set to 0, progress reporting will be disabled.
Commandline: --progress-report-time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 56
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520
Introduced: MariaDB 5.3

`protocol_version`

Description: Client/server protocol version number.
Scope: Global
Dynamic: No
Data Type: numeric
Range: to

`proxy_user`

Description: Set to the proxy user account name if the current client is a proxy, else NULL.
Scope: Session
Dynamic: No
Data Type: string
Introduced: MariaDB/MySQL 5.5.7

`pseudo_slave_mode`

Description: For internal use by the server.
Scope: Session
Dynamic: Yes
Data Type: numeric
Default Value: OFF
Introduced: MariaDB/MySQL 5.5.30

`pseudo_thread_id`

Description: For internal use only.
Scope: Session
Dynamic: Yes
Data Type: numeric

`query_alloc_block_size`

Description: Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
Commandline: --query_alloc_block_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8192
Range - 32 bit: 1024 to 4294967295
Range - 64 bit: 1024 to 18446744073709547520

`query_cache_limit`

Description: Size in bytes for which results larger than this are not stored in the query cache.
Commandline: --query_cache_limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1048576 (1MB)
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`query_cache_min_res_un`

Description: Minimum size in bytes of the blocks allocated for query cache results.
Commandline: --query_cache_min_res_unit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4096 (4KB)
Range - 32 bit: 512 to 4294967295
Range - 64 bit: 512 to 18446744073709547520

`query_cache_size`

Description: Size in bytes available to the query cache. About 40KB is needed for query cache structures, so setting a size lower than this will result in a warning.
Commandline: --query_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Valid Values: 0 upwards in units of 1024.

`query_cache_strip_comments`

Description: If set to 1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the query cache.
Commandline: query-cache-strip-comments
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`query_cache_type`

Description: If set to 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, no only queries with SQL CACHE will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime.
Commandline: --query_cache_type=#
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: ON
Valid Values: 0 or OFF, 1 or ON, 2 or DEMAND

`query_cache_wlock_invalidate`

Description: If set to 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.
Commandline: --query_cache_wlock_invalidate
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`query_prealloc_size`

Description: Size in bytes of the oersistent buffer for query parsing and execution, allocated on connect and freed on disconnect. Increasing may be useful if complex queries are being run, as this will reduce the need for more memory allocations during query operation. See also query_alloc_block_size.
Commandline: --query_prealloc_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8192
Range - 32 bit: 8192 to 4294967295
Range - 64 bit: 8192 to 18446744073709547520

`rand_seed1`

Description: 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. The variable value cannot be viewed.

`rand_seed2`

Description: See rand_seed1.

`range_alloc_block_size`

Description: Size in bytes of blocks allocated during range optimization. The unit size in 1024.
Commandline: --range_alloc_block_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4096
Range - 32 bit: 4096 to 4294967295

`read_buffer_size`

Description: Each thread performing a sequential scan (for MyISAM, Aria and MERGE tables) allocates a buffer of this size in bytes for each table scanned. Increase if you perform many sequential scans. If not in a multiple of 4KB, will be rounded down to the nearest multiple. Also used in ORDER BY's for caching indexes in a temporary file (not temporary table), for caching results of nested queries, for bulk inserts into partitions, and to determine the memory block size of MEMORY tables.
Commandline: --read_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 131072
Range: 8200 to 2147479552

`read_only`

Description: When set to 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 or ANALYZE TABLE statements are excluded from this limitation. From MariaDB/MySQL 5.5, if read_only is set to 1, SET PASSWORD is limited only to users with the SUPER privilege. Changing this to 1 will fail if you have table locks or transactions pending, while it will wait until other locks are released or transactions are completed.
Commandline: --read-only
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`read_rnd_buffer_size`

Description: Size in bytes of the buffer used when reading rows from a MyISAM table in sorted order after a key sort. Larger values improve ORDER BY performance, although rather increase the size by SESSION where the need arises to avoid excessive memory use.
Commandline: --read_rnd_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144
Range: 8200 to 2147483647

`relay_log`

Description: Relay log basename. If not set, the basename will be hostname-relay-bin.
Commandline: --relay-log=file_name
Scope: Global
Dynamic: No
Data Type: filename
Default Value: (none)

`relay_log_index`

Description: Name of the relay log index file. Defaults to hostname-relay-bin.index.
Commandline: --relay-log-index
Scope: Global
Dynamic: No
Data Type: string

`relay_log_info_file`

Description: Name of the relay log information file.
Commandline: --relay-log-info-file=file_name
Scope: Global
Dynamic: No
Data Type: string
Default Value: relay-log.info

`relay_log_purge`

Description: If set to 1 (the default), relay logs will be purged as soon as they are no longer necessary.
Commandline: --relay-log-purge={0|1}
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON

`relay_log_recovery`

Description: If set to 1 (0 is default), the slave will drop all relay logs that haven't yet been processed, and retrieve relay logs from the master. Can be useful after the slave has crashed to prevent the processing of corrupt relay logs.
Commandline: --relay-log-recovery
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`relay_log_space_limit`

Description: Specifies the maximum space to be used for the relay logs. By default 0, or no limit.
Commandline: --relay_log_space_limit=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`replicate_annotate_row_events`

Description: Tells the slave to reproduce annotate_rows_events received from the master in its own binary log. This option is sensible only when used in tandem with the log_slave_updates option.
Commandline: --replicate-annotate-rows-events
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 5.3

`replicate_do_db`

Description: Slave threads will be restricted to replicating the specified databases, provided in comma-delimited format. Will not work with cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. Use replicate_wild_do_table for this purpose. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple databases. See Dynamic replication variables for more information.
Commandline: --replicate-do-db=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`replicate_do_table`

Description: Slave threads will be restricted to replicating the specified tables, provided in comma-delimited format. See Dynamic replication variables for more information. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple tables.
Commandline: --replicate-do-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`replicate_events_marked_for_skip`

Description: Tells the slave whether to replicate events that are marked with the @@skip_replication flag. See Selectively skipping replication of binlog events for more information.
Commandline: --replicate-events-marked-for-skip
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: replicate
Valid Values: REPLICATE, FILTER_ON_SLAVE, FILTER_ON_MASTER
Introduced: MariaDB 5.5.21

`replicate_ignore_db`

Description: Slave threads will be prohibited from replicating the specified databases, provided in comma-delimited format. Will not work with cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. Use replicate_wild_ignore_table for this purpose. See Dynamic replication variables for more information. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple databases.
Commandline: --replicate-ignore-db=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`replicate_ignore_table`

Description: Slave threads will be prohibited from replicating the specified tables, provided in comma-delimited format. See Dynamic replication variables for more information. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple tables.
Commandline: --replicate-ignore-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`replicate_wild_do_table`

Description: Slave threads will be restricted to replicating tables that match the specified wildcard pattern. For example replicate-wild-do-table=foo%.bar% will replicate only updates to tables in all databases that start with foo and whose table names start with bar. Will work with cross-database updates. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple wildcard patterns. See Dynamic replication variables for more information.
Commandline: --replicate-wild-do-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`replicate_wild_ignore_table`

Description: Slave threads will be prohibited from replicating tables that match the specified wildcard pattern. For example replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar. Will work with cross-database updates. The commandline directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple wildcard patterns. See Dynamic replication variables for more information.
Commandline: --replicate-wild-ignore-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: (empty)
Introduced: MariaDB 5.5.22

`report_host`

Description: The host name or IP address the slave reports to the master when it registers. If left unset, the slave will not register itself. Reported by SHOW SLAVE HOSTS.
Commandline: --report-host=host_name
Scope: Global
Dynamic: No
Data Type: string

`report_password`

Description: Slave password reported to the master when it registers. Reported by SHOW SLAVE HOSTS if --show-slave-auth-info is set. This password has no connection with user privileges or with the replication user account password.
Commandline: --report-password=password
Scope: Global
Dynamic: No
Data Type: string

`report_port`

Description: The commandline option sets the TCP/IP port for connecting to the slave that will be reported to the replicating master during the slave's registration. Viewing the variable will show this value.
Commandline: --report-port=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0
Range: 0 to 65535

`report_user`

Description: Slave's account user name reported to the master when it registers. Reported by SHOW SLAVE HOSTS if --show-slave-auth-info is set. This username has no connection with user privileges or with the replication user account.
Commandline: --report-user=name
Scope: Global
Dynamic: No
Data Type: string

`rowid_merge_buff_size`

Description: The maximum size in bytes of the memory available to the Rowid-merge strategy. See Non-semi-join subquery optimizations for more information.
Commandline: --rowid-merge-buff-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: #8388608
Range: 0 to 2147483647

`rpl_recovery_rank`

Description: Unused, will be removed

`safe_show_database`

Description: This variable was removed in MariaDB/MySQL 5.5.3, and has been replaced by the more flexible SHOW DATABASES privilege.
Commandline: --safe-show-database (until MySQL 4.1.1)
Scope: Global
Dynamic: Yes
Data Type: boolean
Removed: MariaDB/MySQL 5.5.3

`secure_auth`

Description: Connections will be blocked unless clients use the current password format. Prior to MySQL 4.1 passwords used an old, less secure format, and by default these connections are accepted. For the mysql client, the --secure-auth option blocks connections to servers requiring the old format. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format.
Commandline: --secure-auth
Scope: Global
Dynamic: Yes

`secure_file_priv`

Description: LOAD DATA, SELECT ... INTO and LOAD FILE() will only work with files in the specified path. If not set, the default, the statements will work with any files that can be accessed.
Commandline: --secure-file-priv=path
Scope: Global
Dynamic: No
Data Type: path name
Default Value: None

`server_id`

Description: Used to identify master and slave servers in replication. The server_id must be unique for each server in the replicating group. If left at 0, the default, a slave will not connect to a master, and a master will refuse all slave connections. The server_id can go up to 2³²-1, but see server_id_bits.
Commandline: --server-id =#
Scope: Global, Session (>= MariaDB 10.0.2 only - see Global Transaction ID)
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 4294967295

`server_id_bits`

Description: Only supported by MySQL Cluster, not the standard server. Allows the effective server-id to be determined by indicating the number of least significant bits within the 32-bit server-id that actually identify the server. This allows the remaining bits to be used elsewhere. The server_id maximum when server_id_bits is set is 2^{server_id_bits}-1 rather than 2³²-1, the default maximum.
Commandline: --server-id-bits=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 32
Range: 7 to 32

`shared_memory`

Description: In Windows, determines whether the server permits shared memory connections. See also shared_memory_base_name.
Scope: Global
Dynamic: No

`shared_memory_base_name`

Description: The name of the shared memory to use for shared memory connections in Windows. Mainly used when running more than one instance on the same physical machine. By default the name is MYSQL and is case sensitive. See also shared_memory.
Scope: Global
Dynamic: No
Data Type: string
Default Value: MYSQL

`skip_external_locking`

Description: If set, external locking for MyISAM tables is disabled.
Commandline: --skip-external-locking
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 1

`skip_name_resolve`

Description: If set to 1 (0 is the default), only IP addresses are used for connections. Host names are not resolved. All host values in the GRANT tables must be IP addresses (or localhost).
Commandline: --skip-name-resolve
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`skip_networking`

Description: If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server by be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server.
Commandline: --skip-networking
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`skip_replication`

Description: Changes are logged into the binary log with the @@skip_replication flag set. See Selectively skipping replication of binlog events for more information.
Commandline: --skip-replication
Scope: Session
Dynamic: Yes
Data Type: boolean
Default Value: OGG
Introduced: MariaDB 5.5.21

`skip_show_database`

Description: If set to 1, (0 is the default), only users with the SHOW DATABASES privilege can use the SHOW DATABASES statement to see all database names.
Commandline: --skip-show-database
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`slave_allow_batching`

Description: Only available as part of MySQL Cluster, this variable determines whether to enable batched updates on MySQL Cluster replication slaves. The default is 0, where batch updating is enabled.
Commandline: --slave-allow-batching
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 0

`slave_compressed_protocol`

Description: If set to 1 (0 is the default), will use compression for the slave/master protocol if both master and slave support this.
Commandline: --slave_compressed_protocol
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 0

`slave_exec_mode`

Description: Determines the mode used for replication error checking and conflict resolution. STRICT mode is the default, and catches all all errors and conflicts. IDEMPOTENT mode suppresses duplicate key or no key errors, which can be useful in certain replication scenarios, such as when there are multiple masters, or circular replication. MySQL Cluster ignores this setting, always treating it as IDEMPOTENT.
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: IDEMPOTENT (NDB), STRICT (All)
Valid Values: IDEMPOTENT, STRICT

`slave_load_tmpdir`

Description: Directory where the slave stores temporary files for replicating LOAD DATA INFILE statements. If not set, the slave will use tmpdir. Should be set to a disk-based directory that will survive restarts, or else replication may fail.
Commandline: --slave-load-tmpdir=path
Scope: Global
Dynamic: No
Data Type: file name
Default Value: /tmp

`slave_max_allowed_packet`

Description: Maximum packet size in bytes for slave SQL and I/O threads. This value overrides max_allowed_packet for replication purposes. Set in multiples of 1024 (the minimum) up to 1GB
Commandline: --slave-max-allowed-packet=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1073741824
Range: 1024 to 1073741824
Introduced: MariaDB/MySQL 5.5.26

`slave_net_timeout`

Description: Time in seconds for the slave to wait for more data from the master before considering the connection broken, after which it will abort the read and attempt to reconnect. The retry interval is determined by the MASTER_CONNECT_RETRY open for the CHANGE MASTER statement, while the maximum number of reconnection attempts is set by the master-retry-count variable. The first reconnect attempt takes place immediately.
Commandline: --slave-net-timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 3600 (1 hour)
Range: 1 upwards

`slave_skip_errors`

Description: When an error occurs on the slave, replication usually halts. This option permits a list of error codes to ignore, and for which replication will continue. This option should never be needed in normal use, and careless use could lead to slaves that are out of sync with masters. Error codes are in the format of the number from the slave error log. Using all as an option permits the slave the keep replicating no matter what error it encounters, an option you would never normally need in production and which could rapidly lead to data inconsistencies. MySQL Cluster NDB >= 7.2.6 permits the ddl_exists_errors option, indicating error codes 1007,1008,4050,1051,1054,1060,1061,1068,1094 and 1146.
Commandline: --slave-skip-errors=[error_code1,error_code2,...|all|ddl_exist_errors]
Scope: Global
Dynamic: No
Data Type: string
Default Value: OFF
Valid Values: [list of error codes], all, ddl_exist_errors

`slave_sql_verify_checksum`

Description: Verify binlog checksums when the slave SQL thread reads events from the relay log.
Commandline: --slave_sql_verify_checksum=[0|1]
Scope: Global
Access Type: Can be changed dynamically
Data Type: bool
Default Value: ON (1)
Introduced: MariaDB 5.3

`slave_transaction_retries`

Description: Number of times a replication slave retries to execute an SQL thread after it fails due to InnDB deadlock or by exceeding the transaction execution time limit. If after this number of tries the SQL thread has still failed to execute, the slave will stop with an error. See also the innodb_lock_wait_timeout system variable.
Commandline: --slave_transaction_retries=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 10
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`slave_type_conversions`

Description: Determines the type conversion mode on the slave when using row-based replication, including MySQL Cluster Replication. Multiple options can be set, delimited by commas. If left empty, the default, type conversions are disallowed. The slave needs to be restarted for changes to take effect.
Commandline: --slave_type_conversions=set
Scope: Global
Dynamic: No
Data Type: set
Default Value: Empty variable
Valid Values: ALL_LOSSY, ALL_NON_LOSSY, empty
Introduced: MariaDB/MySQL 5.5.3

`slow_launch_time`

Description: Time in seconds. If a thread takes longer than this to launch, the slow_launch_threads server status variable is incremented.
Commandline: --slow_launch_time=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 2

`slow_query_log`

Description: If set to 0, the default unless the --slow-query-log option is used, the slow query log is disabled, while if set to 1, the slow query log is enabled. See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if slow_query_log is set to 1.
Commandline: --slow-query-log
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 0

`slow_query_log_file`

Description: Name of the slow query log file.
Commandline: --slow-query-log-file=file_name
Scope: Global
Dynamic: Yes
Data Type: file name
Default Value: host_name-slow.log

`socket`

Description: On Unix-like systems, this is the name of the socket file used for local client connections, by default /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.
Commandline: --socket=name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: /tmp/mysql.sock (Unix), MySQL (Windows)

`sort_buffer_size`

Description: Each session performing a sort allocates a buffer with this amount of memory. Not specific to any storage engine. See Optimizing sort_buffer_size for more.
Commandline: --sort_buffer_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: number
Default Value: 4M (4194304)

`sql_auto_is_null`

Description: If set to 1, the query 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.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0 (>= MariaDB/MySQL 5.5.3), 1 (MariaDB/MySQL <= 5.5.2)

`sql_big_selects`

Description: If set to 0, MariaDB will not perform large SELECTs. See max_join_size for details. If max_join_size is set to anything but DEFAULT, sql_big_selects is automatically set to 0. If sql_big_selects is again set, max_join_size will be ignored.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_big_tables`

Description: A synonym for big_tables.

`sql_buffer_result`

Description: If set to 1 (0 is default), results from SELECT statements are always placed into temporary tables. This can help the server when it takes a long time to send the results to the client by allowing the table locks to be freed early.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`sql_log_bin`

Description: If set to 0 (1 is the default), no logging to the binary log is done for the client. Only clients with the SUPER privilege can update this variable.
Scope: Session
Dynamic: Yes
Data Type: boolean
Default Value: ##

`sql_log_off`

Description: If set to 1 (0 is the default), no logging to the general query log is done for the client. Only clients with the SUPER privilege can update this variable.
Scope: Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`sql_log_update`

Description: Removed. Use sql_log_bin instead.
Removed: MariaDB/MySQL 5.5

`sql_low_priority_updates`

Description: Synonym for low_priority_updates, the preferred name.

`sql_max_join_size`

Description: Synonym for max_join_size, the preferred name.

`sql_mode`

Description: Sets the SQL Mode. Multiple modes can be set, separated by a comma.
Commandline: --sql-mode=value[,value[,value...]]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: (empty string)
Valid Values: See SQL Mode for the full list.

`sql_notes`

Description: If set to 1, the default, warning_count is incremented each time a Note warning is encountered. If set to 0, Note warnings are not recorded. mysqldump has outputs to set this variable to 0 so that no unnecessary increments occur when data is reloaded.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_quote_show_create`

Description: If set to 1, the default, the server will quote identifiers for SHOW CREATE DATABASE and [show-reate-table|SHOW CREATE TABLE]] statements. Quoting is disabled if set to 0. Enable to ensure replications works when identifiers require quoting.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_safe_updates`

Description: If set to 1, UPDATE's and DELETE's need either a key in the WHERE clause, or a LIMIT clause, or else they will aborted. Prevents the common mistake of accidentally deleting or updating every row in a table.
Dynamic: Yes
Data Type: boolean
Default Value: OFF (0)

`sql_select_limit`

Description: Maximum number of rows that can be returned from a SELECT query. Default is the maximum number of rows permitted per table by the server, usually 2³²-1 or 2⁶⁴-1. Can be restored to the default value after being changed by assigning it a value of DEFAULT.
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0

`sql_slave_skip_counter`

Description: Number of events that a slave skips from the master. If this would cause the slave to begin in the middle of an event group, the slave will instead begin from the beginning of the next event group. See SET GLOBAL sql_slave_skip_counter.
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0

`sql_warnings`

Description: If set to 1, single-row INSERT's will produce a string containing warning information if a warning occurs.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF (0)

`ssl-ca`

Description: Path to a PEM file containing list of trusted SSL certificate authorities. directory containing trusted SSL authority certificates, in PEM format. If this and ssl_ca are both unspecified, the client will not authenticate the server certificate. The server still verifies the client using any --ssl-ca and -ssl-capath values given to it at startup, or based on any requirements established from GRANT statements.
Commandline: --ssl-ca=file_name
Scope: Global
Dynamic: No
Data Type: file name

`ssl-capath`

Description: Path to the directory containing trusted SSL authority certificates, in PEM format. If this and ssl_ca are both unspecified, the client will not authenticate the server certificate. The server still verifies the client using any --ssl-capath and -ssl-ca values given to it at startup, or based on any requirements established from GRANT statements. MariaDB distributions built with OpenSSL support --ssl-capath, while those built with yaSSL do not normally, since yaSSL wants all components of the CA certificate to be contained within a single certificate tree, and each certificate in the file give a unique SubjectName value. To get around this, create a new file containing concatenated individual certificate files in the tree, and specify this new file as the --ssl-capath option.
Commandline: --ssl-capath=directory_name
Scope: Global
Dynamic: No
Data Type: directory name

`ssl_cert`

Description: Name of the SSL certificate file used to establish a secure SSL connection.
Commandline: --ssl-cert=name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: None

`ssl_cipher`

Description: List of permitted ciphers to use for SSL encryption.
Commandline: --ssl-cipher=name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: None

`ssl_key`

Description: The SSL key file used to establish a secure SSL connection
Commandline: --ssl-key=name
Scope: Global
Dynamic: No
Data Type: string
Default Value: None

`storage_engine`

Description: See default_storage_engine.
Deprecated: MariaDB/MySQL 5.5.3

`stored_program_cache`

Description: Limit to the number of stored routines held in the stored procedures and stored functions caches. Each time a stored routine is executed, this limit is first checked, and if the number held in the cache exceeds this, that cache is flushed and memory freed.
Commandline: --stored-program-cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 256
Range: 256 to 524288
Introduced: MariaDB/MySQL 5.5.21

`sync_binlog`

Description: MariaDB will synchronize its binary log file to disk after this many events. The default is 0, in which case the operating system handles flushing the file to disk. 1 is the safest, but slowest, choice, since the file is flushed after each write. If autocommit is enabled, there is one write per statement, otherwise there's one write per transaction. If the disk has cache backed by battery, synchronization will be fast and a more conservative number can be chosen.
Commandline: --sync-binlog=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`sync_frm`

Description: If set to 1, the default, each time a non-temporary table is created, its .frm definition file is synced to disk. Fractionally slower, but safer in case of a crash.
Commandline: --sync-frm
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: TRUE

`sync_master_info`

Description: A replication slave will synchronize its master.info file to disk after this many events. Usually best left at 0, the default, in which case the operating system handles flushing the file to disk.
Commandline: --sync-master-info=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0

`sync_relay_log`

Description: The MariaDB server will synchronize its relay log to disk after this many writes to the log. The default is 0, in which case the operating system handles flushing the file to disk. 1 is the safest, but slowest, choice, since the file is flushed after each write. If autocommit is enabled, there is one write per statement, otherwise there's one write per transaction. If the disk has cache backed by battery, synchronization will be fast and a more conservative number can be chosen.
Commandline: --sync-relay-log=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0

`sync_relay_log_info`

Description: A replication slave will synchronize its relay-log.info file to disk after this many transactions. The default is 0, in which case the operating system handles flushing the file to disk. 1 is the most secure choice.
Commandline: --sync-relay-log-info=#
Scope: Global,
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: to

`system_time_zone`

Description: Server system time zone, inherited from the machine setting when the server starts. Usually specified by the TZ environment variable, can also be specified by the --timezone option of the mysqld_safe script, or affected by the environment of the account starting the server. Not the same as time_zone, which is used for client time zones.
Scope: Global
Dynamic: No
Data Type: string

`table_definition_cache`

Description: Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the cache size can speed up table opening. Unlike the table_open_cache, as the table_definition_cache doesn't use file descriptors, and is much smaller.
Commandline: --table_definition_cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 400
Range: 400 to 64524288

`table_lock_wait_timeout`

Description: Unused, and removed in MariaDB/MySQL 5.5.3
Commandline: --table_lock_wait_timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 50
Range: 1 to 1073741824
Removed: MariaDB/MySQL 5.5.3

`table_open_cache`

Description: Number of open tables for all threads. See Optimizing table_open_cache for suggestions on optimizing. Increasing table_open_cache increases the number of file descriptors required. Previously called table_cache prior to MySQL 5.1.3
Commandline: --table-open-cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 400

`table_type`

Description: Removed and replaced by storage_engine in MariaDB/MySQL 5.5.3. Use default_storage_engine instead.

`thread_cache_size`

Description: Number of threads server caches for re-use. If this limit hasn't been reached, when a client disconnects, its threads are put into the cache, and re-used where possible. Normally this setting has little effect, as the other aspects of the thread implementation are more important, but increasing it can help servers with high volumes of connections per second so that most can use a cached, rather than a new, thread. The cache miss rate can be calculated as the server status variables threads_created/connections.
Commandline: --thread_cache_size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 128

`thread_concurrency`

Description: Allows applications to give the system a hint about the desired number of threads. Specific to Solaris only, invokes thr_setconcurrency().
Commandline: --thread_concurrency=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10
Range: 1 to 512

`thread_handling`

Description: Determines how the server handles threads. The default, one-thread-per-connection, sees the server use one thread to handle each client connection, while no-threads sees the server use a single thread for all connections. If the thread pool plugin is enabled, it will set this value to dynamically-loaded and handle threads in its own way.
Commandline: --thread_handling=name
Scope: Global
Dynamic: No
Data Type: enumeration
Default Value: one-thread-per-connection
Valid Values: no-threads, one-thread-per-connection or dynamically-loaded (>= MariaDB/MySQL 5.5.16 only with the thread plugin loaded)

`thread_pool_algorithm`

Description: Only available if the thread pool plugin is enabled. If set to the default, 0, the thread pool plugin uses a more conservative low-concurrency algorithm, well-tested and stable. If set to 1, the plugin uses a more aggressive algorithm which performs 5-10% better in some situations, but degrades with more connections. This algorithm is experimental and not supported.
- Commandline: thread_pool_algorithm
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0
Range: 0 to 1
Introduced: MariaDB/MySQL 5.5.16

`thread_pool_high_priority_connection`

Description: Only available if the thread pool plugin is enabled. If set to 0, the default, statement queueing uses both the low and the high priority queues. If set to 1, queued statements always use the high priority queue.
Commandline: --thread_pool_high_priority_connection=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 1
Introduced: MariaDB/MySQL 5.5.16

`thread_pool_idle_timeout`

Description: Time in seconds until an idle worker thread exits. Unix-only. Use thread_pool_min_threads in Windows. See Thread pool in MariaDB 5.5.
Commandline: thread-pool-idle-timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 60
Introduced: MariaDB 5.5

`thread_pool_max_threads`

Description: Maximum number of threads in the thread pool. Once this limit is reached, no new threads will be created. See Thread pool in MariaDB 5.5.
Commandline: thread-pool-max-threads=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 500
Range: 1 to 65536
Introduced: MariaDB 5.5

`thread_pool_max_unused_threads`

Description: Only available if the thread pool plugin is enabled. The maximum number of unused threads permitted in the thread pool. By default 0 (no limit), setting a value limits the memory that can be used by sleeping threads. If a thread is ready to sleep but the sleeping threads are already at their maximum, the thread will instead exit.
Commandline: --thread_pool_max_unused_threads=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 4096
Introduced: MariaDB/MySQL 5.5.16

`thread_pool_min_threads`

Description: Minimum number of threads in the thread pool. In bursty environments, after a period of inactivity, threads would normally be retired. When the next burst arrives, it would take time to reach the optimal level. Setting this value higher than the default would prevent thread retirement even if inactive. Windows-only. Use thread_pool_idle_timeout in Unix. See Thread pool in MariaDB 5.5 for more information.
Data Type: numeric
Default Value: 1
Introduced: MariaDB 5.5

`thread_pool_oversubscribe`

Description: Internal parameter, not meant to be changed. The higher the value for this parameter, the more threads that could run at the same time. A lower value can lead to more sleeps and wakeups. See Thread pool in MariaDB 5.5 for more information.
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 3
Range: 1 to 65536
Introduced: MariaDB 5.5

`thread_pool_prio_kickup_timer`

Description: Only available if the thread pool plugin is enabled. Time in milliseconds before moving a statement waiting for execution on the low priority queue to the high priority queue. The default is 1 second (1000 milliseconds).
Commandline: --thread_pool_prio_kickup_timer=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1000
Range: 0 to 4294967294
Introduced: MariaDB/MySQL 5.5.16

`thread_pool_size`

Description: Only available if the thread pool plugin is enabled. Number of threads in a thread pool, which determines how many statements can execute simultaneously.
Commandline: --thread_pool_size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 16
Range: 1 to 64
Introduced: MariaDB/MySQL 5.5.16

`thread_pool_stall_limit`

Description: Only available if the thread pool plugin is enabled. Time a statement has to complete after starting before being defined as stalled. After this, the thread group can begin another statement. The unit is 10 milliseconds, so the default value of 6 is 60 milliseconds. Higher values can help avoid starting too many new statements while old, long-running ones are still executing, while lower values can help prevent deadlock.
Commandline: --thread_pool_stall_limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 6
Range: 4 to 600
Introduced: MariaDB/MySQL 5.5.16

`thread_stack`

Description: Stack size for each thread. If set too small, limits recursion depth of stored procedures and complexity of SQL statements the server can handle in memory. Also affects limits in the crash-me test.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 294912

`time_format`

Description: Unused.

`time_zone`

Description: The current time zone, used to initialize the time zone for a client when it connects. Set to SYSTEM by default, in which the client uses the system time zone value.
Commandline: --default_time_zone=string
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: SYSTEM

`timed_mutexes`

Description: Determines whether InnoDB mutexes are timed. OFF, the default, disables mutex timing, while ON enables it. See also SHOW ENGINE for more on mutex statistics.
Commandline: --timed_mutexes
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`timestamp`

Description: Sets the time for the client. This will affect the result returned by the NOW() function, not the SYSDATE() function, unless the server is started with the --sysdate-is-now option, in which case SYSDATE() becomes an alias of now, and will also be affected. Also used to get the original timestamp when restoring rows from the binary log.
Scope: Session
Dynamic: Yes
Valid Values: timestamp_value (Unix epoch timestamp, not MySQL timestamp), DEFAULT

`tmp_table_size`

Description: The largest size for temporary tables in memory (not MEMORY tables) although if max_heap_table_size is smaller the lower limit will apply. If a table exceeds the limit, MariaDB converts it to a MyISAM or Aria table. You can see if it's necessary to increase by comparing the status variables 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.
Commandline: --tmp_table_size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: system-dependent
Range: 1024 to 4294967295

`tmpdir`

Description: Directory for storing temporary tables and files. Can specify a list (separated by semicolons in Windows, and colons in Unix that will then be used in round-robin fashion. This can be used for load balancing across several disks. Note that if the server is a replication slave, and slave_load_tmpdir, which overrides tmpdir for slave replication, is not set, you should not set tmpdir to a directory that is cleared when the machine restarts, or else replication may fail.
Commandline: --tmpdir=path or -t path
Scope: Global
Dynamic: No
Type: directory name/s

`transaction_alloc_block_size`

Description: Size in bytes to increase the memory pool available to each transaction when the available pool is not large enough. See transaction_prealloc_size.
Commandline: --transaction_alloc_block_size=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 8192
Range - 32-bit: 1024 to 4294967295
Range - 64-bit: 1024 to 18446744073709547520
Block Size: 1024

`transaction_prealloc_size`

Description: Initial size of a memory pool available to each transaction for various memory allocations. If the memory pool is not large enough for an allocation, it is increased by transaction_alloc_block_size bytes, and truncated back to transaction_prealloc_size bytes when the transaction is completed. If set large enough to contain all statements in a transaction, extra malloc() calls are avoided.
Commandline: --transaction_prealloc_size=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 4096
Range - 32-bit: 1024 to 4294967295
Range - 64-bit: 1024 to 18446744073709547520
Block Size: 1024

`tx_isolation`

Description: The transaction isolation level. See also SET TRANSACTION.
Commandline: --transaction-isolation=name
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: REPEATABLE-READ
Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

`unique_checks`

Description: If set to 1, the default, secondary indexes in InnoDB tables are performed. If set to 0, storage engines can (but are not required to) assume that duplicate keys are not present in input data. Set to 0 to speed up imports of large tables to InnoDB. The storage engine will still issue a duplicate key error if it detects one, even if set to 0.
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default Value: 1

`updatable_views_with_limit`

Description: Determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table. 0 prohibits this, while 1 permits it while issuing a warning (the default).
Commandline: --updatable_views_with_limit=#
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default Value: 1

`userstat`

Description: If set to 1, user statistics will be activated.
Commandline: --userstat=1
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB

`version`

Description: Server version number. It may also include a suffix with configuration or build information. -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
Scope: Global
Dynamic: No
Type: string

`version_comment`

Description: Value of the COMPILATION_COMMENT option specified by CMake when building MariaDB, for example mariadb.org binary distribution
Scope: Global
Dynamic: No
Type: string

`version_compile_machine`

Description: The machine type or architecture MariaDB was built on, for example i686
Scope: Global
Dynamic: No
Type: string

`version_compile_os`

Description: Operating system that MariaDB was built on, for example debian-linux-gnu
Scope: Global
Dynamic: No
Type: string

`wait_timeout`

Description: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.
Commandline: --wait_timeout=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 28800
Range: 1 to 2147483

`warning_count`

Description: Read-only variable indicating the number of warnings, errors and notes resulting from the most recent statement that generated messages. See SHOW WARNINGS for more. Note warnings will only be recorded if sql_notes is true (the default).
Scope: Session
Dynamic: No
Type: numeric

Comments

Comments loading...

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Server System Variables

Contents

About the Server System Variables

Setting Server System Variables

List of Server System Variables

auto_increment_increment

auto_increment_offset

autocommit

automatic_sp_privileges

back_log

basedir

big_tables

bind_address

binlog_cache_size

binlog_checksum

binlog_direct_non_transactional_updates

binlog_format

binlog_optimize_thread_scheduling

binlog_stmt_cache_size

bulk_insert_buffer_size

character_set_client

character_set_connection

character_set_database

character_set_filesystem

character_set_results

character_set_server

character_set_system

character_sets_dir

collation_connection

collation_database

collation_server

completion_type

concurrent_insert

connect_timeout

datadir

date_format

datetime_format

deadlock_search_depth_long

deadlock_search_depth_short

deadlock_timeout_long

deadlock_timeout_short

debug

debug_sync

default_storage_engine

default_week_format

delay_key_write

delayed_insert_limit

delayed_insert_timeout

delayed_queue_size

div_precision_increment

engine_condition_pushdown

error_count

event_scheduler

expensive_subquery_limit

expire_logs_days

external_user

extra_max_connections

extra_port

flush

flush_time

foreign_key_checks

ft_boolean_syntax

ft_max_word_len

ft_min_word_len

ft_query_expansion_limit

ft_stopword_file

general_log

general_log_file

group_concat_max_len

have_compress

have_crypt

have_csv

have_dynamic_loading

have_geometry

have_innodb

have_ndbcluster

have_openssl

have_partitioning

have_profiling

have_query_cache