Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 30.3Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.9Kb
Man Pages (Zip) - 293.2Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

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. 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.4 Dynamic Variable Summary

Variable NameVariable TypeVariable Scope
audit_log_connection_policyenumerationGLOBAL
audit_log_exclude_accountsstringGLOBAL
audit_log_flushbooleanGLOBAL
audit_log_include_accountsstringGLOBAL
audit_log_policyenumerationGLOBAL
audit_log_rotate_on_sizeintegerGLOBAL
audit_log_statement_policyenumerationGLOBAL
auto_increment_incrementintegerGLOBAL | SESSION
auto_increment_offsetintegerGLOBAL | SESSION
autocommitbooleanGLOBAL | SESSION
automatic_sp_privilegesbooleanGLOBAL
avoid_temporal_upgradebooleanGLOBAL
big_tablesbooleanGLOBAL | SESSION
binlog_cache_sizeintegerGLOBAL
binlog_checksumstringGLOBAL
binlog_direct_non_transactional_updatesbooleanGLOBAL | SESSION
binlog_error_actionenumerationGLOBAL | SESSION
binlog_formatenumerationGLOBAL | SESSION
binlog_max_flush_queue_timeintegerGLOBAL
binlog_order_commitsbooleanGLOBAL
binlog_row_image=image_typeenumerationGLOBAL | SESSION
binlog_rows_query_log_eventsbooleanGLOBAL | SESSION
binlog_stmt_cache_sizeintegerGLOBAL
binlogging_impossible_modeenumerationGLOBAL | SESSION
block_encryption_modestringGLOBAL | 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_typeenumerationGLOBAL | SESSION
concurrent_insertenumerationGLOBAL
connect_timeoutintegerGLOBAL
debugstringGLOBAL | SESSION
debug_syncstringSESSION
default_storage_engineenumerationGLOBAL | SESSION
default_tmp_storage_engineenumerationGLOBAL | SESSION
default_week_formatintegerGLOBAL | SESSION
delay_key_writeenumerationGLOBAL
delayed_insert_limitintegerGLOBAL
delayed_insert_timeoutintegerGLOBAL
delayed_queue_sizeintegerGLOBAL
div_precision_incrementintegerGLOBAL | SESSION
end_markers_in_jsonbooleanGLOBAL | SESSION
engine_condition_pushdownbooleanGLOBAL | SESSION
eq_range_index_dive_limitintegerGLOBAL | SESSION
event_schedulerenumerationGLOBAL
expire_logs_daysintegerGLOBAL
flushbooleanGLOBAL
flush_timeintegerGLOBAL
foreign_key_checksbooleanGLOBAL | SESSION
ft_boolean_syntaxstringGLOBAL
general_logbooleanGLOBAL
general_log_filefilenameGLOBAL
group_concat_max_lenintegerGLOBAL | SESSION
gtid_nextenumerationSESSION
gtid_purgedstringGLOBAL
host_cache_sizeintegerGLOBAL
identityintegerSESSION
init_connectstringGLOBAL
init_slavestringGLOBAL
innodb_adaptive_flushingbooleanGLOBAL
innodb_adaptive_flushing_lwmintegerGLOBAL
innodb_adaptive_hash_indexbooleanGLOBAL
innodb_adaptive_max_sleep_delayintegerGLOBAL
innodb_api_bk_commit_intervalintegerGLOBAL
innodb_api_trx_levelintegerGLOBAL
innodb_autoextend_incrementintegerGLOBAL
innodb_buffer_pool_dump_at_shutdownbooleanGLOBAL
innodb_buffer_pool_dump_nowbooleanGLOBAL
innodb_buffer_pool_filenamefilenameGLOBAL
innodb_buffer_pool_load_abortbooleanGLOBAL
innodb_buffer_pool_load_nowbooleanGLOBAL
innodb_change_buffer_max_sizeintegerGLOBAL
innodb_change_bufferingenumerationGLOBAL
innodb_change_buffering_debugintegerGLOBAL
innodb_checksum_algorithmenumerationGLOBAL
innodb_cmp_per_index_enabledbooleanGLOBAL
innodb_commit_concurrencyintegerGLOBAL
innodb_compression_failure_threshold_pctintegerGLOBAL
innodb_compression_levelintegerGLOBAL
innodb_compression_pad_pct_maxintegerGLOBAL
innodb_concurrency_ticketsintegerGLOBAL
innodb_disable_sort_file_cachebooleanGLOBAL
innodb_fast_shutdownintegerGLOBAL
innodb_fil_make_page_dirty_debugintegerGLOBAL
innodb_file_formatstringGLOBAL
innodb_file_format_maxstringGLOBAL
innodb_file_per_tablebooleanGLOBAL
innodb_flush_log_at_timeoutintegerGLOBAL
innodb_flush_log_at_trx_commitenumerationGLOBAL
innodb_flush_neighborsenumerationGLOBAL
innodb_flushing_avg_loopsintegerGLOBAL
innodb_ft_aux_tablestringGLOBAL
innodb_ft_enable_diag_printbooleanGLOBAL
innodb_ft_enable_stopwordbooleanGLOBAL
innodb_ft_num_word_optimizeintegerGLOBAL
innodb_ft_result_cache_limitintegerGLOBAL
innodb_ft_server_stopword_tablestringGLOBAL
innodb_ft_user_stopword_tablestringGLOBAL | SESSION
innodb_io_capacityintegerGLOBAL
innodb_io_capacity_maxintegerGLOBAL
innodb_large_prefixbooleanGLOBAL
innodb_limit_optimistic_insert_debugintegerGLOBAL
innodb_lock_wait_timeoutintegerGLOBAL | SESSION
innodb_log_compressed_pagesbooleanGLOBAL
innodb_lru_scan_depthintegerGLOBAL
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_dirty_pages_pct_lwmnumericGLOBAL
innodb_max_purge_lagintegerGLOBAL
innodb_max_purge_lag_delayintegerGLOBAL
innodb_monitor_disablestringGLOBAL
innodb_monitor_enablestringGLOBAL
innodb_monitor_resetstringGLOBAL
innodb_monitor_reset_allstringGLOBAL
innodb_old_blocks_pctintegerGLOBAL
innodb_old_blocks_timeintegerGLOBAL
innodb_online_alter_log_max_sizeintegerGLOBAL
innodb_optimize_fulltext_onlybooleanGLOBAL
innodb_print_all_deadlocksbooleanGLOBAL
innodb_purge_batch_sizeintegerGLOBAL
innodb_random_read_aheadbooleanGLOBAL
innodb_read_ahead_thresholdintegerGLOBAL
innodb_replication_delayintegerGLOBAL
innodb_rollback_segmentsintegerGLOBAL
innodb_saved_page_number_debugintegerGLOBAL
innodb_spin_wait_delayintegerGLOBAL
innodb_stats_auto_recalcbooleanGLOBAL
innodb_stats_methodenumerationGLOBAL
innodb_stats_on_metadatabooleanGLOBAL
innodb_stats_persistentbooleanGLOBAL
innodb_stats_persistent_sample_pagesintegerGLOBAL
innodb_stats_sample_pagesintegerGLOBAL
innodb_stats_transient_sample_pagesintegerGLOBAL
innodb_status_outputbooleanGLOBAL
innodb_status_output_locksbooleanGLOBAL
innodb_strict_modebooleanGLOBAL | SESSION
innodb_support_xabooleanGLOBAL | SESSION
innodb_sync_spin_loopsintegerGLOBAL
innodb_table_locksbooleanGLOBAL | SESSION
innodb_thread_concurrencyintegerGLOBAL
innodb_thread_sleep_delayintegerGLOBAL
innodb_tmpdirdirnameGLOBAL | SESSION
innodb_trx_purge_view_update_only_debugbooleanGLOBAL
innodb_trx_rseg_n_slots_debugintegerGLOBAL
innodb_undo_logsintegerGLOBAL
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_messagesstringGLOBAL | SESSION
lc_time_namesstringGLOBAL | SESSION
local_infilebooleanGLOBAL
lock_wait_timeoutintegerGLOBAL | SESSION
logfilenameGLOBAL
log_bin_trust_function_creatorsbooleanGLOBAL
log_outputsetGLOBAL
log_queries_not_using_indexesbooleanGLOBAL
log_slow_admin_statementsbooleanGLOBAL
log_slow_queriesbooleanGLOBAL
log_slow_slave_statementsbooleanGLOBAL
log_throttle_queries_not_using_indexesintegerGLOBAL
log_warningsintegerGLOBAL
long_query_timenumericGLOBAL | SESSION
low_priority_updatesbooleanGLOBAL | SESSION
master_info_repositorystringGLOBAL
master_verify_checksumbooleanGLOBAL
max_allowed_packetintegerGLOBAL | SESSION
max_binlog_cache_sizeintegerGLOBAL
max_binlog_sizeintegerGLOBAL
max_binlog_stmt_cache_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_tmp_tablesintegerGLOBAL | 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
mysql_firewall_modebooleanGLOBAL
mysql_firewall_tracebooleanGLOBAL
ndb_autoincrement_prefetch_szintegerGLOBAL | SESSION
ndb_blob_read_batch_bytesintegerGLOBAL | SESSION
ndb_blob_write_batch_bytesintegerGLOBAL | SESSION
ndb_cache_check_timeintegerGLOBAL
ndb_clear_apply_statusbooleanGLOBAL
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_deferred_constraintsintegerGLOBAL | SESSION
ndb_distributionenumerationGLOBAL
ndb_distribution={KEYHASH|LINHASH}enumerationGLOBAL
ndb_eventbuffer_free_percentintegerGLOBAL
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_exclusive_readsbooleanGLOBAL | SESSION
ndb_log_exclusive_readsbooleanGLOBAL | SESSION
ndb_log_update_as_writebooleanGLOBAL
ndb_log_updated_onlybooleanGLOBAL
ndb_optimization_delayintegerGLOBAL
ndb_recv_thread_cpu_maskbitmapGLOBAL
ndb_report_thresh_binlog_epoch_slipintegerGLOBAL
ndb_report_thresh_binlog_mem_usageintegerGLOBAL
ndb_show_foreign_key_mock_tablesbooleanGLOBAL
ndb_slave_last_conflict_epochenumerationGLOBAL
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_passwordsenumerationGLOBAL | SESSION
optimizer_join_cache_levelintegerGLOBAL | SESSION
optimizer_prune_levelbooleanGLOBAL | SESSION
optimizer_search_depthintegerGLOBAL | SESSION
optimizer_switchsetGLOBAL | SESSION
optimizer_tracestringGLOBAL | SESSION
optimizer_trace_featuresstringGLOBAL | SESSION
optimizer_trace_limitintegerGLOBAL | SESSION
optimizer_trace_max_mem_sizeintegerGLOBAL | SESSION
optimizer_trace_offsetintegerGLOBAL | SESSION
preload_buffer_sizeintegerGLOBAL | SESSION
profilingbooleanGLOBAL | SESSION
profiling_history_sizeintegerGLOBAL | SESSION
pseudo_slave_modeintegerSESSION
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_info_repositorystringGLOBAL
relay_log_purgebooleanGLOBAL
relay_log_recoverybooleanGLOBAL
rpl_semi_sync_master_enabledbooleanGLOBAL
rpl_semi_sync_master_timeoutintegerGLOBAL
rpl_semi_sync_master_trace_levelintegerGLOBAL
rpl_semi_sync_master_wait_no_slavebooleanGLOBAL
rpl_semi_sync_slave_enabledbooleanGLOBAL
rpl_semi_sync_slave_trace_levelintegerGLOBAL
rpl_stop_slave_timeoutintegerGLOBAL
secure_authbooleanGLOBAL
server_idintegerGLOBAL
show_old_temporalsbooleanGLOBAL | SESSION
slave_allow_batchingbooleanGLOBAL
slave_checkpoint_group=#integerGLOBAL
slave_checkpoint_period=#integerGLOBAL
slave_compressed_protocolbooleanGLOBAL
slave_exec_modeenumerationGLOBAL
slave_max_allowed_packetintegerGLOBAL
slave_net_timeoutintegerGLOBAL
slave_parallel_workersintegerGLOBAL
slave_pending_jobs_size_maxintegerGLOBAL
slave_rows_search_algorithms=listsetGLOBAL
slave_sql_verify_checksumbooleanGLOBAL
slave_transaction_retriesintegerGLOBAL
slow_launch_timeintegerGLOBAL
slow_query_logbooleanGLOBAL
slow_query_log_filefilenameGLOBAL
sort_buffer_sizeintegerGLOBAL | SESSION
sql_auto_is_nullbooleanGLOBAL | SESSION
sql_big_selectsbooleanGLOBAL | SESSION
sql_big_tablesbooleanGLOBAL | SESSION
sql_buffer_resultbooleanGLOBAL | SESSION
sql_log_binbooleanSESSION
sql_log_offbooleanGLOBAL | SESSION
sql_low_priority_updatesbooleanGLOBAL | SESSION
sql_max_join_sizeintegerGLOBAL | SESSION
sql_modesetGLOBAL | SESSION
sql_notesbooleanGLOBAL | SESSION
sql_quote_show_createbooleanGLOBAL | SESSION
sql_safe_updatesbooleanGLOBAL | SESSION
sql_select_limitintegerGLOBAL | SESSION
sql_slave_skip_counterintegerGLOBAL
sql_warningsbooleanGLOBAL | SESSION
storage_engineenumerationGLOBAL | SESSION
stored_program_cacheintegerGLOBAL
sync_binlogintegerGLOBAL
sync_frmbooleanGLOBAL
sync_master_infointegerGLOBAL
sync_relay_logintegerGLOBAL
sync_relay_log_infointegerGLOBAL
table_definition_cacheintegerGLOBAL
table_open_cacheintegerGLOBAL
thread_cache_sizeintegerGLOBAL
thread_pool_high_priority_connectionintegerGLOBAL | SESSION
thread_pool_max_unused_threadsintegerGLOBAL
thread_pool_prio_kickup_timerintegerGLOBAL | SESSION
thread_pool_stall_limitintegerGLOBAL
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
tx_read_onlybooleanGLOBAL | SESSION
unique_checksbooleanGLOBAL | SESSION
updatable_views_with_limitbooleanGLOBAL | SESSION
validate_password_dictionary_filefilenameGLOBAL
validate_password_lengthintegerGLOBAL
validate_password_mixed_case_countintegerGLOBAL
validate_password_number_countintegerGLOBAL
validate_password_policyenumerationGLOBAL
validate_password_special_char_countintegerGLOBAL
wait_timeoutintegerGLOBAL | SESSION


User Comments
Sign Up Login You must be logged in to post a comment.