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
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
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
expire_logs_daysintegerGLOBAL
flushbooleanGLOBAL
flush_timeintegerGLOBAL
foreign_key_checksbooleanSESSION
ft_boolean_syntaxstringGLOBAL
group_concat_max_lenintegerGLOBAL | SESSION
identityintegerSESSION
init_connectstringGLOBAL
init_slavestringGLOBAL
innodb_autoextend_incrementintegerGLOBAL
innodb_commit_concurrencyintegerGLOBAL
innodb_concurrency_ticketsintegerGLOBAL
innodb_fast_shutdownintegerGLOBAL
innodb_flush_log_at_trx_commitenumerationGLOBAL
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_purge_lagintegerGLOBAL
innodb_support_xabooleanGLOBAL | SESSION
innodb_sync_spin_loopsintegerGLOBAL
innodb_table_locksbooleanGLOBAL | SESSION
innodb_thread_concurrencyintegerGLOBAL
innodb_thread_sleep_delayintegerGLOBAL
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
log_bin_trust_function_creatorsbooleanGLOBAL
log_bin_trust_routine_creatorsbooleanGLOBAL
log_queries_not_using_indexesbooleanGLOBAL
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
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
ndb_autoincrement_prefetch_szintegerGLOBAL | SESSION
ndb_cache_check_timeintegerGLOBAL
ndb_force_sendbooleanGLOBAL | SESSION
ndb_index_stat_cache_entriesintegerGLOBAL | SESSION
ndb_index_stat_enablebooleanGLOBAL | SESSION
ndb_index_stat_update_freqintegerGLOBAL | SESSION
ndb_use_exact_countbooleanGLOBAL | SESSION
ndb_use_transactionsbooleanGLOBAL | SESSION
net_buffer_lengthintegerGLOBAL | SESSION
net_read_timeoutintegerGLOBAL | SESSION
net_retry_countintegerGLOBAL | SESSION
net_write_timeoutintegerGLOBAL | SESSION
newbooleanGLOBAL | SESSION
old_passwordsbooleanGLOBAL | SESSION
optimizer_prune_levelbooleanGLOBAL | SESSION
optimizer_search_depthintegerGLOBAL | 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_compressed_protocolbooleanGLOBAL
slave_net_timeoutintegerGLOBAL
slave_transaction_retriesintegerGLOBAL
slow_launch_timeintegerGLOBAL
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_lock_wait_timeoutintegerGLOBAL
table_typeenumerationGLOBAL | SESSION
thread_cache_sizeintegerGLOBAL
time_zonestringGLOBAL | SESSION
timed_mutexesbooleanGLOBAL
timestampnumericSESSION
tmp_table_sizeintegerGLOBAL | SESSION
transaction_alloc_block_sizeintegerGLOBAL | SESSION
transaction_prealloc_sizeintegerGLOBAL | SESSION
tx_isolationenumerationGLOBAL | SESSION
unique_checksbooleanSESSION
updatable_views_with_limitbooleanGLOBAL | SESSION
wait_timeoutintegerGLOBAL | SESSION