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
audit_log_flushbooleanGLOBAL
audit_log_policyenumerationGLOBAL
audit_log_rotate_on_sizenumericGLOBAL
auto_increment_incrementnumericGLOBAL | SESSION
auto_increment_offsetnumericGLOBAL | SESSION
autocommitbooleanGLOBAL | SESSION
automatic_sp_privilegesbooleanGLOBAL
big_tablesbooleanGLOBAL | SESSION
binlog_cache_sizenumericGLOBAL
binlog_direct_non_transactional_updatesbooleanGLOBAL | SESSION
binlog_formatenumerationGLOBAL | SESSION
binlog_stmt_cache_sizenumericGLOBAL
bulk_insert_buffer_sizenumericGLOBAL | 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_typenumericGLOBAL | SESSION
concurrent_insertbooleanGLOBAL
connect_timeoutnumericGLOBAL
debugstringGLOBAL | SESSION
debug_syncstringSESSION
default_storage_engineenumerationGLOBAL | SESSION
default_week_formatnumericGLOBAL | SESSION
delay_key_writeenumerationGLOBAL
delayed_insert_limitnumericGLOBAL
delayed_insert_timeoutnumericGLOBAL
delayed_queue_sizenumericGLOBAL
div_precision_incrementnumericGLOBAL | SESSION
engine_condition_pushdownbooleanGLOBAL | SESSION
event_schedulerenumerationGLOBAL
expire_logs_daysnumericGLOBAL
flushbooleanGLOBAL
flush_timenumericGLOBAL
foreign_key_checksbooleanGLOBAL | SESSION
ft_boolean_syntaxstringGLOBAL
general_logbooleanGLOBAL
general_log_filefilenameGLOBAL
group_concat_max_lennumericGLOBAL | SESSION
identitynumericSESSION
init_connectstringGLOBAL
init_slavestringGLOBAL
innodb_adaptive_flushingbooleanGLOBAL
innodb_adaptive_hash_indexbooleanGLOBAL
innodb_autoextend_incrementnumericGLOBAL
innodb_change_bufferingenumerationGLOBAL
innodb_commit_concurrencynumericGLOBAL
innodb_concurrency_ticketsnumericGLOBAL
innodb_fast_shutdownnumericGLOBAL
innodb_file_formatstringGLOBAL
innodb_file_format_maxstringGLOBAL
innodb_file_per_tablebooleanGLOBAL
innodb_flush_log_at_trx_commitenumerationGLOBAL
innodb_io_capacitynumericGLOBAL
innodb_large_prefixbooleanGLOBAL
innodb_lock_wait_timeoutnumericGLOBAL | SESSION
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_purge_lagnumericGLOBAL
innodb_old_blocks_pctnumericGLOBAL
innodb_old_blocks_timenumericGLOBAL
innodb_print_all_deadlocksbooleanGLOBAL
innodb_purge_batch_sizenumericGLOBAL
innodb_random_read_aheadbooleanGLOBAL
innodb_read_ahead_thresholdnumericGLOBAL
innodb_replication_delaynumericGLOBAL
innodb_rollback_segmentsnumericGLOBAL
innodb_spin_wait_delaynumericGLOBAL
innodb_stats_methodenumerationGLOBAL
innodb_stats_on_metadatabooleanGLOBAL
innodb_stats_sample_pagesnumericGLOBAL
innodb_strict_modebooleanGLOBAL | SESSION
innodb_support_xabooleanGLOBAL | SESSION
innodb_sync_spin_loopsnumericGLOBAL
innodb_table_locksbooleanGLOBAL | SESSION
innodb_thread_concurrencynumericGLOBAL
innodb_thread_sleep_delaynumericGLOBAL
insert_idnumericSESSION
interactive_timeoutnumericGLOBAL | SESSION
join_buffer_sizenumericGLOBAL | SESSION
keep_files_on_createbooleanGLOBAL | SESSION
key_buffer_sizenumericGLOBAL
key_cache_age_thresholdnumericGLOBAL
key_cache_block_sizenumericGLOBAL
key_cache_division_limitnumericGLOBAL
last_insert_idnumericSESSION
lc_messagesstringGLOBAL | SESSION
lc_time_namesstringGLOBAL | SESSION
local_infilebooleanGLOBAL
lock_wait_timeoutnumericGLOBAL | SESSION
logfilenameGLOBAL
log_bin_trust_function_creatorsbooleanGLOBAL
log_bin_trust_routine_creatorsbooleanGLOBAL
log_outputsetGLOBAL
log_queries_not_using_indexesbooleanGLOBAL
log_slow_queriesbooleanGLOBAL
log_warningsnumericGLOBAL | SESSION
long_query_timenumericGLOBAL | SESSION
low_priority_updatesbooleanGLOBAL | SESSION
max_allowed_packetnumericGLOBAL
max_binlog_cache_sizenumericGLOBAL
max_binlog_sizenumericGLOBAL
max_binlog_stmt_cache_sizenumericGLOBAL
max_connect_errorsnumericGLOBAL
max_connectionsnumericGLOBAL
max_delayed_threadsnumericGLOBAL | SESSION
max_error_countnumericGLOBAL | SESSION
max_heap_table_sizenumericGLOBAL | SESSION
max_insert_delayed_threadsnumericGLOBAL | SESSION
max_join_sizenumericGLOBAL | SESSION
max_length_for_sort_datanumericGLOBAL | SESSION
max_prepared_stmt_countnumericGLOBAL
max_relay_log_sizenumericGLOBAL
max_seeks_for_keynumericGLOBAL | SESSION
max_sort_lengthnumericGLOBAL | SESSION
max_sp_recursion_depthnumericGLOBAL | SESSION
max_user_connectionsnumericGLOBAL | SESSION
max_write_lock_countnumericGLOBAL
min_examined_row_limitnumericGLOBAL | SESSION
myisam_data_pointer_sizenumericGLOBAL
myisam_max_sort_file_sizenumericGLOBAL
myisam_repair_threadsnumericGLOBAL | SESSION
myisam_sort_buffer_sizenumericGLOBAL | SESSION
myisam_stats_methodenumerationGLOBAL | SESSION
myisam_use_mmapbooleanGLOBAL
ndb_autoincrement_prefetch_sznumericGLOBAL | SESSION
ndb_blob_read_batch_bytesnumericGLOBAL | SESSION
ndb_blob_write_batch_bytesnumericGLOBAL | SESSION
ndb_cache_check_timenumericGLOBAL
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_distribution={KEYHASH|LINHASH}enumerationGLOBAL
ndb_distributionenumerationGLOBAL
ndb_eventbuffer_max_allocnumericGLOBAL
ndb_extra_loggingnumericGLOBAL
ndb_force_sendbooleanGLOBAL | 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_delaynumericGLOBAL
ndb_table_no_loggingbooleanSESSION
ndb_table_temporarybooleanSESSION
ndb_use_exact_countbooleanGLOBAL | SESSION
ndb_use_transactionsbooleanGLOBAL | SESSION
ndbinfo_max_bytesnumericGLOBAL | SESSION
ndbinfo_max_rowsnumericGLOBAL | SESSION
ndbinfo_offlinebooleanGLOBAL
ndbinfo_show_hiddenbooleanGLOBAL | SESSION
ndbinfo_table_prefixstringGLOBAL | SESSION
net_buffer_lengthnumericGLOBAL | SESSION
net_read_timeoutnumericGLOBAL | SESSION
net_retry_countnumericGLOBAL | SESSION
net_write_timeoutnumericGLOBAL | SESSION
newbooleanGLOBAL | SESSION
old_alter_tablebooleanGLOBAL | SESSION
old_passwordsbooleanGLOBAL | SESSION
optimizer_prune_levelbooleanGLOBAL | SESSION
optimizer_search_depthnumericGLOBAL | SESSION
optimizer_switchsetGLOBAL | SESSION
preload_buffer_sizenumericGLOBAL | SESSION
profilingbooleanGLOBAL | SESSION
profiling_history_sizenumericGLOBAL | SESSION
pseudo_slave_modenumericSESSION
pseudo_thread_idnumericSESSION
query_alloc_block_sizenumericGLOBAL | SESSION
query_cache_limitnumericGLOBAL
query_cache_min_res_unitnumericGLOBAL
query_cache_sizenumericGLOBAL
query_cache_typeenumerationGLOBAL | SESSION
query_cache_wlock_invalidatebooleanGLOBAL | SESSION
query_prealloc_sizenumericGLOBAL | SESSION
rand_seed1numericSESSION
rand_seed2numericSESSION
range_alloc_block_sizenumericGLOBAL | SESSION
read_buffer_sizenumericGLOBAL | SESSION
read_onlybooleanGLOBAL
read_rnd_buffer_sizenumericGLOBAL | SESSION
relay_log_purgebooleanGLOBAL
relay_log_recoverybooleanGLOBAL
rpl_recovery_ranknumericGLOBAL
rpl_semi_sync_master_enabledbooleanGLOBAL
rpl_semi_sync_master_timeoutnumericGLOBAL
rpl_semi_sync_master_trace_levelnumericGLOBAL
rpl_semi_sync_master_wait_no_slavebooleanGLOBAL
rpl_semi_sync_slave_enabledbooleanGLOBAL
rpl_semi_sync_slave_trace_levelnumericGLOBAL
safe_show_databasebooleanGLOBAL
secure_authbooleanGLOBAL
server_idnumericGLOBAL
slave_allow_batchingbooleanGLOBAL
slave_compressed_protocolbooleanGLOBAL
slave_exec_modeenumerationGLOBAL
slave_max_allowed_packetnumericGLOBAL
slave_net_timeoutnumericGLOBAL
slave_transaction_retriesnumericGLOBAL
slow_launch_timenumericGLOBAL
slow_query_logbooleanGLOBAL
slow_query_log_filefilenameGLOBAL
sort_buffer_sizenumericGLOBAL | SESSION
sql_auto_is_nullbooleanGLOBAL | SESSION
sql_big_selectsbooleanGLOBAL | SESSION
sql_big_tablesbooleanGLOBAL | SESSION
sql_buffer_resultbooleanGLOBAL | SESSION
sql_log_binbooleanGLOBAL | SESSION
sql_log_offbooleanGLOBAL | SESSION
sql_log_updatebooleanSESSION
sql_low_priority_updatesbooleanGLOBAL | SESSION
sql_max_join_sizenumericGLOBAL | SESSION
sql_modesetGLOBAL | SESSION
sql_notesbooleanGLOBAL | SESSION
sql_quote_show_createbooleanGLOBAL | SESSION
sql_safe_updatesbooleanGLOBAL | SESSION
sql_select_limitnumericGLOBAL | SESSION
sql_slave_skip_counternumericGLOBAL
sql_warningsbooleanGLOBAL | SESSION
storage_engineenumerationGLOBAL | SESSION
stored_program_cachenumericGLOBAL
sync_binlognumericGLOBAL
sync_frmbooleanGLOBAL
sync_master_infonumericGLOBAL
sync_relay_lognumericGLOBAL
sync_relay_log_infonumericGLOBAL
table_definition_cachenumericGLOBAL
table_lock_wait_timeoutnumericGLOBAL
table_open_cachenumericGLOBAL
table_typeenumerationGLOBAL | SESSION
thread_cache_sizenumericGLOBAL
thread_pool_high_priority_connectionnumericGLOBAL | SESSION
thread_pool_max_unused_threadsnumericGLOBAL
thread_pool_prio_kickup_timernumericGLOBAL | SESSION
thread_pool_stall_limitnumericGLOBAL
time_zonestringGLOBAL | SESSION
timed_mutexesbooleanGLOBAL
timestampnumericSESSION
tmp_table_sizenumericGLOBAL | SESSION
transaction_alloc_block_sizenumericGLOBAL | SESSION
transaction_allow_batchingbooleanSESSION
transaction_prealloc_sizenumericGLOBAL | SESSION
tx_isolationenumerationGLOBAL | SESSION
unique_checksbooleanGLOBAL | SESSION
updatable_views_with_limitbooleanGLOBAL | SESSION
wait_timeoutnumericGLOBAL | SESSION