5.1.4 Server System Variables

The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions.

There are several ways to see the names and values of system variables:

This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 releases. For historical information concerning their implementation, please see http://dev.mysql.com/doc/refman/5.0/en/, and http://dev.mysql.com/doc/refman/4.1/en/.

The following table lists all available system variables.

Table 5.2 System Variable Summary

NameCmd-LineOption fileSystem VarVar ScopeDynamic
auto_increment_increment  YesBothYes
auto_increment_offset  YesBothYes
autocommitYesYesYesSessionYes
automatic_sp_privileges  YesGlobalYes
back_log  YesGlobalNo
basedirYesYesYesGlobalNo
big-tablesYesYes  Yes
- Variable: big_tables  YesSessionYes
bind-addressYesYesYesGlobalNo
binlog_cache_sizeYesYesYesGlobalYes
binlog_direct_non_transactional_updatesYesYesYesBothYes
binlog-formatYesYes  Yes
- Variable: binlog_format  YesBothYes
bulk_insert_buffer_sizeYesYesYesBothYes
character_set_client  YesBothYes
character_set_connection  YesBothYes
character_set_database[a]  YesBothYes
character-set-filesystemYesYes  Yes
- Variable: character_set_filesystem  YesBothYes
character_set_results  YesBothYes
character-set-serverYesYes  Yes
- Variable: character_set_server  YesBothYes
character_set_system  YesGlobalNo
character-sets-dirYesYes  No
- Variable: character_sets_dir  YesGlobalNo
collation_connection  YesBothYes
collation_database[b]  YesBothYes
collation-serverYesYes  Yes
- Variable: collation_server  YesBothYes
completion_typeYesYesYesBothYes
concurrent_insertYesYesYesGlobalYes
connect_timeoutYesYesYesGlobalYes
datadirYesYesYesGlobalNo
date_format  YesBothNo
datetime_format  YesBothNo
debugYesYesYesBothYes
debug_sync  YesBothYes
default-storage-engineYesYesYesBothYes
default_week_formatYesYesYesBothYes
delay-key-writeYesYes  Yes
- Variable: delay_key_write  YesGlobalYes
delayed_insert_limitYesYesYesGlobalYes
delayed_insert_timeoutYesYesYesGlobalYes
delayed_queue_sizeYesYesYesGlobalYes
div_precision_incrementYesYesYesBothYes
engine-condition-pushdownYesYes  Yes
- Variable: engine_condition_pushdown  YesBothYes
error_count  YesSessionNo
event-schedulerYesYes  Yes
- Variable: event_scheduler  YesGlobalYes
expire_logs_daysYesYesYesGlobalYes
flushYesYesYesGlobalYes
flush_timeYesYesYesGlobalYes
foreign_key_checks  YesSessionYes
ft_boolean_syntaxYesYesYesGlobalYes
ft_max_word_lenYesYesYesGlobalNo
ft_min_word_lenYesYesYesGlobalNo
ft_query_expansion_limitYesYesYesGlobalNo
ft_stopword_fileYesYesYesGlobalNo
general-logYesYes  Yes
- Variable: general_log  YesGlobalYes
general_log_fileYesYesYesGlobalYes
group_concat_max_lenYesYesYesBothYes
have_archive  YesGlobalNo
have_blackhole_engine  YesGlobalNo
have_community_features  YesGlobalNo
have_compress  YesGlobalNo
have_crypt  YesGlobalNo
have_csv  YesGlobalNo
have_dynamic_loading  YesGlobalNo
have_example_engine  YesGlobalNo
have_federated_engine  YesGlobalNo
have_geometry  YesGlobalNo
have_innodb  YesGlobalNo
have_isam  YesGlobalNo
have_merge_engine  YesGlobalNo
have_ndbcluster  YesGlobalNo
have_openssl  YesGlobalNo
have_partition_engine  YesGlobalNo
have_partitioning  YesGlobalNo
have_query_cache  YesGlobalNo
have_raid  YesGlobalNo
have_row_based_replication  YesGlobalNo
have_rtree_keys  YesGlobalNo
have_ssl  YesGlobalNo
have_symlink  YesGlobalNo
hostname  YesGlobalNo
identity  YesSessionYes
ignore-builtin-innodbYesYes  No
- Variable: ignore_builtin_innodb  YesGlobalNo
init_connectYesYesYesGlobalYes
init-fileYesYes  No
- Variable: init_file  YesGlobalNo
init_slaveYesYesYesGlobalYes
innodb_adaptive_flushingYesYesYesGlobalYes
innodb_adaptive_hash_indexYesYesYesGlobalNo
innodb_additional_mem_pool_sizeYesYesYesGlobalNo
innodb_autoextend_incrementYesYesYesGlobalYes
innodb_autoinc_lock_modeYesYesYesGlobalNo
innodb_buffer_pool_awe_mem_mbYesYesYesGlobalNo
innodb_buffer_pool_sizeYesYesYesGlobalNo
innodb_change_bufferingYesYesYesGlobalYes
innodb_checksumsYesYesYesGlobalNo
innodb_commit_concurrencyYesYesYesGlobalYes
innodb_concurrency_ticketsYesYesYesGlobalYes
innodb_data_file_pathYesYesYesGlobalNo
innodb_data_home_dirYesYesYesGlobalNo
innodb_doublewriteYesYesYesGlobalNo
innodb_fast_shutdownYesYesYesGlobalYes
innodb_file_formatYesYesYesGlobalYes
innodb_file_format_checkYesYesYesGlobalYes
innodb_file_io_threadsYesYesYesGlobalNo
innodb_file_per_tableYesYesYesGlobalNo
innodb_flush_log_at_trx_commitYesYesYesGlobalYes
innodb_flush_methodYesYesYesGlobalNo
innodb_force_recoveryYesYesYesGlobalNo
innodb_io_capacityYesYesYesGlobalNo
innodb_lock_wait_timeoutYesYesYesBothNo
innodb_locks_unsafe_for_binlogYesYesYesGlobalNo
innodb_log_arch_dirYesYesYesGlobalNo
innodb_log_archiveYesYesYesGlobalNo
innodb_log_buffer_sizeYesYesYesGlobalNo
innodb_log_file_sizeYesYesYesGlobalNo
innodb_log_files_in_groupYesYesYesGlobalNo
innodb_log_group_home_dirYesYesYesGlobalNo
innodb_max_dirty_pages_pctYesYesYesGlobalYes
innodb_max_purge_lagYesYesYesGlobalYes
innodb_mirrored_log_groupsYesYesYesGlobalNo
innodb_old_blocks_pctYesYesYesGlobalYes
innodb_old_blocks_timeYesYesYesGlobalYes
innodb_open_filesYesYesYesGlobalNo
innodb_random_read_aheadYesYesYesGlobalYes
innodb_read_ahead_thresholdYesYesYesGlobalYes
innodb_read_io_threadsYesYesYesGlobalNo
innodb_replication_delayYesYesYesGlobalYes
innodb_rollback_on_timeoutYesYesYesGlobalNo
innodb_spin_wait_delayYesYesYesGlobalYes
innodb_stats_methodYesYesYesGlobalYes
innodb_stats_on_metadataYesYesYesGlobalYes
innodb_stats_sample_pagesYesYesYesGlobalYes
innodb_strict_modeYesYesYesBothYes
innodb_support_xaYesYesYesBothYes
innodb_sync_spin_loopsYesYesYesGlobalYes
innodb_table_locksYesYesYesBothYes
innodb_thread_concurrencyYesYesYesGlobalYes
innodb_thread_sleep_delayYesYesYesGlobalYes
innodb_use_legacy_cardinality_algorithmYesYesYesGlobalYes
innodb_use_sys_mallocYesYesYesGlobalNo
innodb_version  YesGlobalNo
innodb_write_io_threadsYesYesYesGlobalNo
insert_id  YesSessionYes
interactive_timeoutYesYesYesBothYes
join_buffer_sizeYesYesYesBothYes
keep_files_on_createYesYesYesBothYes
key_buffer_sizeYesYesYesGlobalYes
key_cache_age_thresholdYesYesYesGlobalYes
key_cache_block_sizeYesYesYesGlobalYes
key_cache_division_limitYesYesYesGlobalYes
languageYesYesYesGlobalNo
large_files_support  YesGlobalNo
large_page_size  YesGlobalNo
large-pagesYesYes  No
- Variable: large_pages  YesGlobalNo
last_insert_id  YesSessionYes
lc_time_names  YesBothYes
license  YesGlobalNo
local_infile  YesGlobalYes
locked_in_memory  YesGlobalNo
logYesYesYesGlobalYes
log_bin  YesGlobalNo
log-binYesYesYesGlobalNo
log-bin-trust-function-creatorsYesYes  Yes
- Variable: log_bin_trust_function_creators  YesGlobalYes
log-bin-trust-routine-creatorsYesYes  Yes
- Variable: log_bin_trust_routine_creators  YesGlobalYes
log_bin_use_v1_row_eventsYesYesYesGlobalNo
log-bin-use-v1-row-eventsYesYes  No
- Variable: log_bin_use_v1_row_events  YesGlobalNo
log-errorYesYes  No
- Variable: log_error  YesGlobalNo
log-outputYesYes  Yes
- Variable: log_output  YesGlobalYes
log-queries-not-using-indexesYesYes  Yes
- Variable: log_queries_not_using_indexes  YesGlobalYes
log-slave-updatesYesYes  No
- Variable: log_slave_updates  YesGlobalNo
log_slave_updatesYesYesYesGlobalNo
log-slow-queriesYesYes  Yes
- Variable: log_slow_queries  YesGlobalYes
log-warningsYesYes  Yes
- Variable: log_warnings  YesBothYes
long_query_timeYesYesYesBothYes
low-priority-updatesYesYes  Yes
- Variable: low_priority_updates  YesBothYes
lower_case_file_system  YesGlobalNo
lower_case_table_namesYesYesYesGlobalNo
max_allowed_packetYesYesYesGlobalYes
max_binlog_cache_sizeYesYesYesGlobalYes
max_binlog_sizeYesYesYesGlobalYes
max_connect_errorsYesYesYesGlobalYes
max_connectionsYesYesYesGlobalYes
max_delayed_threadsYesYesYesBothYes
max_error_countYesYesYesBothYes
max_heap_table_sizeYesYesYesBothYes
max_insert_delayed_threads  YesBothYes
max_join_sizeYesYesYesBothYes
max_length_for_sort_dataYesYesYesBothYes
max_long_data_sizeYesYesYesGlobalNo
max_prepared_stmt_countYesYesYesGlobalYes
max_relay_log_sizeYesYesYesGlobalYes
max_seeks_for_keyYesYesYesBothYes
max_sort_lengthYesYesYesBothYes
max_sp_recursion_depthYesYesYesBothYes
max_user_connectionsYesYesYesBothYes
max_write_lock_countYesYesYesGlobalYes
memlockYesYesYesGlobalNo
min-examined-row-limitYesYesYesBothYes
multi_range_countYesYesYesBothYes
myisam_data_pointer_sizeYesYesYesGlobalYes
myisam_max_sort_file_sizeYesYesYesGlobalYes
myisam_mmap_sizeYesYesYesGlobalNo
myisam_recover_options  YesGlobalNo
myisam_repair_threadsYesYesYesBothYes
myisam_sort_buffer_sizeYesYesYesBothYes
myisam_stats_methodYesYesYesBothYes
myisam_use_mmapYesYesYesGlobalYes
named_pipe  YesGlobalNo
ndb_autoincrement_prefetch_szYesYesYesBothYes
ndb-batch-sizeYesYesYesGlobalNo
ndb-blob-read-batch-bytesYesYesYesBothYes
ndb-blob-write-batch-bytesYesYesYesBothYes
ndb_cache_check_timeYesYesYesGlobalYes
ndb-cluster-connection-poolYesYesYesGlobalNo
ndb-deferred-constraintsYesYes  Yes
- Variable: ndb_deferred_constraints  YesBothYes
ndb_deferred_constraintsYesYesYesBothYes
ndb_distributionYesYesYesGlobalYes
ndb-distributionYesYes  Yes
- Variable: ndb_distribution  YesGlobalYes
ndb_eventbuffer_max_allocYesYesYesGlobalYes
ndb_extra_loggingYesYesYesGlobalYes
ndb_force_sendYesYesYesBothYes
ndb_join_pushdown  YesGlobalNo
ndb-log-apply-statusYesYes  No
- Variable: ndb_log_apply_status  YesGlobalNo
ndb_log_binYes YesBothYes
ndb_log_binlog_indexYes YesGlobalYes
ndb_log_empty_epochsYesYesYesGlobalYes
ndb-log-empty-epochsYesYesYesGlobalYes
ndb-log-origYesYes  No
- Variable: ndb_log_orig  YesGlobalNo
ndb-log-transaction-idYesYes  No
- Variable: ndb_log_transaction_id  YesGlobalNo
ndb_log_transaction_id  YesGlobalNo
ndb-log-update-as-writeYesYesYesGlobalYes
ndb_log_updated_onlyYesYesYesGlobalYes
ndb_optimization_delay  YesGlobalYes
ndb_table_no_logging  YesSessionYes
ndb_table_temporary  YesSessionYes
ndb_use_copying_alter_table  YesBothNo
ndb_use_exact_count  YesBothYes
ndb_use_transactionsYesYesYesBothYes
ndb_version  YesGlobalNo
ndb_version_string  YesGlobalNo
ndb-wait-connectedYesYesYesGlobalNo
ndb-wait-setupYesYesYesGlobalNo
ndbinfo_database  YesGlobalNo
ndbinfo_max_bytesYes YesBothYes
ndbinfo_max_rowsYes YesBothYes
ndbinfo_offline  YesGlobalYes
ndbinfo_show_hiddenYes YesBothYes
ndbinfo_table_prefixYes YesBothYes
ndbinfo_version  YesGlobalNo
net_buffer_lengthYesYesYesBothYes
net_read_timeoutYesYesYesBothYes
net_retry_countYesYesYesBothYes
net_write_timeoutYesYesYesBothYes
newYesYesYesBothYes
oldYesYesYesGlobalNo
old-alter-tableYesYes  Yes
- Variable: old_alter_table  YesBothYes
old_passwords  YesBothYes
open-files-limitYesYes  No
- Variable: open_files_limit  YesGlobalNo
optimizer_prune_levelYesYesYesBothYes
optimizer_search_depthYesYesYesBothYes
optimizer_switchYesYesYesBothYes
pid-fileYesYes  No
- Variable: pid_file  YesGlobalNo
plugin_dirYesYesYesGlobalNo
portYesYesYesGlobalNo
preload_buffer_sizeYesYesYesBothYes
prepared_stmt_count  YesGlobalNo
profiling  YesSessionYes
profiling_history_sizeYesYesYesBothYes
protocol_version  YesGlobalNo
pseudo_thread_id  YesSessionYes
query_alloc_block_sizeYesYesYesBothYes
query_cache_limitYesYesYesGlobalYes
query_cache_min_res_unitYesYesYesGlobalYes
query_cache_sizeYesYesYesGlobalYes
query_cache_typeYesYesYesBothYes
query_cache_wlock_invalidateYesYesYesBothYes
query_prealloc_sizeYesYesYesBothYes
rand_seed1  YesSessionYes
rand_seed2  YesSessionYes
range_alloc_block_sizeYesYesYesBothYes
read_buffer_sizeYesYesYesBothYes
read_onlyYesYesYesGlobalYes
read_rnd_buffer_sizeYesYesYesBothYes
relay-logYesYes  No
- Variable: relay_log  YesGlobalNo
relay-log-indexYesYes  No
- Variable: relay_log_index  YesGlobalNo
relay_log_indexYesYesYesGlobalNo
relay_log_info_fileYesYesYesGlobalNo
relay_log_purgeYesYesYesGlobalYes
relay_log_space_limitYesYesYesGlobalNo
report-hostYesYes  No
- Variable: report_host  YesGlobalNo
report-passwordYesYes  No
- Variable: report_password  YesGlobalNo
report-portYesYes  No
- Variable: report_port  YesGlobalNo
report-userYesYes  No
- Variable: report_user  YesGlobalNo
rpl_recovery_rank  YesGlobalYes
safe-show-databaseYesYesYesGlobalYes
secure-authYesYes  Yes
- Variable: secure_auth  YesGlobalYes
secure-file-privYesYes  No
- Variable: secure_file_priv  YesGlobalNo
server-idYesYes  Yes
- Variable: server_id  YesGlobalYes
server-id-bitsYesYes  No
- Variable: server_id_bits  YesGlobalNo
shared_memoryYesYesYesGlobalNo
shared_memory_base_name  YesGlobalNo
skip_external_lockingYesYesYesGlobalNo
skip-name-resolveYesYes  No
- Variable: skip_name_resolve  YesGlobalNo
skip-networkingYesYes  No
- Variable: skip_networking  YesGlobalNo
skip-show-databaseYesYes  No
- Variable: skip_show_database  YesGlobalNo
slave_allow_batchingYesYesYesGlobalYes
slave_compressed_protocolYesYesYesGlobalYes
slave_exec_modeYesYesYesGlobalYes
slave-load-tmpdirYesYes  No
- Variable: slave_load_tmpdir  YesGlobalNo
slave_max_allowed_packet  YesGlobalYes
slave-net-timeoutYesYes  Yes
- Variable: slave_net_timeout  YesGlobalYes
slave-skip-errorsYesYes  No
- Variable: slave_skip_errors  YesGlobalNo
slave_transaction_retriesYesYesYesGlobalYes
slave_type_conversionsYesYesYesGlobalNo
slow_launch_timeYesYesYesGlobalYes
slow-query-logYesYes  Yes
- Variable: slow_query_log  YesGlobalYes
slow_query_log_fileYesYesYesGlobalYes
socketYesYesYesGlobalNo
sort_buffer_sizeYesYesYesBothYes
sql_auto_is_null  YesSessionYes
sql_big_selects  YesSessionYes
sql_big_tables  YesSessionYes
sql_buffer_result  YesSessionYes
sql_log_bin  YesSessionYes
sql_log_off  YesSessionYes
sql_log_update  YesSessionYes
sql_low_priority_updates  YesBothYes
sql_max_join_size  YesBothYes
sql-modeYesYes  Yes
- Variable: sql_mode  YesBothYes
sql_notes  YesSessionYes
sql_quote_show_create  YesSessionYes
sql_safe_updates  YesSessionYes
sql_select_limit  YesBothYes
sql_slave_skip_counter  YesGlobalYes
sql_warnings  YesSessionYes
ssl-caYesYes  No
- Variable: ssl_ca  YesGlobalNo
ssl-capathYesYes  No
- Variable: ssl_capath  YesGlobalNo
ssl-certYesYes  No
- Variable: ssl_cert  YesGlobalNo
ssl-cipherYesYes  No
- Variable: ssl_cipher  YesGlobalNo
ssl-keyYesYes  No
- Variable: ssl_key  YesGlobalNo
storage_engine  YesBothYes
sync_binlogYesYesYesGlobalYes
sync_frmYesYesYesGlobalYes
system_time_zone  YesGlobalNo
table_cacheYesYesYesGlobalYes
table_definition_cache  YesGlobalYes
table_lock_wait_timeoutYesYesYesGlobalYes
table_open_cache  YesGlobalYes
table_type  YesBothYes
thread_cache_sizeYesYesYesGlobalYes
thread_concurrencyYesYesYesGlobalNo
thread_handlingYesYesYesGlobalNo
thread_stackYesYesYesGlobalNo
time_format  YesBothNo
time_zone  YesBothYes
timed_mutexesYesYesYesGlobalYes
timestamp  YesSessionYes
tmp_table_sizeYesYesYesBothYes
tmpdirYesYesYesGlobalNo
transaction_alloc_block_sizeYesYesYesBothYes
transaction_allow_batching  YesSessionYes
transaction_prealloc_sizeYesYesYesBothYes
tx_isolation  YesBothYes
unique_checks  YesSessionYes
updatable_views_with_limitYesYesYesBothYes
version  YesGlobalNo
version_comment  YesGlobalNo
version_compile_machine  YesGlobalNo
version_compile_os  YesGlobalNo
wait_timeoutYesYesYesBothYes
warning_count  YesSessionNo

[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

[b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.


For additional system variable information, see these sections:

Note

Some of the following variable descriptions refer to enabling or disabling a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not.

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Some system variables take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is /var/mysql/data. If a file-valued variable is given as a relative path name, it will be located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.