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.6 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.3 System Variable Summary

NameCmd-LineOption FileSystem VarVar ScopeDynamic
audit_log_buffer_sizeYesYesYesGlobalNo
audit_log_connection_policyYesYesYesGlobalYes
audit_log_current_session  YesBothNo
audit_log_exclude_accountsYesYesYesGlobalYes
audit_log_fileYesYesYesGlobalNo
audit_log_flush  YesGlobalYes
audit_log_formatYesYesYesGlobalNo
audit_log_include_accountsYesYesYesGlobalYes
audit_log_policyYesYesYesGlobalVaries
audit_log_rotate_on_sizeYesYesYesGlobalYes
audit_log_statement_policyYesYesYesGlobalYes
audit_log_strategyYesYesYesGlobalNo
auto_increment_increment  YesBothYes
auto_increment_offset  YesBothYes
autocommitYesYesYesBothYes
automatic_sp_privileges  YesGlobalYes
back_log  YesGlobalNo
basedirYesYesYesGlobalNo
big-tablesYesYes  Yes
- Variable: big_tables  YesBothYes
bind-addressYesYes  No
- Variable: bind_address  YesGlobalNo
binlog_cache_sizeYesYesYesGlobalYes
binlog_checksum  YesGlobalYes
binlog_direct_non_transactional_updatesYesYesYesBothYes
binlog_error_actionYesYesYesBothYes
binlog-formatYesYes  Yes
- Variable: binlog_format  YesBothYes
binlog_max_flush_queue_time  YesGlobalYes
binlog_order_commits  YesGlobalYes
binlog_row_imageYesYesYesBothYes
binlog_rows_query_log_events  YesBothYes
binlog_stmt_cache_sizeYesYesYesGlobalYes
binlogging_impossible_modeYesYesYesBothYes
block_encryption_modeYesYesYesBothYes
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
core_file  YesGlobalNo
daemon_memcached_enable_binlogYesYesYesGlobalNo
daemon_memcached_engine_lib_nameYesYesYesGlobalNo
daemon_memcached_engine_lib_pathYesYesYesGlobalNo
daemon_memcached_optionYesYesYesGlobalNo
daemon_memcached_r_batch_sizeYesYesYesGlobalNo
daemon_memcached_w_batch_sizeYesYesYesGlobalNo
datadirYesYesYesGlobalNo
date_format  YesGlobalNo
datetime_format  YesGlobalNo
debugYesYesYesBothYes
debug_sync  YesSessionYes
default-storage-engineYesYes  Yes
- Variable: default_storage_engine  YesBothYes
default_tmp_storage_engineYesYesYesBothYes
default_week_formatYesYesYesBothYes
delay-key-writeYesYes  Yes
- Variable: delay_key_write  YesGlobalYes
delayed_insert_limitYesYesYesGlobalYes
delayed_insert_timeoutYesYesYesGlobalYes
delayed_queue_sizeYesYesYesGlobalYes
disable_gtid_unsafe_statementsYesYesYesGlobalNo
disable-gtid-unsafe-statementsYesYesYesGlobalNo
disconnect_on_expired_passwordYesYesYesSessionNo
div_precision_incrementYesYesYesBothYes
end_markers_in_json  YesBothYes
enforce_gtid_consistencyYesYesYesGlobalNo
enforce-gtid-consistencyYesYesYesGlobalNo
engine-condition-pushdownYesYes  Yes
- Variable: engine_condition_pushdown  YesBothYes
eq_range_index_dive_limit  YesBothYes
error_count  YesSessionNo
event-schedulerYesYes  Yes
- Variable: event_scheduler  YesGlobalYes
expire_logs_daysYesYesYesGlobalYes
explicit_defaults_for_timestampYesYesYesSessionNo
external_user  YesSessionNo
flushYesYesYesGlobalYes
flush_timeYesYesYesGlobalYes
foreign_key_checks  YesBothYes
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
gtid_done  YesBothNo
gtid_executed  YesBothNo
gtid_lost  YesGlobalNo
gtid_mode  YesGlobalNo
gtid-modeYesYes  No
- Variable: gtid_mode  YesGlobalNo
gtid_next  YesSessionYes
gtid_owned  YesBothNo
gtid_purged  YesGlobalYes
have_compress  YesGlobalNo
have_crypt  YesGlobalNo
have_csv  YesGlobalNo
have_dynamic_loading  YesGlobalNo
have_geometry  YesGlobalNo
have_innodb  YesGlobalNo
have_ndbcluster  YesGlobalNo
have_openssl  YesGlobalNo
have_partitioning  YesGlobalNo
have_profiling  YesGlobalNo
have_query_cache  YesGlobalNo
have_rtree_keys  YesGlobalNo
have_ssl  YesGlobalNo
have_symlink  YesGlobalNo
host_cache_size  YesGlobalYes
hostname  YesGlobalNo
identity  YesSessionYes
ignore-builtin-innodbYesYes  No
- Variable: ignore_builtin_innodb  YesGlobalNo
ignore_db_dirs  YesGlobalNo
init_connectYesYesYesGlobalYes
init-fileYesYes  No
- Variable: init_file  YesGlobalNo
init_slaveYesYesYesGlobalYes
innodb_adaptive_flushingYesYesYesGlobalYes
innodb_adaptive_flushing_lwmYesYesYesGlobalYes
innodb_adaptive_hash_indexYesYesYesGlobalYes
innodb_adaptive_max_sleep_delayYesYesYesGlobalYes
innodb_additional_mem_pool_sizeYesYesYesGlobalNo
innodb_api_bk_commit_intervalYesYesYesGlobalYes
innodb_api_disable_rowlockYesYesYesGlobalNo
innodb_api_enable_binlogYesYesYesGlobalNo
innodb_api_enable_mdlYesYesYesGlobalNo
innodb_api_trx_levelYesYesYesGlobalYes
innodb_autoextend_incrementYesYesYesGlobalYes
innodb_autoinc_lock_modeYesYesYesGlobalNo
innodb_buffer_pool_dump_at_shutdownYesYesYesGlobalYes
innodb_buffer_pool_dump_nowYesYesYesGlobalYes
innodb_buffer_pool_filenameYesYesYesGlobalYes
innodb_buffer_pool_instancesYesYesYesGlobalNo
innodb_buffer_pool_load_abortYesYesYesGlobalYes
innodb_buffer_pool_load_at_startupYesYesYesGlobalNo
innodb_buffer_pool_load_nowYesYesYesGlobalYes
innodb_buffer_pool_sizeYesYesYesGlobalNo
innodb_change_buffer_max_sizeYesYesYesGlobalYes
innodb_change_bufferingYesYesYesGlobalYes
innodb_checksum_algorithmYesYesYesGlobalYes
innodb_checksumsYesYesYesGlobalNo
innodb_cmp_per_index_enabledYesYesYesGlobalYes
innodb_commit_concurrencyYesYesYesGlobalYes
innodb_compression_failure_threshold_pctYesYesYesGlobalYes
innodb_compression_levelYesYesYesGlobalYes
innodb_compression_pad_pct_maxYesYesYesGlobalYes
innodb_concurrency_ticketsYesYesYesGlobalYes
innodb_data_file_pathYesYesYesGlobalNo
innodb_data_home_dirYesYesYesGlobalNo
innodb_disable_sort_file_cacheYesYesYesGlobalYes
innodb_doublewriteYesYesYesGlobalNo
innodb_fast_shutdownYesYesYesGlobalYes
innodb_file_formatYesYesYesGlobalYes
innodb_file_format_checkYesYesYesGlobalNo
innodb_file_format_maxYesYesYesGlobalYes
innodb_file_per_tableYesYesYesGlobalYes
innodb_flush_log_at_timeout  YesGlobalYes
innodb_flush_log_at_trx_commitYesYesYesGlobalYes
innodb_flush_methodYesYesYesGlobalNo
innodb_flush_neighborsYesYesYesGlobalYes
innodb_flushing_avg_loopsYesYesYesGlobalYes
innodb_force_load_corruptedYesYesYesGlobalNo
innodb_force_recoveryYesYesYesGlobalNo
innodb_ft_aux_table  YesGlobalYes
innodb_ft_cache_sizeYesYesYesGlobalNo
innodb_ft_enable_diag_printYesYesYesGlobalYes
innodb_ft_enable_stopwordYesYesYesGlobalYes
innodb_ft_max_token_sizeYesYesYesGlobalNo
innodb_ft_min_token_sizeYesYesYesGlobalNo
innodb_ft_num_word_optimizeYesYesYesGlobalYes
innodb_ft_result_cache_limitYesYesYesGlobalYes
innodb_ft_server_stopword_tableYesYesYesGlobalYes
innodb_ft_sort_pll_degreeYesYesYesGlobalNo
innodb_ft_total_cache_sizeYesYesYesGlobalNo
innodb_ft_user_stopword_tableYesYesYesBothYes
innodb_io_capacityYesYesYesGlobalYes
innodb_io_capacity_maxYesYesYesGlobalYes
innodb_large_prefixYesYesYesGlobalYes
innodb_lock_wait_timeoutYesYesYesBothYes
innodb_locks_unsafe_for_binlogYesYesYesGlobalNo
innodb_log_buffer_sizeYesYesYesGlobalNo
innodb_log_compressed_pagesYesYesYesGlobalYes
innodb_log_file_sizeYesYesYesGlobalNo
innodb_log_files_in_groupYesYesYesGlobalNo
innodb_log_group_home_dirYesYesYesGlobalNo
innodb_lru_scan_depthYesYesYesGlobalYes
innodb_max_dirty_pages_pctYesYesYesGlobalYes
innodb_max_dirty_pages_pct_lwmYesYesYesGlobalYes
innodb_max_purge_lagYesYesYesGlobalYes
innodb_max_purge_lag_delayYesYesYesGlobalYes
innodb_mirrored_log_groupsYesYesYesGlobalNo
innodb_monitor_disableYesYesYesGlobalYes
innodb_monitor_enableYesYesYesGlobalYes
innodb_monitor_resetYesYesYesGlobalYes
innodb_monitor_reset_allYesYesYesGlobalYes
innodb_old_blocks_pctYesYesYesGlobalYes
innodb_old_blocks_timeYesYesYesGlobalYes
innodb_online_alter_log_max_sizeYesYesYesGlobalYes
innodb_open_filesYesYesYesGlobalNo
innodb_optimize_fulltext_onlyYesYesYesGlobalYes
innodb_page_sizeYesYesYesGlobalNo
innodb_print_all_deadlocksYesYesYesGlobalYes
innodb_purge_batch_sizeYesYesYesGlobalYes
innodb_purge_threadsYesYesYesGlobalNo
innodb_random_read_aheadYesYesYesGlobalYes
innodb_read_ahead_thresholdYesYesYesGlobalYes
innodb_read_io_threadsYesYesYesGlobalNo
innodb_read_onlyYesYesYesGlobalNo
innodb_replication_delayYesYesYesGlobalYes
innodb_rollback_on_timeoutYesYesYesGlobalNo
innodb_rollback_segmentsYesYesYesGlobalYes
innodb_sort_buffer_sizeYesYesYesGlobalNo
innodb_spin_wait_delayYesYesYesGlobalYes
innodb_stats_auto_recalcYesYesYesGlobalYes
innodb_stats_methodYesYesYesGlobalYes
innodb_stats_on_metadataYesYesYesGlobalYes
innodb_stats_persistentYesYesYesGlobalYes
innodb_stats_persistent_sample_pagesYesYesYesGlobalYes
innodb_stats_sample_pagesYesYesYesGlobalYes
innodb_stats_transient_sample_pagesYesYesYesGlobalYes
innodb_status_outputYesYesYesGlobalYes
innodb_status_output_locksYesYesYesGlobalYes
innodb_strict_modeYesYesYesBothYes
innodb_support_xaYesYesYesBothYes
innodb_sync_array_sizeYesYesYesGlobalNo
innodb_sync_spin_loopsYesYesYesGlobalYes
innodb_table_locksYesYesYesBothYes
innodb_thread_concurrencyYesYesYesGlobalYes
innodb_thread_sleep_delayYesYesYesGlobalYes
innodb_undo_directoryYesYesYesGlobalNo
innodb_undo_logsYesYesYesGlobalYes
innodb_undo_tablespacesYesYesYesGlobalNo
innodb_use_native_aioYesYesYesGlobalNo
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-messagesYesYes  Yes
- Variable: lc_messages  YesBothYes
lc-messages-dirYesYes  No
- Variable: lc_messages_dir  YesGlobalNo
lc_time_names  YesBothYes
license  YesGlobalNo
local_infile  YesGlobalYes
lock_wait_timeoutYesYesYesBothYes
locked_in_memory  YesGlobalNo
logYesYesYesGlobalYes
log_bin  YesGlobalNo
log-binYesYesYesGlobalNo
log_bin_basename  YesGlobalNo
log_bin_index  YesGlobalNo
log-bin-trust-function-creatorsYesYes  Yes
- Variable: log_bin_trust_function_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_admin_statements  YesGlobalYes
log-slow-queriesYesYes  Yes
- Variable: log_slow_queries  YesGlobalYes
log_slow_slave_statements  YesGlobalYes
log_throttle_queries_not_using_indexes  YesGlobalYes
log-warningsYesYes  Yes
- Variable: log_warnings  YesVariesYes
long_query_timeYesYesYesBothYes
low-priority-updatesYesYes  Yes
- Variable: low_priority_updates  YesBothYes
lower_case_file_system  YesGlobalNo
lower_case_table_namesYesYesYesGlobalNo
master_info_repositoryYesYesYesGlobalYes
master_verify_checksum  YesGlobalYes
max_allowed_packetYesYesYesGlobalYes
max_binlog_cache_sizeYesYesYesGlobalYes
max_binlog_sizeYesYesYesGlobalYes
max_binlog_stmt_cache_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_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
metadata_locks_cache_size  YesGlobalNo
metadata_locks_hash_instances  YesGlobalNo
min-examined-row-limitYesYesYesBothYes
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_index_stat_cache_entriesYesYesYesBothYes
ndb_index_stat_enableYesYesYesBothYes
ndb_index_stat_optionYesYesYesBothYes
ndb_index_stat_update_freqYesYesYesBothYes
ndb_join_pushdown  YesBothYes
ndb-log-apply-statusYesYes  No
- Variable: ndb_log_apply_status  YesGlobalNo
ndb_log_apply_statusYesYesYesGlobalNo
ndb_log_binYes YesBothYes
ndb_log_binlog_indexYes YesGlobalYes
ndb_log_empty_epochsYesYesYesGlobalYes
ndb-log-empty-epochsYesYesYesGlobalYes
ndb-log-exclusive-readsYesYes  Yes
- Variable: ndb_log_exclusive_reads  YesBothYes
ndb_log_exclusive_readsYesYesYesBothYes
ndb-log-origYesYes  No
- Variable: ndb_log_orig  YesGlobalNo
ndb_log_origYesYesYesGlobalNo
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_optimized_node_selectionYesYesYesGlobalNo
ndb_recv_thread_cpu_mask  YesGlobalYes
ndb_show_foreign_key_mock_tablesYesYesYesGlobalYes
ndb_slave_conflict_roleYesYesYesGlobalYes
Ndb_slave_last_conflict_epoch  YesGlobalNo
Ndb_slave_max_replicated_epoch  YesGlobalNo
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_join_cache_levelYesYesYesBothYes
optimizer_prune_levelYesYesYesBothYes
optimizer_search_depthYesYesYesBothYes
optimizer_switchYesYesYesBothYes
optimizer_trace  YesBothYes
optimizer_trace_features  YesBothYes
optimizer_trace_limit  YesBothYes
optimizer_trace_max_mem_size  YesBothYes
optimizer_trace_offset  YesBothYes
performance_schemaYesYesYesGlobalNo
performance_schema_accounts_sizeYesYesYesGlobalNo
performance_schema_digests_sizeYesYesYesGlobalNo
performance_schema_events_stages_history_long_sizeYesYesYesGlobalNo
performance_schema_events_stages_history_sizeYesYesYesGlobalNo
performance_schema_events_statements_history_long_sizeYesYesYesGlobalNo
performance_schema_events_statements_history_sizeYesYesYesGlobalNo
performance_schema_events_waits_history_long_sizeYesYesYesGlobalNo
performance_schema_events_waits_history_sizeYesYesYesGlobalNo
performance_schema_hosts_sizeYesYesYesGlobalNo
performance_schema_max_cond_classesYesYesYesGlobalNo
performance_schema_max_cond_instancesYesYesYesGlobalNo
performance_schema_max_file_classesYesYesYesGlobalNo
performance_schema_max_file_handlesYesYesYesGlobalNo
performance_schema_max_file_instancesYesYesYesGlobalNo
performance_schema_max_mutex_classesYesYesYesGlobalNo
performance_schema_max_mutex_instancesYesYesYesGlobalNo
performance_schema_max_rwlock_classesYesYesYesGlobalNo
performance_schema_max_rwlock_instancesYesYesYesGlobalNo
performance_schema_max_socket_classesYesYesYesGlobalNo
performance_schema_max_socket_instancesYesYesYesGlobalNo
performance_schema_max_stage_classesYesYesYesGlobalNo
performance_schema_max_statement_classesYesYesYesGlobalNo
performance_schema_max_table_handlesYesYesYesGlobalNo
performance_schema_max_table_instancesYesYesYesGlobalNo
performance_schema_max_thread_classesYesYesYesGlobalNo
performance_schema_max_thread_instancesYesYesYesGlobalNo
performance_schema_session_connect_attrs_sizeYesYesYesGlobalNo
performance_schema_setup_actors_sizeYesYesYesGlobalNo
performance_schema_setup_objects_sizeYesYesYesGlobalNo
performance_schema_users_sizeYesYesYesGlobalNo
pid-fileYesYes  No
- Variable: pid_file  YesGlobalNo
plugin_dirYesYesYesGlobalNo
portYesYesYesGlobalNo
preload_buffer_sizeYesYesYesBothYes
profiling  YesBothYes
profiling_history_sizeYesYesYesBothYes
protocol_version  YesGlobalNo
proxy_user  YesSessionNo
pseudo_slave_mode  YesSessionYes
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_basename  YesGlobalNo
relay-log-indexYesYes  No
- Variable: relay_log_index  YesGlobalNo
relay_log_indexYesYesYesGlobalNo
relay_log_info_fileYesYesYesGlobalNo
relay_log_info_repository  YesGlobalYes
relay_log_purgeYesYesYesGlobalYes
relay_log_recoveryYesYesYesGlobalVaries
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_semi_sync_master_enabled  YesGlobalYes
rpl_semi_sync_master_timeout  YesGlobalYes
rpl_semi_sync_master_trace_level  YesGlobalYes
rpl_semi_sync_master_wait_no_slave  YesGlobalYes
rpl_semi_sync_slave_enabled  YesGlobalYes
rpl_semi_sync_slave_trace_level  YesGlobalYes
rpl_stop_slave_timeoutYesYesYesGlobalYes
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_bitsYesYesYesGlobalNo
server-id-bitsYesYes  No
- Variable: server_id_bits  YesGlobalNo
server_uuid  YesGlobalNo
sha256_password_private_key_path  YesGlobalNo
sha256_password_public_key_path  YesGlobalNo
shared_memoryYesYesYesGlobalNo
shared_memory_base_name  YesGlobalNo
simplified_binlog_gtid_recoveryYesYesYesGlobalNo
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_checkpoint_groupYesYesYesGlobalYes
slave_checkpoint_periodYesYesYesGlobalYes
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_parallel_workers  YesGlobalYes
slave_pending_jobs_size_max  YesGlobalYes
slave_rows_search_algorithms  YesGlobalYes
slave-skip-errorsYesYes  No
- Variable: slave_skip_errors  YesGlobalNo
slave_sql_verify_checksum  YesGlobalYes
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  YesBothYes
sql_big_selects  YesBothYes
sql_big_tables  YesBothYes
sql_buffer_result  YesBothYes
sql_log_bin  YesBothYes
sql_log_off  YesBothYes
sql_low_priority_updates  YesBothYes
sql_max_join_size  YesBothYes
sql-modeYesYes  Yes
- Variable: sql_mode  YesBothYes
sql_notes  YesBothYes
sql_quote_show_create  YesBothYes
sql_safe_updates  YesBothYes
sql_select_limit  YesBothYes
sql_slave_skip_counter  YesGlobalYes
sql_warnings  YesBothYes
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-crlYesYes  No
- Variable: ssl_crl  YesGlobalNo
ssl-crlpathYesYes  No
- Variable: ssl_crlpath  YesGlobalNo
ssl-keyYesYes  No
- Variable: ssl_key  YesGlobalNo
storage_engine  YesBothYes
stored_program_cacheYesYesYesGlobalYes
sync_binlogYesYesYesGlobalYes
sync_frmYesYesYesGlobalYes
sync_master_infoYesYesYesGlobalYes
sync_relay_logYesYesYesGlobalYes
sync_relay_log_infoYesYesYesGlobalYes
system_time_zone  YesGlobalNo
table_definition_cache  YesGlobalYes
table_open_cache  YesGlobalYes
table_open_cache_instances  YesGlobalNo
thread_cache_sizeYesYesYesGlobalYes
thread_concurrencyYesYesYesGlobalNo
thread_handlingYesYesYesGlobalNo
thread_pool_algorithmYesYesYesGlobalNo
thread_pool_high_priority_connectionYesYesYesBothYes
thread_pool_max_unused_threadsYesYesYesGlobalYes
thread_pool_prio_kickup_timerYesYesYesBothYes
thread_pool_sizeYesYesYesGlobalNo
thread_pool_stall_limitYesYesYesGlobalYes
thread_stackYesYesYesGlobalNo
time_format  YesGlobalNo
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
tx_read_only  YesBothYes
unique_checks  YesBothYes
updatable_views_with_limitYesYesYesBothYes
validate_password_dictionary_file  YesGlobalNo
validate_password_length  YesGlobalYes
validate_password_mixed_case_count  YesGlobalYes
validate_password_number_count  YesGlobalYes
validate_password_policy  YesGlobalYes
validate_password_special_char_count  YesGlobalYes
validate_user_plugins  YesGlobalNo
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, before MySQL 5.6.2, 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. As of MySQL 5.6.2, boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case sensitive). See Section 4.2.5, “Program Option Modifiers”.

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.