5.1.5.2 Dynamic System Variables

Many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also obtain their values using SELECT. See Section 5.1.5, “Using System Variables”.

The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply. The table also lists session options that can be set with the SET statement. Section 5.1.4, “Server System Variables”, discusses these options.

Variables that have a type of string take a string value. Variables that have a type of numeric take a numeric value. Variables that have a type of boolean can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.) Variables that are marked as enumeration normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumerated system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

Table 5.3 Dynamic Variable Summary

Variable NameVariable TypeVariable Scope
auto_increment_incrementintegerGLOBAL | SESSION
auto_increment_offsetintegerGLOBAL | SESSION
autocommitbooleanSESSION
automatic_sp_privilegesbooleanGLOBAL
big_tablesbooleanSESSION
binlog_cache_sizeintegerGLOBAL
binlog_direct_non_transactional_updatesbooleanGLOBAL | SESSION
binlog_formatenumerationGLOBAL | SESSION
bulk_insert_buffer_sizeintegerGLOBAL | SESSION
character_set_clientstringGLOBAL | SESSION
character_set_connectionstringGLOBAL | SESSION
character_set_databasestringGLOBAL | SESSION
character_set_filesystemstringGLOBAL | SESSION
character_set_resultsstringGLOBAL | SESSION
character_set_serverstringGLOBAL | SESSION
collation_connectionstringGLOBAL | SESSION
collation_databasestringGLOBAL | SESSION
collation_serverstringGLOBAL | SESSION
completion_typeintegerGLOBAL | SESSION
concurrent_insertintegerGLOBAL
connect_timeoutintegerGLOBAL
debugstringGLOBAL | SESSION
debug_syncstringGLOBAL | SESSION
storage_engineenumerationGLOBAL | SESSION
default_week_formatintegerGLOBAL | SESSION
delay_key_writeenumerationGLOBAL
delayed_insert_limitintegerGLOBAL
delayed_insert_timeoutintegerGLOBAL
delayed_queue_sizeintegerGLOBAL
div_precision_incrementintegerGLOBAL | SESSION
engine_condition_pushdownbooleanGLOBAL | SESSION
event_schedulerenumerationGLOBAL
expire_logs_daysintegerGLOBAL
flushbooleanGLOBAL
flush_timeintegerGLOBAL
foreign_key_checksbooleanSESSION
ft_boolean_syntaxstringGLOBAL
general_logbooleanGLOBAL
general_log_filefilenameGLOBAL
group_concat_max_lenintegerGLOBAL | SESSION
identityintegerSESSION
init_connectstringGLOBAL
init_slavestringGLOBAL
innodb_adaptive_flushingbooleanGLOBAL
innodb_autoextend_incrementintegerGLOBAL
innodb_change_bufferingenumerationGLOBAL
innodb_change_buffering_debugintegerGLOBAL
innodb_commit_concurrencyintegerGLOBAL
innodb_concurrency_ticketsintegerGLOBAL
innodb_fast_shutdownintegerGLOBAL
innodb_file_formatstringGLOBAL
innodb_file_format_checkstringGLOBAL
innodb_flush_log_at_trx_commitenumerationGLOBAL
innodb_limit_optimistic_insert_debugintegerGLOBAL
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_purge_lagintegerGLOBAL
innodb_old_blocks_pctintegerGLOBAL
innodb_old_blocks_timeintegerGLOBAL
innodb_random_read_aheadbooleanGLOBAL
innodb_read_ahead_thresholdintegerGLOBAL
innodb_replication_delayintegerGLOBAL
innodb_spin_wait_delayintegerGLOBAL
innodb_stats_methodenumerationGLOBAL
innodb_stats_on_metadatabooleanGLOBAL
innodb_stats_sample_pagesintegerGLOBAL
innodb_strict_modebooleanGLOBAL | SESSION
innodb_support_xabooleanGLOBAL | SESSION
innodb_sync_spin_loopsintegerGLOBAL
innodb_table_locksbooleanGLOBAL | SESSION
innodb_thread_concurrencyintegerGLOBAL
innodb_thread_sleep_delayintegerGLOBAL
innodb_trx_purge_view_update_only_debugbooleanGLOBAL
innodb_trx_rseg_n_slots_debugintegerGLOBAL
innodb_use_legacy_cardinality_algorithmbooleanGLOBAL
insert_idintegerSESSION
interactive_timeoutintegerGLOBAL | SESSION
join_buffer_sizeintegerGLOBAL | SESSION
keep_files_on_createbooleanGLOBAL | SESSION
key_buffer_sizeintegerGLOBAL
key_cache_age_thresholdintegerGLOBAL
key_cache_block_sizeintegerGLOBAL
key_cache_division_limitintegerGLOBAL
last_insert_idintegerSESSION
lc_time_namesstringGLOBAL | SESSION
local_infilebooleanGLOBAL
logfilenameGLOBAL
log_bin_trust_function_creatorsbooleanGLOBAL
log_bin_trust_routine_creatorsbooleanGLOBAL
log_outputsetGLOBAL
log_queries_not_using_indexesbooleanGLOBAL
log_slow_queriesbooleanGLOBAL
log_warningsintegerGLOBAL | SESSION
long_query_timenumericGLOBAL | SESSION
low_priority_updatesbooleanGLOBAL | SESSION
max_allowed_packetintegerGLOBAL
max_binlog_cache_sizeintegerGLOBAL
max_binlog_sizeintegerGLOBAL
max_connect_errorsintegerGLOBAL
max_connectionsintegerGLOBAL
max_delayed_threadsintegerGLOBAL | SESSION
max_error_countintegerGLOBAL | SESSION
max_heap_table_sizeintegerGLOBAL | SESSION
max_insert_delayed_threadsintegerGLOBAL | SESSION
max_join_sizeintegerGLOBAL | SESSION
max_length_for_sort_dataintegerGLOBAL | SESSION
max_prepared_stmt_countintegerGLOBAL
max_relay_log_sizeintegerGLOBAL
max_seeks_for_keyintegerGLOBAL | SESSION
max_sort_lengthintegerGLOBAL | SESSION
max_sp_recursion_depthintegerGLOBAL | SESSION
max_user_connectionsintegerGLOBAL | SESSION
max_write_lock_countintegerGLOBAL
min_examined_row_limitintegerGLOBAL | SESSION
multi_range_countintegerGLOBAL | SESSION
myisam_data_pointer_sizeintegerGLOBAL
myisam_max_sort_file_sizeintegerGLOBAL
myisam_repair_threadsintegerGLOBAL | SESSION
myisam_sort_buffer_sizeintegerGLOBAL | SESSION
myisam_stats_methodenumerationGLOBAL | SESSION
myisam_use_mmapbooleanGLOBAL
ndb_autoincrement_prefetch_szintegerGLOBAL | SESSION
ndb_blob_read_batch_bytesintegerGLOBAL | SESSION
ndb_blob_write_batch_bytesintegerGLOBAL | SESSION
ndb_cache_check_timeintegerGLOBAL
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_distribution={KEYHASH|LINHASH}enumerationGLOBAL
ndb_distributionenumerationGLOBAL
ndb_eventbuffer_max_allocintegerGLOBAL
ndb_extra_loggingintegerGLOBAL
ndb_force_sendbooleanGLOBAL | SESSION
ndb_index_stat_cache_entriesintegerGLOBAL | SESSION
ndb_index_stat_enablebooleanGLOBAL | SESSION
ndb_index_stat_optionstringGLOBAL | SESSION
ndb_index_stat_update_freqintegerGLOBAL | SESSION
ndb_join_pushdownbooleanGLOBAL | SESSION
ndb_log_binbooleanGLOBAL | SESSION
ndb_log_binlog_indexbooleanGLOBAL
ndb_log_empty_epochsbooleanGLOBAL
ndb_log_empty_epochsbooleanGLOBAL
ndb_log_update_as_writebooleanGLOBAL
ndb_log_updated_onlybooleanGLOBAL
ndb_optimization_delayintegerGLOBAL
ndb_table_no_loggingbooleanSESSION
ndb_table_temporarybooleanSESSION
ndb_use_exact_countbooleanGLOBAL | SESSION
ndb_use_transactionsbooleanGLOBAL | SESSION
ndbinfo_max_bytesintegerGLOBAL | SESSION
ndbinfo_max_rowsintegerGLOBAL | SESSION
ndbinfo_offlinebooleanGLOBAL
ndbinfo_show_hiddenbooleanGLOBAL | SESSION
ndbinfo_table_prefixstringGLOBAL | SESSION
net_buffer_lengthintegerGLOBAL | SESSION
net_read_timeoutintegerGLOBAL | SESSION
net_retry_countintegerGLOBAL | SESSION
net_write_timeoutintegerGLOBAL | SESSION
newbooleanGLOBAL | SESSION
old_alter_tablebooleanGLOBAL | SESSION
old_passwordsbooleanGLOBAL | SESSION
optimizer_prune_levelbooleanGLOBAL | SESSION
optimizer_search_depthintegerGLOBAL | SESSION
optimizer_switchsetGLOBAL | SESSION
preload_buffer_sizeintegerGLOBAL | SESSION
profilingbooleanSESSION
profiling_history_sizeintegerGLOBAL | SESSION
pseudo_thread_idintegerSESSION
query_alloc_block_sizeintegerGLOBAL | SESSION
query_cache_limitintegerGLOBAL
query_cache_min_res_unitintegerGLOBAL
query_cache_sizeintegerGLOBAL
query_cache_typeenumerationGLOBAL | SESSION
query_cache_wlock_invalidatebooleanGLOBAL | SESSION
query_prealloc_sizeintegerGLOBAL | SESSION
rand_seed1integerSESSION
rand_seed2integerSESSION
range_alloc_block_sizeintegerGLOBAL | SESSION
read_buffer_sizeintegerGLOBAL | SESSION
read_onlybooleanGLOBAL
read_rnd_buffer_sizeintegerGLOBAL | SESSION
relay_log_purgebooleanGLOBAL
rpl_recovery_rankintegerGLOBAL
secure_authbooleanGLOBAL
server_idintegerGLOBAL
slave_allow_batchingbooleanGLOBAL
slave_compressed_protocolbooleanGLOBAL
slave_exec_modeenumerationGLOBAL
slave_max_allowed_packetintegerGLOBAL
slave_net_timeoutintegerGLOBAL
slave_transaction_retriesintegerGLOBAL
slow_launch_timeintegerGLOBAL
slow_query_logbooleanGLOBAL
slow_query_log_filefilenameGLOBAL
sort_buffer_sizeintegerGLOBAL | SESSION
sql_auto_is_nullbooleanSESSION
sql_big_selectsbooleanSESSION
sql_big_tablesbooleanSESSION
sql_buffer_resultbooleanSESSION
sql_log_binbooleanSESSION
sql_log_offbooleanSESSION
sql_log_updatebooleanSESSION
sql_low_priority_updatesbooleanGLOBAL | SESSION
sql_max_join_sizeintegerGLOBAL | SESSION
sql_modesetGLOBAL | SESSION
sql_notesbooleanSESSION
sql_quote_show_createbooleanSESSION
sql_safe_updatesbooleanSESSION
sql_select_limitintegerGLOBAL | SESSION
sql_slave_skip_counterintegerGLOBAL
sql_warningsbooleanSESSION
storage_engineenumerationGLOBAL | SESSION
sync_binlogintegerGLOBAL
sync_frmbooleanGLOBAL
table_cacheintegerGLOBAL
table_definition_cacheintegerGLOBAL
table_lock_wait_timeoutintegerGLOBAL
table_open_cacheintegerGLOBAL
table_typeenumerationGLOBAL | SESSION
thread_cache_sizeintegerGLOBAL
time_zonestringGLOBAL | SESSION
timed_mutexesbooleanGLOBAL
timestampnumericSESSION
tmp_table_sizeintegerGLOBAL | SESSION
transaction_alloc_block_sizeintegerGLOBAL | SESSION
transaction_allow_batchingbooleanSESSION
transaction_prealloc_sizeintegerGLOBAL | SESSION
tx_isolationenumerationGLOBAL | SESSION
unique_checksbooleanSESSION
updatable_views_with_limitbooleanGLOBAL | SESSION
wait_timeoutintegerGLOBAL | SESSION