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, “Session 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.4. Dynamic Variable Summary
Variable Name | Variable Type | Variable Scope |
---|---|---|
auto_increment_increment |
numeric |
GLOBAL | SESSION
|
auto_increment_offset |
numeric |
GLOBAL | SESSION
|
autocommit |
boolean | SESSION |
automatic_sp_privileges |
boolean | GLOBAL |
big_tables |
boolean | SESSION |
binlog_cache_size |
numeric | GLOBAL |
bulk_insert_buffer_size |
numeric |
GLOBAL | SESSION
|
character_set_client |
string |
GLOBAL | SESSION
|
character_set_connection |
string |
GLOBAL | SESSION
|
character_set_database |
string |
GLOBAL | SESSION
|
character_set_filesystem |
string |
GLOBAL | SESSION
|
character_set_results |
string |
GLOBAL | SESSION
|
character_set_server |
string |
GLOBAL | SESSION
|
collation_connection |
string |
GLOBAL | SESSION
|
collation_database |
string |
GLOBAL | SESSION
|
collation_server |
string |
GLOBAL | SESSION
|
completion_type |
numeric |
GLOBAL | SESSION
|
concurrent_insert |
boolean | GLOBAL |
connect_timeout |
numeric | GLOBAL |
debug |
string |
GLOBAL | SESSION
|
default_week_format |
numeric |
GLOBAL | SESSION
|
delay_key_write |
enumeration | GLOBAL |
delayed_insert_limit |
numeric | GLOBAL |
delayed_insert_timeout |
numeric | GLOBAL |
delayed_queue_size |
numeric | GLOBAL |
div_precision_increment |
numeric |
GLOBAL | SESSION
|
engine_condition_pushdown |
boolean |
GLOBAL | SESSION
|
expire_logs_days |
numeric | GLOBAL |
flush |
boolean | GLOBAL |
flush_time |
numeric | GLOBAL |
foreign_key_checks |
boolean | SESSION |
ft_boolean_syntax |
string | GLOBAL |
group_concat_max_len |
numeric |
GLOBAL | SESSION
|
identity |
numeric | SESSION |
init_connect |
string | GLOBAL |
init_slave |
string | GLOBAL |
innodb_autoextend_increment |
numeric | GLOBAL |
innodb_commit_concurrency |
numeric | GLOBAL |
innodb_concurrency_tickets |
numeric | GLOBAL |
innodb_fast_shutdown |
boolean | GLOBAL |
innodb_flush_log_at_trx_commit |
numeric | GLOBAL |
innodb_max_dirty_pages_pct |
numeric | GLOBAL |
innodb_max_purge_lag |
numeric | GLOBAL |
innodb_support_xa |
boolean |
GLOBAL | SESSION
|
innodb_sync_spin_loops |
numeric | GLOBAL |
innodb_table_locks |
boolean |
GLOBAL | SESSION
|
innodb_thread_concurrency |
numeric | GLOBAL |
innodb_thread_sleep_delay |
numeric | GLOBAL |
innodb_use_legacy_cardinality_algorithm |
boolean | GLOBAL |
insert_id |
numeric | SESSION |
interactive_timeout |
numeric |
GLOBAL | SESSION
|
join_buffer_size |
numeric |
GLOBAL | SESSION
|
keep_files_on_create |
boolean |
GLOBAL | SESSION
|
key_buffer_size |
numeric | GLOBAL |
key_cache_age_threshold |
numeric | GLOBAL |
key_cache_block_size |
numeric | GLOBAL |
key_cache_division_limit |
numeric | GLOBAL |
last_insert_id |
numeric | SESSION |
lc_time_names |
string |
GLOBAL | SESSION
|
local_infile |
GLOBAL |
|
log_bin_trust_function_creators |
boolean | GLOBAL |
log_bin_trust_routine_creators |
boolean | GLOBAL |
log_queries_not_using_indexes |
boolean | GLOBAL |
log_warnings |
numeric |
GLOBAL | SESSION
|
long_query_time |
numeric |
GLOBAL | SESSION
|
low_priority_updates |
boolean |
GLOBAL | SESSION
|
max_allowed_packet |
numeric |
GLOBAL | SESSION
|
max_binlog_cache_size |
numeric | GLOBAL |
max_binlog_size |
numeric | GLOBAL |
max_connect_errors |
numeric | GLOBAL |
max_connections |
numeric | GLOBAL |
max_delayed_threads |
numeric |
GLOBAL | SESSION
|
max_error_count |
numeric |
GLOBAL | SESSION
|
max_heap_table_size |
numeric |
GLOBAL | SESSION
|
max_insert_delayed_threads |
numeric |
GLOBAL | SESSION
|
max_join_size |
numeric |
GLOBAL | SESSION
|
max_length_for_sort_data |
numeric |
GLOBAL | SESSION
|
max_prepared_stmt_count |
numeric | GLOBAL |
max_relay_log_size |
numeric | GLOBAL |
max_seeks_for_key |
numeric |
GLOBAL | SESSION
|
max_sort_length |
numeric |
GLOBAL | SESSION
|
max_sp_recursion_depth |
numeric |
GLOBAL | SESSION
|
max_tmp_tables |
numeric |
GLOBAL | SESSION
|
max_user_connections |
numeric |
GLOBAL | SESSION
|
max_write_lock_count |
numeric | GLOBAL |
multi_range_count |
numeric |
GLOBAL | SESSION
|
myisam_data_pointer_size |
numeric | GLOBAL |
myisam_max_sort_file_size |
numeric | GLOBAL |
myisam_repair_threads |
numeric |
GLOBAL | SESSION
|
myisam_sort_buffer_size |
numeric |
GLOBAL | SESSION
|
myisam_stats_method |
enumeration |
GLOBAL | SESSION
|
ndb_autoincrement_prefetch_sz |
numeric |
GLOBAL | SESSION
|
ndb_cache_check_time |
numeric | GLOBAL |
ndb_force_send |
boolean |
GLOBAL | SESSION
|
ndb_use_exact_count |
boolean |
GLOBAL | SESSION
|
ndb_use_transactions |
boolean |
GLOBAL | SESSION
|
net_buffer_length |
numeric |
GLOBAL | SESSION
|
net_read_timeout |
numeric |
GLOBAL | SESSION
|
net_retry_count |
numeric |
GLOBAL | SESSION
|
net_write_timeout |
numeric |
GLOBAL | SESSION
|
new |
boolean |
GLOBAL | SESSION
|
old_passwords |
boolean |
GLOBAL | SESSION
|
optimizer_prune_level |
boolean |
GLOBAL | SESSION
|
optimizer_search_depth |
numeric |
GLOBAL | SESSION
|
preload_buffer_size |
numeric |
GLOBAL | SESSION
|
profiling |
boolean | SESSION |
profiling_history_size |
numeric |
GLOBAL | SESSION
|
pseudo_thread_id |
numeric | SESSION |
query_alloc_block_size |
numeric |
GLOBAL | SESSION
|
query_cache_limit |
numeric | GLOBAL |
query_cache_min_res_unit |
numeric | GLOBAL |
query_cache_size |
numeric | GLOBAL |
query_cache_type |
enumeration |
GLOBAL | SESSION
|
query_cache_wlock_invalidate |
boolean |
GLOBAL | SESSION
|
query_prealloc_size |
numeric |
GLOBAL | SESSION
|
rand_seed1 |
numeric | SESSION |
rand_seed2 |
numeric | SESSION |
range_alloc_block_size |
numeric |
GLOBAL | SESSION
|
read_buffer_size |
numeric |
GLOBAL | SESSION
|
read_only |
numeric | GLOBAL |
read_rnd_buffer_size |
numeric |
GLOBAL | SESSION
|
relay_log_purge |
boolean | GLOBAL |
rpl_recovery_rank |
numeric | GLOBAL |
safe_show_database |
boolean | GLOBAL |
secure_auth |
boolean | GLOBAL |
server_id |
numeric | GLOBAL |
slave_compressed_protocol |
boolean | GLOBAL |
slave_net_timeout |
numeric | GLOBAL |
slave_transaction_retries |
numeric | GLOBAL |
slow_launch_time |
numeric | GLOBAL |
sort_buffer_size |
numeric |
GLOBAL | SESSION
|
sql_auto_is_null |
boolean | SESSION |
sql_big_selects |
boolean |
GLOBAL | SESSION
|
sql_big_tables |
boolean | SESSION |
sql_buffer_result |
boolean | SESSION |
sql_log_bin |
boolean | SESSION |
sql_log_off |
boolean | SESSION |
sql_log_update |
boolean | SESSION |
sql_low_priority_updates |
boolean |
GLOBAL | SESSION
|
sql_max_join_size |
numeric |
GLOBAL | SESSION
|
sql_mode |
set |
GLOBAL | SESSION
|
sql_notes |
boolean | SESSION |
sql_quote_show_create |
boolean | SESSION |
sql_safe_updates |
boolean | SESSION |
sql_select_limit |
numeric |
GLOBAL | SESSION
|
sql_slave_skip_counter |
numeric | GLOBAL |
sql_warnings |
boolean | SESSION |
storage_engine |
enumeration |
GLOBAL | SESSION
|
sync_binlog |
numeric | GLOBAL |
sync_frm |
boolean | GLOBAL |
table_cache |
numeric | GLOBAL |
table_lock_wait_timeout |
numeric | GLOBAL |
table_type |
enumeration |
GLOBAL | SESSION
|
thread_cache_size |
numeric | GLOBAL |
time_zone |
string |
GLOBAL | SESSION
|
timed_mutexes |
boolean | GLOBAL |
timestamp |
numeric | SESSION |
tmp_table_size |
numeric |
GLOBAL | SESSION
|
transaction_alloc_block_size |
numeric |
GLOBAL | SESSION
|
transaction_prealloc_size |
numeric |
GLOBAL | SESSION
|
tx_isolation |
enumeration |
GLOBAL | SESSION
|
unique_checks |
boolean | SESSION |
updatable_views_with_limit |
boolean |
GLOBAL | SESSION
|
wait_timeout |
numeric |
GLOBAL | SESSION
|
MySQL Enterprise. Improper configuration of system variables can adversely affect performance and security. The MySQL Enterprise Monitor continually monitors system variables and provides expert advice about appropriate settings. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
Add your own comment.