-
mysqld --verbose --help
- To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
- To see the current values used by a running server, use the
SHOW VARIABLES
statement.
This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 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.2. System Variable Summary
Name | Cmd-Line | Option file | System Var | Var Scope | Dynamic |
---|---|---|---|---|---|
auto_increment_increment | Yes | Yes | Yes | Both | Yes |
auto_increment_offset | Yes | Yes | Yes | Both | Yes |
autocommit | Yes | Session | Yes | ||
automatic_sp_privileges | Yes | Global | Yes | ||
back_log | Yes | Yes | Yes | Global | No |
basedir | Yes | Yes | Yes | Global | No |
big-tables | Yes | Yes | Yes | ||
– Variable: big_tables | Yes | Session | Yes | ||
binlog_cache_size | Yes | Yes | Yes | Global | Yes |
binlog_direct_non_transactional_updates | Yes | Yes | Yes | Both | Yes |
binlog-format | Yes | Yes | Yes | ||
– Variable: binlog_format | Yes | Both | Yes | ||
bulk_insert_buffer_size | Yes | Yes | Yes | Both | Yes |
character_set_client | Yes | Both | Yes | ||
character_set_connection | Yes | Both | Yes | ||
character_set_database[a] | Yes | Both | Yes | ||
character-set-filesystem | Yes | Yes | Yes | ||
– Variable: character_set_filesystem | Yes | Both | Yes | ||
character_set_results | Yes | Both | Yes | ||
character-set-server | Yes | Yes | Yes | ||
– Variable: character_set_server | Yes | Both | Yes | ||
character_set_system | Yes | Global | No | ||
character-sets-dir | Yes | Yes | No | ||
– Variable: character_sets_dir | Yes | Global | No | ||
collation_connection | Yes | Both | Yes | ||
collation_database[b] | Yes | Both | Yes | ||
collation-server | Yes | Yes | Yes | ||
– Variable: collation_server | Yes | Both | Yes | ||
completion_type | Yes | Yes | Yes | Both | Yes |
concurrent_insert | Yes | Yes | Yes | Global | Yes |
connect_timeout | Yes | Yes | Yes | Global | Yes |
datadir | Yes | Yes | Yes | Global | No |
date_format | Yes | Both | No | ||
datetime_format | Yes | Yes | Yes | Both | No |
debug | Yes | Yes | Yes | Both | Yes |
debug_sync | Yes | Both | Yes | ||
default-storage-engine | Yes | Yes | Yes | Both | Yes |
default_week_format | Yes | Yes | Yes | Both | Yes |
delay-key-write | Yes | Yes | Yes | ||
– Variable: delay_key_write | Yes | Global | Yes | ||
delayed_insert_limit | Yes | Yes | Yes | Global | Yes |
delayed_insert_timeout | Yes | Yes | Yes | Global | Yes |
delayed_queue_size | Yes | Yes | Yes | Global | Yes |
div_precision_increment | Yes | Yes | Yes | Both | Yes |
engine-condition-pushdown | Yes | Yes | Yes | ||
– Variable: engine_condition_pushdown | Yes | Both | Yes | ||
error_count | Yes | Session | No | ||
event-scheduler | Yes | Yes | Yes | ||
– Variable: event_scheduler | Yes | Global | Yes | ||
expire_logs_days | Yes | Yes | Yes | Global | Yes |
flush | Yes | Yes | Yes | Global | Yes |
flush_time | Yes | Yes | Yes | Global | Yes |
foreign_key_checks | Yes | Session | Yes | ||
ft_boolean_syntax | Yes | Yes | Yes | Global | Yes |
ft_max_word_len | Yes | Yes | Yes | Global | No |
ft_min_word_len | Yes | Yes | Yes | Global | No |
ft_query_expansion_limit | Yes | Yes | Yes | Global | No |
ft_stopword_file | Yes | Yes | Yes | Global | No |
general-log | Yes | Yes | Yes | ||
– Variable: general_log | Yes | Global | Yes | ||
general_log_file | Yes | Yes | Yes | Global | Yes |
group_concat_max_len | Yes | Yes | Yes | Both | Yes |
have_archive | Yes | Global | No | ||
have_blackhole_engine | Yes | Global | No | ||
have_community_features | Yes | Global | No | ||
have_compress | Yes | Global | No | ||
have_crypt | Yes | Global | No | ||
have_csv | Yes | Global | No | ||
have_dynamic_loading | Yes | Global | No | ||
have_example_engine | Yes | Global | No | ||
have_federated_engine | Yes | Global | No | ||
have_geometry | Yes | Global | No | ||
have_innodb | Yes | Global | No | ||
have_isam | Yes | Global | No | ||
have_merge_engine | Yes | Global | No | ||
have_ndbcluster | Yes | Global | No | ||
have_openssl | Yes | Global | No | ||
have_partitioning | Yes | Global | No | ||
have_query_cache | Yes | Global | No | ||
have_raid | Yes | Global | No | ||
have_row_based_replication | Yes | Global | No | ||
have_rtree_keys | Yes | Global | No | ||
have_ssl | Yes | Global | No | ||
have_symlink | Yes | Global | No | ||
hostname | Yes | Global | No | ||
identity | Yes | Session | Yes | ||
ignore_builtin_innodb | Yes | Yes | Yes | Global | No |
init_connect | Yes | Yes | Yes | Global | Yes |
init-file | Yes | Yes | No | ||
– Variable: init_file | Yes | Global | No | ||
init_slave | Yes | Yes | Yes | Global | Yes |
innodb_adaptive_flushing | Yes | Yes | Yes | Global | Yes |
innodb_adaptive_hash_index | Yes | Yes | Yes | Global | No |
innodb_additional_mem_pool_size | Yes | Yes | Yes | Global | No |
innodb_autoextend_increment | Yes | Yes | Yes | Global | Yes |
innodb_autoinc_lock_mode | Yes | Yes | Yes | Global | No |
innodb_buffer_pool_awe_mem_mb | Yes | Yes | Yes | Global | No |
innodb_buffer_pool_size | Yes | Yes | Yes | Global | No |
innodb_change_buffering | Yes | Yes | Yes | Global | Yes |
innodb_checksums | Yes | Yes | Yes | Global | No |
innodb_commit_concurrency | Yes | Yes | Yes | Global | Yes |
innodb_concurrency_tickets | Yes | Yes | Yes | Global | Yes |
innodb_data_file_path | Yes | Yes | Yes | Global | No |
innodb_data_home_dir | Yes | Yes | Yes | Global | No |
innodb_doublewrite | Yes | Yes | Yes | Global | No |
innodb_fast_shutdown | Yes | Yes | Yes | Global | Yes |
innodb_file_format | Yes | Yes | Yes | Global | Yes |
innodb_file_format_check | Yes | Yes | Yes | Global | Yes |
innodb_file_io_threads | Yes | Yes | Yes | Global | No |
innodb_file_per_table | Yes | Yes | Yes | Global | No |
innodb_flush_log_at_trx_commit | Yes | Yes | Yes | Global | Yes |
innodb_flush_method | Yes | Yes | Yes | Global | No |
innodb_force_recovery | Yes | Yes | Yes | Global | No |
innodb_io_capacity | Yes | Yes | Yes | Global | No |
innodb_lock_wait_timeout | Yes | Yes | Yes | Both | Yes |
innodb_locks_unsafe_for_binlog | Yes | Yes | Yes | Global | No |
innodb_log_arch_dir | Yes | Yes | Yes | Global | No |
innodb_log_archive | Yes | Yes | Yes | Global | No |
innodb_log_buffer_size | Yes | Yes | Yes | Global | No |
innodb_log_file_size | Yes | Yes | Yes | Global | No |
innodb_log_files_in_group | Yes | Yes | Yes | Global | No |
innodb_log_group_home_dir | Yes | Yes | Yes | Global | No |
innodb_max_dirty_pages_pct | Yes | Yes | Yes | Global | Yes |
innodb_max_purge_lag | Yes | Yes | Yes | Global | Yes |
innodb_mirrored_log_groups | Yes | Yes | Yes | Global | No |
innodb_old_blocks_pct | Yes | Yes | Yes | Global | Yes |
innodb_old_blocks_time | Yes | Yes | Yes | Global | Yes |
innodb_open_files | Yes | Yes | Yes | Global | No |
innodb_read_ahead_threshold | Yes | Yes | Yes | Global | Yes |
innodb_read_io_threads | Yes | Yes | Yes | Global | No |
innodb_replication_delay | Yes | Yes | Yes | Global | Yes |
innodb_rollback_on_timeout | Yes | Yes | Yes | Global | No |
innodb_spin_wait_delay | Yes | Yes | Yes | Global | Yes |
innodb_stats_on_metadata | Yes | Yes | Yes | Global | Yes |
innodb_stats_sample_pages | Yes | Yes | Yes | Global | Yes |
innodb_strict_mode | Yes | Yes | Yes | Both | Yes |
innodb_support_xa | Yes | Yes | Yes | Both | Yes |
innodb_sync_spin_loops | Yes | Yes | Yes | Global | Yes |
innodb_table_locks | Yes | Yes | Yes | Both | Yes |
innodb_thread_concurrency | Yes | Yes | Yes | Global | Yes |
innodb_thread_sleep_delay | Yes | Yes | Yes | Global | Yes |
innodb_use_legacy_cardinality_algorithm | Yes | Yes | Yes | Global | Yes |
innodb_use_sys_malloc | Yes | Yes | Yes | Global | No |
innodb_version | Yes | Global | No | ||
innodb_write_io_threads | Yes | Yes | Yes | Global | No |
insert_id | Yes | Session | Yes | ||
interactive_timeout | Yes | Yes | Yes | Both | Yes |
join_buffer_size | Yes | Yes | Yes | Both | Yes |
keep_files_on_create | Yes | Yes | Yes | Both | Yes |
key_buffer_size | Yes | Yes | Yes | Global | Yes |
key_cache_age_threshold | Yes | Yes | Yes | Global | Yes |
key_cache_block_size | Yes | Yes | Yes | Global | Yes |
key_cache_division_limit | Yes | Yes | Yes | Global | Yes |
language | Yes | Yes | Yes | Global | No |
large_files_support | Yes | Global | No | ||
large_page_size | Yes | Global | No | ||
large-pages | Yes | Yes | No | ||
– Variable: large_pages | Yes | Global | No | ||
last_insert_id | Yes | Session | Yes | ||
lc_time_names | Yes | Both | Yes | ||
license | Yes | Global | No | ||
local_infile | Yes | Global | Yes | ||
locked_in_memory | Yes | Global | No | ||
log | Yes | Yes | Yes | Global | Yes |
log_bin | Yes | Global | No | ||
log-bin | Yes | Yes | Yes | Global | No |
log-bin-trust-function-creators | Yes | Yes | Yes | ||
– Variable: log_bin_trust_function_creators | Yes | Global | Yes | ||
log-bin-trust-routine-creators | Yes | Yes | Yes | ||
– Variable: log_bin_trust_routine_creators | Yes | Global | Yes | ||
log-error | Yes | Yes | No | ||
– Variable: log_error | Yes | Global | No | ||
log-output | Yes | Yes | Yes | ||
– Variable: log_output | Yes | Global | Yes | ||
log-queries-not-using-indexes | Yes | Yes | Yes | ||
– Variable: log_queries_not_using_indexes | Yes | Global | Yes | ||
log-slave-updates | Yes | Yes | No | ||
– Variable: log_slave_updates | Yes | Global | No | ||
log-slow-queries | Yes | Yes | Yes | ||
– Variable: log_slow_queries | Yes | Global | Yes | ||
log-warnings | Yes | Yes | Yes | ||
– Variable: log_warnings | Yes | Both | Yes | ||
long_query_time | Yes | Yes | Yes | Both | Yes |
low-priority-updates | Yes | Yes | Yes | ||
– Variable: low_priority_updates | Yes | Both | Yes | ||
lower_case_file_system | Yes | Yes | Yes | Global | No |
lower_case_table_names | Yes | Yes | Yes | Global | No |
master-bind | Yes | Yes | Yes | No | |
max_allowed_packet | Yes | Yes | Yes | Global | Yes |
max_binlog_cache_size | Yes | Yes | Yes | Global | Yes |
max_binlog_size | Yes | Yes | Yes | Global | Yes |
max_connect_errors | Yes | Yes | Yes | Global | Yes |
max_connections | Yes | Yes | Yes | Global | Yes |
max_delayed_threads | Yes | Yes | Yes | Both | Yes |
max_error_count | Yes | Yes | Yes | Both | Yes |
max_heap_table_size | Yes | Yes | Yes | Both | Yes |
max_insert_delayed_threads | Yes | Both | Yes | ||
max_join_size | Yes | Yes | Yes | Both | Yes |
max_length_for_sort_data | Yes | Yes | Yes | Both | Yes |
max_prepared_stmt_count | Yes | Yes | Yes | Global | Yes |
max_relay_log_size | Yes | Yes | Yes | Global | Yes |
max_seeks_for_key | Yes | Yes | Yes | Both | Yes |
max_sort_length | Yes | Yes | Yes | Both | Yes |
max_sp_recursion_depth | Yes | Yes | Yes | Both | Yes |
max_tmp_tables | Yes | Yes | Yes | Both | Yes |
max_user_connections | Yes | Yes | Yes | Both | Yes |
max_write_lock_count | Yes | Yes | Yes | Global | Yes |
memlock | Yes | Yes | Yes | Global | No |
min-examined-row-limit | Yes | Yes | Yes | Both | Yes |
multi_range_count | Yes | Yes | Yes | Both | Yes |
myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
myisam_mmap_size | Yes | Yes | Yes | Global | No |
myisam_recover_options | Yes | Global | No | ||
myisam_repair_threads | Yes | Yes | Yes | Both | Yes |
myisam_sort_buffer_size | Yes | Yes | Yes | Both | Yes |
myisam_stats_method | Yes | Yes | Yes | Both | Yes |
myisam_use_mmap | Yes | Yes | Yes | Global | Yes |
named_pipe | Yes | Global | No | ||
ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
ndb-batch-size | Yes | Yes | Yes | Global | No |
ndb_cache_check_time | Yes | Yes | Yes | Global | Yes |
ndb_extra_logging | Yes | Yes | Yes | Global | Yes |
ndb_force_send | Yes | Yes | Yes | Both | Yes |
ndb_log_bin | Yes | Yes | Both | Yes | |
ndb_log_binlog_index | Yes | Yes | Global | Yes | |
ndb_log_empty_epochs | Yes | Yes | Yes | Global | Yes |
ndb_log_orig | Yes | Global | No | ||
ndb-log-update-as-write | Yes | Yes | Yes | Global | Yes |
ndb_log_updated_only | Yes | Yes | Yes | Global | Yes |
ndb_optimization_delay | Yes | Global | Yes | ||
ndb_table_no_logging | Yes | Session | Yes | ||
ndb_table_temporary | Yes | Session | Yes | ||
ndb_use_copying_alter_table | Yes | Both | No | ||
ndb_use_exact_count | Yes | Both | Yes | ||
ndb_use_transactions | Yes | Yes | Yes | Both | Yes |
ndb_wait_connected | Yes | Yes | Yes | No | |
ndb-wait-setup | Yes | Yes | Yes | No | |
ndbinfo_database | Yes | No | |||
ndbinfo_max_bytes | Yes | Yes | Both | Yes | |
ndbinfo_max_rows | Yes | Yes | Both | Yes | |
ndbinfo_show_hidden | Yes | Yes | Both | Yes | |
ndbinfo_table_prefix | Yes | Yes | Both | Yes | |
ndbinfo_version | Yes | Global | No | ||
net_buffer_length | Yes | Yes | Yes | Both | Yes |
net_read_timeout | Yes | Yes | Yes | Both | Yes |
net_retry_count | Yes | Yes | Yes | Both | Yes |
net_write_timeout | Yes | Yes | Yes | Both | Yes |
new | Yes | Yes | Yes | Both | Yes |
old | Yes | Yes | Yes | Global | No |
old-alter-table | Yes | Yes | Yes | ||
– Variable: old_alter_table | Yes | Both | Yes | ||
old-passwords | Yes | Yes | Yes | ||
– Variable: old_passwords | Yes | Both | Yes | ||
open-files-limit | Yes | Yes | No | ||
– Variable: open_files_limit | Yes | Global | No | ||
optimizer_prune_level | Yes | Yes | Yes | Both | Yes |
optimizer_search_depth | Yes | Yes | Yes | Both | Yes |
optimizer_switch | Yes | Yes | Yes | Both | Yes |
partition | Yes | Yes | No | ||
– Variable: have_partitioning | Yes | Global | No | ||
pid-file | Yes | Yes | No | ||
– Variable: pid_file | Yes | Global | No | ||
plugin_dir | Yes | Yes | Yes | Global | No |
port | Yes | Yes | Yes | Global | No |
preload_buffer_size | Yes | Yes | Yes | Both | Yes |
prepared_stmt_count | Yes | Global | No | ||
profiling | Yes | Session | Yes | ||
profiling_history_size | Yes | Both | Yes | ||
protocol_version | Yes | Global | No | ||
pseudo_thread_id | Yes | Session | Yes | ||
query_alloc_block_size | Yes | Yes | Yes | Both | Yes |
query_cache_limit | Yes | Yes | Yes | Global | Yes |
query_cache_min_res_unit | Yes | Yes | Yes | Global | Yes |
query_cache_size | Yes | Yes | Yes | Global | Yes |
query_cache_type | Yes | Yes | Yes | Both | Yes |
query_cache_wlock_invalidate | Yes | Yes | Yes | Both | Yes |
query_prealloc_size | Yes | Yes | Yes | Both | Yes |
rand_seed1 | Yes | Session | Yes | ||
rand_seed2 | Yes | Session | Yes | ||
range_alloc_block_size | Yes | Yes | Yes | Both | Yes |
read_buffer_size | Yes | Yes | Yes | Both | Yes |
read_only | Yes | Yes | Yes | Global | Yes |
read_rnd_buffer_size | Yes | Yes | Yes | Both | Yes |
relay_log_purge | Yes | Yes | Yes | Global | Yes |
relay_log_space_limit | Yes | Yes | Yes | Global | No |
report-host | Yes | Yes | No | ||
– Variable: report_host | Yes | Global | No | ||
report-password | Yes | Yes | No | ||
– Variable: report_password | Yes | Global | No | ||
report-port | Yes | Yes | No | ||
– Variable: report_port | Yes | Global | No | ||
report-user | Yes | Yes | No | ||
– Variable: report_user | Yes | Global | No | ||
rpl_recovery_rank | Yes | Global | Yes | ||
safe-show-database | Yes | Yes | Yes | Global | Yes |
secure-auth | Yes | Yes | Yes | ||
– Variable: secure_auth | Yes | Global | Yes | ||
secure-file-priv | Yes | Yes | No | ||
– Variable: secure_file_priv | Yes | Global | No | ||
server-id | Yes | Yes | Yes | ||
– Variable: server_id | Yes | Global | Yes | ||
shared_memory | Yes | Global | No | ||
shared_memory_base_name | Yes | Global | No | ||
skip-external-locking | Yes | Yes | No | ||
– Variable: skip_external_locking | Yes | Global | No | ||
skip-name-resolve | Yes | Yes | Yes | Global | No |
skip-networking | Yes | Yes | No | ||
– Variable: skip_networking | Yes | Global | No | ||
skip-show-database | Yes | Yes | No | ||
– Variable: skip_show_database | Yes | Global | No | ||
slave-allow-batching | Yes | Yes | Yes | ||
– Variable: slave_allow_batching | Yes | Global | Yes | ||
slave_compressed_protocol | Yes | Yes | Yes | Global | Yes |
slave_exec_mode | Yes | Global | Yes | ||
slave-load-tmpdir | Yes | Yes | No | ||
– Variable: slave_load_tmpdir | Yes | Global | No | ||
slave-net-timeout | Yes | Yes | Yes | ||
– Variable: slave_net_timeout | Yes | Global | Yes | ||
slave-skip-errors | Yes | Yes | No | ||
– Variable: slave_skip_errors | Yes | Global | No | ||
slave_transaction_retries | Yes | Yes | Yes | Global | Yes |
slave_type_conversions | Yes | Yes | Yes | Global | No |
slow_launch_time | Yes | Yes | Yes | Global | Yes |
slow-query-log | Yes | Yes | Yes | ||
– Variable: slow_query_log | Yes | Global | Yes | ||
slow_query_log_file | Yes | Yes | Yes | Global | Yes |
socket | Yes | Yes | Yes | Global | No |
sort_buffer_size | Yes | Yes | Yes | Both | Yes |
sql_auto_is_null | Yes | Session | Yes | ||
sql_big_selects | Yes | Both | Yes | ||
sql_big_tables | Yes | Session | Yes | ||
sql_buffer_result | Yes | Session | Yes | ||
sql_log_bin | Yes | Session | Yes | ||
sql_log_off | Yes | Session | Yes | ||
sql_log_update | Yes | Session | Yes | ||
sql_low_priority_updates | Yes | Both | Yes | ||
sql_max_join_size | Yes | Both | Yes | ||
sql-mode | Yes | Yes | Yes | ||
– Variable: sql_mode | Yes | Both | Yes | ||
sql_notes | Yes | Session | Yes | ||
sql_quote_show_create | Yes | Session | Yes | ||
sql_safe_updates | Yes | Session | Yes | ||
sql_select_limit | Yes | Both | Yes | ||
sql_slave_skip_counter | Yes | Global | Yes | ||
sql_warnings | Yes | Session | Yes | ||
ssl-ca | Yes | Yes | No | ||
– Variable: ssl_ca | Yes | Global | No | ||
ssl-capath | Yes | Yes | No | ||
– Variable: ssl_capath | Yes | Global | No | ||
ssl-cert | Yes | Yes | No | ||
– Variable: ssl_cert | Yes | Global | No | ||
ssl-cipher | Yes | Yes | No | ||
– Variable: ssl_cipher | Yes | Global | No | ||
ssl-key | Yes | Yes | No | ||
– Variable: ssl_key | Yes | Global | No | ||
storage_engine | Yes | Both | Yes | ||
sync_binlog | Yes | Yes | Yes | Global | Yes |
sync_frm | Yes | Yes | Yes | Global | Yes |
system_time_zone | Yes | Global | No | ||
table_cache | Yes | Yes | Yes | Global | Yes |
table_definition_cache | Yes | Yes | Yes | Global | Yes |
table_lock_wait_timeout | Yes | Yes | Yes | Global | Yes |
table_open_cache | Yes | Yes | Yes | Global | Yes |
table_type | Yes | Both | Yes | ||
thread_cache_size | Yes | Yes | Yes | Global | Yes |
thread_concurrency | Yes | Yes | Yes | Global | No |
thread_handling | Yes | Yes | Yes | Global | No |
thread_stack | Yes | Yes | Yes | Global | No |
time_format | Yes | Yes | Yes | Both | No |
time_zone | Yes | Yes | Yes | Both | Yes |
timed_mutexes | Yes | Yes | Yes | Global | Yes |
timestamp | Yes | Session | Yes | ||
tmp_table_size | Yes | Yes | Yes | Both | Yes |
tmpdir | Yes | Yes | Yes | Global | No |
transaction_alloc_block_size | Yes | Yes | Yes | Both | Yes |
transaction_allow_batching | Yes | Session | Yes | ||
transaction_prealloc_size | Yes | Yes | Yes | Both | Yes |
tx_isolation | Yes | Both | Yes | ||
unique_checks | Yes | Session | Yes | ||
updatable_views_with_limit | Yes | Yes | Yes | Both | Yes |
version | Yes | Yes | Yes | Global | No |
version_comment | Yes | Global | No | ||
version_compile_machine | Yes | Global | No | ||
version_compile_os | Yes | Global | No | ||
wait_timeout | Yes | Yes | Yes | Both | Yes |
warning_count | Yes | Session | No | ||
[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:
- Section 5.1.5, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
- Section 5.1.5.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
- Information on tuning system variables can be found in Section 7.5.3, “Tuning Server Parameters”.
- Section 13.6.3, “
InnoDB
Startup Options and System Variables”, listsInnoDB
system variables. - Section 17.3.4.3, “MySQL Cluster System Variables”, lists system variables which are specific to MySQL Cluster.
- For information on server system variables specific to replication, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.
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, 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.
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.
-
autocommit
The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use
COMMIT
to accept a transaction orROLLBACK
to cancel it. By default, client connections begin withautocommit
set to 1. If you changeautocommit
mode from 0 to 1, MySQL performs an automaticCOMMIT
of any open transaction. Another way to begin a transaction is to use aSTART TRANSACTION
orBEGIN
statement. See Section 12.3.1, “START TRANSACTION
,COMMIT
, andROLLBACK
Syntax”. -
automatic_sp_privileges
Variable Name automatic_sp_privileges
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default TRUE
When this variable has a value of 1 (the default), the server automatically grants the
EXECUTE
andALTER ROUTINE
privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (TheALTER ROUTINE
privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. Ifautomatic_sp_privileges
is 0, the server does not automatically add or drop these privileges.The creator of a routine is the account used to execute the
CREATE
statement for it. This might not be the same as the account named as theDEFINER
in the routine definition.See also Section 19.2.2, “Stored Routines and MySQL Privileges”.
-
back_log
Command-Line Format --back_log=#
Config-File Format back_log
Option Sets Variable Yes, back_log
Variable Name back_log
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 50
Range 1-65535
The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The
back_log
value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time.In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix
listen()
system call should have more details. Check your OS documentation for the maximum value for this variable.back_log
cannot be set higher than your operating system limit. -
basedir
Command-Line Format --basedir=name
Config-File Format basedir
Option Sets Variable Yes, basedir
Variable Name basedir
Variable Scope Global Dynamic Variable No Permitted Values Type filename
The MySQL installation base directory. This variable can be set with the
--basedir
option. Relative path names for other variables usually are resolved relative to the base directory. -
big_tables
If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error
The table
does not occur fortbl_name
is fullSELECT
operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required.Note
SLEEP()
via MySQL :: MySQL 5.0 Reference Manual :: 16.4.1 Replication Features and Issues.