MySQL 5.5 Reference Manual Including MySQL NDB Cluster 7.2 Reference Guide Dynamic System Variables

Many server system variables are dynamic and can be set at runtime. See Section, “SET Syntax for Variable Assignment”. For a description of the privilege requirements for setting system variables, see Section, “System Variable Privileges”

The following table lists all dynamic system variables applicable within mysqld.

The table lists each variable's data type and scope. The last column indicates whether the scope for each variable is Global, Session, or both. Please see the corresponding item descriptions for details on setting and using the variables. Where appropriate, direct links to further information about the items are provided.

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 the ENUM data type used for table columns, for which the first enumeration value corresponds to 1.

Table 5.4 Dynamic System Variable Summary

Variable Name Variable Type Variable Scope
audit_log_flush Boolean Global
audit_log_policy Enumeration Global
audit_log_rotate_on_size Integer Global
auto_increment_increment Integer Both
auto_increment_offset Integer Both
autocommit Boolean Varies
automatic_sp_privileges Boolean Global
big_tables Boolean Varies
binlog_cache_size Integer Global
binlog_direct_non_transactional_updates Boolean Both
binlog_format Enumeration Both
binlog_stmt_cache_size Integer Global
bulk_insert_buffer_size Integer Both
character_set_client String Both
character_set_connection String Both
character_set_database String Both
character_set_filesystem String Both
character_set_results String Both
character_set_server String Both
collation_connection String Both
collation_database String Both
collation_server String Both
completion_type Enumeration Both
concurrent_insert Enumeration Global
connect_timeout Integer Global
debug String Both
debug_sync String Varies
default_storage_engine Enumeration Both
default_week_format Integer Both
delay_key_write Enumeration Global
delayed_insert_limit Integer Global
delayed_insert_timeout Integer Global
delayed_queue_size Integer Global
div_precision_increment Integer Both
engine_condition_pushdown Boolean Both
event_scheduler Enumeration Global
expire_logs_days Integer Global
flush Boolean Global
flush_time Integer Global
foreign_key_checks Boolean Varies
ft_boolean_syntax String Global
general_log Boolean Global
general_log_file File name Global
group_concat_max_len Integer Both
identity Integer Session
init_connect String Global
init_slave String Global
innodb_adaptive_flushing Boolean Global
innodb_adaptive_hash_index Boolean Global
innodb_autoextend_increment Integer Global
innodb_change_buffering Enumeration Global
innodb_change_buffering_debug Integer Global
innodb_commit_concurrency Integer Global
innodb_concurrency_tickets Integer Global
innodb_fast_shutdown Integer Global
innodb_file_format String Global
innodb_file_format_check Boolean Global
innodb_file_format_max String Global
innodb_file_per_table Boolean Global
innodb_flush_log_at_trx_commit Enumeration Global
innodb_io_capacity Integer Global
innodb_large_prefix Boolean Global
innodb_limit_optimistic_insert_debug Integer Global
innodb_lock_wait_timeout Integer Both
innodb_max_dirty_pages_pct Numeric Global
innodb_max_purge_lag Integer Global
innodb_old_blocks_pct Integer Global
innodb_old_blocks_time Integer Global
innodb_print_all_deadlocks Boolean Global
innodb_purge_batch_size Integer Global
innodb_random_read_ahead Boolean Global
innodb_read_ahead_threshold Integer Global
innodb_replication_delay Integer Global
innodb_rollback_segments Integer Global
innodb_spin_wait_delay Integer Global
innodb_stats_method Enumeration Global
innodb_stats_on_metadata Boolean Global
innodb_stats_sample_pages Integer Global
innodb_strict_mode Boolean Both
innodb_support_xa Boolean Both
innodb_sync_spin_loops Integer Global
innodb_table_locks Boolean Both
innodb_thread_concurrency Integer Global
innodb_thread_sleep_delay Integer Global
innodb_trx_purge_view_update_only_debug Boolean Global
innodb_trx_rseg_n_slots_debug Integer Global
insert_id Integer Session
interactive_timeout Integer Both
join_buffer_size Integer Both
keep_files_on_create Boolean Both
key_buffer_size Integer Global
key_cache_age_threshold Integer Global
key_cache_block_size Integer Global
key_cache_division_limit Integer Global
last_insert_id Integer Session
lc_messages String Both
lc_time_names String Both
local_infile Boolean Global
lock_wait_timeout Integer Both
log File name Global
log_bin_trust_function_creators Boolean Global
log_output Set Global
log_queries_not_using_indexes Boolean Global
log_slow_queries Boolean Global
log_warnings Integer Both
long_query_time Numeric Both
low_priority_updates Boolean Both
max_allowed_packet Integer Both
max_binlog_cache_size Integer Global
max_binlog_size Integer Global
max_binlog_stmt_cache_size Integer Global
max_connect_errors Integer Global
max_connections Integer Global
max_delayed_threads Integer Both
max_error_count Integer Both
max_heap_table_size Integer Both
max_insert_delayed_threads Integer Both
max_join_size Integer Both
max_length_for_sort_data Integer Both
max_prepared_stmt_count Integer Global
max_relay_log_size Integer Global
max_seeks_for_key Integer Both
max_sort_length Integer Both
max_sp_recursion_depth Integer Both
max_tmp_tables Integer Both
max_user_connections Integer Both
max_write_lock_count Integer Global
min_examined_row_limit Integer Both
multi_range_count Integer Both
myisam_data_pointer_size Integer Global
myisam_max_sort_file_size Integer Global
myisam_repair_threads Integer Both
myisam_sort_buffer_size Integer Both
myisam_stats_method Enumeration Both
myisam_use_mmap Boolean Global
ndb_autoincrement_prefetch_sz Integer Both
ndb_blob_read_batch_bytes Integer Both
ndb_blob_write_batch_bytes Integer Both
ndb_cache_check_time Integer Global
ndb_deferred_constraints Integer Both
ndb_deferred_constraints Integer Both
ndb_distribution Enumeration Global
ndb_distribution Enumeration Global
ndb_eventbuffer_max_alloc Integer Global
ndb_extra_logging Integer Global
ndb_force_send Boolean Both
ndb_index_stat_cache_entries Integer Both
ndb_index_stat_enable Boolean Both
ndb_index_stat_option String Both
ndb_index_stat_update_freq Integer Both
ndb_join_pushdown Boolean Both
ndb_log_bin Boolean Both
ndb_log_binlog_index Boolean Global
ndb_log_empty_epochs Boolean Global
ndb_log_empty_epochs Boolean Global
ndb_log_empty_update Boolean Global
ndb_log_empty_update Boolean Global
ndb_log_update_as_write Boolean Global
ndb_log_updated_only Boolean Global
ndb_optimization_delay Integer Global
ndb_report_thresh_binlog_epoch_slip Integer Global
ndb_report_thresh_binlog_mem_usage Integer Global
ndb_table_no_logging Boolean Session
ndb_table_temporary Boolean Session
ndb_use_exact_count Boolean Both
ndb_use_transactions Boolean Both
ndbinfo_max_bytes Integer Both
ndbinfo_max_rows Integer Both
ndbinfo_offline Boolean Global
ndbinfo_show_hidden Boolean Both
ndbinfo_table_prefix String Both
net_buffer_length Integer Both
net_read_timeout Integer Both
net_retry_count Integer Both
net_write_timeout Integer Both
new Boolean Both
old_alter_table Boolean Both
old_passwords Boolean Both
optimizer_prune_level Integer Both
optimizer_search_depth Integer Both
optimizer_switch Set Both
preload_buffer_size Integer Both
profiling Boolean Varies
profiling_history_size Integer Both
pseudo_slave_mode Integer Session
pseudo_thread_id Integer Session
query_alloc_block_size Integer Both
query_cache_limit Integer Global
query_cache_min_res_unit Integer Global
query_cache_size Integer Global
query_cache_type Enumeration Both
query_cache_wlock_invalidate Boolean Both
query_prealloc_size Integer Both
rand_seed1 Integer Session
rand_seed2 Integer Session
range_alloc_block_size Integer Both
read_buffer_size Integer Both
read_only Boolean Global
read_rnd_buffer_size Integer Both
relay_log_purge Boolean Global
relay_log_recovery Boolean Global
rpl_recovery_rank Integer Global
rpl_semi_sync_master_enabled Boolean Global
rpl_semi_sync_master_timeout Integer Global
rpl_semi_sync_master_trace_level Integer Global
rpl_semi_sync_master_wait_no_slave Boolean Global
rpl_semi_sync_slave_enabled Boolean Global
rpl_semi_sync_slave_trace_level Integer Global
secure_auth Boolean Global
server_id Integer Global
slave_allow_batching Boolean Global
slave_compressed_protocol Boolean Global
slave_exec_mode Enumeration Global
slave_max_allowed_packet Integer Global
slave_net_timeout Integer Global
slave_transaction_retries Integer Global
slow_launch_time Integer Global
slow_query_log Boolean Global
slow_query_log_file File name Global
sort_buffer_size Integer Both
sql_auto_is_null Boolean Varies
sql_big_selects Boolean Varies
sql_big_tables Boolean Varies
sql_buffer_result Boolean Varies
sql_log_bin Boolean Varies
sql_log_off Boolean Varies
sql_low_priority_updates Boolean Both
sql_max_join_size Integer Both
sql_mode Set Both
sql_notes Boolean Varies
sql_quote_show_create Boolean Varies
sql_safe_updates Boolean Varies
sql_select_limit Integer Both
sql_slave_skip_counter Integer Global
sql_warnings Boolean Varies
storage_engine Enumeration Both
stored_program_cache Integer Global
sync_binlog Integer Global
sync_frm Boolean Global
sync_master_info Integer Global
sync_relay_log Integer Global
sync_relay_log_info Integer Global
table_definition_cache Integer Global
table_open_cache Integer Global
thread_cache_size Integer Global
thread_pool_high_priority_connection Integer Both
thread_pool_max_unused_threads Integer Global
thread_pool_prio_kickup_timer Integer Both
thread_pool_stall_limit Integer Global
time_zone String Both
timed_mutexes Boolean Global
timestamp Numeric Session
tmp_table_size Integer Both
transaction_alloc_block_size Integer Both
transaction_allow_batching Boolean Session
transaction_prealloc_size Integer Both
tx_isolation Enumeration Both
unique_checks Boolean Varies
updatable_views_with_limit Boolean Both
wait_timeout Integer Both